execution plan and stale statistics
905989 Jan 4, 2013 10:22 AMHi Gurus,
Oracle version 10.2.0.4.
Because of issues with skewed statistics DBAs had locked stats on a table. In short the stats were left stale for couple of years
The stats were as follows:
Following update stats on the loaded copy of schema into test, the figures are much healthier
I ran a piece of query before and after updating stats and the following plans were noted.
Before stats done
Thanks
Oracle version 10.2.0.4.
Because of issues with skewed statistics DBAs had locked stats on a table. In short the stats were left stale for couple of years
The stats were as follows:
TABLE_NAME NUM_ROWS BLOCKS LAST_ANALYZED Locked
------------------------------ ---------- ---------- ------------------- -----
TABLE 60010840 4759487 07/11/2010 06:10:53 ALL
Actual rows
----------
101881527
So we had state stats Following update stats on the loaded copy of schema into test, the figures are much healthier
TABLE_NAME NUM_ROWS BLOCKS LAST_ANALYZED Locked
------------------------------ ---------- ---------- ------------------- -----
TABLE 101608528 8457437 04/01/2013 08:30:05
Actual rows
----------
101608528
Note that the NUM_ROWS and ACTUAL_ROWS now tally I ran a piece of query before and after updating stats and the following plans were noted.
Before stats done
18093 rows selected.
Elapsed: 00:06:41.71
Execution Plan
----------------------------------------------------------
Plan hash value: 2046255496
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | E-Bytes | Cost (%CPU)| E-Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 1800 | 64 (2) | 00:00:01 |
| 1 | MERGE JOIN CARTESIAN | | 10 | 1800 | 64 (2) | 00:00:01 |
|* 2 | TABLE ACCESS FULL | TABLE2 | 1 | 37 | 11 (0) | 00:00:01 |
| 3 | BUFFER SORT | | 10 | 1430 | 53 (2) | 00:00:01 |
|* 4 | TABLE ACCESS BY INDEX ROWID| TABLE | 10 | 1430 | 53 (2) | 00:00:01 |
|* 5 | INDEX RANGE SCAN | TABLE_IDX1 | 35 | | 23 (0) | 00:00:01 |
-----------------------------------------------------------------------------------------------------
After stats done18093 rows selected.
Elapsed: 00:05:00.70
Execution Plan
----------------------------------------------------------
Plan hash value: 2046255496
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | E-Bytes | Cost (%CPU)| E-Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3894 | 699K | 20058 (2)| 00:04:01 |
| 1 | MERGE JOIN CARTESIAN | | 3894 | 699K | 20058 (2)| 00:04:01 |
|* 2 | TABLE ACCESS FULL | TABLE2 | 1 | 37 | 11 (0)| 00:00:01 |
| 3 | BUFFER SORT | | 3850 | 552K | 20047 (2)| 00:04:01 |
|* 4 | TABLE ACCESS BY INDEX ROWID| TABLE | 3850 | 552K | 20047 (2)| 00:04:01 |
|* 5 | INDEX RANGE SCAN | TABLE_IDX1 | 14811 | | 8722 (3)| 00:01:45 |
-----------------------------------------------------------------------------------------------------
Now my question is that although execution plans are the same before and after updating stats, the estimated figures are much more realistic following update stats? Additionlly is my understanding correct that Oracle expected 10 rows returned with old stats whereas after stats update it expects 3894 rows to be returned by the query. In reality the query returned 18093 rows. There has been an improved timing of 5 minutes compared to 6min 41 sec before. a gain of 25%?Thanks