Tuesday 10-09 2007
- ...00:18 (Kirk)
No Format Verified that only the 2 tables have lost their stats: SQL Statement which produced this data: select table_name, to_char(last_analyzed,'mm/dd/yyyy hh24:mi:ss') from user_tables where lower(table_name) like 'coursemanagement_%' TABLE_NAME TO_CHAR(LAST_ANALYZED,'MM/DD/YYYYHH24:MI:SS') COURSEMANAGEMENT_ENROLLMENT_TA 10/08/2007 22:01:33 COURSEMANAGEMENT_ENROLLMENT_TB 10/08/2007 22:01:47 COURSEMANAGEMENT_INSTRUCT_TA NULL COURSEMANAGEMENT_INSTRUCT_TB 10/08/2007 22:01:10 COURSEMANAGEMENT_MEMBERSHIP_TA 10/08/2007 22:00:39 COURSEMANAGEMENT_MEMBERSHIP_TB 10/08/2007 22:00:18 COURSEMANAGEMENT_OFFERING_TA 10/08/2007 22:00:11 COURSEMANAGEMENT_OFFERING_TB 10/08/2007 22:00:08 COURSEMANAGEMENT_SECTION_TA NULL COURSEMANAGEMENT_SECTION_TB 10/08/2007 22:02:05 and none of the indexes lost theirs: SQL Statement which produced this data: select index_name, to_char(last_analyzed,'mm/dd/yyyy hh24:mi:ss') from user_indexes where lower(index_name) like '%cm_enroll_t%' INDEX_NAME TO_CHAR(LAST_ANALYZED,'MM/DD/YYYYHH24:MI:SS') CM_ENROLL_TA_EID_IDX 10/08/2007 22:01:33 CM_ENROLL_TA_ES_IDX 10/08/2007 22:01:34 CM_ENROLL_TA_PERSON_IDX 10/08/2007 22:01:37 CM_ENROLL_TB_EID_IDX 10/08/2007 22:01:50 CM_ENROLL_TB_ES_IDX 10/08/2007 22:01:51 CM_ENROLL_TB_PERSON_IDX 10/08/2007 22:01:55
Monday 10-08-2007
- 23:42 (Prabhu)
No Format Here is what I have done. 1. ran our problem query(section) at 10:30pm after stats. Noticed full scans. 2. took AWR 3. dropped stats for the two tables involved in the query. 4. ran the query again. Noticed idexes being used. 5. took AWR 6. reran the query to make sure indexes are in place PS: cron job will be switching tables from "A" to "B" tomorrow at 06:00 hrs.
- AWR 2007-10-08 22:00
- AWR 2007-10-08 23:19
- 22:39 (Thomas) Detected Full Table Access
No Format TIMESTAMP: 10-08-2007 22:39:25 OPERATION: TABLE ACCESS OPTIONS: FULL OBJECT_OWNER: SAKAI_EXTERNAL OBJECT_TYPE: TABLE TIME: 79 COST: 6506 CARDINALITY: 456438 BYTES: 199463406 CPU_COST: 379747898 IO_COST: 6479 SQL_FULLTEXT: SELECT sectioncmi0_.eid AS eid248_, sectioncmi0_.CATEGORY AS category248_,sectioncmi0_.title AS title248_,sectioncmi0_.description AS descript4_248_,sectioncmi0_.createdby AS createdby248_,sectioncmi0_.createddate AS createdd6_248_,sectioncmi0_.lastmodifiedby AS lastmodi7_248_,sectioncmi0_.lastmodifieddate AS lastmodi8_248_,sectioncmi0_.courseoffering_eid AS courseof9_248_,sectioncmi0_.enrollmentset_eid AS enrollm10_248_,sectioncmi0_.PARENT AS parent248_, sectioncmi0_.MAXSIZE ASmaxsize248_FROM sakai_external.coursemanagement_section sectioncmi0_,sakai_external.coursemanagement_instructors instructor1_WHERE instructor1_.section_eid = sectioncmi0_.eidAND instructor1_.person_eid = '00125158'
- 22:23 (Thomas) Detected Full Table Access
No Format TIMESTAMP: 10-08-2007 22:23:00 OPERATION: TABLE ACCESS OPTIONS: FULL OBJECT_OWNER: SAKAI_EXTERNAL OBJECT_TYPE: TABLE TIME: 79 COST: 6506 CARDINALITY: 456438 BYTES: 199463406 CPU_COST: 379747898 IO_COST: 6479 SQL_FULLTEXT: /* Formatted on 2007/10/08 22:22 (Formatter Plus v4.8.6) */SELECT sectioncmi0_.eid AS eid248_, sectioncmi0_.CATEGORY AS category248_, sectioncmi0_.title AS title248_, sectioncmi0_.description AS descript4_248_, sectioncmi0_.createdby AS createdby248_, sectioncmi0_.createddate AS createdd6_248_, sectioncmi0_.lastmodifiedby AS lastmodi7_248_, sectioncmi0_.lastmodifieddate AS lastmodi8_248_, sectioncmi0_.courseoffering_eid AS courseof9_248_, sectioncmi0_.enrollmentset_eid AS enrollm10_248_, sectioncmi0_.PARENT AS parent248_, sectioncmi0_.MAXSIZE AS maxsize248_ FROM coursemanagement_section sectioncmi0_, coursemanagement_instructors instructor1_ WHERE instructor1_.section_eid = sectioncmi0_.eid AND instructor1_.person_eid = :1
...