...
- 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
- 09:15 (Prabhu)
Attachments patterns awrrpt_1_3250_3251.txt
- 08:57 (Drew)
No Format After reading how the tables are created and the synonyms are used in the database, I start to think the execution plan mess could be caused by the use of synonyms (just think). Please read the following two articles/email regarding the synonym and performance. http://expertanswercenter.techtarget.com/eac/knowledgebaseAnswer/0,295199,sid63_gci977691,00.html http://www.freelists.org/archives/oracle-l/11-2004/msg00075.html In the first article, it says "The use of private synonyms can cause multiple versions of the same SQL text to be maintained in the library cache, thus reducing the sharing of SQL". I would think this is even true when we have different table definitions. I am thinking if you can test a procedure without using synonyms like the following: One set of script will cover all the reload. The "alter table rename" is as fast as you create the synonym. <> --The table coursemanagement_section_new and all it's indexes have been created. --Two set of the tables are used. Only one reload script is needed. INSERT INTO coursemanagement_section_new SELECT * FROM coursemanagement_section_v; commit; alter table coursemanagement_section rename to coursemanagement_section_old; alter table coursemanagement_section_new rename to coursemanagement_section; analyze table coursemanagement_section compute statistics; alter table coursemanagement_section_old rename to coursemanagement_section_new; truncate table coursemanagement_section_new; --repeat the above in each relaod. --That will get rid of the synonym. </> This is just some thoughts based on the articles on synonyms and I don't know if this can resolve the problem. I think it worth of trying if other options cannot resolve the problem. In case you don't have access to the first website listed above, I copy the text and attach it at the end of this note (see below).
- 05:05 (Patrick) At of 5:05 am, Monday, 10/8, the "B" set of external tables are current and in production.
...