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 Go to next message
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 221 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 #293746 is a reply to message #293745] Mon, 14 January 2008 18:08 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
>I have also attached the file as txt attachment.
That was the only saving grace for this whole post because at least it was readable.

Whoever wrote the SQL should be given a GOLD Star for doing a whole lot of work for returning 24 rows from a single table.

>FROM n2spcrev obj
IMO, should be the whole & complete FROM clause.
I suspect that the result set could be obtained after a single FTS of table N2SPCREV.
A whole lot of time is being spent withing the WHERE clause.

How many total rows in N2SPCREV table?
Re: SQL statement takes too long to run [message #293752 is a reply to message #293746] Mon, 14 January 2008 19:01 Go to previous messageGo to next message
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 Go to previous messageGo to next message
BlackSwan
Messages: 25039
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 #293759 is a reply to message #293745] Mon, 14 January 2008 19:45 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
> AND obj.n1uniquekey LIKE '%'
Please explain what the line above contributes to the result set.
Re: SQL statement takes too long to run [message #293765 is a reply to message #293759] Mon, 14 January 2008 20:27 Go to previous messageGo to next message
rleishman
Messages: 3724
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 #294153 is a reply to message #293765] Wed, 16 January 2008 10:54 Go to previous messageGo to next message
sachinp36
Messages: 3
Registered: January 2008
Junior Member
Ross,

Thanks for your answer. It looks promising. I am going to try
creating a new functional index on n1terminationdate column.
The database already has individual indexes on Leftrel and Rightrel.

Sachin
Re: SQL statement takes too long to run [message #294220 is a reply to message #294153] Wed, 16 January 2008 21:06 Go to previous message
rleishman
Messages: 3724
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
Previous Topic: last queries
Next Topic: Query wih bind variables
Goto Forum:
  


Current Time: Tue Dec 06 04:29:03 CST 2016

Total time taken to generate the page: 0.13728 seconds