| DBMS_STATS pack [message #50678] |
Mon, 01 April 2002 03:27  |
nadella srikanth
Messages: 6
Registered: March 2002
|
Junior Member |
|
|
Hi,
Can somebody please help me by explaining how to use the dbms_stats pack and its procedures?
Thanks in advance.
|
|
|
|
| Re: DBMS_STATS pack [message #50679 is a reply to message #50678] |
Mon, 01 April 2002 04:15  |
Grant
Messages: 578
Registered: January 2002
|
Senior Member |
|
|
This should get you started. Take a look at the scripts. The standard schema is perfstat.
To install the package, either change directory to the ORACLE_HOME
rdbms/admin directory, or fully specify the ORACLE_HOME/rdbms/admin
directory when calling the installation script, statscre.
e.g.
on Unix:
SQL> connect internal
SQL> @?/rdbms/admin/statscre
on NT:
SQL> connect internal
SQL> @%ORACLE_HOME%rdbmsadminstatscre
The statscre install script automatically calls 3 other scripts
needed:
1. statscusr -> creates the user and grants privileges
2. statsctab -> creates the tables
3. statspack -> creates the package
Check each of the three output files produced (statscusr.lis,
statsctab.lis, statspack.lis) by the installation to ensure no
errors were encountered, before continuing on to the next step.
2. The setup phase is now complete.
If you wish to, you may decide to change the password of the
PERFSTAT user for security purposes.
Then you just take snapshots for your peak time:
connect perfstat/perfstat
exec statspack.snap;
Then run reports with:
connect perfstat/perfstat
@?/rdbms/admin/statsrep
|
|
|
|
|
|
| Re: DBMS_STATS pack [message #50739 is a reply to message #50678] |
Thu, 04 April 2002 03:35 |
Grant
Messages: 578
Registered: January 2002
|
Senior Member |
|
|
Sorry must have been brain dead that day. I had statspack on the brain I guess. Anyway, there are a number of ways you can gather stats:
ANALYZE SQL command
DBMS_UTILITY.ANALYZE_SCHEMA procedure
DBMS_UTILITY.ANALYZE_DATABASE procedure
DBMS_DDL.ANALYZE_OBJECT procedure
8.1 DBMS_STATS.GATHER_xx_STATS procedures
I use the first two. I have not used DBMS_STATS so I wouldn't be much help. I find it much easier to use DBMS_UTILITY.
SQL> EXEC DBMS_UTILITY.ANALYZE_SCHEMA('SCOTT','COMPUTE');
If I just need a table done I would:
SQL> ANALYZE TABLE EMP COMPUTE STATISTICS;
You will need to look at the Oracle docs for all your options. You can delete, compute, or take samples by rows or percent. You can look at them using DBA_TABLES and DBA_INDEXES at columns NUM_ROWS and LAST_ANALYZED and so on.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
