Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> index vs fts

index vs fts

From: Joe Armstrong-Champ <joseph.armstrong-champ_at_tufts.edu>
Date: Fri, 16 Nov 2007 16:05:30 -0500
Message-ID: <473E061A.6080308@tufts.edu>


I have a performance problem with a query since upgrading to 10.2.0.3 from 9.2.0.7 (OS is aix 5.3). I ran a 10046 trace through tkprof and it shows index access on a table which accounts for most of the table (ps_job). So I thought that I'd force a fts using a hint. The sql is selecting from a view so I put the hint everywhere the table is selected. I also tried putting the However, it doesn't change the access path for the table.

I have also been researching and trying different things for the merge join cartesian to no avail.

Any ideas appreciated. Thanks. Joe

main sql:
SELECT DISTINCT EMPLID, EMPL_RCD, NAME, LAST_NAME_SRCH, NAME_AC, PER_STATUS FROM JOE
WHERE ROWSECCLASS='TAXES' AND LAST_NAME_SRCH LIKE 'THIB%' ORDER BY LAST_NAME_SRCH, EMPLID, EMPL_RCD view:
select /* full(PS_JOB) */

   3 A.EMPLID ,JOB.EMPL_RCD ,SEC.ROWSECCLASS ,SEC.ACCESS_CD ,A.NAME

   4  ,A.LAST_NAME_SRCH ,A.NAME_AC ,A.PER_STATUS FROM PS_PERSONAL_DATA A
   5  ,PS_JOB JOB ,PS_TFTH_JOB TJOB ,PS_TFTH_SEC_CLASS TSEC
   6  ,PS_SCRTY_TBL_DEPT SEC WHERE A.EMPLID=JOB.EMPLID AND
   7 A.EMPLID=TJOB.EMPLID AND JOB.EMPLID=TJOB.EMPLID AND    8 JOB.EMPL_RCD=TJOB.EMPL_RCD AND JOB.EFFDT=TJOB.EFFDT AND   9 JOB.EFFSEQ=TJOB.EFFSEQ AND ( JOB.EFFDT>=TO_DATE(TO_CHAR(SYSDATE 10 ,'YYYY-MM-DD'),'YYYY-MM-DD') OR (JOB.EFFDT= ( SELECT /* full(ps_job) */ MAX(JOB2.EFFDT)
  11 FROM PS_JOB JOB2 WHERE JOB.EMPLID=JOB2.EMPLID AND   12 JOB.EMPL_RCD=JOB2.EMPL_RCD AND JOB2.EFFDT<=TO_DATE(TO_CHAR(SYSDATE 13 ,'YYYY-MM-DD'),'YYYY-MM-DD') ) AND JOB.EFFSEQ= ( SELECT /* full(ps_job) */
  14 MAX(JOB3.EFFSEQ) FROM PS_JOB JOB3 WHERE JOB.EMPLID=JOB3.EMPLID AND   15 JOB.EMPL_RCD=JOB3.EMPL_RCD AND JOB.EFFDT=JOB3.EFFDT ) ) ) AND   16 SEC.ACCESS_CD='Y' AND EXISTS ( SELECT 'X' FROM PSTREENODE TN WHERE
  17   TN.SETID = SEC.SETID AND TN.SETID = JOB.SETID_DEPT AND
  18   TN.TREE_NAME='DEPT_SECURITY' AND TN.EFFDT= SEC.TREE_EFFDT AND
  19   TN.TREE_NODE=JOB.DEPTID AND TN.TREE_NODE_NUM BETWEEN
  20 SEC.TREE_NODE_NUM AND SEC.TREE_NODE_NUM_END AND NOT EXISTS ( SELECT   21 'X' FROM PS_SCRTY_TBL_DEPT SEC2 WHERE SEC.ROWSECCLASS =   22 SEC2.ROWSECCLASS AND SEC.SETID = SEC2.SETID AND SEC.TREE_NODE_NUM <>   23 SEC2.TREE_NODE_NUM AND TN.TREE_NODE_NUM BETWEEN SEC2.TREE_NODE_NUM   24 AND SEC2.TREE_NODE_NUM_END AND SEC2.TREE_NODE_NUM BETWEEN   25 SEC.TREE_NODE_NUM AND SEC.TREE_NODE_NUM_END ) ) AND   26 SEC.ROWSECCLASS=TSEC.OPRID AND
  27 TJOB.TFTH_EMPL_SEC_CLAS=TSEC.TFTH_EMPL_SEC_CLAS tkprof:
