...
- 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.
Code Block 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.
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
...