dbms_stats????

[kool_max]

hello, I am very new to this DBA position. But today i was told by other DBA to run this query which took forever.

Exec dbms_stats.gather_database_stats

Just wanted to know what exactly its doing or a little overview of that. Is it createing a report?? if so where can i go to read the report. And also i am using TOAD as my third party tool. Can you create a explain plan from TOAD. If so anybody who uses TOAD can please give me a way to get a explain plan for it. Thanks in advance.

[gopu_g]

DBMS_STATS
The DBMS_STATS package was introduced in Oracle 8i and is Oracles preferred method of gathering object statistics. Oracle list a number of benefits to using it including parallel execution, long term storage of statistics and transfer of statistics between servers. Once again, it follows a similar format to the other methods:

EXEC DBMS_STATS.gather_database_stats;
EXEC DBMS_STATS.gather_database_stats(estimate_percent => 15);

EXEC DBMS_STATS.gather_schema_stats('SCOTT');
EXEC DBMS_STATS.gather_schema_stats('SCOTT', estimate_percent => 15);

EXEC DBMS_STATS.gather_table_stats('SCOTT', 'EMPLOYEES');
EXEC DBMS_STATS.gather_table_stats('SCOTT', 'EMPLOYEES', estimate_percent => 15);

EXEC DBMS_STATS.gather_index_stats('SCOTT', 'EMPLOYEES_PK');
EXEC DBMS_STATS.gather_index_stats('SCOTT', 'EMPLOYEES_PK', estimate_percent => 15);


This package also gives you the ability to delete statistics:

EXEC DBMS_STATS.delete_database_stats;
EXEC DBMS_STATS.delete_schema_stats('SCOTT');
EXEC DBMS_STATS.delete_table_stats('SCOTT', 'EMPLOYEES');
EXEC DBMS_STATS.delete_index_stats('SCOTT', 'EMPLOYEES_PK');

[KenEwald]

..today i was told by other DBA to run this query which took forever.

Exec dbms_stats.gather_database_stats

If he asks you to jump off a bridge, read the manual first.

Bottom line, it's a package to manage the statistical information on database objects that determines how SQL is executed.

I would think the Toad docs explain how to run explain plans. If you can, I'd suggest using sqlplus and plustrace.

Cheers,
Ken

[tamilselvan]

Read my ppt:

http://www.oracleact.com/papers/beac...rf-class-3.pdf