call     count       cpu    elapsed       disk      query    current 
     rows
------- ------  -------- ---------- ---------- ---------- ---------- 
Parse        1      3.57       3.52          0          0          0 
        0
Execute      1      0.00       0.00          0          0          0 
        0
Fetch        2     44.43      43.77          0    1753238          0 
        8

------- ------ -------- ---------- ---------- ---------- ----------
total        4     48.00      47.30          0    1753238          0 
        8

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 57 (******)

Rows Row Source Operation

-------  ---------------------------------------------------
       8  SORT UNIQUE (cr=1753238 pr=0 pw=0 time=43778730 us)
       8   CONCATENATION  (cr=1753238 pr=0 pw=0 time=43778274 us)
       8    NESTED LOOPS  (cr=1745298 pr=0 pw=0 time=40799627 us)
      40     NESTED LOOPS  (cr=1745176 pr=0 pw=0 time=40797667 us)
  220905      NESTED LOOPS  (cr=1303364 pr=0 pw=0 time=34022966 us)
    2090       MERGE JOIN CARTESIAN (cr=448 pr=0 pw=0 time=101854 us)
     418        NESTED LOOPS ANTI (cr=446 pr=0 pw=0 time=84796 us)
     418         NESTED LOOPS  (cr=19 pr=0 pw=0 time=4062 us)
       1          TABLE ACCESS BY INDEX ROWID PS_SCRTY_TBL_DEPT (cr=12 
pr=0 pw=0 time=1086 us)
     162           INDEX RANGE SCAN PSBSCRTY_TBL_DEPT (cr=1 pr=0 pw=0 
time=459 us)(object id 67418)
     418          INDEX RANGE SCAN PSAPSTREENODE (cr=7 pr=0 pw=0 
time=2133 us)(object id 52677)
       0         TABLE ACCESS BY INDEX ROWID PS_SCRTY_TBL_DEPT (cr=427 
pr=0 pw=0 time=77200 us)
    1015          INDEX RANGE SCAN PSASCRTY_TBL_DEPT (cr=2 pr=0 pw=0 
time=68027 us)(object id 67417)
    2090        BUFFER SORT (cr=2 pr=0 pw=0 time=10765 us)
       5         INDEX RANGE SCAN PS_TFTH_SEC_CLASS (cr=2 pr=0 pw=0 
time=70 us)(object id 69600)
  220905       TABLE ACCESS BY INDEX ROWID PS_JOB (cr=1302916 pr=0 pw=0 
time=33545835 us)                      <================================
  220905        INDEX RANGE SCAN PS0JOB (cr=1090726 pr=0 pw=0 
time=30223701 us)(object id 61797) <====================================
  319917         SORT AGGREGATE (cr=639836 pr=0 pw=0 time=16715544 us)
  319902          FIRST ROW  (cr=639836 pr=0 pw=0 time=13793836 us)
  319902           INDEX RANGE SCAN (MIN/MAX) PSAJOB (cr=639836 pr=0 
pw=0 time=12374022 us)(object id 61802)
  219263         SORT AGGREGATE (cr=438528 pr=0 pw=0 time=8253168 us)
  219263          FIRST ROW  (cr=438528 pr=0 pw=0 time=6409909 us)
  219263           INDEX RANGE SCAN (MIN/MAX) PSAJOB (cr=438528 pr=0 
pw=0 time=5426980 us)(object id 61802)
      40      TABLE ACCESS BY INDEX ROWID PS_PERSONAL_DATA (cr=441812 
pr=0 pw=0 time=6196248 us)
  220905       INDEX UNIQUE SCAN PS_PERSONAL_DATA (cr=220907 pr=0 pw=0 
time=3088617 us)(object id 64183)
       8     TABLE ACCESS BY INDEX ROWID PS_TFTH_JOB (cr=122 pr=0 pw=0 
time=1817 us)
      40      INDEX UNIQUE SCAN PS_TFTH_JOB (cr=82 pr=0 pw=0 time=1176 
us)(object id 69425)
       0    FILTER  (cr=7940 pr=0 pw=0 time=2978511 us)
       0     NESTED LOOPS  (cr=7940 pr=0 pw=0 time=2978504 us)
       0      NESTED LOOPS  (cr=7940 pr=0 pw=0 time=2978494 us)
     846       NESTED LOOPS  (cr=6246 pr=0 pw=0 time=2952413 us)
     846        NESTED LOOPS  (cr=3706 pr=0 pw=0 time=2920219 us)
     418         NESTED LOOPS ANTI (cr=446 pr=0 pw=0 time=93795 us)
     418          NESTED LOOPS  (cr=19 pr=0 pw=0 time=3441 us)
       1           TABLE ACCESS BY INDEX ROWID PS_SCRTY_TBL_DEPT (cr=12 
pr=0 pw=0 time=888 us)
     162            INDEX RANGE SCAN PSBSCRTY_TBL_DEPT (cr=1 pr=0 pw=0 
time=358 us)(object id 67418)
     418           INDEX RANGE SCAN PSAPSTREENODE (cr=7 pr=0 pw=0 
time=1711 us)(object id 52677)
       0          TABLE ACCESS BY INDEX ROWID PS_SCRTY_TBL_DEPT (cr=427 
pr=0 pw=0 time=87743 us)
    1015           INDEX RANGE SCAN PSASCRTY_TBL_DEPT (cr=2 pr=0 pw=0 
time=79175 us)(object id 67417)
     846         TABLE ACCESS BY INDEX ROWID PS_JOB (cr=3260 pr=0 pw=0 
time=2823743 us)
     846          INDEX RANGE SCAN PS0JOB (cr=2474 pr=0 pw=0 
time=2807302 us)(object id 61797)
     846        TABLE ACCESS BY INDEX ROWID PS_TFTH_JOB (cr=2540 pr=0 
pw=0 time=29486 us)
     846         INDEX UNIQUE SCAN PS_TFTH_JOB (cr=1694 pr=0 pw=0 
time=18334 us)(object id 69425)
       0       TABLE ACCESS BY INDEX ROWID PS_PERSONAL_DATA (cr=1694 
pr=0 pw=0 time=23952 us)
     846        INDEX UNIQUE SCAN PS_PERSONAL_DATA (cr=848 pr=0 pw=0 
time=11784 us)(object id 64183)
       0      INDEX UNIQUE SCAN PS_TFTH_SEC_CLASS (cr=0 pr=0 pw=0 time=0 
us)(object id 69600)
  319917     SORT AGGREGATE (cr=639836 pr=0 pw=0 time=16715544 us)
  319902          FIRST ROW  (cr=639836 pr=0 pw=0 time=13793836 us)
  319902           INDEX RANGE SCAN (MIN/MAX) PSAJOB (cr=639836 pr=0 
pw=0 time=12374022 us)(object id 61802)
  219263     SORT AGGREGATE (cr=438528 pr=0 pw=0 time=8253168 us)
  219263          FIRST ROW  (cr=438528 pr=0 pw=0 time=6409909 us)
  219263           INDEX RANGE SCAN (MIN/MAX) PSAJOB (cr=438528 pr=0 
pw=0 time=5426980 us)(object id 61802)

