Page Comparison

...

• 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

...

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

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

Views to access AWR data

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

...