DBVerify can not deal with datafile which name spans two lines

In one case, we need to issue 'DBV' to check the block corruption of a 9.2.0.8 production database datafiles. However, there were two datafiles which names span two lines and dbv command failed on such filenames even with quotation marks. (of course multiple line filename is not desired and must be there due to carelessness).

We end up copying these two files with proper names, then run the DBV on the copied files.

Though it looks like a very small maintenance task, I tested the following procedure in order to feel more comfortable when fixing it in the maintenance windows.

Test Steps:
(1) create the bad file name - multiple line file name

SQL> create tablespace temp_tbs datafile '/tmp/tmp_ 
  2  data_01.dbf' size 10M;
 

Tablespace created.



bxxxxd2:/tmp [sxxxxxt2] $ ls -l tmp*
-rw-r-----   1 oracle   dba      10493952 Mar 30 19:59 tmp_
data_01.dbf

(2) shutdown database

(3) copy
cp "tmp_
data_01.dbf" tmp_data_01_good.dbf

Note: the double quotation mark is necessary.

(4) mount database


(5) rename

SQL> ALTER DATABASE RENAME FILE '/tmp/tmp_
data_01.dbf' to '/tmp/tmp_data_01_good.dbf';  2  
 
Database altered.
 

(6) open the database

SQL> alter database open;

Database altered.

(7) drop the temp_tbs

SQL> drop tablespace temp_tbs including contents and datafiles;

Tablespace dropped.

Get DDL of all tables and indexes in a Schema

DBMS_METADATA package can be conveniently used to get the DDL of all tables and indexes in a schema. The sample syntax is as follows:

set pagesize 0
set long 90000
set feedback off
set echo off 
spool ddl.txt 
SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name, 'MY_SCHEMA') || '/'
     FROM dba_tables u
  where owner='MY_SCHEMA';


SELECT DBMS_METADATA.GET_DDL('INDEX',u.index_name, 'MY_SCHEMA') || '/'
     FROM dba_INDEXES u
    where owner='MY_SCHEMA';
spool off;

Database system-wise slow related to adding datafiles to an ASSM tablespace

From about Feburay to March, one of our databases (Oracle 9.2.0.8) experienced system-wise slow from time to time, which can last about 1-2 hours. We typically see more than 100 active sessions during the slow period, whereas normaly active sessios are less than 30 during bussiness hours. Also we can find from v$session_wait that more than 100 sessions from application server user are waiting for such events as "log file sync", "direct path write(lob)" and "db file sequential read". From the statspack report, we confirmed that the "direct path write(lob)" did not appear in the top 5 timed events section before or after the problem period.

From application team, we know that there is a process that consists of three SQLs issued against a large table with a CLOB column:

(1) INSERT INTO TABLE_WITH_CLOB (MID,AID,OID,SEID,OT,MN,MT,MESSAGE,MP,MPT,AS,PBS)
VALUES(:1,:2,:3,:4,:5,:6,sysdate,EMPTY_CLOB(),:7,sysdate,:8,:9)

(2) SELECT MESSAGE FROM TABLE_WITH_CLOB WHERE MID = :1 FOR UPDATE