Rows Execution Plan

-------  ---------------------------------------------------
       0  SELECT STATEMENT   MODE: ALL_ROWS
       8   SORT (UNIQUE)
       8    CONCATENATION
       8     NESTED LOOPS
      40      NESTED LOOPS
  220905       NESTED LOOPS
    2090        MERGE JOIN (CARTESIAN)
     418         NESTED LOOPS (ANTI)
     418          NESTED LOOPS
       1           TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID)
                       OF 'PS_SCRTY_TBL_DEPT' (TABLE)
     162            INDEX   MODE: ANALYZED (RANGE SCAN) OF
                        'PSBSCRTY_TBL_DEPT' (INDEX)
     418           INDEX   MODE: ANALYZED (RANGE SCAN) OF
                       'PSAPSTREENODE' (INDEX)
       0          TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID) OF

'PS_SCRTY_TBL_DEPT' (TABLE)
1015 INDEX MODE: ANALYZED (RANGE SCAN) OF 'PSASCRTY_TBL_DEPT' (INDEX) 2090 BUFFER (SORT) 5 INDEX MODE: ANALYZED (RANGE SCAN) OF
'PS_TFTH_SEC_CLASS' (INDEX (UNIQUE))
220905 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF 'PS_JOB' (TABLE) 220905 INDEX MODE: ANALYZED (RANGE SCAN) OF 'PS0JOB' (INDEX) 319917 SORT (AGGREGATE) 319902 FIRST ROW 319902 INDEX MODE: ANALYZED (RANGE SCAN (MIN/MAX)) OF 'PSAJOB' (INDEX) 219263 SORT (AGGREGATE) 219263 FIRST ROW 219263 INDEX MODE: ANALYZED (RANGE SCAN (MIN/MAX)) OF 'PSAJOB' (INDEX) 40 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF 'PS_PERSONAL_DATA' (TABLE) 220905 INDEX MODE: ANALYZED (UNIQUE SCAN) OF 'PS_PERSONAL_DATA' (INDEX (UNIQUE)) 8 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF 'PS_TFTH_JOB' (TABLE) 40 INDEX MODE: ANALYZED (UNIQUE SCAN) OF 'PS_TFTH_JOB' (INDEX (UNIQUE)) 0 FILTER 0 NESTED LOOPS 0 NESTED LOOPS 846 NESTED LOOPS 846 NESTED LOOPS 418 NESTED LOOPS (ANTI) 418 NESTED LOOPS 1 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF 'PS_SCRTY_TBL_DEPT' (TABLE) 162 INDEX MODE: ANALYZED (RANGE SCAN) OF 'PSBSCRTY_TBL_DEPT' (INDEX) 418 INDEX MODE: ANALYZED (RANGE SCAN) OF 'PSAPSTREENODE' (INDEX) 0 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF 'PS_SCRTY_TBL_DEPT' (TABLE) 1015 INDEX MODE: ANALYZED (RANGE SCAN) OF 'PSASCRTY_TBL_DEPT' (INDEX) 846 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'PS_JOB' (TABLE)
846 INDEX MODE: ANALYZED (RANGE SCAN) OF 'PS0JOB' (INDEX) 846 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF 'PS_TFTH_JOB' (TABLE) 846 INDEX MODE: ANALYZED (UNIQUE SCAN) OF
'PS_TFTH_JOB' (INDEX (UNIQUE))
0 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF 'PS_PERSONAL_DATA' (TABLE) 846 INDEX MODE: ANALYZED (UNIQUE SCAN) OF 'PS_PERSONAL_DATA' (INDEX (UNIQUE)) 0 INDEX MODE: ANALYZED (UNIQUE SCAN) OF 'PS_TFTH_SEC_CLASS' (INDEX (UNIQUE)) 319917 SORT (AGGREGATE) 319902 FIRST ROW 319902 INDEX MODE: ANALYZED (RANGE SCAN (MIN/MAX)) OF 'PSAJOB' (INDEX) 219263 SORT (AGGREGATE) 219263 FIRST ROW 219263 INDEX MODE: ANALYZED (RANGE SCAN (MIN/MAX)) OF 'PSAJOB' (INDEX) Elapsed times include waiting on following events: Event waited on Times Max. Wait Total
Waited
--
http://www.freelists.org/webpage/oracle-l
Received on Fri Nov 16 2007 - 15:05:30 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US