Page Comparison

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
• second query lists all of the foreign keys that do not have the appropriate indexes in place on the child table. It shows the foreign key constraints that cause locking problems.

  • Code Block

    SELECT acc.owner||'-> '||acc.constraint_name||'('||acc.column_name ||'['||acc.position||'])'||' ***** Missing Index' FROM all_cons_columns acc, all_constraints ac WHERE ac.constraint_name = acc.constraint_name AND ac.constraint_type = 'R' AND (acc.owner, acc.table_name, acc.column_name, acc.position) IN (SELECT acc.owner, acc.table_name, acc.column_name, acc.position FROM all_cons_columns acc, all_constraints ac WHERE ac.constraint_name = acc.constraint_name AND ac.constraint_type = 'R' MINUS SELECT table_owner, table_name, column_name, column_position FROM all_ind_columns) ORDER BY acc.owner, acc.constraint_name, acc.column_name, acc.position;

  • e.g. caje-prod internal

    • No Format

      SAKAI-> FK20252854D62F7017(META_DATA[1]) ***** Missing Index SAKAI-> FK25C0A259BE381194(REPORTDEFID[1]) ***** Missing Index SAKAI-> FK2709988631446627(ITEMID[1]) ***** Missing Index SAKAI-> FK27099886895D4813(SECTIONID[1]) ***** Missing Index SAKAI-> FK270998869482C945(ASSESSMENTID[1]) ***** Missing Index SAKAI-> FK3BC123AA391BE33A(SURROGATEKEY[1]) ***** Missing Index SAKAI-> FK4BF41E45AE4A118A(SYLLABUSID[1]) ***** Missing Index SAKAI-> FK4C9D99E06F98CFF(GRADABLE_OBJECT_ID[1]) ***** Missing Index SAKAI-> FK4FBA80FE56A169CC(SURROGATE_KEY[1]) ***** Missing Index SAKAI-> FK54E9B20956234182(LICENSE_CODE[1]) ***** Missing Index SAKAI-> FK5B9A5CFD26827043(COURSE_OFFERING[1]) ***** Missing Index SAKAI-> FK759996A7F09DEFAE(CATEGORY_ID[1]) ***** Missing Index SAKAI-> FK75B43C0D21BCC7D2(USERSURROGATEKEY[1]) ***** Missing Index SAKAI-> FK7977DFF06F98CFF(GRADABLE_OBJECT_ID[1]) ***** Missing Index SAKAI-> FK7B2D5CDE5B252FAE(OF_URROGATEKEY[1]) ***** Missing Index SAKAI-> FK7C870191552B7E63(SELECTED_GRADE_MAPPING_ID[1]) ***** Missing Index SAKAI-> FK99FA8CB83288DBBD(ITEMID[1]) ***** Missing Index SAKAI-> FK99FA8CB870CE2BD(SECTIONID[1]) ***** Missing Index SAKAI-> FK99FA8CB8CAC2365B(ASSESSMENTID[1]) ***** Missing Index SAKAI-> FKA221A1F721BCC7D2(USERSURROGATEKEY[1]) ***** Missing Index SAKAI-> FKADE11225181E947A(GB_GRADING_SCALE_T[1]) ***** Missing Index SAKAI-> FKB2FE801D325D7986(GRADEBOOK_ID[1]) ***** Missing Index SAKAI-> FKBC88AA27D02EF633(PUBLISHEDASSESSMENTID[1]) ***** Missing Index SAKAI-> FKBFCBD9AE7F976CD6(CANON_COURSE[1]) ***** Missing Index SAKAI-> FKC8532ED721BCC7D2(USERSURROGATEKEY[1]) ***** Missing Index SAKAI-> FKCD333737325D7986(GRADEBOOK_ID[1]) ***** Missing Index SAKAI-> FKD4CF5A194D7EA7B3(ITEMGRADINGID[1]) ***** Missing Index SAKAI-> FKD96A9BC64F7C8841(CROSS_LISTING[1]) ***** Missing Index SAKAI-> FKE03761CB74C7E92B(OF_SURROGATEKEY[1]) ***** Missing Index SAKAI-> FKE03761CB7DEF8466(T_SURROGATEKEY[1]) ***** Missing Index SAKAI-> FKE03761CBA306F94D(A_SURROGATEKEY[1]) ***** Missing Index SAKAI-> FKE15DCD9BD0506F16(SECTION_ID[1]) ***** Missing Index SAKAI-> MODULE_ID(MODULE_ID[1]) ***** Missing Index SAKAI-> MODULE_ID_SEC(MODULE_ID[1]) ***** Missing Index SAKAI-> SYS_C004616(JOB_GROUP[2]) ***** Missing Index SAKAI-> SYS_C004616(JOB_NAME[1]) ***** Missing Index SAKAI-> SYS_C004622(MAINTAIN_ROLE[1]) ***** Missing Index SAKAI-> SYS_C004631(ROLE_KEY[1]) ***** Missing Index

Views to access AWR data