(3) Update the CLOB column: MESSAGE ( I don't know the exact sql for this)


During the problem period, the process from multiple sessions are extremly slow. What caused the slowness puzzled us for quite a long time. Initially, whenever the problem occurs, we start to analyze this table. But there are no solid evidences that this really helps. We even set up a cron job to analyze this table every day. One time, I observed that the problem was gone before the analyze job even finished, this indicated that the problem was not related to the optimizer stats about this table.

I was asked to establish more metrices to describe the problem. For example, if we can quantify the work load on this table, possibly we can tell the application team that the problem occurs is because the workload increases. So this is not controlled by our DBA team and we can feel less embarassed about unable to find the cause.

By interacting with Oracle support,I finally think that the file/tablespace I/O stats may be used to quantify the workload.

I used the following query to obtain the file I/O stats from statspack report data:
(Assume COL_CLOB_1 is the tablespace for the CLOB data in the questioned table, COL_CLOB_1_n.dbf is the datafile name typically)

column tsname format a25
column filename format a25
column read_tim format  999,999
column write_tim format 999,999,999

select
   snap_time start_snap_tim,
--   old.tsname,
--   old.filename,
   new.phyrds-old.phyrds   phy_rds,
   new.phywrts-old.phywrts phy_wrts,
   new.readtim-old.readtim read_tim,
   new.writetim-old.writetim write_tim
from
   perfstat.stats$filestatxs old,
   perfstat.stats$filestatxs new,
   perfstat.stats$snapshot   sn
where
   old.snap_id = sn.snap_id
and
   old.filename = new.filename
and
   new.snap_id = sn.snap_id+1
-- and (new.phyrds-old.phyrds) < 0
and
   old.tsname = 'COL_CLOB_LOB_1'
   and old.filename like '%_1_4.dbf'
and to_char(snap_time, 'YYYY-MM-DD') = '&date_char'
order by 1, 2
;

Soon I have discovered that the write_tim value is execeptionally high during the problem period, for example, on Mar 17 from
15:10 to 16:00.

>>>> datafile '/db2/u06/oradata/dbprd/COL_CLOB_1_04.dbf' I/O stats

 START_SNAP_TIM         PHY_RDS   PHY_WRTS READ_TIM    WRITE_TIM
 ------------------- ---------- ---------- -------- ------------
 2008-03-17 13:40:02          7       3527       26       23,517
 2008-03-17 13:50:02         20       3481      124       23,237
 2008-03-17 14:00:04          5       3735        1       28,764
 2008-03-17 14:10:01          9       3730       26       26,216
 2008-03-17 14:20:03          7       3869        1       32,174
 2008-03-17 14:30:03          7       3887       19       30,551
 2008-03-17 14:40:02          7       3890       47       29,537
 2008-03-17 14:50:02         11       3775       33       29,188
 2008-03-17 15:00:04          6       3947       38       27,910
 2008-03-17 15:10:03          7       1294    1,000    2,619,551
 2008-03-17 15:20:10          6        935    1,006    3,076,456
 2008-03-17 15:30:30          2        924    1,491    2,486,059
 2008-03-17 15:40:35          2        846      151    2,762,804
 2008-03-17 15:50:17          1        906      308    2,682,676
 2008-03-17 16:00:46          9       2158      402    1,395,569
 2008-03-17 16:10:12         14      10507       12      144,262
 2008-03-17 16:20:05         16      10177       22       56,643
 2008-03-17 16:30:05         19       7908       86       77,863
 2008-03-17 16:40:03         11       3947        9       21,317
 2008-03-17 16:50:01          8       4079        7       23,791
 2008-03-17 17:00:04          8       3699       13       22,332

I've also foud out that every time we added new datafile to this tablespace (I was able to dig out this info from the alert log), we can see the "write_tim" spike. And every database slow incident we know did occur shortly after we added a new datafile to this tablespace. This tablespace grows very fast, about 10 GB per week. We (differnet DBAs whoever on-call) usually add 3-4GB datafile immediately once getting the space alert email. With this observation, I have suggested that we should only add datafile during weekend or weeknight, avoid doing so during the normal bussiness hours. Since then, we have never encountered the same slowness problem again.

In this case, though we are able to avoid the problem, we don't understant the root-cause fully. I suspect there are something to do with the ASSM tablespace feature. It seems there is a high overhead to intitialize a newly added datafile.

Monitoring Index Usage

1. Enable monitoring

ALTER INDEX [index_name] MONITORING USAGE;

2. View the infomation

col owner format a10
col index_name format a20
col monitoring format a10
col used format a6
 select
    u.name "owner",
    io.name "index_name",
--    t.name "table_name",
    decode(bitand(i.flags, 65536), 0, 'no', 'yes') "monitoring",
    decode(bitand(nvl(ou.flags,0), 1), 0, 'no', 'yes') "used",
    ou.start_monitoring "start_monitoring",
    ou.end_monitoring "end_monitoring"
 from
    sys.obj$ io,
    sys.obj$ t,
    sys.ind$ i,
    sys.object_usage ou,
    sys.user$  u
 where
    t.obj# = i.bo#
 and
    io.owner# = u.user#
 and
    io.obj# = i.obj#
 and
    u.name not in ('sys','system')
 and
    i.obj# = ou.obj#(+)
 and t.name='TAB';

Note: v$object_usage has to be queried by connecting as specific schema user.
The above query can show info for indexes under different schema.

3. Check whether the index is monitorred or not

SELECT index_name,
       table_name,
       monitoring,
       used,
       start_monitoring,
       end_monitoring
FROM   v$object_usage
WHERE  index_name = [index_name]
 and   table_name= [table_name];

Speed up the optimizer analyze job in a replicated environment

In an application, the production database is replicated to a reporting database and a DR database through SharePlex replication technology. The analyze jobs for the optimizer stats run on three instances bi-weekly. The problem is that the analyze job takes as long as 24 hours in the reporting database, which impact other jobs seriously. I have proposed a new procedure in order to reduce the time needed for the analyze job and potentially allow collectng more accurate optimizer stats within the time window available. The basic idea is to distribute the job among three instances and then combine the results, kind of divide-and-conquer.

Below I describe the steps. And overall time to obtain the stats was about 7-8 hours from the first time implementation, which involvs some manual steps. I think it is possible to automate the whole procedure if needed.


Steps:


1. Backup the current stats in PROD, RPT and DR instances:

1.1 create_stat_table

begin
DBMS_STATS.CREATE_STAT_TABLE (
'DB_ADMIN',
'MYSCHMEA_STATS_052508'
);
end;
/

1.2 export_schema_stats

begin
DBMS_STATS.EXPORT_SCHEMA_STATS (
'MYSCHEMA',
'MYSCHEMA_STATS_052508' ,
NULL,
'DB_ADMIN');
end;
/

2. Analyze group A table stats in PRD

Note: all tables that under MYSCHEMA are divided into two groups: group A and group B

It took about 4.5 hrs

3. Analyze group B table stats in DR

It took about 4.5 hrs

4. Analyze additional index stats in rpt

Note: Some indexes only exist in the reporting database

It took about 5.5 hrs

5. Sync up stats among three database

5.1 In PRD: export group A table/index stats

a. create stats table

begin
DBMS_STATS.CREATE_STAT_TABLE (
'DB_ADMIN',
'TAB_A_STATS',
NULL);
end;
/

b. run script: exp_tab_a_stats.sql



5.2 In DR: export group B table/index stats

begin
DBMS_STATS.CREATE_STAT_TABLE (
'DB_ADMIN',
'TAB_B_STATS',
NULL);
end;
/

script: exp_tab_b_stats.sql

5.3 In PRD: import group B table/index stats
a.
create table db_admin.tab_b_stats as
select * from db_admin.tab_b_stats@DB_DR;

b.
run script: imp_tab_b_stats.sql

Verify:

select table_name, last_analyzed from dba_tables where owner='MYSCHEMA' and last_analyzed < sysdate -1;

select index_name, last_analyzed from dba_indexes where owner='MYSCHEMA' and last_analyzed < sysdate -1;

5.4 RRT: import group A table/index stats


create table db_admin.tab_b_stats
as select * from db_admin.tab_b_stats@DB_DR;

create table db_admin.tab_a_stats
as select * from db_admin.tab_a_stats@DB_PRD;

script: imp_tab_a_stats.sql

5.5 Rpt: import group B table/index stats
script: imp_tab_b_stats.sql

5.6 Dr: import group A table/index stats
create table db_admin.tab_a_stats
as select * from db_admin.tab_a_stats@DB_PRD_lnk;

script: imp_tab_a_stats.sql


Note: to generate the imp/exp table stats scripts, I used the AWK script: for example:

# exp_stats.awk
{
   print "begin"
   print "  dbms_stats.export_table_stats("
   print "       'MYSCHEMA', "
   print "       '" $1 "',"
   print "       stattab => 'TAB_B_STATS' ,"
   print "       statown => 'DB_ADMIN' ,"
   print "       cascade => TRUE, "
   print "  );"
   print "end;"
   print "/"
   print " "
}


# imp_stats.awk
{
   print "begin"
   print "  dbms_stats.import_table_stats("
   print "       'MYSCHEMA', "
   print "       '" $1 "',"
   print "       stattab => 'TAB_B_STATS' ,"
   print "       statown => 'DB_ADMIN' ,"
   print "       cascade => TRUE "
   print "  );"
   print "end;"
   print "/"
   print " "
}

Clone a Production database for Disaster Recovery Purpose

I was engaged in a project months ago which was to create a DR database by cloning a terabyte production database from  the data center in CA to the data center in NY and set up  a one-way SharePlex replication from production to DR.

One of the major tasks of this project was to transfer the production database datafiles and archived logs to the DR site. Prior to the implementation window for the replication setup, it was required that the production and DR databases in sync.

To transfer the production database datafiles, we adopted the following procedure for each tablespace:

1. Put the tablespace in backup mode
alter tablespace [tablespace_name] begin backup

2. Transfer the datafiles that belong to the tablespace.
  We used compressed transfer method,  for example,at DR site:

ssh2 prod_hostname 'cat ${FNAME}| gzip -c' | gunzip -c > ${FILELOC}/${FNAME}

[Ed. Jul 26,2009, Check here for a SQL to generate the command ]

3. End the backup mode

alter tablespace [tablespace_name] end backup


All of the production database datafiles were transferred to the DR site one week before the implementation window. During the time of datafile transfer and the waiting period, we also transferred the archived logs contininusely , which would be needed to recover the database. It should be noted that the production database could generate about 30-50 GB archived logs each week day. At peak time, a 500M archived log can be generate at about every 6-7 min.

To automatically transfer the archived log, a cron job was set up at DR site, which check every 5 min whether there are new archived logs generated at production site; if it finds any, it starts the transfer.(Jan 13,2010 updated: it is also possible to use standby technology to achieve the same goal, see a post here) We have tested that it took about 5 min to do the transfer for one archived log (zip, transfer and unzip). The script for the cron job is shown as follows:

-------------------    transfer archived logs script ---------------------
#!/bin/sh
# Script: xfr_archlog.sh
#

SCRIPTLOC=/home/oracle/xfr_cron
ARCHLOGLOC=/db1arch/archive/dbdr

TSTAMP=`date +%a`

cd ${SCRIPTLOC}

#
# --- check if there are any archived logs file newer than
# --- the reference file saved in reffile.txt. Save any candidates to xfr_files.txt
#

NEWFILE=`cat reffile.txt`
echo 'find /db1arch/archive/dbprd -name "*.arc" -newer ' $NEWFILE > sshcommand.tmp
SSHCOMMAND=`cat sshcommand.tmp`
ssh prod_hostname $SSHCOMMAND > xfr_files.txt

COUNT=`grep -c "arc" xfr_files.txt`

#
# --- if there are new archived logs that need to be transferred, then
# --- update the xfr_files.txt
#

if [ $COUNT -ge 1 ]; then
echo "~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~" >> xfr_files_${TSTAMP}.log
echo "*** `date`" >> xfr_files_${TSTAMP}.log
echo "The following files need to be transfered:" >> xfr_files_${TSTAMP}.log
cat xfr_files.txt >> xfr_files_${TSTAMP}.log
tail -1 xfr_files.txt > reffile.txt
fi
# tail -10 xfr_files_${TSTAMP}.log

#
# --- if there are new archived logs that need to be transferred, then
# --- generate the script: xfr_files.sh
#

if [ $COUNT -ge 1 ]; then
echo "#!/bin/sh" > xfr_files.sh
cat xfr_files.txt while read FNAME
do
echo $FNAME
FNAMESHORT=`basename $FNAME`
echo "ssh2 prod_hostname 'cat ${FNAME}  gzip -c'  gunzip -c >${ARCHLOGLOC}/$FNAMESHORT &" >> xfr_files.sh
done

echo 'wait' >> xfr_files.sh
chmod ug+x xfr_files.sh
echo "files transfer started at `date` waiting for completion \n" tee -a xfr_files_${TSTAMP}.log
echo ">>> contents of xfr_files.sh" tee -a xfr_files_${TSTAMP}.log
cat xfr_files.sh tee -a xfr_files_${TSTAMP}.log
#
# --- execute the script
#

xfr_files.sh

fi

------------end of the transfer archived logs script ---------

Prior to the implementation, the DR database is either shutdown or in the media recovery mode.
To apply the archived log at DR site, we took the following basic steps:

1. Mount the database
2. Issue the recover database command as follows:
SQL> RECOVER database until cancel using backup controlfile;

--- sample screen output ----

SQL> recover database until cancel using backup controlfile;
ORA-00279: change 9977668133031 generated at 04/29/2008 16:21:51 needed for
thread 1
ORA-00289: suggestion : /db1arch/archive/dbdr/dbprd_1_78535.arc
ORA-00280: change 9977668133031 for thread 1 is in sequence #78535


Specify log: {=suggested  filename  AUTO  CANCEL}


ORA-00279: change 9977668512137 generated at 04/29/2008 16:32:01 needed for
thread 1
ORA-00289: suggestion : /db1arch/archive/dbdr/dbprd_1_78536.arc
ORA-00280: change 9977668512137 for thread 1 is in sequence #78536
ORA-00278: log file '/db1arch/archive/dbdr/dbprd_1_78535.arc' no longer
needed for this recovery


Specify log: {=suggested  filename  AUTO  CANCEL}

--- end of sample screen output ----

3. We can choose 'AUTO' to apply all of the available logs if appropriate

4. Shutdown the database.

5. Prior to the implementation windows, make sure the db is in sync

6. Open the database
SQL> alter database open resetlogs.

We have encountered some situations during the meida revovery stage:

1. A new datafile was added to a tablespace in the production database, and this tablespace was also in the DR database.

In this case, during media recovery, an unnamed file was generated, we had to rename it. For example:

We may see similar error as follows:

-------

ORA-00279: change 9977688828260 generated at 04/30/2008 03:12:30 needed for
thread 1
ORA-00289: suggestion : /db1arch/archive/dbdr/dbprd_1_78592.arc
ORA-00280: change 9977688828260 for thread 1 is in sequence #78592
ORA-00278: log file '/db1arch/archive/dbdr/dbprd_1_78591.arc' no longer
needed for this recovery


Specify log: {=suggested  filename  AUTO  CANCEL}
ORA-00283: recovery session canceled due to errors
ORA-01244: unnamed datafile(s) added to controlfile by media recovery
ORA-01110: data file 191: '/db1/u02/oradata/dbprd/DB_DATA_4_02.dbf'


ORA-01112: media recovery not started


SQL> RECOVER  database using backup controlfile until cancel;
ORA-00283: recovery session canceled due to errors
ORA-01111: name for data file 191 is unknown - rename to correct file
ORA-01110: data file 191: '/apps/opt/oracle/product/9.2.0/dbs/UNNAMED00191'
ORA-01157: cannot identify/lock data file 191 - see DBWR trace file
ORA-01111: name for data file 191 is unknown - rename to correct file
ORA-01110: data file 191: '/apps/opt/oracle/product/9.2.0/dbs/UNNAMED00191'
-------

To fix this problem, mount the database and issue the following command:

SQL> alter database create datafile
'/apps/opt/oracle/product/9.2.0/dbs/UNNAMED00191' as
'/db1/u02/oradata/dbdr/DB_DATA_4_02.dbf';

Note: we have different directory structure between production and DR site. Typically, production has .../dbprd/...; DR has .../dbdr/...

2. A new datafile was added to a tablespace in the production database, however this tablespace was not in the DR database.

Unfortunately I did't remember exactly how this issue was resolved. Probably, we renamed the unnamed file to something, then dropped it.

[Ed. Sep 11, 2009 Peform: mount db; alter database datafile_name drop offline; this should fix]


Note: There were some tablespaces not required in DR and thus their datafiles were not copied over.At first I had some concerns about that as I thought Oracle would throw errors during the recovery of the DR database  if it could not find the datafiles to apply the archived logs.  It turned out that Oracle just silently ignored those log entries.

There is a Metalink note (224274.1): "How To Make A Copy Of An Open Database For Duplication To A Different Machine", which served as a good reference for this task.

For setting up the SharePlex replication, fortunately we had a good document to follow, it completed without any surprises.

Identify Largest Physical I/O Producer from the TKPROF row source operations

The following is my reading notes from a post at the AskTom Site:

(http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1032431852141 )

Question
~~~~~~~~~
   The following SQL running ~ 9min, anyway to speed up?

SQL
~~~
update hiir p
   set ytd_anr = (SELECT SUM(h.value_or_amt)
                FROM    period_table pe,
                        mfg_dist_dlr_prod_history h
                WHERE   h.mfg_no                        = p.mfg_no
                AND     h.mfg_loc                       = p.mfg_loc_no
                AND     h.dist_no                       = p.dist_no
                AND     h.dist_loc                      = p.dist_loc_no
                AND     h.dlr_no                        = p.dlr_no
                AND     h.prod_code                     = p.prod_code
                AND     h.program_no                    = p.program_no
                AND     h.recv_branch_no                = p.recv_branch_no
                AND     h.rt_mfg_flag                   = p.rt_mfg_flag
                AND     h.rt_dist_flag                  = p.rt_dist_flag
                AND     h.rt_dlr_flag                   = p.rt_dlr_flag
                AND     h.period                        = pe.period
                AND     pe.year                         = :l_current_year
                AND     h.data_type                     = :l_data_type)
where rownum < 10000


SQL_TRACE and TKPROF
~~~~~~~~~~~~~~~~~~~~

Notes: issuse the following:  
       alter system set statistics_level=all
      to get the addtional info in the "Row Source Operation" section of the TKPROF

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          4          0           0
Execute      1     98.06     541.14     735758    1454850      30986        9999
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2     98.06     541.14     735758    1454854      30986        9999


Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 165

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  UPDATE  (cr=1454772 r=735739 w=0 time=541076424 us)
   9999   COUNT STOPKEY (cr=19766 r=3755 w=0 time=968199 us)
   9999    TABLE ACCESS FULL HIIR (cr=19766 r=3755 w=0 time=935182 us)
   9999   SORT AGGREGATE (cr=1434947 r=731923 w=0 time=537588304 us)
  19784    NESTED LOOPS  (cr=1434947 r=731923 w=0 time=537385558 us)
 290539     TABLE ACCESS BY INDEX ROWID MFG_DIST_DLR_PROD_HISTORY (cr=1144406 r=731922 w=0 time=532734557 us)
 290539      INDEX RANGE SCAN MDDP_PK (cr=872544 r=622899 w=0 time=183584801 us)(object id 40318)
  19784     TABLE ACCESS BY INDEX ROWID PERIOD_TABLE (cr=290541 r=1 w=0 time=3097982 us)
 290539      INDEX UNIQUE SCAN PERIOD_TABLE_PK (cr=2 r=0 w=0 time=1030705 us)(object id 40381)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  db file sequential read                    735709        0.12        458.79
  latch free                                      1        0.00          0.00
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1        0.00          0.00
********************************************************************************

Table basic info
~~~~~~~~~~~~~~~~~~

period_table              - 88 records
hiir                      - 30,000 records
mfg_dist_dlr_prod_history - 34 million records

Index
~~~~~

The unique key MDDP_PK is:

CREATE UNIQUE INDEX MDDP_PK ON MFG_DIST_DLR_PROD_HISTORY (
  MFG_NO, 
  MFG_LOC, 
  DIST_NO, 
  DIST_LOC, 
  DLR_NO, 
  PERIOD, 
  DATA_TYPE, 
  RECV_BRANCH_NO, 
  PROD_CODE, 
  PROGRAM_NO, 
  RT_MFG_FLAG, 
  RT_DIST_FLAG, 
  RT_DLR_FLAG
)
TABLESPACE IMAX_INDEX3
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          2048M
            NEXT             10M
            MINEXTENTS       1
            MAXEXTENTS       2147483645
            PCTINCREASE      0
            FREELISTS        1
            FREELIST GROUPS  1
            BUFFER_POOL      DEFAULT
           );




