Home » RDBMS Server » Performance Tuning » SYSTEM STATISTICS
SYSTEM STATISTICS [message #64845] Wed, 18 February 2004 21:21 Go to next message
Prasad
Messages: 104
Registered: October 2000
Senior Member
Hi All,

  I would like to know the benefit or bottlenecks gathering system statistics Where the type of operations remains both in Day and night same using DBMS_STATS package. Some authors(Tom kyte) Recomends not to capture system statistics till 10g, But If we set optimizer_mode= All_rows,first_rows,Choose and If we are not capturing is anyway slows down dictionary queries.And What is the role of timed_os_statistics=true parameter in capturing CPU AND IO statistics.  Please share u'r ideas,thoughts,views,opinions,adivices.

Thanks and Regards

Prasad 

 
Re: SYSTEM STATISTICS [message #64853 is a reply to message #64845] Thu, 19 February 2004 09:20 Go to previous messageGo to next message
Thiru
Messages: 1089
Registered: May 2002
Senior Member
Did Tom provide any reasons for not capturing system statistics in 9i ?
It does provide CBO with some useful system statistics(like single block and multiblock read time).
I havent used timed_os_statistics yet.

-Thiru
Re: SYSTEM STATISTICS [message #64855 is a reply to message #64853] Thu, 19 February 2004 21:24 Go to previous messageGo to next message
Prasad
Messages: 104
Registered: October 2000
Senior Member
Hi Thiru,

I am not able to figure out that thread but now I am seeing the same replies as you stated above.Ok things bit clear.

Just for curiosity What is diffrence between GATHER_SYSTEM_STAS and GATHER_SCHMEA_STATS giving SYS and SYSTEM as schema owner.

Secondly I am facing problem with placing all dbms_stats package in procedure and to call as regualr job. It is throwing as Insufficent priveleges even for / as sysdba user. But the Script I hv converted as shell script using cronjob its working but with any errors. My doubt is when U are using in procedure underlaying objects has to given explicitly for that logged in user.

Finally What sort of queries will be benefical by capturing histograms, I have something mind still needs more input on these.

Regards
Prasad
Re: SYSTEM STATISTICS [message #64858 is a reply to message #64855] Fri, 20 February 2004 06:42 Go to previous message
Thiru
Messages: 1089
Registered: May 2002
Senior Member
Prasad,
Gather_System_stats and Gather_Schema_Stats ( SYS, SYSTEM) serve different purposes. The former collects statistics about the OS like Read time,Write time, CPU cycles etc , nothing related to SCHEMA, while the later collects schema statistics(tables,indexes etc) on the SYS and SYSTEM schemas.

You will need to grant ' Select any table' and 'Analyze any' system privileges directly to the user who owns the job. Remember,roles are disabled within stored Pl/SQL objects.

for eg)
SQL> execute dbms_job.run(1);
BEGIN dbms_job.run(1); END;

*
ERROR at line 1:
ORA-12011: execution of 1 jobs failed
ORA-06512: at "SYS.DBMS_IJOB", line 406
ORA-06512: at "SYS.DBMS_JOB", line 272
ORA-06512: at line 1

SQL>  grant select any table to ops$oracle;

Grant succeeded.

SQL>  execute dbms_job.run(1);

PL/SQL procedure successfully completed.



Histograms are useful when you have skewed data distribution and provides the optimizer with information about how the data values are distrubuted in the table,including min and max values . But their use is not pronounced when employing bind variables in sql. When bind variables are used, in 9i, optimizer does a 'bind variable peeking' to get the literal value during its first hard parse and uses it for its subsequent executions , skipping the histogram data.

-Thiru
Previous Topic: Locking Table
Next Topic: Explain Plan - Which is the best?
Goto Forum:
  


Current Time: Thu Mar 28 13:37:19 CDT 2024