Analyse
Jon
I need some advice on the use of the PL/SQL dbms_stats package.
I am able to analyze the tables in a database, however, I want to keep the
current table stats in case there is performance degradation after the
analyze is performed. I have been testing the use of the dbms_stats package
and I am able to create the stats table and export the current stats in a
test database. However, when I imported the stats back, I noticed that the
empty_blocks, avg_space, avg_space_freelist_blocks and num_freelist_blocks
in dba_tables were all set to zero. I checked this table before the export
and the values of these fields were non-zero. I'd like to know if this is
the normal behaviour of the dbms_stats.export/import or should I expect to
see the original values in these columns?
I found the following statement in the Oracle 8.1.7 Performace & Tuning
Guide:
"To verify that table statistics are available, execute the following
against the DBA_TABLES:
SELECT TABLE_NAME, NUM_ROWS, BLOCKS, AVG_ROW_LEN,
TO_CHAR(LAST_ANALYZED, 'MM/DD/YYYY H24:MI:SS')
FROM DBA_TABLES
WHERE TABLE_NAME IN ('TABLE1', 'TABLE2');"
The above makes me think that as long as the table_name, num_rows, blocks,
avg_row_len and last_analyzed values are imported correctly (which in my
tests were) ie same values as before the export, then the optimizer should
behave as it did before the analyze tables was performed ie using the former
table stats. Would this be a reasonable assumption?
These are the commands I executed:
a) To create the stats table and export current stats:
dbms_stats.create_table ('oracle', 'curr_stats');
dbms_stats.export_table_stats ('oracle', 'emp', null, 'curr_stats',
'23JUL02 1030', true, 'oracle');
b) To import the previous stats:
dbms_stats.delete_table_stats ('oracle', 'emp');
dbms_stats.import_table_stats ('oracle', 'emp', null, 'curr_stats',
'23JUL02 1030', true, 'oracle');
Thanks.
Monica
PS. I'm using Oracle Server Enterprise 8.1.6.3 on Solaris 2.6 platform.
Telemachus
SQL> exec dbms_stats.create_stat_table('DATA_HOLDER','STAT_TABLE',NULL);
PL/SQL procedure successfully completed.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
SQL> create table boyoboy(col1 number);
Table created.
SQL> insert into boyoboy select object_id from all_objects;
10213 rows created.
SQL> commit;
Commit complete.
SQL> exec
dbms_stats.gather_table_stats('DATA_HOLDER','BOYOBOY',NULL,NULL,FALSE,
'FOR ALL COLUMNS SIZE
1',NULL,'ALL',TRUE,'STAT_TABLE','PRE_COMP','DATA_HOLDER',F
ALSE);
PL/SQL procedure successfully completed.
SQL> SELECT TABLE_NAME, NUM_ROWS, BLOCKS, AVG_ROW_LEN,LAST_ANALYZED FROM
DBA_TA
BLES
2 WHERE TABLE_NAME IN ('BOYOBOY');
TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN LAST_ANAL
------------------------------ ---------- ---------- ----------- ---------
BOYOBOY 10213 10 4 23-JUL-02
SQL> exec dbms_stats.delete_table_stats('DATA_HOLDER','BOYOBOY');
PL/SQL procedure successfully completed.
TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN LAST_ANAL
------------------------------ ---------- ---------- ----------- ---------
BOYOBOY
SQL> exec
dbms_stats.import_Table_stats('DATA_HOLDER','BOYOBOY',NULL,'STAT_TABLE
','PRE_COMP',TRUE,'DATA_HOLDER',FALSE);
PL/SQL procedure successfully completed.
SQL> SELECT TABLE_NAME, NUM_ROWS, BLOCKS, AVG_ROW_LEN,LAST_ANALYZED FROM
DBA_TA
BLES
2 WHERE TABLE_NAME IN ('BOYOBOY');
TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN LAST_ANAL
------------------------------ ---------- ---------- ----------- ---------
BOYOBOY
He's right you know !
But !
SQL> exec
dbms_stats.gather_table_stats('DATA_HOLDER','BOYOBOY',NULL,NULL,FALSE,
'FOR ALL COLUMNS SIZE
1',NULL,'ALL',TRUE,'STAT_TABLE','PRE_COMP2','DATA_HOLDER',
FALSE);
PL/SQL procedure successfully completed.
SQL> exec
dbms_stats.gather_table_stats('DATA_HOLDER','BOYOBOY',NULL,NULL,FALSE,
'FOR ALL COLUMNS SIZE
1',NULL,'ALL',TRUE,'STAT_TABLE','PRE_COMP3','DATA_HOLDER',
FALSE);
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.delete_table_stats('DATA_HOLDER','BOYOBOY');
SQL> SELECT TABLE_NAME, NUM_ROWS, BLOCKS, AVG_ROW_LEN,LAST_ANALYZED FROM
DBA_
BLES WHERE TABLE_NAME IN ('BOYOBOY');
TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN LAST_ANAL
------------------------------ ---------- ---------- ----------- ---------
BOYOBOY
PL/SQL procedure successfully completed.
SQL> exec
dbms_stats.import_Table_stats('DATA_HOLDER','BOYOBOY',NULL,'STAT_TABLE
','PRE_COMP3',TRUE,'DATA_HOLDER',FALSE);
PL/SQL procedure successfully completed.
SQL> SELECT TABLE_NAME, NUM_ROWS, BLOCKS, AVG_ROW_LEN,LAST_ANALYZED FROM
DBA_TA
BLES WHERE TABLE_NAME IN ('BOYOBOY');
TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN LAST_ANAL
------------------------------ ---------- ---------- ----------- ---------
BOYOBOY 10213 10 4 23-JUL-02
Wheee !!!!
( I needed the practice ).
Be careful ... the documentation states why this is so.
GATHER_TABLE_STATS saves the CURRENT statistics. The results it generates
go straight to the dictionary.
it flows as follows
first gather stats go to dictionary ... pre_comp null stats go to my
stat_table.
delete stats and import pre_comp
we're back to null.
generate pre_comp2 and save null stats. now we have good stats in dictionary
generate pre_comp3 and save GOOD stats. now we have good stats in
stat_stable
"Jon" <jo...@iprimus.com.au> wrote in message
news:3d3d1a71$1...@news.iprimus.com.au...
T R
with dbms_stats in 8.1.6. We have been on 8.1.7 for some time so I am not
aware of the details. I have been using this package for some time and had
good success.
The only problems I came across while using the statspack is if the
index_names were system assigned. For example, if a testing db has an index
on a table called sys_yxy and the production db has the same index on the
same table called sys_xyx, then importing of stats from testing to
production is not possilbe unless you alter the export file itself! The
reason the index names are system generated is because we have a third-party
application that doesn't the dba's control over the creation of these.
"Jon" <jo...@iprimus.com.au> wrote in message
news:3d3d1a71$1...@news.iprimus.com.au...
>