Re: Analyse
Date: Tue, 23 Jul 2002 11:08:11 +0100
Message-ID: <fW9%8.4097$zX3.3501_at_news.indigo.ie>
Prior ..
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" <jonov_at_iprimus.com.au> wrote in message
news:3d3d1a71$1_1_at_news.iprimus.com.au...
>
> 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.
>
>
>
>
>
Received on Tue Jul 23 2002 - 12:08:11 CEST