Monday, November 21, 2011

Oracle10g+ Plan Table


Oracle is now maintaining a PLAN_TABLE for us. This table is used extensively in the SQL tuning process. It is also used by Oracle's autotrace and other tools. I show various uses for it in my SQL Tuning course. Oracle's version is owned by SYS and is named PLAN_TABLE$. It does have public access.

To see if it is working correctly for you:
  • Simply DROP your PLAN_TABLE.
  • in sql*plus...'set autotrace on'
  • execute ANY sql (select * from emp; for example...).

IF sql*plus complains about a missing Plan Table, have your administrator create a public synonym for SYS.PLAN_TABLE$. This is easier than using PLAN_TABLE$ in each of your scripts/TOAD/etc. Some tools such as SQL Developer, the PLAN_TABLE name is not adjustable.

Good luck!