From 9i onwards, if you enable event 10132 in your session, then every statement you subsequently optimise will be dumped into your trace file, along with the structure of the actual execution plan that was used. For example, from an early version of 9i:
Current SQL statement for this session:
select * from t1 where v1 = :n1
Plan Table
--------
----------------------------------------------------------------------------------------
| Operation | Name |Rows |Bytes |Cost | TQ |IN-OUT| PQ Distrib |Pstart| Pstop |
----------------------------------------------------------------------------------------
| SELECT STATEMENT | | 0 | 0 | 10 | | | | | |
| TABLE ACCESS FULL | T1 | 1 | 123 | 10 | | | | | |
----------------------------------------------------------------------------------------
You will notice that the plan allows for parallel and partitioned execution, although in this example neither feature was used, so the relevant columns stay blank. You’ll also notice that there is no dump of the predicate information – which is a little disappointing.
In 10g you get a lot more information, including the predicate information, peeked values of bind variables, optimizer environment, and a couple of other odds and ends. Moreover, the plan structure is trimmed of redundant columns.
One thought that you might want to consider before your next upgrade – if you don’t have a library of SQL statements with expected execution plans. You could enable this event system-wide for a couple of days before the upgrade and collect all the SQL with execution plans.
After the upgrade, if you find some SQL performing very badly this gives you the option for searching through the trace files to find the pre-upgrade execution plan, which should give you have some idea of how best to fix the problem.
Of course, you only want to do this system-wide setting if you’re fairly sure that the overhead won’t be excessive – after all, you get a dump into the session trace file for every statement optimised: so you probably want to check the statistic parse count (hard) to see how much of an overhead you’re going to get from all the extra I/O to the dump directory.
We talked about taking all the data from v$sql_plan and storing it in a base table. Then doing a ‘diff’ once a day to see if any plans changed.
That seems easier than dumping to a trace file.
You can then use email notification if something changed use history data to extend it.
Comment by Ryan — November 27, 2006 @ 3:00 pm UTC Nov 27,2006 |
Ryan, the drawback (or trade-off) with your approach is the frequency with which you dump the v$sql_plan view.
If you don’t dump frequently you may miss some activity SQL completely as plans can age out of memory very quickly.
If you dump frequently the workload can rapidly become prohibitive, especially if you try to correlate the plans you already have dumped with the plans that are currently in memory.
A slightly cheaper alternative may be to dump the hash_value and plan_hash_value from v$sql, and only dump a plan when you find a hash_value with a new plan_hash_value.
Comment by Jonathan Lewis — November 27, 2006 @ 3:45 pm UTC Nov 27,2006 |
Jonathan,
When 10132 set system-wide, does this also dumps oracle background process works (sqls) as when we enable instance level trace?
I guess, this would be better than 10053 event.
Jaffar
Comment by Syed Jaffar Hussain — November 27, 2006 @ 4:40 pm UTC Nov 27,2006 |
Jaffar, this would be vastly better than the 10053 as (a) it is shorter (b) it is more readable, and (c) it happens for a rule-based query. It does seem to work for background processes if you do ‘alter system’ – but many of the backgrounds reuse their SQL quite regularly, and some seem to hold the cursors – so unless the event goes in the spfile (init.ora) there may be background SQL that you don’t see. Mind you, you can’t really tune background SQL.
Comment by Jonathan Lewis — November 29, 2006 @ 7:33 pm UTC Nov 29,2006 |
I do agree with you Jonathan that we can’t tune the background sqls. I was just wondering that when we enable this event system-wite, it won’t just create a large trace file and slowdown the performance of the production. I do understand that its very rare that people will enable this event system-wide as long as its not so important to set system-wide.
I will do some R&D about this event and thanks for discussing about this event.
Jaffar
Comment by Syed Jaffar Hussain — November 30, 2006 @ 2:44 pm UTC Nov 30,2006 |
[...] you want to cover all options, you could also set event 10132 just before executing the query (which means you wouldn’t need to dump the query, as it would [...]
Pingback by How parallel « Oracle Scratchpad — March 14, 2007 @ 7:17 am UTC Mar 14,2007 |
[...] child cursors produced for the query had the same optimizer environment settings. I even did a 10132 event dump of running the query in the 2 schemas, but I could not for the life of me put my finger [...]
Pingback by techblog » Blog Archive » Oracle Linguistic Indexes — January 4, 2008 @ 2:48 pm UTC Jan 4,2008 |
You can also query SYS.WRH$_SQL_PLAN based on the sql_id in Oracle 10g. That will give you a pretty good idea of all of the different query plans used for a particular sql statement.
Comment by Mike Andrews — May 29, 2008 @ 5:33 pm UTC May 29,2008 |
Mike,
That’s a pretty useful repository for queries that are known to be expensive – if there are variations in execution plans, you may find them there. But AWR typically captures a small percentage of the SQL you run, and only the expensive SQL (by a variety of classifications).
When you upgrade it is often a small collection of previously inexpensive queries that suddenly change execution path. And those are less likely to appear in wrh$_sql_plan. (You may get lucky, of course, and find that wrh$_sql_plan – which you can query with awrsqrpt.sql – has captured some cheap plans as well as the expensive plans).
Comment by Jonathan Lewis — May 30, 2008 @ 9:26 am UTC May 30,2008 |