You defined a routine to gather tables and indexes statistics and you noticed that gathering them using DBMS_STATS generated a different execution plan. If you use ANALYZE TABLE, it generates better execution plan and performance of database is not affected
Symptom(s)
~~~~~~~~~~
Bad performance of sql statements Performance of database degradated Users are complaining about response time of database
Change(s)
~~~~~~~~~~
You used to gathering statistics with analyze table statement but now is using DBMS_STAT's procedures
Cause
~~~~~~~
Check how the procedure DBMS_STAT.GATHER_SCHEMA|TABLE_stats is invoked.
exec DBMS_STATS.GATHER_SCHEMA_STATS('SCOTT',NULL,FALSE,NULL,2,'ALL',TRUE); In this case, method_opt assumes null and DBMS_STATS doesn´t collect statistics for columns and it results in bad executions plans
Fix
~~~~
Make sure that you use named parameters and avoid use values without parameter to invoke the procedure DBMS_STAT.GATHER_SCHEMA|TABLE_stats
exec DBMS_STATS.GATHER_SCHEMA_STATS( - OWNNAME => 'SCOTT', BLOCK_SAMPLE => FALSE, DEGREE => 2, - GRANULARITY => 'ALL' ,CASCADE => TRUE );
Also make sure that the ANALYZE command to the DBMS_STATS command are equivalent by comparing them
Partager