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)

APEX 3.0.1 Login problem solved

After installation of  APEX 3.0.1, I can not login with the ADMIN user. Check file
error_log.1199361600 at C:\oraclexe\app\oracle\product\10.2.0\http_1\Apache\Apache\logs>
I can see some entries like that:

[Thu Jan 03 12:41:52 2008] [error] [client 192.168.2.2] [ecid: 1199382112:192.168.2.2
:3600:4072:7,0] File does not exist: c:/oraclexe/app/oracle/product/10.2.0/http_1/
apache/imageshtmldb/apex_logo.gif

Note there is no '/' between images and htmldb.

In the file dads.conf located at C:\oraclexe\app\oracle\product\10.2.0\http_1\Apache\modplsql\conf,
I change the follow line
Alias /i/ "C:\oraclexe\app\oracle\product\10.2.0\http_1\Apache\images"
to
Alias /i/ "C:\oraclexe\app\oracle\product\10.2.0\http_1\Apache\images/"

, then I stop/restart the Apache server,  the problem is solved


The content of my dads.conf
-----------------------------

Alias /i/ "C:\oraclexe\app\oracle\product\10.2.0\http_1\Apache\images/"
AddType text/xml       xbl
AddType text/x-component       htc


 Order deny,allow
 PlsqlDocumentPath docs
 AllowOverride None
 PlsqlDocumentProcedure      wwv_flow_file_mgr.process_download
 PlsqlDatabaseConnectString NTBK:1521:TEST10G ServiceNameFormat
 PlsqlNLSLanguage            AMERICAN_AMERICA.AL32UTF8
 PlsqlAuthenticationMode     Basic
 SetHandler                  pls_handler
 PlsqlDocumentTablename      wwv_flow_file_objects$
 PlsqlDatabaseUsername       APEX_PUBLIC_USER
 PlsqlDefaultPage            apex
 PlsqlDatabasePassword       oracle
 Allow from all