Analysis/Response
~~~~~~~~~~~~~~~~~

i) largest physical IO producer:

290539      INDEX RANGE SCAN MDDP_PK (cr=872544 r=622899 w=0 time=183584801 us)(object id 40318)

This step did 
- 872544 logical IOs
- 622899 physicaly IOs
- 0 writes (to temp for example...)


ii) look at the ORDERING of the columns in the index MDDP_PK  

 predicates:

                WHERE   h.mfg_no                        = p.mfg_no
                AND     h.mfg_loc                       = p.mfg_loc_no
                AND     h.dist_no                       = p.dist_no
                AND     h.dist_loc                      = p.dist_loc_no
                AND     h.dlr_no                        = p.dlr_no
                AND     h.prod_code                     = p.prod_code
                AND     h.program_no                    = p.program_no
                AND     h.recv_branch_no                = p.recv_branch_no
                AND     h.rt_mfg_flag                   = p.rt_mfg_flag
                AND     h.rt_dist_flag                  = p.rt_dist_flag
                AND     h.rt_dlr_flag                   = p.rt_dlr_flag

 Index column order:

  MFG_NO, 
  MFG_LOC, 
  DIST_NO, 
  DIST_LOC, 
  DLR_NO, 
  PERIOD,          -- not in predicates
  DATA_TYPE,       -- not in predicates
  RECV_BRANCH_NO, 
  PROD_CODE, 
  PROGRAM_NO, 
  RT_MFG_FLAG, 
  RT_DIST_FLAG, 
  RT_DLR_FLAG


If possible, rewrite it to  

  MFG_NO, 
  MFG_LOC, 
  DIST_NO, 
  DIST_LOC, 
  DLR_NO, 
  RECV_BRANCH_NO, 
  PROD_CODE, 
  PROGRAM_NO, 
  RT_MFG_FLAG, 
  RT_DIST_FLAG, 
  RT_DLR_FLAG
  PERIOD,          -- not in predicates
  DATA_TYPE,       -- not in predicates


If your key is (a,b,c,d,e)  and you do:

 where a = :a and b = :b and e = :e

we can range scan the index for all A and B values - looking for E's, if you had a key on 
(a,b,e,c,d) - we would go right to a,b,e and stop immediately - no big scan.

Privileges acquired through role

The following example demonstrated that the privileges acquired through role are not honored in PL/SQL procedures (also functions, triggers)


'dennis' has DBA role, however, it has problem to create a procedure, which uses dbms_lock package


dennis@TESTDB11> create or replace procedure t1_load(i_tag varchar2) as
2 m_date date;
3 begin
4 dbms_lock.sleep(0.01);
5 end;
6 /

Warning: Procedure created with compilation errors.

dennis@TESTDB11> show error;
Errors for PROCEDURE T1_LOAD:

LINE/COL ERROR
-------- -----------------------------------------------------------------
4/8 PL/SQL: Statement ignored
4/8 PLS-00201: identifier 'DBMS_LOCK' must be declared


'dennis' auctually does not have any problems to use dbms_lock package, if it is not used in the procedure:

dennis@TESTDB11> begin
2 dbms_lock.sleep(0.01);
3 end;
4 /

PL/SQL procedure successfully completed.


After explicitly grant the execute on dbms_lock to dennis, problem solved:

sys@TESTDB11> grant execute on dbms_lock to dennis;

Grant succeeded.


dennis@TESTDB11> create or replace procedure t1_load(i_tag varchar2) as
2 m_date date;
3 begin
4 dbms_lock.sleep(0.01);
5 end;
6 /

Procedure created.

Resolve an issue of duplicated rows in a Shareplex replication environment

Table SMC is one of the tables that are replicated from the production database to the reporting database. In production, id is the PK column and there is also a unique constraint (UK) on the columns (oid,eid,mid,st) of the table.

