RE: *Measuring sql performance (elapsed time and scalability) by number of logical reads
- From: "Cary Millsap" <cary.millsap@xxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 2 May 2006 09:45:36 -0500
"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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Radoulov, Dimitre
Sent: Tuesday, May 02, 2006 9:28 AM
To: Wolfgang Breitling
Cc: oracle-l@xxxxxxxxxxxxx
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
|
Rows | Bytes | Cost (%CPU)|
------------------------------------------------------------------------
--------------------------------
| 0 | SELECT STATEMENT |
|
23 | 1472 | 2533 (4)|
|* 1 | VIEW |
|
23 | 1472 | |
|* 2 | COUNT STOPKEY |
|
| | |
| 3 | VIEW |
|
199 | 10149 | |
|* 4 | SORT UNIQUE STOPKEY |
|
199 | 24079 | 2532 (4)|
|* 5 | HASH JOIN |
|
199 | 24079 | 2530 (4)|
|* 6 | INDEX FAST FULL SCAN | XIF01LESSICO_PN
|
2493 | 62325 | 74 (10)|
|* 7 | HASH JOIN |
|
20168 | 1890K| 2453 (4)|
| 8 | NESTED LOOPS |
|
20168 | 1516K| 2192 (2)|
| 9 | NESTED LOOPS |
|
1070 | 60990 | 27 (4)|
| 10 | NESTED LOOPS |
|
4 | 148 | 11 (10)|
| 11 | TABLE ACCESS BY INDEX ROWID| AN_TELAI
|
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
|
168K| 3129K| 236 (14)|
------------------------------------------------------------------------
--------------------------------
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")
LIKE '1118647')
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")
14 - access("FDC"."PV_CODE"="VPT"."PV_CODE")
15 - access("VPT"."COD_TAVOLA_GRAFICA"="ATP"."COD_TAVOLA_GRAFICA")
Regards,
Dimitre
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
