...
- Change Oracle's statistics gathering function to run between 04:00 - 05:00 instead at 22:00
- Oracle assumes to be the only active user during stats gathering
- All the tables, indexes, views need to be created in the same order via scripts
- Switch to RBO (Rule Based Optimizer) for certain views
- Eventual deterministic/stable performance after queries have been tuned
- Need to be careful that we're not joining these non-analyzed tables to analyzed ones
- We may need to switch our OPTIMIZER_MODE setting over to CHOOSE?
- Keep in mind after rebuilding the CM tables there are only 2 tables and 2 queries that run into trouble regularly. We should be careful not to implement DB wide strategies to attack these 2 problems. We need to understand why they differ and see if they can be tuned. (KA)
Activity Log
Thursday 11-11-2007
- 06:07 (Patrick) Recreated 5 CM tables
Wednesday 10-10-2007
- 23:18 (Prabhu)
No Format I think, we need to verify colums widths/datatypes of underlying tables, view, mviews. In case of a join same columns of different tables should have same datatype and column length to make CBO work correctly. On RAC and TEST i set the hidden parameter "_keep_remote_column_size" to TRUE, which will allow mviews to maintain the column size same as its source(banner). We did not have this parameter set to true in production. When we had a problem with mview refresh, we fixed it by altering the column widths of following as below. This was a quick fix from oracle support. I feel, it is worth trying to set this paramter and recreate these two mviews. Thanks, Prabhu alter table BANNER_ENROLLMENTSET modify CATEGORY varchar2(25); alter table BANNER_ENROLLMENTSET modify CREATEDBY varchar2(25); alter table BANNER_ENROLLMENTSET modify LASTMODIFIEDBY varchar2(25); alter table BANNER_ENROLLMENT modify CREATEDBY varchar2(25); alter table BANNER_ENROLLMENT modify LASTMODIFIEDBY varchar2(25); alter table BANNER_CM_SECTION modify CREATEDBY varchar2(25); alter table BANNER_CM_SECTION modify LASTMODIFIEDBY varchar2(25);
- 22:19 (Prabhu) Removed Stats from COURSEMANAGEMENT_INSTRUCT_TA, COURSEMANAGEMENT_SECTION_TA and all associated Indexes
- 18:07 (Prabhu) Recreated 5 CM tables
- 06:07 (Prabhu) Recreated 5 CM tables
...