|
|
 |
|
Automating Oracle
Oracle Tips by Mike Ault |
Recalculation of Table Statistics
DBMS_OUTPUT.PUT_LINE('Row count for '||tab_name||': '||to_char(row_count));
DBMS_OUTPUT.PUT_LINE('Ratio: '||to_char(row_count/rows));
IF (row_count/rows)>1.25 OR (rows/row_count)>1.25 THEN
BEGIN
IF (row_count<1000000) THEN
string :=
'ANALYZE TABLE '||tab_name||'
COMPUTE STATISTICS ';
ELSE
string :=
'ANALYZE TABLE '||tab_name||' ESTIMATE STATISTICS SAMPLE
30 PERCENT';
END IF;
cur := DBMS_SQL.OPEN_CURSOR;
DBMS_OUTPUT.PUT_LINE('Beginning analysis');
DBMS_SQL.PARSE(cur,string,dbms_sql.v7);
ret := DBMS_SQL.EXECUTE(cur) ;
DBMS_SQL.CLOSE_CURSOR(cur);
DBMS_OUTPUT.PUT_LINE(' Table: '||tab_name||' had to be
analyzed.');
write_out(' Table: '||tab_name||' had to be
analyzed.', row_count/rows,33,sysdate,0);
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20002,'Error in analyze:
'||to_char(sqlcode)||' on '||tab_name,TRUE);
write_out(' Table: '||tab_name||' error during
analyze. '||to_char(sqlcode), row_count/rows,33,sysdate,0);
IF dbms_sql.is_open(cur) THEN
dbms_sql.close_cursor(cur);
END IF;
END;
END IF;
END LOOP;
CLOSE get_tab_count;
END check_tables;
Figure 6: Procedure to Check If Tables Need
Analysis
SEE CODE DEPOT FOR FULL SCRIPTS
 |
For more information on this topic, I recommend Don
Burleson's latest book "Oracle
Tuning: The Definitive Reference".
You can buy it direct from the publisher for 50%-off and get
instant access to the code depot of Oracle tuning scripts: |
http://www.rampant-books.com/book_1002_oracle_tuning_definitive_reference_2nd_ed.htm
 |
Expert Remote DBA
BC is America's oldest and largest Remote DBA Oracle support
provider. Get real Remote DBA experts, call
BC Remote DBA today. |
 |
|
|
|
|