Monday 10-08-2007
Sunday 10-07-2007
- 13:05 (Drew)
I forgot to mention this parameter can be adjusted on line without affecting end users. When you see the full table scan (with all the stats), you adjust this parameter (as sys) and then run the plan and stats (set autotrace traceonly) in the application owner account and see if you can see the plan change. This parameter is easy to adjust without shutting down the database. When I worked at at Ford Motor company, we adjusted that parameter to achieve better performance.
- 12:51 (Drew)
Without the stats, the query will use rule-based optimizer (RBO) and will pick up all of the indexes it can find because it's one of the rules. Generally, if the tables are created correctly, the stats would certainly help. The query is a simple one and should not be difficult for CBO to find the best way. Here I think the drop and recreate table is not a good practice in that case. The two tables (before dropped and the recreated one) are different objects in the database with different object id. Oracle will gather stats for that table for sure. If you run: select table_name, to_char(last_analyzed,'mm/dd/yyyy hh24:mi:ss') from user_table where lower(table_name)='coursemanagement_section_ta'; You will find when it's last analyzed. Have you tried to adjust this parameter, optimizer_index_cost_adj ? If not, please try to read through the following and see if that can help. To start with, you can let this parameter equals 50. http://www.dba-oracle.com/oracle_tips_cost_adj.htm
- 12:20 (Prabhu) I disable monitor when tables dropped and enable when new ones created as below.
---Enable monitoring SQL> alter index sakai_external.CM_INST_TA_PERSON_IDX monitoring usage; Index altered. SQL> alter index sakai_external.CM_SECTION_TA_EID_IDX monitoring usage; Index altered. SQL> select index_name, table_name, monitoring, used, end_monitoring from v$object_usage; no rows selected ---Disable monitoring alter index sakai_external.CM_INST_TB_PERSON_IDX nomonitoring usage; alter index sakai_external.CM_SECTION_TB_EID_IDX nomonitoring usage;
- 07:00 (Patrick) I've rebuilt the TA tables with current data and the synonyms now point there. The job completed at 7am.
Saturday 10-06-2007
- 24:00 (Oracle) run stats
- 06:58 (Patrick) I switched to the recreated TB tables. The job started at 6:58am and completed at 7:06.
- I checked the problem query on both the TA and TB tables and it looks like the indexes are still being used.
- I noticed that there are 151 pairs of duplicate EID's in the SECTION table. Have we considered changing the view to weed those out?
Friday 10-05-2007
- 22:00 (Oracle) run stats
- 14:49 (Patrick)
Thanks, Brian. I created the indexes and added these statements to the script that recreates the tables for the synonym switch. There are currently no outstanding queries in the poor performance category.
- 11:43 (Brian)
Oh, I see, I didn't put an index on it because it was all null. Okay, here are indexes for the courseset_eid and courseoffering_eid columns, both of which are all null. CREATE INDEX CM_MEMBERSHIP_TA_CS_IDX ON COURSEMANAGEMENT_MEMBERSHIP_TA (COURSESET_EID) STORAGE(INITIAL 50M NEXT 10M); CREATE INDEX CM_MEMBERSHIP_TB_CS_IDX ON COURSEMANAGEMENT_MEMBERSHIP_TB (COURSESET_EID) STORAGE(INITIAL 50M NEXT 10M); CREATE INDEX CM_MEMBERSHIP_TA_CO_IDX ON COURSEMANAGEMENT_MEMBERSHIP_TA (COURSEOFFERING_EID) STORAGE(INITIAL 50M NEXT 10M); CREATE INDEX CM_MEMBERSHIP_TB_CO_IDX ON COURSEMANAGEMENT_MEMBERSHIP_TB (COURSEOFFERING_EID) STORAGE(INITIAL 50M NEXT 10M); That will create the indexes on both tables.
- 11:18 (Patrick)
sar -q Linux 2.6.9-55.0.6.ELsmp (caje) 10/05/2007 12:00:01 AM runq-sz plist-sz ldavg-1 ldavg-5 ldavg-15 12:10:01 AM 1 1211 0.72 0.91 0.98 02:10:01 AM 0 1211 0.12 0.18 0.47 02:20:02 AM 0 1215 1.32 1.28 0.89 02:30:01 AM 0 1209 0.09 0.36 0.61 02:40:01 AM 0 1211 0.05 0.14 0.37 02:50:01 AM 0 1215 0.95 0.49 0.38 03:00:09 AM 1 1212 0.05 0.39 0.45 03:10:01 AM 0 1211 0.23 0.20 0.31 03:20:01 AM 0 1214 0.21 0.20 0.24 03:30:01 AM 1 1208 1.15 0.91 0.57 03:40:01 AM 0 1210 0.06 0.45 0.54 03:50:02 AM 0 1217 0.52 0.28 0.37 04:00:01 AM 1 1214 1.21 1.36 0.93 04:10:02 AM 0 1225 1.55 1.86 1.45 04:20:02 AM 0 1226 1.24 1.54 1.42 04:30:02 AM 0 1220 1.36 1.19 1.26 04:40:02 AM 0 1220 1.14 1.45 1.41 04:50:07 AM 0 1224 1.34 1.28 1.28 05:00:06 AM 1 1220 1.32 1.36 1.34 06:00:01 AM 0 1215 0.65 0.33 0.51 06:40:02 AM 0 1212 1.16 0.75 0.66 07:20:01 AM 0 1222 1.57 2.18 1.83 07:40:01 AM 1 1213 0.63 1.14 1.64 08:00:01 AM 5 1216 4.51 3.28 2.21 08:40:04 AM 13 1227 17.44 17.31 12.78 08:50:13 AM 14 1254 20.77 19.95 16.40 09:10:19 AM 9 1235 21.66 23.12 20.19 09:30:01 AM 0 1237 1.48 3.77 10.36 09:40:01 AM 0 1236 0.57 1.29 5.91 10:00:06 AM 2 1235 1.67 1.40 2.42 Average: 2 1220 2.89 3.01 3.01 It's really frustrating that, even at 7:40am, the system is breezing along. Prabhu and I will work to schedule a rebuild of the tables at 7:00 am each morning. That job runs about 20 minutes. We will then switch the synonyms by 7:30am. We are also opening a service request with Oracle.
- 11:17 (Drew)
If execution plan stability is an issue for you after modifying the Oracle default window, you can consider the Plan Stability specified in this Oracle document: http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/outlines.htm#sthref1787
- 09:07 (Patrick)
This query is active for a number of sessions (around 10) today: 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 Here is the explain plan: SELECT STATEMENT () TABLE ACCESS (BY INDEX ROWID) COURSEMANAGEMENT_INSTRUCT_TA NESTED LOOPS () TABLE ACCESS (FULL) COURSEMANAGEMENT_SECTION_TA INDEX (RANGE SCAN) CM_INST_TA_PERSON_IDX
- 08:47 (Patrick)
Building a set of new tables after DB performance degraded
Thursday 10-04-2007
- 15:20 (Patrick) Run coursemanagement_merge_procs.sql
- coursemanagement_merge_procs.sql
- Script run fine for:
- coursemanagement_section
- coursemanagement_offering
- coursemanagement_enrollment
- Script hung during the delete records process for:
- coursemanagement_instructors
- coursemanagement_membership
- 11:35 (Patrick) Recreated the 5 CM tables after DB load increased over 20
Wednesday 10-03-2007
- ... ?
Tuesday 10-02-2007
- ... ?
Monday 10-01-2007
- ... ?
Sunday 09-30-2007
- ... ?
Saturday 09-29-2007
- ... ?