|
|
DocumentThree ways to Analyze Tables |
|
There are 2 Oracle supplied packages procedures which can be used to analyze tables. Always make sure you read the latest release notes for you platform before running these procedures.
PROCEDURE dbms_utility.analyze_schema
| Argument Name | Type In/Out | Default ? |
| SCHEMA | VARCHAR2 | IN |
| METHOD | VARCHAR2 | IN |
| ESTIMATE_ROWS | NUMBER | IN |
| ESTIMATE_PERCENT | NUMBER | IN |
| METHOD_OPT | VARCHAR2 | IN |
METHOD_OPT is one of ' FOR TABLE','FOR ALL COLUMNS [SIZE n]','FOR ALL INDEXED COLUMNS [SIZE n]','FOR ALL INDEXES'.
PROCEDURE dbms_utility.analyze_database
| Argument Name | Type In/Out | Default ? |
| METHOD | VARCHAR2 | IN |
| ESTIMATE_ROWS | NUMBER | IN |
| ESTIMATE_PERCENT | NUMBER | IN |
| METHOD_OPT | VARCHAR2 | IN |
To run these procedures, from the SQL prompt type
SQL> execute dbms_utility.analyze_schema('SCOTT','ESTIMATE')
Alternatively use,
SQL> execute dbms_utility.analyze_database('COMPUTE')
This is a simple technique of using SQL to generate SQL. The main benefit of using this technique as oppose to Method 1 is that since it is a SQL query, you have control down to table level about which tables you analyze. It is not uncommon that in a single schema some tables will need to be analyzed on a more frequent basis that other tables in the same schema For example, frequently changing OLTP tables may need analyzing daily whereas static/historical/archive data will only need analyzing once just after population.
spool analyze.lst
set pages 0 set verify off
select 'ANALYZE TABLE '||owner||'.'||table_name||' ESTIMATE STATISTICS;'
from dba_tables
where owner not in ('SYS','SYSTEM')
/
spool off
start analyze.lst
The third method combines an Oracle supplied package and dynamic SQL using the DBMS_DDL.ANALYZE_OBJECT package procedure. Therefore, we can use this procedure to create a new dynamic analyze table script, which can be easily tailored to individual databases requirements.
PROCEDURE dbms_ddl.analyze_object
| Argument Name | Type | In/Out | Default |
| TYPE | VARCHAR2 | IN | |
| SCHEMA | VARCHAR2 | IN | |
| NAME | VARCHAR2 | IN | |
| METHOD | VARCHAR2 | IN | |
| ESTIMATE_ROWS | NUMBER | IN | Y |
| ESTIMATE_PERCENT | NUMBER | IN | Y |
| METHOD_OPT | VARCHAR2 | IN | Y |
TYPE is 'TABLE','INDEX','CLUSTER'. If SCHEMA is NULL then the procedure assumes that its the current schema.
spool analyze.lst
set pages 0 set verify off
select 'execute dbms_ddl.analyze_object('||chr(39)||segment_type||chr(39)||
','||chr(39)||owner||chr(39)||
','||chr(39)||segment_name||chr(39)||
','||chr(39)||'ESTIMATE'||chr(39)||
')'
from dba_segments
where owner not in ('SYS','SYSTEM')
and segment_type in ('TABLE','INDEX','CLUSTER')
/
spool off
start analyze.lst
Return to Index of SQL Scripts
General Information :
Home
| Company Profile
| Contact Us
| Legal
Services :
Build Services
| Revive your Oracle Setup
| Low Cost Development
| Creative Solutions
| Training
| Proactive Database Support
Free Oracle Resources:
DBA Scripts
| Oracle Links
| Quiz