| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: *Measuring sql performance (elapsed time and scalability) by number of logical reads
"You can't tell how long something took by counting how many times it
happened."
This one is actually my own.
Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com
Nullius in verba
Hotsos Symposium 2007 / March 4-8 / Dallas Visit www.hotsos.com for curriculum and schedule details...
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Radoulov, Dimitre
Sent: Tuesday, May 02, 2006 9:28 AM
To: Wolfgang Breitling
Cc: oracle-l_at_freelists.org
Subject: Re: *Measuring sql performance (elapsed time and scalability)
by number of logical reads
> To quote Cary Millsap (who, I believe quotes someone else) "you can't
tell
> how
> long something took by counting how often it occured". Trying to use
any
> count
> (lio, pio, BCHR, ...) as s substitute for elapsed time is misguided
from
> the start.
> Your summary below contains the answer. Remember
>
> response time (elapsed time) = service time + wait time.
>
> as an aside, note that there are no counts anywhere in that formula.
In
> your
> case the service time (cpu) is the difference. Due to the
peculiarities of
> cpu
> time accounting, wait time is -0.01 for both of your queries, but plan
1
> takes 4
> times the cpu to do the work.
>
> Just another example why it is important to gather system statistics
so
> that the
> CBO will consider cpu time when making a plan selection. That doesn't
mean
> he/she/it will get it right every time, but there is a better chance
with
> cpu
> costing.
Thanks,
but the CPU costing is on.
SNAME PNAME PVAL1
PVAL2
------------------------------ ------------------------------ ---------- ---------------------------------------- SYSSTATS_INFO STATUS COMPLETED SYSSTATS_INFO FLAGS 1 SYSSTATS_MAIN SREADTIM 1.373 SYSSTATS_MAIN MREADTIM 5.556 SYSSTATS_MAIN CPUSPEED 585 SYSSTATS_MAIN MBRC 12 SYSSTATS_MAIN MAXTHR 21000192 SYSSTATS_MAIN SLAVETHR -1
PLAN_TABLE_OUTPUT
| Id | Operation | Name| Rows | Bytes | Cost (%CPU)|
| 0 | SELECT STATEMENT | | 23 | 1472 | 43215 (1)| |* 1 | VIEW | | 23 | 1472 | | |* 2 | COUNT STOPKEY | | | | | | 3 | VIEW | | 199 | 10149 | | |* 4 | SORT UNIQUE STOPKEY | | 199 | 24079 | 43213 (1)| | 5 | CONCATENATION | | | | | | 6 | TABLE ACCESS BY INDEX ROWID | LESSICO_PN | 1 | 25 | 2 (50)| | 7 | NESTED LOOPS | | 1 | 62 | 15 (7)| | 8 | NESTED LOOPS | | 2 | 162 | 17 (6)| | 9 | NESTED LOOPS | | 18 | 1818 | 21 (5)| | 10 | NESTED LOOPS | | 1 | 121 | 95 (4)| | 11 | NESTED LOOPS | | 4 | 148 | 11 (10)| | 12 | TABLE ACCESS BY INDEX ROWID| AN_TELAI | 3 | 57 | 5 (20)| |* 13 | INDEX RANGE SCAN | XIF04AN_TELAI | 1 | | 4 (25)| |* 14 | INDEX RANGE SCAN | XIF02FILTRO_DATI_CATALOGO | 1 | 18 | 3 (34)| |* 15 | INDEX RANGE SCAN | PK_VP_TAVOLE | 1 | 20 | 5 (20)| |* 16 | INDEX RANGE SCAN | XPKASSOC_TAVOLE_PARTS | 8 | 160 | 3 (34)| |* 17 | INDEX RANGE SCAN | XPKAN_PARTS | 3 | 57 | 3 (34)| |* 18 | INDEX RANGE SCAN | XPKLESSICO_PN | 1 | | 2 (50)| | 19 | NESTED LOOPS | | 1 | 121 | 95 (4)| | 20 | NESTED LOOPS | | 18 | 1818 | 21 (5)| | 21 | NESTED LOOPS | | 2 | 162 | 17 (6)| | 22 | NESTED LOOPS | | 1 | 62 | 15 (7)| | 23 | NESTED LOOPS | | 4 | 148 | 11 (10)| | 24 | TABLE ACCESS BY INDEX ROWID | AN_TELAI | 3 | 57 | 5 (20)| |* 25 | INDEX RANGE SCAN | XIF04AN_TELAI | 1 | | 4 (25)| |* 26 | INDEX RANGE SCAN | XIF02FILTRO_DATI_CATALOGO | 1 | 18 | 3 (34)| |* 27 | INDEX RANGE SCAN | XIF01LESSICO_PN | 1 | 25 | 2 (50)| |* 28 | INDEX RANGE SCAN | XIF01AN_PARTS | 3 | 57 | 3 (34)| |* 29 | INDEX RANGE SCAN | XIF01ASSOC_TAVOLE_PARTS | 8 | 160 | 3 (34)| |* 30 | INDEX RANGE SCAN | PK_VP_TAVOLE | 1 | 20 | 5 (20)| ------------------------------------------------------------------------ ----------------------------------
Predicate Information (identified by operation id):
1 - filter("B"."R">0)
2 - filter(ROWNUM<=23)
4 - filter(ROWNUM<=23)
13 - access("ATI"."TARGA"='FNZ8243')
14 - access("FDC"."ID_SUBCATALOGO"=0 AND
"FDC"."PV_CODE"="ATI"."PV_CODE")
15 - access("FDC"."PV_CODE"="VPT"."PV_CODE")
16 - access("VPT"."COD_TAVOLA_GRAFICA"="ATP"."COD_TAVOLA_GRAFICA")
17 - access("AP"."PART_NUMBER"="ATP"."PART_NUMBER")
filter(TRIM("AP"."PART_NUMBER") LIKE '1118647')
18 - access("LSC"."CODICE_LESSICO"="AP"."CODICE_LESSICO" AND
"LSC"."LANGUAGE_CODE"=1)
25 - access("ATI"."TARGA"='FNZ8243')
26 - access("FDC"."ID_SUBCATALOGO"=0 AND
"FDC"."PV_CODE"="ATI"."PV_CODE")
27 - access("LSC"."DS_LUNGA" LIKE '1118647' AND
"LSC"."LANGUAGE_CODE"=1)
28 - access("LSC"."CODICE_LESSICO"="AP"."CODICE_LESSICO")
filter(LNNVL(TRIM("AP"."PART_NUMBER") LIKE '1118647'))
29 - access("AP"."PART_NUMBER"="ATP"."PART_NUMBER")
30 - access("FDC"."PV_CODE"="VPT"."PV_CODE" AND
"VPT"."COD_TAVOLA_GRAFICA"="ATP"."COD_TAVOLA_GRAFICA")
filter("VPT"."COD_TAVOLA_GRAFICA"="ATP"."COD_TAVOLA_GRAFICA")
PLAN_TABLE_OUTPUT
| Id | Operation | Name|
| 0 | SELECT STATEMENT ||
|* 1 | VIEW | | 23 | 1472 | | |* 2 | COUNT STOPKEY | | | | | | 3 | VIEW | | 199 | 10149 | | |* 4 | SORT UNIQUE STOPKEY ||
|* 5 | HASH JOIN ||
|* 7 | HASH JOIN ||
| 8 | NESTED LOOPS ||
| 9 | NESTED LOOPS ||
| 10 | NESTED LOOPS ||
3 | 57 | 5 (20)| |* 12 | INDEX RANGE SCAN | XIF04AN_TELAI | 1 | | 4 (25)| |* 13 | INDEX RANGE SCAN | XIF02FILTRO_DATI_CATALOGO | 1 | 18 | 3 (34)| |* 14 | INDEX RANGE SCAN | PK_VP_TAVOLE | 254 | 5080 | 5 (20)| |* 15 | INDEX RANGE SCAN | XPKASSOC_TAVOLE_PARTS | 19 | 380 | 3 (34)| | 16 | INDEX FAST FULL SCAN | XPKAN_PARTS|
Predicate Information (identified by operation id):
1 - filter("B"."R">0)
2 - filter(ROWNUM<=23)
4 - filter(ROWNUM<=23)
5 - access("LSC"."CODICE_LESSICO"="AP"."CODICE_LESSICO")
filter("LSC"."DS_LUNGA" LIKE '1118647' OR
TRIM("AP"."PART_NUMBER")
6 - filter("LSC"."LANGUAGE_CODE"=1)
7 - access("AP"."PART_NUMBER"="ATP"."PART_NUMBER")
12 - access("ATI"."TARGA"='FNZ8243')
13 - access("FDC"."ID_SUBCATALOGO"=0 AND
"FDC"."PV_CODE"="ATI"."PV_CODE")
Regards,
Dimitre
-- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Tue May 02 2006 - 09:45:36 CDT
 |
 |