Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: same sql, 160ms with rownum 13968, 25s with rownum 13969

Re: same sql, 160ms with rownum 13968, 25s with rownum 13969

From: seb <seb_at_h3d.com>
Date: 20 Nov 2001 06:21:19 -0800
Message-ID: <d85a771c.0111200621.631f1c23@posting.google.com> 





> Maybe the disparity in execution times is because some of the data was

> cached in the SGA (look at the sorts).  The use of sql_trace is an OK

> start, but to **really** analyze what is going on, you need to use

> tkprof.




rownum 'limit' change today.


here my tkprof ouput (with explain). Maybe i don't know read the ouput
but it doesn't explain me where is the problem and wky i have 25second
diff.



select count(idapplicant) from applicants WHERE
  applicants.idapplicantstatus != '3' AND applicants.idapplicantstatus
!='6'


AND applicants.idapplicantstatus != '12' AND Applicants.Typeofjobkey3
= '1'


  AND Applicants.Applicantentrydate >= '03-AOU-2001'
  AND  (



  Applicants.Solicitationtype ='0' OR


  Applicants.Idapplicant IN


   (SELECT Applications.Idapplicant FROM Applications WHERE
Applications.Applicationtreated ='2'


   MINUS



   SELECT Applications.Idapplicant FROM Applications,Applicants
   WHERE Applicants.Idapplicant=Applications.Idapplicant

   and Applicants.Solicitationtype ='1' and
Applications.Applicationtreated !='2')
  OR ( Applicants.Solicitationtype ='1' AND


Applicants.Idapplicantstatus


='12' )


)


and rownum <=13875


call     count       cpu    elapsed       disk      query    current  
     rows




------- ------  -------- ---------- ---------- ---------- ---------- 



Parse        1      0.03       0.03          0          0          0  
        0
Execute      1      0.00       0.00          0          0          0  
        0
Fetch        2      0.16       0.16          0        135          4  
        1




------- ------  -------- ---------- ---------- ---------- ---------- 



total        4      0.19       0.19          0        135          4  
        1





Misses in library cache during parse: 1


Optimizer goal: CHOOSE


Parsing user id: 40  (CFPAPP27)



Rows     Row Source Operation

-------  ---------------------------------------------------
      1  SORT AGGREGATE 




  13875   COUNT STOPKEY 



  13875    FILTER 


  13875     INDEX FAST FULL SCAN (object id 32107)
      0     MINUS 
      0      SORT UNIQUE 
      0       INDEX FAST FULL SCAN (object id 30798)
      0      SORT UNIQUE 
      0       NESTED LOOPS 
      0        TABLE ACCESS BY INDEX ROWID APPLICANTS 
      0         INDEX UNIQUE SCAN (object id 30793)
      0        INDEX FAST FULL SCAN (object id 30798)


Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: CHOOSE
      1   SORT (AGGREGATE)
  13875    COUNT (STOPKEY)
  13875     FILTER
  13875      INDEX (FAST FULL SCAN) OF 'APPLICANTS_IDX_005'


(NON-UNIQUE)



      0      MINUS
      0       SORT (UNIQUE)
      0        INDEX   GOAL: ANALYZED (FAST FULL SCAN) OF 
                   'APPLICATIONS_IDX_010' (NON-UNIQUE)
      0       SORT (UNIQUE)
      0        NESTED LOOPS
      0         TABLE ACCESS   GOAL: ANALYZED (BY INDEX ROWID) OF 
                    'APPLICANTS'
      0          INDEX   GOAL: ANALYZED (UNIQUE SCAN) OF 
                     'PK_APPLICANTS' (UNIQUE)
      0         INDEX   GOAL: ANALYZED (FAST FULL SCAN) OF 
                    'APPLICATIONS_IDX_010' (NON-UNIQUE)

********************************************************************************





select count(idapplicant) from applicants WHERE
  applicants.idapplicantstatus != '3' AND applicants.idapplicantstatus
!='6'


AND applicants.idapplicantstatus != '12' AND Applicants.Typeofjobkey3
= '1'


  AND Applicants.Applicantentrydate >= '03-AOU-2001'
  AND  (



  Applicants.Solicitationtype ='0' OR


  Applicants.Idapplicant IN


   (SELECT Applications.Idapplicant FROM Applications WHERE
Applications.Applicationtreated ='2'


   MINUS



   SELECT Applications.Idapplicant FROM Applications,Applicants
   WHERE Applicants.Idapplicant=Applications.Idapplicant

   and Applicants.Solicitationtype ='1' and
Applications.Applicationtreated !='2')
  OR ( Applicants.Solicitationtype ='1' AND


Applicants.Idapplicantstatus


='12' )


)


and rownum <=13876


call     count       cpu    elapsed       disk      query    current  
     rows




------- ------  -------- ---------- ---------- ---------- ---------- 



Parse        1      0.02       0.02          0          0          0  
        0
Execute      1      0.00       0.00          0          0          0  
        0
Fetch        2     25.70      25.70          0      62468       3924  
        1




------- ------  -------- ---------- ---------- ---------- ---------- 



total        4     25.72      25.72          0      62468       3924  
        1





Misses in library cache during parse: 1


Optimizer goal: CHOOSE


Parsing user id: 40  (CFPAPP27)



Rows     Row Source Operation

-------  ---------------------------------------------------
      1  SORT AGGREGATE 




  13876   COUNT STOPKEY 



  13876    FILTER 


  14366     INDEX FAST FULL SCAN (object id 32107)
    490     MINUS 
      0      SORT UNIQUE 
      0       INDEX FAST FULL SCAN (object id 30798)
    490      SORT UNIQUE 
    633       NESTED LOOPS 
    980        TABLE ACCESS BY INDEX ROWID APPLICANTS 
    980         INDEX UNIQUE SCAN (object id 30793)
    633        INDEX FAST FULL SCAN (object id 30798)


Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: CHOOSE
      1   SORT (AGGREGATE)
  13876    COUNT (STOPKEY)
  13876     FILTER
  14366      INDEX (FAST FULL SCAN) OF 'APPLICANTS_IDX_005'


(NON-UNIQUE)



    490      MINUS
      0       SORT (UNIQUE)
      0        INDEX   GOAL: ANALYZED (FAST FULL SCAN) OF 
                   'APPLICATIONS_IDX_010' (NON-UNIQUE)
    490       SORT (UNIQUE)
    633        NESTED LOOPS
    980         TABLE ACCESS   GOAL: ANALYZED (BY INDEX ROWID) OF 
                    'APPLICANTS'
    980          INDEX   GOAL: ANALYZED (UNIQUE SCAN) OF 
                     'PK_APPLICANTS' (UNIQUE)
    633         INDEX   GOAL: ANALYZED (FAST FULL SCAN) OF 
                    'APPLICATIONS_IDX_010' (NON-UNIQUE)




********************************************************************************





OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS



call     count       cpu    elapsed       disk      query    current  
     rows




------- ------  -------- ---------- ---------- ---------- ---------- 



Parse        3      0.05       0.05          0          0          0  
        0
Execute      4      0.00       0.02          0          0          0  
        0
Fetch        4     25.86      25.86          0      62603       3928  
        2




------- ------  -------- ---------- ---------- ---------- ---------- 



total       11     25.91      25.93          0      62603       3928  
        2





Misses in library cache during parse: 2
Received on Tue Nov 20 2001 - 08:21:19 CST












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US