Long Running Queries
- This view displays the status of various operations that run for longer than 6 seconds
Code Block select * from V$SESSION_LONGOPS;
Computing the Hit Ratio
- Source: http://www.oreilly.com/catalog/oracle2/chapter/ch10.html
Code Block SELECT SUM(DECODE(name, 'consistent gets',value, 0)) "Consis Gets", SUM(DECODE(name, 'db block gets',value, 0)) "DB Blk Gets", SUM(DECODE(name, 'physical reads',value, 0)) "Phys Reads", (SUM(DECODE(name, 'consistent gets',value, 0)) + SUM(DECODE(name, 'db block gets',value, 0)) - SUM(DECODE(name, 'physical reads',value, 0))) / (SUM(DECODE(name, 'consistent gets',value, 0)) + SUM(DECODE(name, 'db block gets',value, 0)) ) * 100 "Hit Ratio" FROM v$sysstat;- RAC External Results:
No Format Consis Gets DB Blk Gets Phys Reads Hit Ratio -------------- -------------- ------------- ----------------------------------------- 547094930 10815948 316183 99.94332732834795165976312080439485533745
- CAJE-Prod External Results:
No Format Consis Gets DB Blk Gets Phys Reads Hit Ratio -------------- -------------- ------------- ----------------------------------------- 1222124214 26405978 540427901 56.71487125719423531569671484564307596656
...
- Source: http://www.oreilly.com/catalog/oracle2/chapter/ch10.html
- Having invalid objects in your database usually indicates that your underlying tables have been altered to add a new column or have had DDL operations performed on them. The most common objects that become invalid are views, packages, and procedures. Invalid packages and procedures can cause a long response delay because they have to be recompiled. The user is forced to wait for the recompilation to complete. If you do alter your tables, you should always recompile your invalid packages and procedures to avoid user frustration.
Code Block SELECT owner, object_type, object_name, status FROM all_objects WHERE status = 'INVALID' ORDER BY owner, object_type, object_name
- e.g. RAC Extermal
No Format OWNER OBJECT_TYPE OBJECT_NAME STATUS -------------- -------------- ----------------------- --------- SAKAI_EXTERNAL VIEW UCDSAKAI_CLASS_SCHEDULE INVALID SAKAI_EXTERNAL VIEW UCDSAKAI_COURSE INVALID SAKAI_EXTERNAL VIEW UCDSAKAI_COURSE_INFO INVALID
Views to access AWR data
Checking the Number of Objects
- Source: http://www.dba-oracleoreilly.com/oracle10g_tuning/t_dba_hist_data_dictionary_views.htm
...
- catalog/oracle2/chapter/ch10.html
- This listing provides you with a list of the number of objects on a per-user basis. It can be run regularly to make sure that your database is operating as you expect it to.
Code Block SELECT username, COUNT(DECODE(o.type#, 2, o.obj#, '')) Tab, COUNT(DECODE(o.type#, 1, o.obj#, '')) Ind, COUNT(DECODE(o.type#, 5, o.obj#, '')) Syn, COUNT(DECODE(o.type#, 4, o.obj#, '')) Vew, COUNT(DECODE(o.type#, 6, o.obj#, '')) Seq, COUNT(DECODE(o.type#, 7, o.obj#, '')) Prc, COUNT(DECODE(o.type#, 8, o.obj#, '')) Fun, COUNT(DECODE(o.type#, 9, o.obj#, '')) Pck, COUNT(DECODE(o.type#,12, o.obj#, '')) Trg, COUNT(DECODE(o.type#,10, o.obj#, '')) Dep FROM sys.obj$ o, sys.dba_users U WHERE u.user_id = o.owner# (+) GROUP BY username;
Oracle tuning - Tune individual SQL statements
Views to access AWR data
Oracle Performance Tuning, 2nd Edition
Oracle Monitoring and Performance Tuning FAQ
Compressing Extents in Oracle
- Source: http://www.lifeaftercoffee.com/2005/12/07/compressing-extents-in-oracle/
No Format ALTER TABLE system.session_audit MOVE TABLESPACE system STORAGE (INITIAL 10M NEXT 5M);
"Instance Efficiency Percentages" and "Shared Pool Statistics" metrics interpretation
- http://www.oreillydba-oracle.com/catalog/oracle2/chapter/ch10.html