The problem of duplicated rows was identified in terms of there are mutiple rows having same (oid, eid, mid,st) in the SMC table in the reporting database.

The reason for the existing of the duplicated rows may be as follows:
(1) The PK and UK constraints have not been enabled
(2) In the reporting database, single post has been changed to multiple post queue.

Since there is no testing environment available, the actual reason for the problem has not been able to be confirmed by repeating the issue. However, it is obviously wrong that the PK and UK are not enabled in the SMC table in reporting database.

To fix this problem, I have to identify and remove the duplicated rows and then enable the PK and UK.

First of all, by comparing the row counts of the table in production and reporting using the following query, it can be known that the duplication only occurred during one day. ( Since the table is huge: > 100 GB, it is not wise to try to find the duplicated rows in all date range)

select count(*) from sf.smc
where
mc_date >= to_date('2008-05-21 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
and
mc_date <= to_date('2008-05-21 23:59:59', 'YYYY-MM-DD HH24:MI:SS')
/

Then, I created a temporary table from the SMC table in the reporting database as follows:

create table temp_a  nologging
as 
select 
id, oid, eid, mid, st 
FROM
sf.smc
where 
mc_date > to_date('2008-05-21 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
and
mc_date > to_date('2008-05-21 23:59:59', 'YYYY-MM-DD HH24:MI:SS')
/

execute dbms_stats.gather_table_stats(user, 'TEMP_A', null,estimate_percent=>10);

The duplicated rows as well as the baseline row ( i.e. the row to which any other rows compared in order to determine if they are duplicated )are displayed through the following sql:

select id,  oid, eid, st, mid from smc
where (oid, eid, st, mid ) in (
select  oid, eid, st, mid
FROM
temp_a A
WHERE
rowid >
(SELECT min(rowid) FROM temp_a B
WHERE
B.oid = A.oid
and
B.eid = A.eid
and
B.mid = A.mid
and B.st = A.st
)
)
order by 2,3,4,5

The following are the duplicate rows in RPT

ID      OID             EID                   ST              MID
---------- -------------------- -------------------- ----------- ----------
1037693787 CICN087494895        489740124            Change           8
1036151673 CICN087494895        489740124            Change           8
1036151856 CICN087494953        430282795            Change           2
1036152154 CICN087494953        430282795            Change           2
1037621342 ICOG087494888        503141443            Install         19
1036151603 ICOG087494888        503141443            Install         19
1036151617 IICN087494988        503141881            Install          8
1036183677 IICN087494988        503141881            Install          8

8 rows selected.

Note the column ID is different, to decide which row should be removed, we need to know which ID is presented in the produciton database: The following are the corresponding row in the production

ID     OID                EID                   ST             MID
---------- -------------------- -------------------- ---------- ----------
1037693787 CICN087494895        489740124            Change           8
1036152154 CICN087494953        430282795            Change           2
1037621342 ICOG087494888        503141443            Install         19
1036183677 IICN087494988        503141881            Install          8

As a result, the rows with the following id will be deleted in the reporting database:

'1036151673'
'1036151856'
'1036151603'
'1036151617'


Note: schema, table and column names have been modified to hide any possible confidential information


updated: Mar 29, 10 - When dealing with duplicated rows issue, it is obvious we should be very clear about what the meaning of  'duplicate' for a particular table, all  columns value are same or just some columns values are same?. If we only consider PK columns, for example (col1, col2) are supposed to be PK, but the constraint are not enabled and we want to delete the duplicated rows in order to create the PK. We can do:

create table tab_tmp
as
select a.rowid, a.col1, a.col2
   from tab a
  where a.rowid >
     ( select max(rowid)
       from tab b
      where a.col1 = b.col1
         and a.col2=b.col2);

We maybe want to create index on (col1 col2) if not there  first , non-unique of course. Then we delete duplicated row by:

delete from tab a
where a.rowid in ( select rowid from tab_temp);


Updated: Jul 29, 2010 -

In this post: http://viralpatel.net/blogs/2010/06/deleting-duplicate-rows-in-oracle.html , several ways of deleting duplicated rows are summarized. One interesting method is using analytic function, i.e.

DELETE FROM tbl_test
 WHERE ROWID IN (
         SELECT rid
           FROM (SELECT ROWID rid,
                 ROW_NUMBER () OVER (PARTITION BY ser_no, fst_nm, deptid, cmnt ORDER BY ROWID) rn
                 FROM tbl_test)
        WHERE rn <>1);

A test case - Compare two methods to archive a table

In this test, our goal is to archive a table called archsim_smt under schema dennis. We delete the rows from this table and insert the rows into another table under dennis_arch schema. The row that is selected to be delete/insert is based on column order_id. Each order_id has 100 rows in the table archsim_smt. Order_id has a FK constraint with reference to the table archsim_ssd. Each master_order_id in the table archsim_ssd has 50 order_id. So when we input a particular master_order_id, we will end up archiving 5000 rows in the table archsim_smt.

The objects used in the test are set up by following script:

----- setup.sql ---------------

 
-- create two tables in dennis
create table dennis.archsim_ssd 
nologging
as
select
        rownum                  order_id,
        1+ mod(rownum,20)        master_order_id,
        rpad(rownum,10)         small_vc_ssd,
        rpad(rownum,&m_pad)     padding_ssd
from all_objects
where rownum <= 1000
;

/* one master_order_id has 50 order_id;
   one order_id has 100 id_smt;
*/
create table dennis.archsim_smt 
nologging
as
with generator as (
        select  --+ materialize
                rownum  id
        from    all_objects
        where   rownum <= 1000
)
select
        rownum                  id_smt,
        1+mod(rownum,1000)      order_id,
        trunc((rownum-1)/5)     small_num_smt,
        rpad(rownum,10)         small_vc_smt,
        rpad(rownum,&m_pad)     padding_smt
from 
        generator v1,
        generator v2
where rownum <= 100000
;

-- create two empty table in dennis_arch
create table dennis_arch.archsim_ssd
as select * from dennis.archsim_ssd
where 1=0;

create table dennis_arch.archsim_smt
as select * from dennis.archsim_smt
where 1=0;

create unique index dennis.archsim_ssd_pk on dennis.archsim_ssd(order_id); 
create unique index dennis.archsim_smt_pk on dennis.archsim_smt(id_smt); 
alter table dennis.archsim_ssd add constraint archsim_ssd_pk primary key(order_id);
alter table dennis.archsim_smt add constraint archsim_smt_pk primary key(id_smt);
alter table dennis.archsim_smt add constraint archsim_smt_fk foreign key(order_id) references dennis.archsim_ssd;

begin
        dbms_stats.gather_table_stats(
                user,
                'archsim_ssd',
                cascade => true,
                estimate_percent => null,
                method_opt => 'for all columns size 1'
        );
end;
/

begin
        dbms_stats.gather_table_stats(
                user,
                'archsim_smt',
                cascade => true,
                estimate_percent => null,
                method_opt => 'for all columns size 1'
        );
end;
/

This test was performed by executing a script: comp_test.sql, which is shown below.

-------------- Script: comp_test.sql -------------------

rem  comp_test.sql
rem
rem    Comparing two methods to archive a table archsim_smt in 
rem    the dennis schema.
rem
rem
rem    Two methods are different base on how to select the rows for archiving:
rem
rem    Method 1 - single SQL
rem
rem      insert into dennis_arch.archsim_smt
rem      select * from archsim_smt
rem      where order_id in (
rem          select order_id
rem            from archsim_ssd
rem           where master_order_id = 1 );
rem
rem     delete from dennis.archsim_smt
rem      where order_id in (
rem      select order_id
rem        from archsim_ssd
rem       where master_order_id = 1 );
rem
rem      commit;
rem
rem    Method 2 - PLSQL LOOP
rem     ( similar approach is used in the production database right now)
rem       declare
rem         inorderid number;
rem         cursor allorderids is
rem             select distinct order_id
rem              from dennis.archsim_ssd
rem             where  master_order_id = 1;
rem       begin
rem         open allorderids;
rem
rem         FETCH allorderids INTO inorderid;
rem
rem         WHILE allorderids % FOUND
rem         LOOP
rem           INSERT
rem           INTO dennis_arch.archsim_smt
rem           SELECT *
rem             FROM dennis.archsim_smt
rem            WHERE order_id = inorderid;
rem
rem           delete from dennis.archsim_smt
rem             where order_id = inorderid;
rem
rem           inorderid := NULL;
rem           FETCH allorderids INTO inorderid;
rem         END LOOP;
rem
rem         commit;
rem         close allorderids;
rem       end;
rem       /
rem
rem   A tool called Runstat is used to compare two methods. 
rem   
rem   Runstats is a tool developed by Tom Kyte to compare two 
rem   different methods of doing the same thing and show which
rem   one is superior. (http://asktom.oracle.com/tkyte/runstats.html)
rem
rem   Test was done in a 11 g database.
rem

spool comp_test

exec runstats_pkg.rs_start;

-- method 1

@@setup
@@method_1
select count(*) from dennis.archsim_smt;
select count(*) from dennis_arch.archsim_smt;

exec runstats_pkg.rs_middle;

-- method 2

@@setup
@@method_2
select count(*) from dennis.archsim_smt;
select count(*) from dennis_arch.archsim_smt;

exec runstats_pkg.rs_stop;

Results

Run1 ran in 1339 hsecs
Run2 ran in 3333 hsecs
run1 ran in 40.17% of the time

Name                                          Run1      Run2      Diff
STAT...write clones created in           0           1           1
LATCH.PL/SQL warning settings          123         122          -1
LATCH.cp sga latch                       1           0          -1
LATCH.ncodef allocation latch            1           0          -1
LATCH.threshold alerts latch             0           1           1
LATCH.begin backup scn array           301         300          -1
LATCH.dml lock allocation               13          12          -1
LATCH.sort extent pool                  60          59          -1
LATCH.deferred cleanup latch             1           0          -1

...

STAT...redo size                 7,364,552   7,400,712      36,160
LATCH.object queue header oper      33,375      70,180      36,805
LATCH.simulator lru latch            7,650      69,629      61,979
LATCH.simulator hash latch          13,851     125,011     111,160
STAT...consistent gets from ca      55,489     665,732     610,243
STAT...no work - consistent re      53,477     665,735     612,258
STAT...table scan blocks gotte      45,247     657,746     612,499
STAT...consistent gets from ca      60,627     674,250     613,623
STAT...consistent gets              60,627     674,250     613,623
STAT...session logical reads       100,356     714,181     613,825
LATCH.cache buffers chains         197,356     851,159     653,803
STAT...table scan rows gotten      726,518  10,526,397   9,799,879
STAT...physical read total byt 109,912,064 182,550,528  72,638,464
STAT...physical read bytes     109,502,464 182,140,928  72,638,464

Run1 latches total versus Run2 -- different and pct
Run1      Run2      Diff     Pct
391,297   1,279,691     888,394  30.58%

It can be seen from the the Runstat tool, the method 1 has more than 10 times less consistent gets than method 2 ( 60,627 vs 674,250); and also method 1 has much less latches than method 2 (391,297 vs 1,279,691 ). So it is clear that Method 1 is superior. As Tom Kyte said, if someting can be done in simple sql statements,it will be better than PL/SQL precedures generally.

Load failed due to an index partition in unusable state

In Feb, I helped the on-call DBA resolve an issue about index in unusable state. DBA got called by app team:

" One of our load got failed with the below error please look into this and let us know.
database [], user []

WRITER_1_*_1> WRT_8229 Database errors occurred:
ORA-01502: index 'DW_FIOS.IX01_FTV1_FACT_BL_DTL_PRD' or partition of such index is in unusable state
"
We checked the index/partions status by issue the following sql:

select index_name, partition_name, status
from dba_ind_partitions
where index_name='IX01_FTV1_FACT_BL_DTL_PRD';

We found that there was an index partition in unsuable state:

INDEX_NAME                     PARTITION_NAME                 STATUS
------------------------------ ------------------------------ --------
IX01_FTV1_FACT_BL_DTL_PRD      P20080131                      USABLE
IX01_FTV1_FACT_BL_DTL_PRD      P20080219                      USABLE
IX01_FTV1_FACT_BL_DTL_PRD      P20080220                      USABLE
IX01_FTV1_FACT_BL_DTL_PRD      P20080221                      USABLE
IX01_FTV1_FACT_BL_DTL_PRD      P20080222                      UNUSABLE
IX01_FTV1_FACT_BL_DTL_PRD      P20080223                      USABLE
IX01_FTV1_FACT_BL_DTL_PRD      P20080224                      USABLE
IX01_FTV1_FACT_BL_DTL_PRD      P20080225                      USABLE

We rebuild the index partition by issue:

alter index DW_FIOS.IX01_FTV1_FACT_BL_DTL_PRD rebuild partition P20080131 parallel 4 nologging;

After rebuild, we asked the app team re-load data and the job has completed without any problem.


I am not sure how this load data process was designed at the first place. In some case, we may want to rebuild the indexes after the load in order to speed up the load. This may be achived by:
1. Setting the index to unusable
2. At the session level: alter the session set skip_unusable_indexes = true;
3. Load the data
4. Rebuild the index



===
The following test is done in 11g, it seems we can insert into a table that has an index in ususable state:


dennis@TESTDB11> drop table t;

Table dropped.

dennis@TESTDB11> create table t(col1 number, col2 number);

Table created.

dennis@TESTDB11> insert into t values(1,2);

1 row created.

dennis@TESTDB11> insert into t values(2,3);

1 row created.

dennis@TESTDB11> insert into t values(4,5);

1 row created.

dennis@TESTDB11> create index t_idx1 on t(col1);

Index created.

dennis@TESTDB11> select index_name, status from user_indexes;

INDEX_NAME STATUS
------------------------------ --------
T_IDX1 VALID
...

dennis@TESTDB11> alter index t_idx1 unusable;

Index altered.

dennis@TESTDB11> select index_name, status from user_indexes;

INDEX_NAME STATUS
------------------------------ --------
T_IDX1 UNUSABLE

dennis@TESTDB11> insert into t values(4,5);

1 row created.

dennis@TESTDB11> commit;

Commit complete.

dennis@TESTDB11> insert into t values(6,5);

1 row created.

dennis@TESTDB11> select * from t;

COL1 COL2
---------- ----------
1 2
2 3
4 5
4 5
6 5

Test of Logon/logoff triggers

Test environment: Oracle 9.2.0.8

Steps:

1. Create a table to store access info

     create table db_admin.user_access
     (
    user_id           varchar2(30),
    os_user           varchar2(30),
    session_id           number(8),
    host              varchar2(30),
    last_program      varchar2(48),
    last_action       varchar2(32),
    last_module       varchar2(32),
    logon_day                 date,
    logon_time        varchar2(10),
    logoff_day                date,
    logoff_time       varchar2(10),
    elapsed_minutes       number(8)
     )
     ;

2. Create logon trigger

   create or replace trigger db_admin.logon_audit_trigger
   AFTER LOGON ON DATABASE 
   BEGIN
   insert into db_admin.user_access values(
      user,
      sys_context('USERENV','OS_USER'),
      sys_context('USERENV','SESSIONID'),
      sys_context('USERENV','HOST'),
      null,
      null,
      null,
      sysdate,
      to_char(sysdate, 'hh24:mi:ss'),
      null,
      null,
      null
   );
   END;
   /
 
3. Create logoff trigger

   create or replace trigger db_admin.logoff_audit_trigger
   BEFORE LOGOFF ON DATABASE
   BEGIN
   -- ***************************************************
   -- Update the last action accessed
   -- ***************************************************
   update
   user_access
   set
   last_action = (select action from v$session where     
   sys_context('USERENV','SESSIONID') = audsid)
   where
   sys_context('USERENV','SESSIONID') = session_id;
   --***************************************************
   -- Update the last program accessed
   -- ***************************************************
   update
     user_access
   set
   last_program = (select program from v$session where     
   sys_context('USERENV','SESSIONID') = audsid)
   where
   sys_context('USERENV','SESSIONID') = session_id;
   -- ***************************************************
   -- Update the last module accessed
   -- ***************************************************
   update
      user_access
   set
   last_module = (select module from v$session where     
   sys_context('USERENV','SESSIONID') = audsid)
   where
   sys_context('USERENV','SESSIONID') = session_id;
   -- ***************************************************
   -- Update the logoff day
   -- ***************************************************
   update
      user_access 
   set
      logoff_day = sysdate
   where
      sys_context('USERENV','SESSIONID') = session_id;
   -- ***************************************************
   -- Update the logoff time
   -- ***************************************************
   update
      user_access
   set
      logoff_time = to_char(sysdate, 'hh24:mi:ss')
   where
      sys_context('USERENV','SESSIONID') = session_id;
   -- ***************************************************
   -- Compute the elapsed minutes
   -- ***************************************************
   update
   user_access
   set
   elapsed_minutes =     
   round((logoff_day - logon_day)*1440)
   where
   sys_context('USERENV','SESSIONID') = session_id;
   END;
   /
 
4. Generate report:


SQL> ho cat get_rpt.sql
col db_user format a10
col os_user format a10
col host format a15
col logon_time format a25
col logoff_time format a25
set linesize  100
 
select
       user_id db_user,
       os_user,
       host,
       to_char(logon_day, 'YYYY-MM-DD HH24:MI:SS') logon_time,
       to_char(logoff_day, 'YYYY-MM-DD HH24:MI:SS') logoff_time
from db_admin.user_access
where user_id in ('SYS', 'SYSTEM')
/



Example:

DB_USER    OS_USER    HOST            LOGON_TIME                LOGOFF_TIME
---------- ---------- --------------- ------------------------- -------------------------
SYSTEM     oracle     bnscqsd2        2008-05-08 08:35:31       2008-05-08 08:35:39
SYS        oracle     bnscqsd2        2008-05-08 08:49:30
SYSTEM     oracle     bnscqsd2        2008-05-08 08:49:43       2008-05-08 08:49:47
SYS        oracle     bnscqsd2        2008-05-08 09:03:20
SYS        oracle     bnscqsd2        2008-05-08 09:16:58
SYS        oracle     bnscqsd2        2008-05-08 09:18:11
SYS        oracle     bnscqsd2        2008-05-08 09:29:44
SYSTEM     oracle     bnscqsd2        2008-05-08 09:30:18       2008-05-08 09:30:37
SYS        oracle     bnscqsd2        2008-05-08 09:30:37
SYS        oracle     bnscqsd2        2008-05-09 01:00:00
SYS        oracle     bnscqsd2        2008-05-10 01:00:00
SYS        oracle     bnscqsd2        2008-05-11 01:00:00
SYS        oracle     bnscqsd2        2008-05-12 00:00:01
SYS        oracle     bnscqsd2        2008-05-12 01:00:01
SYSTEM     oracle     bnscqsd2        2008-05-12 11:57:43       2008-05-12 11:57:54
SYSTEM     oracle     bnscqsd2        2008-05-12 11:58:13


Note: Logoff info about SYS user failed to get inserted .. 
      The record in the user_access table is updated according to the 'SESSIONID',
      however,  SESSIONID of the session created by 'SYS' always zero


SQL> show user
USER is "OPS$ORACLE"
SQL> select sys_context('USERENV','SESSIONID') from dual;    

SYS_CONTEXT('USERENV','SESSION
-------------------------------------------------------------
1140840
 
SQL> conn / as sysdba
Connected.
SQL> select sys_context('USERENV','SESSIONID') from dual;
 
SYS_CONTEXT('USERENV','SESSION
-------------------------------------------------------------
0
 
SQL> connect system
Enter password: 
Connected.
SQL> select sys_context('USERENV','SESSIONID') from dual;
 
SYS_CONTEXT('USERENV','SESSION
-------------------------------------------------------------
1140841
 
SQL> conn / as sysdba
Connected.
SQL> select sys_context('USERENV','SESSIONID') from dual;
 
SYS_CONTEXT('USERENV','SESSION
-------------------------------------------------------------
0
 
SQL> 

CBO is unable to pick up good execution plan

The problem ocurred in an Oracle 9.2.0.8 database. Many queries suddently run much longer from one day. What kind of changes have been made to the database is not obvious. Checking their execution plans, typically seeing the CBO choose hash join and full table scan for some large tables. By trial and error, the resolution to this problem is to disable hash join at the session level and add index hints. Typically by doing so, the queries can be executed in a few minutes instead of few hours. The execution plans before the problem occurrs have not been kown. It is evident that by collecting optimizer statistics more accuratly can help the CBO choose better plan. It has also been tested that setting the optimizer_index_cost_adj parameter to 10 or 20 can make the CBO choose better plans for some queries. It seems that CBO under-estimate the cost of full table scan.

Below is an example. The query is a relative simple one, which involves join of two large tables:

 select 'Pots,',count(distinct(od.master_order_id))
   from tableOD od,
        tableSD sd
  where
        sd.MASTER_ORDER_ID=od.MASTER_ORDER_ID
    and sd.SUB_ORDER_TYPE in (1,3,4)
    and sd.product_category in (2,3,17)
    and sd.SUPP_ORDER_ID=0
    and sd.service_type='Install'
    and od.RECEIVED_DATE between To_Date('05/20/08 00:00:00','MM/DD/YY HH24:MI:SS')
    and    To_Date('05/20/08 23:59:59','MM/DD/YY HH24:MI:SS')
    and (sd.SSPUNITY in ('N') or sd.SSPUNITY is null )
     and (sd.DRY_LOOP_IND in ('N') or  sd.DRY_LOOP_IND is null )
    and (sd.HARMONY_DATA_RECEIVED = 'N' or sd.HARMONY_DATA_RECEIVED is null )
   group by trunc(od.RECEIVED_DATE),
               sd.SSPUNITY,
               sd.HARMONY_DATA_RECEIVED,
               sd.DRY_LOOP_IND;

The execution plan obtained from v$sql_plan is shown as follows:

##### bad plan: if no index hint and not disable hash join

----------------------------------------------------------------------------------
 Id   Operation                        Name             Rows   Bytes  Cost (%CPU)
----------------------------------------------------------------------------------
   0  SELECT STATEMENT                                                        
   1   SORT GROUP BY                                     1042  63562    504K  (5)
   2    FILTER                                                                
   3     HASH JOIN                                       1042  63562    504K  (5)
   4      TABLE ACCESS FULL           TABLESD            1042  39596    443K  (6)
   5      TABLE ACCESS BY INDEX ROWID TABLEOD             103K  2314K 61262   (1)
   6       INDEX RANGE SCAN           ORD_DTL_RECV_DATE   185K          581   (1)
----------------------------------------------------------------------------------

The better execution plan is obtained by:
1. alter session set hash_join_enabled=false;
2. add: select /*+ INDEX(od ORD_DTL_RECV_DATE) INDEX(sd SSD_MASTER_ORDER_ID_NUK) */

Note we won't get the better plan if only adopting one of the above conditions.


##### good plan

-------------------------------------------------------------------------------------
 Id   Operation                         Name                  Rows   Bytes  Cost (%CPU)
----------------------------------------------------------------------------------------
   0  SELECT STATEMENT                                                                 
   1   SORT GROUP BY                                           1042  63562    576K  (1)
   2    FILTER                                                                         
   3     TABLE ACCESS BY INDEX ROWID   TABLESD                   1     38      6  (17)
   4      NESTED LOOPS                                         1042  63562    576K  (1)
   5       TABLE ACCESS BY INDEX ROWID TABLEOD                 103K  2314K 61262   (1)
   6        INDEX RANGE SCAN           ORD_DTL_RECV_DATE       185K          581   (1)
   7       INDEX RANGE SCAN            SSD_MASTER_ORDER_ID_NUK   2             4  (25)
-------------------------------------------------------------------------------

I also checked the execution plan after collecting more accurate statitics on the table
TABLESD:

exec dbms_stats.gather_table_stats('SSP_FLOW',
'TABLESD',
method_opt => 'FOR COLUMNS MASTER_ORDER_ID SIZE 1',
estimate_percent => 100, cascade =>true);

Note: this analyze job took more than 8 hours


The plan is still not optimal in this case:

--------------------------------------------------------------------------------
 Id   Operation                        Name     Rows   Bytes  Cost (%CPU)
--------------------------------------------------------------------------------
   0  SELECT STATEMENT                                                
   1   SORT GROUP BY                             1042  63562    420K  (6)
   2    FILTER                                                        
   3     NESTED LOOPS                            1042  63562    420K  (6)
   4      TABLE ACCESS FULL           TABLESD    1042  39596    416K  (6)
   5      TABLE ACCESS BY INDEX ROWID TABLEOD       1     23      5  (20)
   6       INDEX RANGE SCAN           TEMP_1        1             4  (25)
--------------------------------------------------------------------------------

How to Display HIGH_VALUE/LOW_VALUE Columns from USER_TAB_COL_STATISTICS

1. create a function as follows (copied from a website)

create or replace function display_raw (rawval raw, type varchar2)  
return varchar2  
is 
   cn     number;  
   cv     varchar2(32);  
   cd     date;  
   cnv    nvarchar2(32);  
   cr     rowid;  
   cc     char(32);  
begin 
   if (type = 'NUMBER') then 
      dbms_stats.convert_raw_value(rawval, cn);  
      return to_char(cn);  
   elsif (type = 'VARCHAR2') then 
      dbms_stats.convert_raw_value(rawval, cv);  
      return to_char(cv);  
   elsif (type = 'DATE') then 
      dbms_stats.convert_raw_value(rawval, cd);  
      return to_char(cd);  
   elsif (type = 'NVARCHAR2') then 
      dbms_stats.convert_raw_value(rawval, cnv);  
      return to_char(cnv);  
   elsif (type = 'ROWID') then 
      dbms_stats.convert_raw_value(rawval, cr);  
      return to_char(cnv);  
   elsif (type = 'CHAR') then 
      dbms_stats.convert_raw_value(rawval, cc);  
      return to_char(cc);  
   else 
      return 'UNKNOWN DATATYPE';  
   end if;  
end;  
/ 

2. Issue the following statement with table name and column name

col column_name format a20
col low_val format a20
col high_val format a20

select 
   a.column_name,  
   display_raw(a.low_value,b.data_type) as low_val,  
   display_raw(a.high_value,b.data_type) as high_val,  
   a.num_distinct
from 
   dba_tab_col_statistics a, dba_tab_cols b  
where 
      a.table_name='TABLE_NAME'
  and a.table_name=b.table_name
  and a.column_name=b.column_name  
  and  a.owner='MYOWNER'
  and b.owner='MYOWNER'
--  and a.column_name ='COL_NAME'
/ 

3. The output looks like:

COLUMN_NAME          LOW_VAL              HIGH_VAL             NUM_DISTINCT
-------------------- -------------------- -------------------- ------------
SSP_VERSION_CHANGE_F Y                    Y                               1
LAG

ONTTYPE              E                    VDSL-1                          5
ILEC_MON             0259843              UAIILGT075933613;          461775
ILEC_PON             DICN059366494        UAIMICN087898169           503658
IS_IVAAP_PROV_COMPLE N                    Y                               2
TE

EQPT_RETR_METHOD     DROPOFF              PICKUP                          3
EQPT_RETR_DATE       2006-10-24 00:00:00  2008-09-07 00:00:00           597
SERVICE_PROPERTIES   0                    32                             11
RELATED_VZUSER_ID    01love               zzzmyers                  1693499
APPROVER_ID          BAAIS15049           X2008591792643550           12475
GEOTAXCODE           0                    null                         6355
COUNTY               ACCOMACK             polk                         1555
WINBACK_IND          N                    Y                               2
LIST_OF_FIOS_TNS     3016170150           985601305141046165          207
                                          83

IS_NOS_ROUTE         N                    Y                               2
RESERVATION_ID       25050                baais28446                   2327
IS_PREFAC            N                    Y                               2
VACATION_START_DATE  2005-05-01 00:00:00  2008-06-22 00:00:00          1038
VACATION_END_DATE    2005-06-01 00:00:00  2009-03-02 00:00:00          1267
UNEP_INDICATOR       N                    Y                               2
VLEC_ID              0127                 ZTK                           194
SUPP_ACTION_IND      SUPP-CHANGE          SUPP-UNCHANGED                  3
IS_VZ1_MODEM         N                    Y                               2
DISP_CODE                                                                 0
DRY_LOOP_IND         N                    Y                               2

(......)

Execution plan does not match between explain time and execution time

Some time ago, I have observed that some sqls used bad execution plan during run time, which are different from the execution plan that we obtained by autotrace or explain plan statement. Here is an example (the environment: 9.2.0.8):

The sql is as follows: (changed the table name for hiding confidential information)

select 'Pots,',count(distinct(od.master_order_id))
 from tableOD od,
      tableSD sd
where
      sd.MASTER_ORDER_ID=od.MASTER_ORDER_ID
  and sd.SUB_ORDER_TYPE in (1,3,4)
  and sd.product_category in (2,3,17)
  and sd.SUPP_ORDER_ID=0
  and sd.service_type='Install'
  and od.RECEIVED_DATE between To_Date('05/20/08  00:00:00','MM/DD/YY HH24:MI:SS')
  and To_Date('05/20/08  23:59:59','MM/DD/YY HH24:MI:SS')
  and (sd.SSPUNITY in ('N') or sd.SSPUNITY is null )
  and (sd.DRY_LOOP_IND in ('N') or  sd.DRY_LOOP_IND is null )
  and (sd.HARMONY_DATA_RECEIVED = 'N' or sd.HARMONY_DATA_RECEIVED is null )
group by trunc(od.RECEIVED_DATE),sd.SSPUNITY,sd.HARMONY_DATA_RECEIVED,sd.DRY_LOOP_IND;

The explain plan obtained from explain plan statment is:

-------------------------------------------------------------------------------------------------
 Id   Operation                         Name                     Rows   Bytes  Cost (%CPU)
-------------------------------------------------------------------------------------------------
   0  SELECT STATEMENT                                               2    122     14  (15)
   1   SORT GROUP BY                                                 2    122     14  (15)
*  2    FILTER                                                                         
*  3     TABLE ACCESS BY INDEX ROWID               TABLESD           1     38      5  (20)
   4      NESTED LOOPS                                               2    122     13   (8)
   5       TABLE ACCESS BY INDEX ROWID             TABLEOD           2     46      5  (20)
*  6        INDEX RANGE SCAN           ORD_DTL_RECV_DATE             2             4  (25)
*  7       INDEX RANGE SCAN            SSD_MASTER_ORDER_ID_NUK       1             4  (25)
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter(TO_DATE('05/20/08   00:00:00','MM/DD/YY HH24:MI:SS')<=TO_DATE('05/20/08                23:59:59','MM/DD/YY HH24:MI:SS'))      3 - filter("TABLESD"."SERVICE_TYPE"='Install' AND               ("TABLESD"."DRY_LOOP_IND"='N' OR "TABLESD"."DRY_LOOP_IND" IS N ULL) AND               ("TABLESD"."HARMONY_DATA_RECEIVED" IS NULL OR               "TABLESD"."HARMONY_DATA_RECEIVED"='N') AND               TO_NUMBER("TABLESD"."SUPP_ORDER_ID")=0 AND               ("TABLESD"."PRODUCT_CATEGORY"=2 OR "TABLESD"."PRODUCT_CATEGORY "=3 OR               "TABLESD"."PRODUCT_CATEGORY"=17) AND ("TABLESD"."SSPUNITY" IS NULL OR               "TABLESD"."SSPUNITY"='N') AND (TO_NUMBER("TABLESD"."SUB_ORDER_TYPE")=1               OR TO_NUMBER("TABLESD"."SUB_ORDER_TYPE")=3 OR               TO_NUMBER("TABLESD"."SUB_ORDER_TYPE")=4))      6 - access("TABLEOD"."RECEIVED_DATE">=TO_DATE('05/20/08   00:00:00','MM/DD/YY
              HH24:MI:SS') AND "TABLEOD"."RECEIVED_DATE"<=TO_DATE('05/20/08               23:59:59','MM/DD/YY HH24:MI:SS'))      7 - access("TABLESD"."MASTER_ORDER_ID"="TABLEOD"."MASTER_ORDER_ID")  33 rows selected.   

However, during execution time, the execution plan obtained from v$sql_plan is:

--------------------------------------------------------------------------------------------
 Id   Operation                        Name                 Rows   Bytes  Cost (%CPU)
--------------------------------------------------------------------------------------------
   0  SELECT STATEMENT                                                            
   1   SORT GROUP BY                                         1042  63562    420K  (6)
   2    FILTER                                                                    
   3     NESTED LOOPS                                        1042  63562    420K  (6)
   4      TABLE ACCESS FULL           TABLESD                1042  39596    416K  (6)
   5      TABLE ACCESS BY INDEX ROWID TABLEOD                   1     23      5  (20)
   6       INDEX RANGE SCAN           TEMP_1                    1             4  (25)
--------------------------------------------------------------------------------------------

Trouble shooting a CPU usage spike issue

Several months ago I have trouble-shooted an CPU usage spike issue. It turns out that the CBO for some reason choose an index with very poor selectivity to access a large table. By not using this index and using other index the sql get executed much much faster. The environment is 9.2.0.8.

The detailed trouble-shooting process is described as follows:

1. Symptom
In the past few days, mointoring script sends out aterts about CPU
usage reaching 100% during short period of time each day.

2. Statspack analysis

Indetified the problem period, generate statspack report for the period.
For example, 2/13/08 13:00 ~ 13:30, identified the top SQL by
buffer get or physical read is:

   Buffer Gets    Executions  Gets per Exec  %Total Time (s)  Time (s) Hash Value
 --------------- ------------ -------------- ------ -------- --------- ----------
      20,273,427           70      289,620.4   38.0  6193.42  26897.15 3530230280
 Module: JDBC Thin Client
 select  m.order_id, m.dsl_tn, m.queue_entered_time, m.service_re
 ady_date, m.abc_user,  m.hold_date, m.fallout_code, DECODE(m.isp
 _lob,1,'Consumer', 2,'Business') isp_lob, m.state, s.ilec_mon, s
 .pots_order_number, s.ilec_pon, l.ISP_NAME   FROM schemaA.tableA
  m , schemaA.tableS s , tableL

3. Otain the full text of the sql
Based on the hash value, obtain the full sql from v$sqltext


4. Format the sql
Using sqlReviwer tool to get formatted sql as follows:

SELECT
  m.order_id,
  m.dsl_tn,
  m.queue_entered_time,
  m.service_ready_date,
  m.abc_user,
  m.hold_date,
  m.fallout_code,
  DECODE(m.isp_lob,1,'Consumer', 2,'Business')  isp_lob,
  m.state,
  s.ilec_mon,
  s.pots_order_number,
  s.ilec_pon,
  l.ISP_NAME 
FROM schemaA.tableA m ,
  schemaA.tableS s ,
  tableL l
WHERE m.map_id = :1
  AND m.map_version = 1
  AND m.curr_status_id = :2
  AND m.abc_user = 'n/a'
  AND m.hold_date IS null
  AND s.order_id =  m.order_id
  AND s.element _id = m.element_id
  AND nvl(m.ISP_ID,500)  = l.ISP_ID 
  AND m.isp_lob != :3
  AND
  (
    (
      m.service_ready_date > sysdate
    )
  OR
    (
      m.service_ready_date <= sysdate      )   )   
  AND    (     m .ABC_LOCATION_INDICATOR IN ('E','FE')   
   OR  m.ABC_LOCATION_INDICATOR IN ('W','FW')    )    
  AND m.product_category IN ( 0,1,2,3,5,6,7,8,9,10,11,13,14,15,16,17,18,19,20,21,
     22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,40,42,43,44 )   
  AND nvl(m.isp_id,500 ) = 500 

5. SQL Trace and TKPROF

Fill in the values for bind variable :1, :2, :3
tkprof report:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.01       0.00          0          0          0           0
Fetch       17    140.66     140.80     307342     308814          0         234
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       19    140.67     140.81     307342     308814          0         234

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 5

Rows     Row Source Operation
-------  ---------------------------------------------------
    234  TABLE ACCESS BY INDEX ROWID ABC_SERVICE_DETAILS
    469   NESTED LOOPS
    234    HASH JOIN
    234     TABLE ACCESS BY INDEX ROWID ABC_MAIN_CURRENT
    267      BITMAP CONVERSION TO ROWIDS
      1       BITMAP AND
      1        BITMAP CONVERSION FROM ROWIDS
    267         INDEX RANGE SCAN SMC_MAP_CURRSTAT_NUK (object id 142322)
     31        BITMAP CONVERSION FROM ROWIDS
140900562         INDEX RANGE SCAN SMC_ABC_USER_NUK (object id 115734)
    386     TABLE ACCESS FULL ABC_ISP_LOOKUP
    234    INDEX RANGE SCAN SSD_ORDER_ID_ELEMENT_ID_NUK (object id 115773)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                      17        0.00          0.00
  db file sequential read                    307340        0.26         26.40
  latch free                                      1        0.01          0.01
  db file scattered read                          1        0.00          0.00
  SQL*Net message from client                    17        5.83          8.84
********************************************************************************

Notes:
- condition: AND m.abc_user = 'n/a', caused the problem
- Oracle used the index range scan
140900562 INDEX RANGE SCAN SMC_ABC_USER_NUK (object id 115734)

The selectivity of the index scan is extremly bad.



6. Rewrite the SQL using materialize hint as follows:

WITH
temp_tab AS
(
SELECT /*+ materialize */
  m.order_id,
  m.dsl_tn,
  m.queue_entered_time,
  m.service_ready_date,
  m.abc_user,
  m.hold_date,
  m.fallout_code,
  DECODE(m.isp_lob,1,'Consumer', 2,'Business')  isp_lob,
  m.state,
  s.ilec_mon,
  s.pots_order_number,
  s.ilec_pon,
  l.ISP_NAME
FROM schemaA.tableA m,
  schemaA.tableS s,
  schemaA.tableL l
WHERE m.map_id = 8 AND m.map_version = 1 AND m.curr_status_id = 3
--  AND m.abc_user = 'n/a'
  AND m.hold_date IS null
  AND s.order_id =  m.order_id
  AND s.element_id = m.element_id
  AND nvl(m.ISP_ID,500)  = l.ISP_ID

  AND m.isp_lob != 2
  AND
  (
    (
      m.service_ready_date > sysdate
    )
  OR
    (
      m.service_ready_date <= sysdate     )   )  
  AND   (     m.ABC_LOCATION_INDICATOR IN ('E','FE')  
   OR  m.ABC_LOCATION_INDICATOR IN ('W','FW')   )  
  AND m.product_category IN   ( 0,1,2,3,5,6,7,8,9,10,11,13,14,15,
      16,17,18,19,20,21 ,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,40,42,43,44 )  
  AND nvl(m.isp_id,500 ) = 500   )
select  *   from temp_tab
where    abc_user='n/a'
/ 

 tkprof:
======= 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.06       0.08         14         33          2           0
Execute      1      0.09       0.18         35       1268          2           0
Fetch       17      0.01       0.00          3         23          1         237
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       19      0.16       0.27         52       1324          5         237 


Rows     Row Source Operation
-------  ---------------------------------------------------
    237   
    237  VIEW 
    237   TABLE ACCESS FULL SYS_TEMP_4254950916


NOtes: in the tkprof report, it shows a global temp table is created and rows are
       inserted into this table:

INSERT /*+ APPEND BYPASS_RECURSIVE_CHECK */ INTO
  "SYS"."SYS_TEMP_0FD9D6604_6B0E3DDA" SELECT /*+ */ "M"."ORDER_ID",
  "M"."DSL_TN","M"."QUEUE_ENTERED_TIME","M"."SERVICE_READY_DATE",
  "M"."ABC_USER","M"."HOLD_DATE","M"."FALLOUT_CODE",DECODE("M"."ISP_LOB",
  :"SYS_B_00",:"SYS_B_01",:"SYS_B_02",:"SYS_B_03"),"M"."STATE","S"."ILEC_MON",
  "S"."POTS_ORDER_NUMBER","S"."ILEC_PON","L"."ISP_NAME" FROM
  "SCHEMAA"."TABLEA" "M","SCHEMAA"."TABLES" "S",
  "SCHEMAA"."TABLEL" "L" WHERE "M"."MAP_ID"=:"SYS_B_04" AND
  "M"."MAP_VERSION"=:"SYS_B_05" AND "M"."CURR_STATUS_ID"=:"SYS_B_06" AND
  "M"."HOLD_DATE" IS NULL AND "S"."ORDER_ID"="M"."ORDER_ID" AND
  "S"."ELEMENT_ID"="M"."ELEMENT_ID" AND "L"."ISP_ID"=NVL("M"."ISP_ID",
  :"SYS_B_07") AND "M"."ISP_LOB"<>:"SYS_B_08" AND
  ("M"."SERVICE_READY_DATE">SYSDATE@! OR "M"."SERVICE_READY_DATE"<=SYSDATE@!)
  AND ("M"."ABC_LOCATION_INDICATOR"=:"SYS_B_09" OR
  "M"."ABC_LOCATION_INDICATOR"=:"SYS_B_10" OR "M"."SSP_LOCATION_INDICATOR"=
  :"SYS_B_11" OR "M"."ABC_LOCATION_INDICATOR"=:"SYS_B_12") AND
  ("M"."PRODUCT_CATEGORY"=:"SYS_B_13" OR "M"."PRODUCT_CATEGORY"=:"SYS_B_14"
  OR "M"."PRODUCT_CATEGORY"=:"SYS_B_15" OR "M"."PRODUCT_CATEGORY"=:"SYS_B_16"
  OR "M"."PRODUCT_CATEGORY"=:"SYS_B_17" OR "M"."PRODUCT_CATEGORY"=:"SYS_B_18"
  OR "M"."PRODUCT_CATEGORY"=:"SYS_B_19" OR "M"."PRODUCT_CATEGORY"=:"SYS_B_20"
  OR "M"."PRODUCT_CATEGORY"=:"SYS_B_21" OR "M"."PRODUCT_CATEGORY"=:"SYS_B_22"
  OR "M"."PRODUCT_CATEGORY"=:"SYS_B_23" OR "M"."PRODUCT_CATEGORY"=:"SYS_B_24"
  OR "M"."PRODUCT_CATEGORY"=:"SYS_B_25" OR "M"."PRODUCT_CATEGORY"=:"SYS_B_26"
  OR "M"."PRODUCT_CATEGORY"=:"SYS_B_27" OR "M"."PRODUCT_CATEGORY"=:"SYS_B_28"
  OR "M"."PRODUCT_CATEGORY"=:"SYS_B_29" OR "M"."PRODUCT_CATEGORY"=:"SYS_B_30"
  OR "M"."PRODUCT_CATEGORY"=:"SYS_B_31" OR "M"."PRODUCT_CATEGORY"=:"SYS_B_32"
  OR "M"."PRODUCT_CATEGORY"=:"SYS_B_33" OR "M"."PRODUCT_CATEGORY"=:"SYS_B_34"
  OR "M"."PRODUCT_CATEGORY"=:"SYS_B_35" OR "M"."PRODUCT_CATEGORY"=:"SYS_B_36"
  OR "M"."PRODUCT_CATEGORY"=:"SYS_B_37" OR "M"."PRODUCT_CATEGORY"=:"SYS_B_38"
  OR "M"."PRODUCT_CATEGORY"=:"SYS_B_39" OR "M"."PRODUCT_CATEGORY"=:"SYS_B_40"
  OR "M"."PRODUCT_CATEGORY"=:"SYS_B_41" OR "M"."PRODUCT_CATEGORY"=:"SYS_B_42"
  OR "M"."PRODUCT_CATEGORY"=:"SYS_B_43" OR "M"."PRODUCT_CATEGORY"=:"SYS_B_44"
  OR "M"."PRODUCT_CATEGORY"=:"SYS_B_45" OR "M"."PRODUCT_CATEGORY"=:"SYS_B_46"
  OR "M"."PRODUCT_CATEGORY"=:"SYS_B_47" OR "M"."PRODUCT_CATEGORY"=:"SYS_B_48"
  OR "M"."PRODUCT_CATEGORY"=:"SYS_B_49" OR "M"."PRODUCT_CATEGORY"=:"SYS_B_50"
  OR "M"."PRODUCT_CATEGORY"=:"SYS_B_51") AND NVL("M"."ISP_ID",:"SYS_B_52")=
  :"SYS_B_53"


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.00          0          0          0           0
Execute      1      0.08       0.17         35       1268          3         237
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.09       0.17         35       1268          3         237

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 5     (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  LOAD AS SELECT 
    237   TABLE ACCESS BY INDEX ROWID TABLES
    475    NESTED LOOPS 
    237     HASH JOIN 
    386      TABLE ACCESS FULL TABLEL
    237      TABLE ACCESS BY INDEX ROWID TABLEA
    270       INDEX RANGE SCAN SMC_MAP_CURRSTAT_NUK (object id 142322)
    237     INDEX RANGE SCAN SSD_ORDER_ID_ELEMENT_ID_NUK (object id 115773)

7. App team comes up with another idea:
Instead of using:
AND m.abc_user = 'n/a'
they change it to:
AND sub_string(m.abc_user, 1,1) not in ('V', 'Z')

   tkprof:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch       22      0.24       0.31        757       1665          0         302
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       24      0.25       0.32        757       1665          0         302

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 99

Rows     Row Source Operation
-------  ---------------------------------------------------
    302  TABLE ACCESS BY INDEX ROWID TABLES
    605   NESTED LOOPS
    302    HASH JOIN
    302     TABLE ACCESS BY INDEX ROWID TABLEA
    351      INDEX RANGE SCAN SMC_MAP_CURRSTAT_NUK (object id 142322)
    386     TABLE ACCESS FULL TABLEL
    302    INDEX RANGE SCAN SSD_ORDER_ID_ELEMENT_ID_NUK (object id 115773)