| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: index vs fts
Check the Performance manuals for information on global hints. These
allow you to "reach down" into a view and specify hints on tables
selected by the view.
Paul Baumgartel
CREDIT SUISSE
Information Technology
Securities Processing Databases Americas
One Madison Avenue
New York, NY 10010
USA
Phone 212.538.1143
paul.baumgartel_at_credit-suisse.com
www.credit-suisse.com
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Joe Armstrong-Champ
Sent: Friday, November 16, 2007 4:06 PM
To: ORACLE-L
Subject: index vs fts
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 AND7 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 BETWEEN20 SEC.TREE_NODE_NUM AND SEC.TREE_NODE_NUM_END AND NOT EXISTS ( SELECT
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 ============================================================================== Please access the attached hyperlink for an important electronic communications disclaimer: http://www.credit-suisse.com/legal/en/disclaimer_email_ib.html ============================================================================== -- http://www.freelists.org/webpage/oracle-lReceived on Mon Nov 19 2007 - 08:14:43 CST
 |
 |