Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 17 Next »

Long Running Queries

  • This view displays the status of various operations that run for longer than 6 seconds
    • select * from V$SESSION_LONGOPS;
      

Computing the Hit Ratio

  • Source: http://www.oreilly.com/catalog/oracle2/chapter/ch10.html
  • 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:
    • Consis Gets     DB Blk Gets     Phys Reads     Hit Ratio                                 
      --------------  --------------  -------------  ----------------------------------------- 
      547094930       10815948        316183         99.94332732834795165976312080439485533745 
      
  • CAJE-Prod External Results:
    • Consis Gets     DB Blk Gets     Phys Reads     Hit Ratio                                 
      --------------  --------------  -------------  ----------------------------------------- 
      1222124214      26405978        540427901      56.71487125719423531569671484564307596656 
      

Looking at the Dictionary Cache

  • Source: http://www.oreilly.com/catalog/oracle2/chapter/ch10.html
  • The dictionary cache miss ratio should ideally be less than 1%, although when the database is first started, the miss ratio will be higher because each dictionary item loaded into memory will record a miss. If the miss ratio is greater than 2% and you have spare memory, increase your SHARED_POOL_SIZE. If the ratio has decreased, you should have improved your performance.
    • SELECT
          SUM(GETS) "Gets",
          SUM(GETMISSES) "Misses",
          TO_CHAR(SUM(getmisses) / SUM(gets) * 100 , '999.99')||'%' "Miss Ratio"
      FROM
          v$rowcache;
      

Finding Foreign Key Relationships

  • 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.
    • 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
      •  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       
        

Listing Columns with Inconsistent Data Types or Lengths

  • Source: http://www.oreilly.com/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.
    • 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
      • ...
         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.
    • SELECT owner, object_type, object_name, status 
      FROM   all_objects
      WHERE  status = 'INVALID'
      ORDER BY owner, object_type, object_name
      
    • e.g. RAC Extermal
      • 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.
    • 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

  • No labels