Listing Columns with Inconsistent Data Types or Lengths

• Source: http://www.dba-oracleoreilly.com/oracle10g_tuning/t_dba_hist_data_dictionary_views.htm

...

• catalog/oracle2/chapter/ch10.html
• The following query lists all columns that have differing lengths or data types but that have the same column name. For example, ACCOUNT_NO may be NUMBER(9) in one table and VARCHAR(9) in another. Having different data types can cause data casting problems and result in indexes not being used.

  • Code Block

    SELECT owner, column_name , table_name||' '||data_type||'('|| DECODE(data_type, 'NUMBER', data_precision, data_length)||')' "Characteristics" FROM all_tab_columns WHERE (column_name, owner) IN (SELECT column_name, owner FROM all_tab_columns GROUP BY column_name, owner HAVING MIN(DECODE(data_type, 'NUMBER', data_precision, data_length)) < MAX(DECODE(data_type, 'NUMBER', data_precision, data_length)) ) AND owner NOT IN ('SYS', 'SYSTEM')

  • e.g. RAC Extermal

    • No Format

      ... SAKAI_EXTERNAL ENROLLMENTSET_EID BANNER_CM_SECTION VARCHAR2(74) SAKAI_EXTERNAL ENROLLMENTSET_EID BANNER_ENROLLMENT VARCHAR2(74) SAKAI_EXTERNAL ENROLLMENTSET_EID BANNER_INSTRUCTORS VARCHAR2(74) SAKAI_EXTERNAL ENROLLMENTSET_EID CMT_INSTRUCTORS VARCHAR2(255) SAKAI_EXTERNAL ENROLLMENTSET_EID CMT_SECTION VARCHAR2(255) SAKAI_EXTERNAL ENROLLMENTSET_EID CM_ENROLLMENT VARCHAR2(74) SAKAI_EXTERNAL ENROLLMENTSET_EID CM_INSTRUCTORS VARCHAR2(74) SAKAI_EXTERNAL ENROLLMENTSET_EID COURSEMANAGEMENT_ENROLLMENT VARCHAR2(100) SAKAI_EXTERNAL ENROLLMENTSET_EID COURSEMANAGEMENT_INSTRUCTORS VARCHAR2(74) SAKAI_EXTERNAL ENROLLMENTSET_EID COURSEMANAGEMENT_SECTION VARCHAR2(74) SAKAI_EXTERNAL ENROLLMENTSET_EID UNEX_ENROLLMENT VARCHAR2(100) SAKAI_EXTERNAL COURSEOFFERING_EID BANNER_CM_SECTION VARCHAR2(82) SAKAI_EXTERNAL COURSEOFFERING_EID BANNER_ENROLLMENTSET VARCHAR2(82) SAKAI_EXTERNAL COURSEOFFERING_EID BANNER_OFFERING_COURSESETS VARCHAR2(82) SAKAI_EXTERNAL COURSEOFFERING_EID CMT_SECTION VARCHAR2(255) SAKAI_EXTERNAL COURSEOFFERING_EID COURSEMANAGEMENT_MEMBERSHIP VARCHAR2(0) SAKAI_EXTERNAL COURSEOFFERING_EID COURSEMANAGEMENT_SECTION VARCHAR2(82) SAKAI_EXTERNAL SAKAI_USER_KERB_NAME UCDSAKAI_COURSE_MEMBERS VARCHAR2(18) SAKAI_EXTERNAL SAKAI_USER_KERB_NAME UCDSAKAI_USER_AFFILIATION VARCHAR2(50) SAKAI_EXTERNAL SAKAI_USER_ROLE_NAME UCDSAKAI_COURSE_MEMBERS VARCHAR2(20) SAKAI_EXTERNAL SAKAI_USER_ROLE_NAME UCDSAKAI_USER_AFFILIATION VARCHAR2(50) SAKAI_EXTERNAL SAKAI_USER_ENTITY_TYPE UCDSAKAI_COURSE_MEMBERS VARCHAR2(20) SAKAI_EXTERNAL SAKAI_USER_ENTITY_TYPE UCDSAKAI_USER_AFFILIATION VARCHAR2(50) ...

Listing Invalid Objects

• 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

Checking the Number of Objects

• Source: http://www.oreilly.com/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

• http://www.dba-oracle.com/art_sql_tune.htm

Views to access AWR data

• http://www.dba-oracle.com/oracle10g_tuning/t_dba_hist_data_dictionary_views.htm

Oracle Performance Tuning, 2nd Edition

• http://www.oreilly.com/catalog/oracle2/chapter/ch10.html

Oracle Monitoring and Performance Tuning FAQ

• http://orafaq.com/faqdbapf.htm
  • My query was fine last week and now it is slow. Why?
  • Why is Oracle not using the bleep index?

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

Oracle Monitoring and Performance Tuning FAQ

• http://orafaq.com/faqdbapf.htm
• My query was fine last week and now it is slow. Why?
Why is Oracle not using the bleep index?oracle10g_tuning/t_instance_efficiency_percentage.htm