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

seb wrote:
>
> hi,
>
> without add a rownum in my sql query, i have in result '17731' and
> execution time is 1m26s .
> when i add a rownum (and rownum <= xx) to 13968 or inferior, it take 160
> ms to execute. But when i put 13969 to rownum, it take 25s !!
>
> here statistic for each query :
>
> with 'and rownum <= 13968' :
>
> 0 recursive calls
> 4 db block gets
> 135 consistent gets
> 0 physical reads
> 0 redo size
> 379 bytes sent via SQL*Net to client
> 424 bytes received via SQL*Net from client
> 2 SQL*Net roundtrips to/from client
> 6 sorts (memory)
> 0 sorts (disk)
> 1 rows processed
>
> with 'and rownum <= 13969' :
>
> 0 recursive calls
> 3916 db block gets
> 62341 consistent gets
> 0 physical reads
> 0 redo size
> 379 bytes sent via SQL*Net to client
> 424 bytes received via SQL*Net from client
> 2 SQL*Net roundtrips to/from client
> 984 sorts (memory)
> 0 sorts (disk)
> 1 rows processed
>
> the executin plan is the same for both query.
>
> With sql analyse, i have ask to generate a index script. after execute
> the generated sql script for index, executions times with or without
> rownum are very fast.
>
> Now, i would like to know exactly why there is a big execution time
> difference between these 2 sql query. How determine the problem ?
> analyse tools ? How know if a table or index need a rebuild ?
>
> I use oracle 8.1.6, size of my base is only 80M.
>
> sql:
> 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' )
> )
>
> thx
> seb

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.

-- 
Ron Reidy
Oracle DBA
Reidy Consulting, L.L.C.