Just a short post today on something that came in as a question for the upcoming Office Hours session which I thought could be covered quickly in a blog post without needing a lot of additional discussion for which Office Hours is more suited to.
The question was:
“When I gather statistics using DBMS_STATS, can I just create a statistic table and pass that as a parameter to get the results of the gather”
And the answer simply is “No” 
but let me clear up the confusion.
Many DBMS_STATS routines allow you to pass the name of the “statistics table” into which statistical information for the table, schema, database etc will be stored. For example, you can see the STAT-prefixed parameter to GATHER_TABLE_STATS
PROCEDURE GATHER_TABLE_STATS
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
OWNNAME VARCHAR2 IN
TABNAME VARCHAR2 IN
PARTNAME VARCHAR2 IN DEFAULT
ESTIMATE_PERCENT NUMBER IN DEFAULT
BLOCK_SAMPLE BOOLEAN IN DEFAULT
METHOD_OPT VARCHAR2 IN DEFAULT
DEGREE NUMBER IN DEFAULT
GRANULARITY VARCHAR2 IN DEFAULT
CASCADE BOOLEAN IN DEFAULT
STATTAB VARCHAR2 IN DEFAULT
STATID VARCHAR2 IN DEFAULT
STATOWN VARCHAR2 IN DEFAULT
NO_INVALIDATE BOOLEAN IN DEFAULT
STATTYPE VARCHAR2 IN DEFAULT
FORCE BOOLEAN IN DEFAULT
CONTEXT CCONTEXT IN DEFAULT
OPTIONS VARCHAR2 IN DEFAULT
The statistics table must be of a certain structure, which is managed via the API as well using the CREATE_STAT_TABLE routine.
SQL> exec dbms_stats.create_stat_table('','st')
PL/SQL procedure successfully completed.
SQL> desc ST
Name Null? Type
----------------------------- -------- --------------------
STATID VARCHAR2(128)
TYPE CHAR(1)
VERSION NUMBER
FLAGS NUMBER
C1 VARCHAR2(128)
C2 VARCHAR2(128)
C3 VARCHAR2(128)
C4 VARCHAR2(128)
C5 VARCHAR2(128)
C6 VARCHAR2(128)
N1 NUMBER
N2 NUMBER
N3 NUMBER
N4 NUMBER
N5 NUMBER
N6 NUMBER
N7 NUMBER
N8 NUMBER
N9 NUMBER
N10 NUMBER
N11 NUMBER
N12 NUMBER
N13 NUMBER
D1 DATE
T1 TIMESTAMP(6) WITH TI
ME ZONE
R1 RAW(1000)
R2 RAW(1000)
R3 RAW(1000)
CH1 VARCHAR2(1000)
CL1 CLOB
BL1 BLOB
But when calling the GATHER_xxx routines, if you pass the name of the statistic table, please note that this is for getting a copy of the relevant statistics before the fresh gathering of statistics is done. We can see this with a simple demo.
SQL> create table t1 as select * from dba_objects;
Table created.
SQL>
SQL> select num_rows
2 from user_tables
3 where table_name = 'T1';
NUM_ROWS
----------
83608
1 row selected.
So we can see that the table T1 has ~83,000 rows in it. Since I’m running this on 12c, there was no need to gather statistics after this initial load, because they were collected automatically as part of the loading process. (Very nifty!).
I now add another ~250,000 rows and perform a new GATHER_TABLE_STATS call, this time passing in the name of the statistics table (ST) that I just created.
SQL>
SQL> insert into t1
2 select * from t1;
83608 rows created.
SQL>
SQL> insert into t1
2 select * from t1;
167216 rows created.
SQL>
SQL> begin
2 dbms_stats.gather_table_stats
3 ( ownname=>'MCDONAC',
4 tabname=>'T1',
5 stattab=>'ST',
6 statid=>'STATS'
7 );
8 end;
9 /
PL/SQL procedure successfully completed.
Digging into the statistic table data, you can see that the number of rows recorded for T1 is 83597, which is the before image of the optimizer stat, not the after image.
SQL> select * from st where c1 = 'T1' and type = 'T'
2 @pr
==============================
STATID : STATS
TYPE : T
VERSION : 8
FLAGS : 2
C1 : T1
C2 :
C3 :
C4 :
C5 : MCDONAC
C6 :
N1 : 83597
N2 : 1607
N3 : 129
N4 : 83597
N5 :
N6 :
N7 :
N8 :
N9 : 0
N10 :
N11 :
N12 :
N13 :
D1 : 16-JAN-19
T1 :
R1 :
R2 :
R3 :
CH1 :
CL1 :
BL1 :
PL/SQL procedure successfully completed.
So just remember that if you want to get the DBMS_STATS information that is the result of a GATHER_xxx call, then you can follow it up with the appropriate EXPORT_xxx call to dump the data.
Learning is not a spectator sport 
Leave a reply to Quanwen Zhao Cancel reply