Monday 10-08-2007
Sunday 10-07-2007
- 12:51 (Drew)
No Format 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
- 07:00 (Patrick) I've rebuilt the TA tables with current data and the synonyms now point there. The job completed at 7am.
- 12:20 (Prabhu) I disable monitor when tables dropped and enable when new ones created as below.
No Format ---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;
...