Home » RDBMS Server » Performance Tuning » SQL statement takes too long to run
| SQL statement takes too long to run [message #293745] |
Mon, 14 January 2008 17:46  |
sachinp36
Messages: 3
Registered: January 2008
|
Junior Member |
|
|
Can anybody help with performance tuning by looking at the following TKPROF file? Our DBA is not much of a help.. I have also attached the file as txt attachment.
Thanks,
Sachin
Sort options: default
********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************
ALTER SESSION SET EVENTS '10046 trace name context forever, level 12'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1 0.00 0.00 0 0 0 0
Misses in library cache during parse: 0
Misses in library cache during execute: 1
Optimizer goal: CHOOSE
Parsing user id: 28 (EFDATA)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 0.00 0.00
SQL*Net more data from client 2 0.00 0.00
********************************************************************************
SELECT obj.obid, obj.n1uniquekey, obj.n1clsrevobid, obj.n1class, obj.n1name,
obj.n1description, obj.n1creationsesid, obj.n1creationdate,
obj.n1lastupdated, obj.n1terminationsesid, obj.n1terminationdate,
obj.n1accessflag, obj.n1owninggroup, obj.n1owner, obj.n1projectname,
obj.n1implchangename, obj.n1lcstate, obj.n1revisestate,
obj.n1revgrpmasterobid, obj.n1supersededdate, obj.n1issuestate,
obj.n1issuedate, obj.n1intrevision, obj.n1extrevision,
obj.n1revschemename, obj.n1majorrevision, obj.n1minorrevision,
obj.n1version, obj.n1checkedoutind, obj.n1workflowobid,
obj.n1workflowname, obj.n1workflowstatus, obj.n1workflowrev,
obj.n1config, obj.n1efcreationdate, obj.n1efterminationdate,
obj.n1workflowsubmitter, obj.n1eficonclass, obj.n1origclassdefuid,
'' AS n1clsclass, '' AS n1clsname, '' AS n1clsdescription,
'' AS n1classification, '' AS ifname
FROM n2spcrev obj,
eft_publishstate eft,
n3rgrprev r2,
n2revgrp o2,
n2ifobj if2,
n3rgrprev r4,
n2revgrp o4,
n2ifobj if4,
n3rgrprev r6,
n2revgrp o6,
n2ifobj if6,
n2ifobj if8,
n3objattr oa8,
n2spcattr sa8,
n2clsattr ca8,
n2ifobj if16,
n3objattr oa16,
n2spcattr sa16,
n2clsattr ca16,
n2ifobj ifo
WHERE ( eft.n1docobid(+) = obj.obid
AND obj.n1issuestate IN ('CURRENT', 'WORKING')
AND (obj.n1revisestate IS NULL OR obj.n1revisestate = '')
AND obj.obid = r2.rightrel
AND r2.leftrel = o2.obid
AND r2.reldefid = 'CNBusinessUnitAllDwgRev'
AND if2.n1bobjobid = o2.obid
AND if2.n1name = 'ICNBusinessUnit'
AND o2.n1name = 'BP'
AND r2.n1terminationdate IS NULL
AND o2.n1terminationdate IS NULL
AND obj.obid = r4.rightrel
AND r4.leftrel = o4.obid
AND r4.reldefid = 'CNPlantAllDwgRev'
AND if4.n1bobjobid = o4.obid
AND if4.n1name = 'ICNPlant'
AND o4.n1name = '24'
AND r4.n1terminationdate IS NULL
AND o4.n1terminationdate IS NULL
AND obj.obid = r6.rightrel
AND r6.leftrel = o6.obid
AND r6.reldefid = 'CNTrainAllDwgRev'
AND if6.n1bobjobid = o6.obid
AND if6.n1name = 'ICNTrain'
AND o6.n1name = '0'
AND r6.n1terminationdate IS NULL
AND o6.n1terminationdate IS NULL
AND obj.obid = if8.n1bobjobid
AND oa8.leftrel = if8.obid
AND oa8.rightrel = sa8.obid
AND sa8.n1strvalue = 'General Drawings'
AND sa8.n1attrclassobid = ca8.obid
AND ca8.n1attrclassname = 'CNCADDwgClass'
AND oa8.n1terminationdate IS NULL
AND obj.obid = if16.n1bobjobid
AND oa16.leftrel = if16.obid
AND oa16.rightrel = sa16.obid
AND sa16.n1strvalue = 'PID'
AND sa16.n1attrclassobid = ca16.obid
AND ca16.n1attrclassname = 'CNCADDwgType'
AND oa16.n1terminationdate IS NULL
AND obj.obid = ifo.n1bobjobid
AND ( obj.n1config IS NULL
OR obj.n1config = ''
OR obj.n1config LIKE 'N3-FMCVRVSVEMAA-3XRHF5IF,%'
)
)
AND NOT EXISTS (
SELECT 1
FROM t2spcrev
WHERE obid = obj.obid
AND (n1config LIKE 'N3-FMCVRVSVEMAA-3XRHF5IF,%'))
AND ( obj.n1owner = 'SachinP'
OR obj.n1owninggroup IN
('ADMIN',
'ALL_VIEWONLY',
'BP-R-Edit',
'BP-Restricted',
'BP-U-Edit',
'BP-Unrestricted',
'CO-U-Edit',
'CO-Unrestricted',
'EI-R-Edit',
'EI-Restricted',
'EI-U-Edit',
'EI-Unrestricted',
'IF-R-Edit',
'IF-Restricted',
'IF-U-Edit',
'IF-Unrestricted',
'LS-R-Edit',
'LS-Restricted',
'LS-U-Edit',
'LS-Unrestricted',
'MN-R-Edit',
'MN-Restricted',
'MN-U-Edit',
'MN-Unrestricted',
'OS-R-Edit',
'OS-Restricted',
'OS-U-Edit',
'OS-Unrestricted',
'PC-R-Edit',
'PC-Restricted',
'PC-U-Edit',
'PC-Unrestricted',
'POWER',
'PUG-R-Edit',
'PUG-Restricted',
'PUG-U-Edit',
'PUG-Unrestricted',
'SUG-R-Edit',
'SUG-Restricted',
'SUG-U-Edit',
'SUG-Unrestricted',
'SUP-R-Edit',
'SUP-Restricted',
'SUP-U-Edit',
'SUP-Unrestricted',
'UT-R-Edit',
'UT-Restricted',
'UT-U-Edit',
'UT-Unrestricted',
'VIEWONLY'
)
)
AND ifo.n1name = 'ICNAllDwgRev'
AND obj.n1uniquekey LIKE '%'
AND ROWNUM <= 32001
ORDER BY obj.n1name
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.12 0.12 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 4 200.82 197.71 17040 73288447 0 41
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 6 200.94 197.83 17040 73288447 0 41
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 28 (EFDATA)
Rows Row Source Operation
------- ---------------------------------------------------
41 SORT ORDER BY
41 COUNT STOPKEY
41 TABLE ACCESS BY INDEX ROWID N3OBJATTR
858817 NESTED LOOPS
756 NESTED LOOPS
106992 HASH JOIN ANTI
106992 TABLE ACCESS BY INDEX ROWID N2IFOBJ
115909 NESTED LOOPS
8916 NESTED LOOPS
743 NESTED LOOPS
743 NESTED LOOPS
957 NESTED LOOPS
957 NESTED LOOPS
957 NESTED LOOPS
1346 NESTED LOOPS
1346 NESTED LOOPS
1346 HASH JOIN OUTER
1346 NESTED LOOPS
1431 NESTED LOOPS
1 NESTED LOOPS
1 NESTED LOOPS
1 NESTED LOOPS
1 NESTED LOOPS
1 NESTED LOOPS
1 TABLE ACCESS BY INDEX ROWID N2CLSATTR
1 INDEX UNIQUE SCAN N2CLSATTR_N1NAME (object id 7217)
1 TABLE ACCESS BY INDEX ROWID N2CLSATTR
1 INDEX UNIQUE SCAN N2CLSATTR_N1NAME (object id 7217)
1 TABLE ACCESS BY INDEX ROWID N2REVGRP
1 INDEX RANGE SCAN N2REVGRP_N1NAME (object id 7251)
1 TABLE ACCESS BY INDEX ROWID N2SPCATTR
1 INDEX RANGE SCAN N2SPCATTR_OBID_N1STRVALUE (object id 7260)
1 TABLE ACCESS BY INDEX ROWID N2SPCATTR
1 INDEX RANGE SCAN N2SPCATTR_OBID_N1STRVALUE (object id 7260)
1 TABLE ACCESS BY INDEX ROWID N2IFOBJ
2 INDEX RANGE SCAN N2IFOBJ_N1BOBJOBID (object id 7229)
1431 TABLE ACCESS BY INDEX ROWID N3RGRPREV
1431 INDEX RANGE SCAN N3RGRPREV_RLTLEFTRELINDEX (object id 7319)
1346 TABLE ACCESS BY INDEX ROWID N2SPCREV
1431 INDEX UNIQUE SCAN PK_N2SPCREV (object id 7266)
10 TABLE ACCESS FULL EFT_PUBLISHSTATE
1346 TABLE ACCESS BY INDEX ROWID N2IFOBJ
16152 INDEX RANGE SCAN N2IFOBJ_N1BOBJOBID (object id 7229)
1346 TABLE ACCESS BY INDEX ROWID N3RGRPREV
18136 INDEX RANGE SCAN N3RGRPREV_RLTRIGHTRELINDEX (object id 7320)
957 TABLE ACCESS BY INDEX ROWID N2REVGRP
1346 INDEX UNIQUE SCAN PK_N2REVGRP (object id 7253)
957 TABLE ACCESS BY INDEX ROWID N2IFOBJ
1914 INDEX RANGE SCAN N2IFOBJ_N1BOBJOBID (object id 7229)
957 TABLE ACCESS BY INDEX ROWID N3RGRPREV
12623 INDEX RANGE SCAN N3RGRPREV_RLTRIGHTRELINDEX (object id 7320)
743 TABLE ACCESS BY INDEX ROWID N2REVGRP
957 INDEX UNIQUE SCAN PK_N2REVGRP (object id 7253)
743 TABLE ACCESS BY INDEX ROWID N2IFOBJ
1486 INDEX RANGE SCAN N2IFOBJ_N1BOBJOBID (object id 7229)
8916 TABLE ACCESS BY INDEX ROWID N2IFOBJ
8916 INDEX RANGE SCAN N2IFOBJ_N1BOBJOBID (object id 7229)
106992 INDEX RANGE SCAN N2IFOBJ_N1BOBJOBID (object id 7229)
0 TABLE ACCESS FULL T2SPCREV
756 TABLE ACCESS BY INDEX ROWID N3OBJATTR
135451872 INDEX RANGE SCAN N3OBJATTR_RLTRIGHTRELINDEX (object id 7307)
858060 INDEX RANGE SCAN N3OBJATTR_RLTRIGHTRELINDEX (object id 7307)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
41 SORT (ORDER BY)
41 COUNT (STOPKEY)
41 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'N3OBJATTR'
858817 NESTED LOOPS
756 NESTED LOOPS
106992 HASH JOIN (ANTI)
106992 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'N2IFOBJ'
115909 NESTED LOOPS
8916 NESTED LOOPS
743 NESTED LOOPS
743 NESTED LOOPS
957 NESTED LOOPS
957 NESTED LOOPS
957 NESTED LOOPS
1346 NESTED LOOPS
1346 NESTED LOOPS
1346 HASH JOIN (OUTER)
1346 NESTED LOOPS
1431 NESTED LOOPS
1 NESTED LOOPS
1 NESTED LOOPS
1 NESTED LOOPS
1 NESTED LOOPS
1 NESTED LOOPS
1 TABLE ACCESS
GOAL: ANALYZED (BY INDEX ROWID) OF
'N2CLSATTR'
1 INDEX GOAL:
ANALYZED (UNIQUE SCAN) OF
'N2CLSATTR_N1NAME' (UNIQUE)
1 TABLE ACCESS
GOAL: ANALYZED (BY INDEX ROWID) OF
'N2CLSATTR'
1 INDEX GOAL:
ANALYZED (UNIQUE SCAN) OF
'N2CLSATTR_N1NAME' (UNIQUE)
1 TABLE ACCESS
GOAL: ANALYZED (BY INDEX ROWID) OF
'N2REVGRP'
1 INDEX GOAL:
ANALYZED (RANGE SCAN) OF
'N2REVGRP_N1NAME' (NON-UNIQUE)
1 TABLE ACCESS GOAL:
ANALYZED (BY INDEX ROWID) OF 'N2SPCATTR'
1 INDEX GOAL:
ANALYZED (RANGE SCAN) OF
'N2SPCATTR_OBID_N1STRVALUE' (NON-UNIQUE)
1 TABLE ACCESS GOAL:
ANALYZED (BY INDEX ROWID) OF 'N2SPCATTR'
1 INDEX GOAL:
ANALYZED (RANGE SCAN) OF
'N2SPCATTR_OBID_N1STRVALUE' (NON-UNIQUE)
1 TABLE ACCESS GOAL:
ANALYZED (BY INDEX ROWID) OF 'N2IFOBJ'
2 INDEX GOAL: ANALYZED
(RANGE SCAN) OF 'N2IFOBJ_N1BOBJOBID'
(NON-UNIQUE)
1431 TABLE ACCESS GOAL:
ANALYZED (BY INDEX ROWID) OF 'N3RGRPREV'
1431 INDEX GOAL: ANALYZED
(RANGE SCAN) OF 'N3RGRPREV_RLTLEFTRELINDEX'
(NON-UNIQUE)
1346 TABLE ACCESS GOAL:
ANALYZED (BY INDEX ROWID) OF 'N2SPCREV'
1431 INDEX GOAL: ANALYZED
(UNIQUE SCAN) OF 'PK_N2SPCREV' (UNIQUE)
10 TABLE ACCESS GOAL: ANALYZED
(FULL) OF 'EFT_PUBLISHSTATE'
1346 TABLE ACCESS GOAL: ANALYZED
(BY INDEX ROWID) OF 'N2IFOBJ'
16152 INDEX GOAL: ANALYZED (RANGE
SCAN) OF 'N2IFOBJ_N1BOBJOBID' (NON-UNIQUE)
1346 TABLE ACCESS GOAL: ANALYZED (BY
INDEX ROWID) OF 'N3RGRPREV'
18136 INDEX GOAL: ANALYZED (RANGE
SCAN) OF 'N3RGRPREV_RLTRIGHTRELINDEX'
(NON-UNIQUE)
957 TABLE ACCESS GOAL: ANALYZED (BY
INDEX ROWID) OF 'N2REVGRP'
1346 INDEX GOAL: ANALYZED (UNIQUE
SCAN) OF 'PK_N2REVGRP' (UNIQUE)
957 TABLE ACCESS GOAL: ANALYZED (BY
INDEX ROWID) OF 'N2IFOBJ'
1914 INDEX GOAL: ANALYZED (RANGE SCAN)
OF 'N2IFOBJ_N1BOBJOBID' (NON-UNIQUE)
957 TABLE ACCESS GOAL: ANALYZED (BY INDEX
ROWID) OF 'N3RGRPREV'
12623 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'N3RGRPREV_RLTRIGHTRELINDEX' (NON-UNIQUE)
743 TABLE ACCESS GOAL: ANALYZED (BY INDEX
ROWID) OF 'N2REVGRP'
957 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF
'PK_N2REVGRP' (UNIQUE)
743 TABLE ACCESS GOAL: ANALYZED (BY INDEX
ROWID) OF 'N2IFOBJ'
1486 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'N2IFOBJ_N1BOBJOBID' (NON-UNIQUE)
8916 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID)
OF 'N2IFOBJ'
8916 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'N2IFOBJ_N1BOBJOBID' (NON-UNIQUE)
106992 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'N2IFOBJ_N1BOBJOBID' (NON-UNIQUE)
0 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'T2SPCREV'
756 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'N3OBJATTR'
135451872 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'N3OBJATTR_RLTRIGHTRELINDEX' (NON-UNIQUE)
858060 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'N3OBJATTR_RLTRIGHTRELINDEX' (NON-UNIQUE)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 4 0.00 0.00
SQL*Net more data to client 4 0.00 0.00
direct path write 1136 0.00 0.00
direct path read 1136 0.00 0.00
SQL*Net message from client 4 2.25 4.77
********************************************************************************
select o.owner#,o.name,o.namespace,o.remoteowner,o.linkname,o.subname,
o.dataobj#,o.flags
from
obj$ o where o.obj#=:1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 5 0.00 0.00 0 0 0 0
Fetch 5 0.00 0.00 0 15 0 5
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 11 0.00 0.00 0 15 0 5
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)
********************************************************************************
ALTER SESSION SET EVENTS '10046 trace name context off'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 0 0 0
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 28 (EFDATA)
********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.12 0.12 0 0 0 0
Execute 3 0.00 0.00 0 0 0 0
Fetch 4 200.82 197.71 17040 73288447 0 41
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 9 200.95 197.83 17040 73288447 0 41
Misses in library cache during parse: 2
Misses in library cache during execute: 1
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 5 0.00 0.00
SQL*Net message from client 5 2.25 4.77
SQL*Net more data from client 2 0.00 0.00
SQL*Net more data to client 4 0.00 0.00
direct path write 1136 0.00 0.00
direct path read 1136 0.00 0.00
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 5 0.00 0.00 0 0 0 0
Fetch 5 0.00 0.00 0 15 0 5
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 11 0.00 0.00 0 15 0 5
Misses in library cache during parse: 1
3 user SQL statements in session.
1 internal SQL statements in session.
4 SQL statements in session.
1 statement EXPLAINed in this session.
********************************************************************************
Trace file: eftst01_ora_22441.trc
Trace file compatibility: 9.02.00
Sort options: default
1 session in tracefile.
3 user SQL statements in trace file.
1 internal SQL statements in trace file.
4 SQL statements in trace file.
4 unique SQL statements in trace file.
1 SQL statements EXPLAINed using schema:
EFDATA.prof$plan_table
Default table was used.
Table was created.
Table was dropped.
2575 lines in trace file.
[formatted by moderator]
-
Attachment: tkprof.txt
(Size: 22.88KB, Downloaded 1274 times)
[Updated on: Mon, 14 January 2008 20:13] by Moderator Report message to a moderator |
|
|
|
|
|
| Re: SQL statement takes too long to run [message #293752 is a reply to message #293746] |
Mon, 14 January 2008 19:01  |
sachinp36
Messages: 3
Registered: January 2008
|
Junior Member |
|
|
Sorry about the cut and past in the main message.
Where clause contains criteria that have to be satisfied to
return results from n2spcrev table. Number of rows in
n2spcrev is 2610.
From TKPROF result can we tell which table scan is taking the
longest and can we add any index on that table to reduce the
time taken.
Thanks,
Sachin
|
|
|
|
| Re: SQL statement takes too long to run [message #293755 is a reply to message #293745] |
Mon, 14 January 2008 19:12 |
 |
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
|
Senior Member |
|
|
http://www.orafaq.com/forum/t/88153/0/
Read above URL & learn how to use <code tags>
>Number of rows in n2spcrev is 2610.
& returning
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.12 0.12 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 4 200.82 197.71 17040 73288447 0 41
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 6 200.94 197.83 17040 73288447 0 41
>From TKPROF result can we tell which table scan is taking the longest and can we add any index on that table to reduce the time taken.
I give up. Can we?
Again, In My Opinion, the WHERE clause needs to COMPLETELY overhauled
& the whole UGLY collection of extraneous tables in the existing FROM clause need to be subordinated into the WHERE clause.
It appears you are hoping for a silver bullet, rather than acknowledging that the real root cause is BADLY written SQL!
I strongly suspect some nasty cartesian product between a pair of the myriad of tables within this query.
[Updated on: Mon, 14 January 2008 19:24] by Moderator Report message to a moderator |
|
|
|
|
|
| Re: SQL statement takes too long to run [message #293765 is a reply to message #293759] |
Mon, 14 January 2008 20:27 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
The problem is in these rows of the plan:
756 TABLE ACCESS BY INDEX ROWID N3OBJATTR
135451872 INDEX RANGE SCAN N3OBJATTR_RLTRIGHTRELINDEX (object id 7307)
You are scanning 135M rows from the N3OBJATTR_RLTRIGHTRELINDEX and filtering out all but 756 of them.
Clearly this is not a good index to use.
These are your predicates on N3OBJATTR (oa16)
AND oa16.leftrel = if16.obid
AND oa16.rightrel = sa16.obid
AND oa16.n1terminationdate IS NULL
Assuming index N3OBJATTR_RLTRIGHTRELINDEX is on column oa16.rightrel, we can assume that there are 135M rows that match on that 2nd condition but not on the 1st and 3rd condition.
So which of the other two conditions are causing almost every row to be filtered out?
If it is oa16.leftrel causing the filtering, you need to use an index on that column, or better, a single index on both (leftrel, rightrel).
If it is n1terminationdate IS NULL that is eliminating most of the 135M rows, then you should create a function-based index on NVL2(n1terminationdate, NULL, 1) and change the condition to NVL2(n1terminationdate, NULL, 1) = 1.
Ross Leishman
|
|
|
|
|
|
| Re: SQL statement takes too long to run [message #294220 is a reply to message #294153] |
Wed, 16 January 2008 21:06 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
So do you know n1terminationdate is the restrictive predicate? Or are you guessing?
There may be millions of rows that match just the LEFTREL predicate, and millions of rows that match the RIGHTREL predicate, but only a few that match both. In this case, single column indexes are useless, you need an index containing both columns.
Don't guess at a solution. Know you data and index it accordingly.
Ross Leishman
|
|
|
|
Goto Forum:
Current Time: Sat Nov 23 05:14:17 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
