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