Saturday, June 20, 2009

Impact of Number of blocks on cost Calculation

I had recently come across,where in an end user found query was running slowly.
The same query is run from multiple session on the server.
select i.Symbol Symbol,
TO_CHAR(i.StartTime,'YYYY-MM-DD,HH24:mi:ss') StartTime,
AvgBidSize,
AvgAskSize
from SCALETW_DBO.RawBidAskSizes t, SCALETW_DBO.IntervalMapTable i
where i.StartTime > TO_DATE('2009-04-02','YYYY-MM-DD') and StartTime < TO_DATE('2009-05-07', 'YYYY-MM-DD')
and Interval = 300
and t.IntervalMapTable_ID = i.IntervalMapTable_ID
and Symbol = :b2 order by i.StartTime

============
Plan Table
============
----------------------------------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
----------------------------------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT | | | | 3492 | |
| 1 | SORT ORDER BY | | 2118 | 83K | 3492 | 00:00:49 |
| 2 | HASH JOIN | | 2118 | 83K | 3491 | 00:00:49 |
| 3 | TABLE ACCESS BY INDEX ROWID | INTERVALMAPTABLE | 2118 | 54K | 55 | 00:00:01 |
| 4 | INDEX RANGE SCAN | TC_INTERVALMAPTABLE2317| 2118 | | 10 | 00:00:01 |
| 5 | TABLE ACCESS FULL | RAWBIDASKSIZES | 6787K | 93M | 3368 | 00:00:48 |
-----------------------------------------------------------------------------------------------------

*****************************
SYSTEM STATISTICS INFORMATION
*****************************
Using NOWORKLOAD Stats
CPUSPEED: 743 millions instruction/sec
IOTFRSPEED: 4096 bytes per millisecond (default is 4096)
IOSEEKTIM: 10 milliseconds (default is 10)
***************************************
Table Stats::
Table: INTERVALMAPTABLE Alias: I
#Rows: 10814605 #Blks: 23917 AvgRowLen: 26.00
Column (#3): INTERVALMAPTABLE_ID(NUMBER)
AvgLen: 7.00 NDV: 10814605 Nulls: 0 Density: 9.2468e-08 Min: 1031906069 Max: 1042684029
Index Stats::
Index: PK_INTERVALMAPTABLE495 Col#: 3
LVLS: 2 #LB: 11598 #DK: 11041507 LB/K: 1.00 DB/K: 1.00 CLUF: 35222.00
Index: SMTEST Col#: 1 4 2
LVLS: 2 #LB: 38782 #DK: 10733526 LB/K: 1.00 DB/K: 1.00 CLUF: 1496237.00
Index: TC_INTERVALMAPTABLE2311 Col#: 4
LVLS: 2 #LB: 23366 #DK: 4331 LB/K: 5.00 DB/K: 54.00 CLUF: 235943.00
Index: TC_INTERVALMAPTABLE2317 Col#: 4 1 2
LVLS: 2 #LB: 36842 #DK: 10695366 LB/K: 1.00 DB/K: 1.00 CLUF: 229583.00
***********************
Table Stats::
Table: RAWBIDASKSIZES Alias: T
#Rows: 6950280 #Blks: 8617 AvgRowLen: 14.00
Column (#3): INTERVALMAPTABLE_ID(NUMBER)
AvgLen: 7.00 NDV: 6950280 Nulls: 0 Density: 1.4388e-07 Min: 1032164991 Max: 1042953912
Index Stats::
Index: PK_RAWBIDASKSIZES508 Col#: 3
LVLS: 2 #LB: 11276 #DK: 7348600 LB/K: 1.00 DB/K: 1.00 CLUF: 20482.00
***************************************
SINGLE TABLE ACCESS PATH
Table: RAWBIDASKSIZES Alias: T
Card: Original: 6950280 Rounded: 6950280 Computed: 6950280.00 Non Adjusted: 6950280.00
Access Path: TableScan
Cost: 3368.06 Resp: 3368.06 Degree: 0
Cost_io: 3233.00 Cost_cpu: 1404507597
Resp_io: 3233.00 Resp_cpu: 1404507597
Best:: AccessPath: TableScan
Cost: 3368.06 Degree: 1 Resp: 3368.06 Card: 6950280.00 Bytes: 0
***************************************
CPUSPEEDNW : 743
IOSeektim : 10
IOTrfSpeed : 4096
DFMRC : 8
SReadtim = IO Seektim + (db_block_size/IO Transfer Speed) = 10 + (16384/4096) = 14
MReadtim = IO Seektim + dfmrc * (db_block_size/IO Transfer Speed) = 10 + 8 * 4 = 42
#Mrds = 8617/8 -- this is default DFMRC = 1077.125
IO Cost = 1077.125 * (42/14) = 1077.125 * 3 = 3232 + 1 = 3233
CPU Cost = 1404507597/(743000*14) = 1404507597 / 10402000 = 135
Total CPU Cost = 3233 + 135 = 3368


I just increased the tried to make FULL Table Scan costlier by increasing num_blocks,
and let the optimizer use the index ,to check will that improve the performance.


execute dbms_stats.set_table_stats(OWNNAME=>'SCALETW_DBO',tabname=>'RAWBIDASKSIZES',numblks=>15000);

----------------------------------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
----------------------------------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT | | | | 3492 | |
| 1 | NESTED LOOPS | | 2118 | 83K | 3492 | 00:00:49 |
| 2 | TABLE ACCESS BY INDEX ROWID | INTERVALMAPTABLE | 2118 | 54K | 55 | 00:00:01 |
| 3 | INDEX RANGE SCAN | TC_INTERVALMAPTABLE2317| 2118 | | 10 | 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID | RAWBIDASKSIZES | 1 | 17 | 2 | 00:00:41 |
| 5 | INDEX UNIQUE SCAN | PK_RAWBIDASKSIZES508 | 1 | | 1 | 00:00:01 |
-----------------------------------------------------------------------------------------------------

I increased the num blocks of the table and we can see the query started using index. We found that query which was taking more than 1 hour, now completes in 2 minutes.I just locked the stats of the table for few weeks and then unlocked it and
regenerated the stats. I found that as volume of the table increased,as a result num blocks also increased and query found using index.