| 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
> 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 PVAL1PVAL2
------------------------------ ------------------------------ ---------- ---------------------------------------- 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
-- http://www.freelists.org/webpage/oracle-lReceived on Tue May 02 2006 - 09:27:39 CDT
 |
 |