| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: same sql, 160ms with rownum 13968, 25s with rownum 13969
Three things to note:
The solution is in the line
NNNNN INDEX FAST FULL SCAN (object id 32107)
The timing is a semi-random based on the condition:
> Applicants.Solicitationtype ='0' OR > Applicants.Idapplicant IN (complex subquery) combined with a rownum limit.
In the fast cases, you get enough data where the
> Applicants.Solicitationtype ='0'
to meet your rownum requirements and never have
to exercise the subquery.
In the slow cases, you collect many rows from
NNNNN INDEX FAST FULL SCAN (object id 32107) where
> Applicants.Solicitationtype !='0'
so you have to exercise the subquery to determine whether
or not the row meets your requirements. If you compare
the number of SORTS with the number of rows in the
> 490 MINUS
line, you will probably find that (plus or minus a few) the
SORT count is twice the MINUS count. This is where
the extra CPU is going.
Oracle is performing the FILTER command as a nested loop subquery.
-- Jonathan Lewis http://www.jlcomp.demon.co.uk Host to The Co-Operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html Author of: Practical Oracle 8i: Building Efficient Databases Screen saver or Life saver: http://www.ud.com Use spare CPU to assist in cancer research. seb wrote in message ...Received on Tue Nov 20 2001 - 09:38:00 CST
>> 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
 |
 |