Page Comparison

...

• 14:11 (Prabhu)
  • Created AWR report !

    Attachments
    old true patterns .awrrpt_1_3235_3236.txt

    !
• 14:09 (Drew)

  • No Format

    If you are sure without stats will help the performance (as Prabhu as shown the table has not been analyzed), after the auto stats collection, run the following as a DBA, will delete the table stats: EXEC DBMS_STATS.delete_table_stats('SAKAI_EXTERNAL', 'COURSEMANAGEMENT_SECTION_TA'); or EXEC DBMS_STATS.delete_table_stats('SAKAI_EXTERNAL', 'COURSEMANAGEMENT_SECTION_TB'); This will remove the stats for that table and put the table into a status as of now, which makes the query perform better. Can you run a AWR report and an autotrace and make sure the non-stats table performs better (as of now)? Be careful if you remove the stats of a table because it may affect other queries.

• 13:05 (Drew)

  • No Format

    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)

  • 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

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

• 07:00 (Patrick) I've rebuilt the TA tables with current data and the synonyms now point there. The job completed at 7am.

...