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