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.