Oracle Tuning And Performance Analysis
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
- Source: http://www.lifeaftercoffee.com/2005/12/07/compressing-extents-in-oracle/
ALTER TABLE system.session_audit MOVE TABLESPACE system STORAGE (INITIAL 10M NEXT 5M);