Some folks believe that the optimizer will not use stale
statistics. This is simply not true.
The stats being stale or not is only used by the stats
collection job to know which tables should have stats collected on them. Below is a script you can use to see this is
true. I’ll go thru the key points in the
script to show what is going on. I was
using Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 on a windows
laptop for this test. In this post I'm only showing the core information form the run for brevity. Run the script to see all the details.
At the top of the script a table is created as a copy of the
good old EMPLOYEES
table from the sample schema. And then
collect stats on it, and run a query.
You can see that the stats are being used, the estimated rows is 107
which is the number for rows in the table.
SQL>
SELECT /*+ qb_name(main) */ COUNT(*) FROM STALE_TAB;
COUNT(*)
----------
----------
107
Here is the plan:
SELECT /*+
qb_name(main) */ COUNT(*) FROM STALE_TAB
Plan hash
value: 3396615767
------------------------------------------------------------------------
| Id | Operation | Name | Rows
| Cost (%CPU)| Time |
------------------------------------------------------------------------
| 0 | SELECT STATEMENT |
| | 3 (100)| |
| 1 |
SORT AGGREGATE | |
1 | | |
| 2 |
TABLE ACCESS FULL| STALE_TAB |
107 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------
14 rows
selected.
A look into the 10053 trace for this query shows this for the
base statistics. These are the stats on
the table just created and used.
***************************************
BASE
STATISTICAL INFORMATION
***********************
Table
Stats::
Table: STALE_TAB Alias: STALE_TAB
#Rows: 107
SSZ: 0 LGR: 0 #Blks:
5 AvgRowLen: 69.00
NEB: 0 ChainCnt: 0.00
ScanRate: 0.00 SPC: 0
RFL: 0 RNF: 0 CBK: 0
CHR: 0 KQDFLG: 1
#IMCUs: 0
IMCRowCnt: 0 IMCJournalRowCnt:
0 #IMCBlocks: 0 IMCQuotient: 0.000000
Then in the script is an update that updates all rows in the
table. The stats are now stale.
SQL>
UPDATE STALE_TAB SET SALARY = SALARY *.15;
107 rows
updated.
SQL>
commit;
Commit
complete.
SQL> --
show the staleness of the stats
SQL>
EXEC DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
PL/SQL procedure
successfully completed.
SQL>
SELECT '****************' STARS, STALE_STATS FROM USER_TAB_STATISTICS WHERE
TABLE_NAME = 'STALE_TAB';
STARS STALE_STATS
----------------
---------------
****************
YES
1 row
selected.
The script then runs a new select on the table. This is using the stale stats. If it were not using the stale stats it would
have to do one of two things. Either use
dynamic sampling or use the default statistics.
It is not doing either of these. If it was
doing dynamic sampling there would be a note about that in the plan, there
isn’t. If it were using the default
statistics the base number of rows for the table would be something like 3186 or 8168, which are the default number of rows in
an 8K block size database, which what this database has. (Side note: the 3186 number appears to be
used for a real table which has not been analyzed, and the 8168 number used for
a casted table.)
Here is the plan:
SQL_ID g63r4gfua0wtq, child number 0
-------------------------------------
SELECT /*+
QB_NAME(MAIN) */ DEPARTMENT_ID, SUM(SALARY) FROM STALE_TAB
GROUP BY
DEPARTMENT_ID
Plan hash
value: 1068443980
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows
| Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT |
| | |
4 (100)| |
| 1 |
HASH GROUP BY | |
11 | 77 | 4
(25)| 00:00:01 |
| 2 |
TABLE ACCESS FULL| STALE_TAB |
107 | 749 | 3
(0)| 00:00:01 |
--------------------------------------------------------------------------------
15 rows
selected.
And here are the stats from the 10053 trace of this above
query, notice this time it pulled the stats on the DEPARTMENT_ID
column. This is because that column is
used in the group by clause. And remember
this is when the stats are stale.
***************************************
BASE
STATISTICAL INFORMATION
***********************
Table
Stats::
Table: STALE_TAB Alias: STALE_TAB
#Rows: 107
SSZ: 0 LGR: 0 #Blks:
5 AvgRowLen: 69.00
NEB: 0 ChainCnt: 0.00
ScanRate: 0.00 SPC: 0
RFL: 0 RNF: 0 CBK: 0
CHR: 0 KQDFLG: 1
#IMCUs: 0
IMCRowCnt: 0 IMCJournalRowCnt:
0 #IMCBlocks: 0 IMCQuotient: 0.000000
Column (#11): DEPARTMENT_ID(NUMBER)
AvgLen: 3 NDV: 11 Nulls: 1 Density:
0.090909 Min: 0.000000 Max: 10.000000
Notice there is nothing saying that the stats
are stale. The optimizer uses stats as
long as they are there, stale or not.
Below this in the script the stats are dropped and another
query is run. Now the optimizer has to
do dynamic sampling since there are no stats.
However the select that ran the very first time is still in the library
cache and still runs without a new parse.
Dropping stats will not invalidate plans that are in the cache.
SQL>
EXEC DBMS_STATS.DELETE_TABLE_STATS ('OP', 'STALE_TAB');
PL/SQL
procedure successfully completed.
SQL>
EXEC DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
PL/SQL
procedure successfully completed.
SQL>
SELECT '****************' STARS, STALE_STATS FROM USER_TAB_STATISTICS WHERE
TABLE_NAME = 'STALE_TAB';
STARS STALE_STATS
----------------
---------------
****************
NULL
1 row
selected.
. . .
SELECT /*+
QB_NAME(MAIN) */ DEPARTMENT_ID, COUNT(*) FROM STALE_TAB
GROUP BY
DEPARTMENT_ID
Plan hash
value: 1068443980
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows
| Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 4 (100)| |
| 1 |
HASH GROUP BY | |
107 | 1391 | 4
(25)| 00:00:01 |
| 2 |
TABLE ACCESS FULL| STALE_TAB |
107 | 1391 | 3
(0)| 00:00:01 |
--------------------------------------------------------------------------------
Note
-----
- dynamic statistics used: dynamic sampling
(level=2)
19 rows
selected.
And here is the
first query being run again after the stats are dropped:
SELECT /*+
qb_name(main) */ COUNT(*) FROM STALE_TAB
Plan hash
value: 3396615767
------------------------------------------------------------------------
| Id | Operation | Name | Rows
| Cost (%CPU)| Time |
------------------------------------------------------------------------
| 0 | SELECT STATEMENT |
| | 3 (100)| |
| 1 |
SORT AGGREGATE | |
1 | | |
| 2 |
TABLE ACCESS FULL| STALE_TAB |
107 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------
14 rows
selected.
* * * * * * * * * * * * * * * * * * * * * * *
* * * * *
Here is the full script if you’d like to try it yourself:
rem demo to
show stale stats are used by the optimizer
rem
November 2018 RVD
rem file
name stale_test.sql
rem
set NULL
NULL
set long
255
set lines
255
set
serveroutput off
set echo on
feedback on termout on heading on
COLUMN
STALE_STATS FORMAT A15
-- create
the test table
-- this
uses the standard EMPLOYEES table as a base from the sample schema
DROP TABLE
STALE_TAB PURGE;
CREATE
TABLE STALE_TAB AS SELECT * FROM EMPLOYEES;
EXEC
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'OP',TABNAME
=>'STALE_TAB',METHOD_OPT=>'FOR ALL COLUMNS SIZE 1');
EXEC
DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
-- run a
query on it and show the plan
SELECT /*+
qb_name(main) */ COUNT(*) FROM STALE_TAB;
-- capture
the SQL_ID
COLUMN
PREV_SQL_ID NEW_VALUE PSQLID
COLUMN
PREV_CHILD_NUMBER NEW_VALUE PCHILDNO
SELECT
PREV_SQL_ID, PREV_CHILD_NUMBER FROM V$SESSION
WHERE
AUDSID = USERENV('SESSIONID');
-- Showing
the plan
SELECT
PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR
('&PSQLID','&PCHILDNO','TYPICAL NOTE'));
-- create a
10053 trace file from the sql
EXECUTE
DBMS_SQLDIAG.DUMP_TRACE(P_SQL_ID=>'&PSQLID', P_CHILD_NUMBER=>'&PCHILDNO', -
P_COMPONENT=>'Optimizer',
P_FILE_ID=>'STALE01_TEST_'||TO_CHAR(SYSDATE, 'HH24MISS'));
-- show the
staleness of the stats
-- stars
are there just to make it eaiser to see in the output
EXEC
DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
SELECT
'****************' STARS, STALE_STATS FROM USER_TAB_STATISTICS WHERE TABLE_NAME
= 'STALE_TAB';
-- update
every row in the table
UPDATE
STALE_TAB SET SALARY = SALARY *.15;
COMMIT;
-- show the
staleness of the stats
EXEC
DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
SELECT
'****************' STARS, STALE_STATS FROM USER_TAB_STATISTICS WHERE TABLE_NAME
= 'STALE_TAB';
-- run
another query and show the plan
-- notice
dynamic sampling is not done, it's using the stale stats
SELECT /*+
QB_NAME(MAIN) */ DEPARTMENT_ID, SUM(SALARY) FROM STALE_TAB GROUP BY
DEPARTMENT_ID;
COLUMN
PREV_SQL_ID NEW_VALUE PSQLID
COLUMN
PREV_CHILD_NUMBER NEW_VALUE PCHILDNO
SELECT
PREV_SQL_ID, PREV_CHILD_NUMBER FROM V$SESSION
WHERE
AUDSID = USERENV('SESSIONID');
-- Showing
the plan
SELECT
PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR
('&PSQLID','&PCHILDNO','TYPICAL NOTE'));
-- create a
10053 trace file from the sql
-- look for
BASE STATISTICAL INFORMATION to see the stats used
-- the
table stat are stale but are still being used
EXECUTE
DBMS_SQLDIAG.DUMP_TRACE(P_SQL_ID=>'&PSQLID', P_CHILD_NUMBER=>'&PCHILDNO', -
P_COMPONENT=>'Optimizer',
P_FILE_ID=>'STALE02_TEST_'||TO_CHAR(SYSDATE, 'HH24MISS'));
-- run the
first query on it and show the plan
-- this is
using the plan that is in the library cache
SELECT /*+
qb_name(main) */ COUNT(*) FROM STALE_TAB;
SELECT
PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR (FORMAT=>'TYPICAL
NOTE'));
-- delete
the stats and run another the query
-- show the
staleness notice they it is NULL because there are no stats
EXEC
DBMS_STATS.DELETE_TABLE_STATS ('OP', 'STALE_TAB');
EXEC
DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
SELECT
'****************' STARS, STALE_STATS FROM USER_TAB_STATISTICS WHERE TABLE_NAME
= 'STALE_TAB';
-- run
another query and show the plan
-- notice
dynamic sampling is done because the stats are gone in this case
SELECT /*+ QB_NAME(MAIN)
*/ DEPARTMENT_ID, COUNT(*) FROM STALE_TAB GROUP BY DEPARTMENT_ID;
SELECT
PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR (FORMAT=>'TYPICAL
NOTE'));
-- run the
first query on it and show the plan
-- this is
using the plan that is in the library cache
-- hence no
dynamic sampling on this one
SELECT /*+
qb_name(main) */ COUNT(*) FROM STALE_TAB;
SELECT
PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR (FORMAT=>'TYPICAL
NOTE'));
No comments:
Post a Comment