index vs fts

• From: Joe Armstrong-Champ <joseph.armstrong-champ@xxxxxxxxx>
• To: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
• Date: Fri, 16 Nov 2007 16:05:30 -0500

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 ---------------------------------------- Waited ---------- ------------ SQL*Net message to client 2 0.00 0.00 SQL*Net message from client 2 52.55 52.55

--
//www.freelists.org/webpage/oracle-l

• Follow-Ups:
  • Re: index vs fts
    • From: krish . hariharan
  • RE: index vs fts
    • From: Allen, Brandon
  • RE: index vs fts
    • From: Baumgartel, Paul
  • Re: index vs fts
    • From: GovindanK

Other related posts:

• » index vs fts
• » Re: index vs fts
• » RE: index vs fts
• » RE: index vs fts
• » Re: index vs fts

1. Home
2. oracle-l
3. Archive
4. 11-2007

---

• » Previous by Date
• » Next by Date
• » Related Posts
• » View list details
• » Manage your subscription

---