I received an email that the online Schedule Builder is live. I might regret saying this but, based on an initial glance, it's an improvement on previous efforts. (Which wouldn't be difficult!)
For anyone who is interested in seeing some of the cool OEM12c Performance Page stuff, my session is :-
Session ID: CON5389
Session Title: Oracle Enterprise Manager 12c Cloud Control Performance Pages: Falling in Love Again
Venue / Room: Moscone West - 3014
Date and Time: 10/2/12, 17:00 - 18:00
I'm looking forward to it!
(Updated Later - wow, it really is a vastly improved Schedule Builder!)
Jul 29: Final Redgate Webinar
Because there have been a number of interesting questions that have cropped up during the post-presentation Q&A sections of both Webinars, James and I decided that a third and final session might be a good idea during which I'll try to work through maybe a handful of the more detailed questions or show other examples and case studies that I've used in the past. At this stage the plans are pretty flexible although the schedule and registration details are here. (and yes, that is the day I get back from Dallas after the Enkitec E4 conference but I'm hoping that the adrenalin rush will help with the jet-lag )
I already have some questions in mind from the earlier webinars, but if you have any OEM Performance Page related questions that you'd like to see covered, feel free to post them in the comments section below. Even if you asked a question during the Q&A and it wasn't answered at the time, it might be worth posting a detailed version below because this can be quite a wide subject!
I guess that James will also invite questions in advance of the webinar. Clearly I can't hope to cover everything in a limited time period so apologies if your question isn't covered but I'll do my best to get to them all somehow.
A small problem that cropped up on a client site this week which might warrant a quick post to help any Google desperadoes that might find themselves in the same spot, not least because there's an easy workaround.
The existing application schema creation scripts used the following type of syntax to create Primary Key constraints and the underlying index in one shot against Interval Partitioned Tables. (Note, this is a simple test case I created for the Service Request that you should be able to try in other environments.)
CREATE TABLE TEST (PK_COLUMN NUMBER, NON_PK_COLUMN VARCHAR2(30)) PARTITION BY RANGE (PK_COLUMN) INTERVAL( 1) ( PARTITION RUN_0 VALUES LESS THAN (1) ); ALTER TABLE TEST ADD CONSTRAINT TEST_PK PRIMARY KEY (PK_COLUMN) USING INDEX (CREATE INDEX TEST_PK ON TEST (PK_COLUMN) LOCAL);
If you try to run this on an 126.96.36.199 database instance, it works fine. Run it on 188.8.131.52 and you'll get the following error.
ORA-00600: internal error code, arguments: [kkpoxPaxdInit0], , , , , , , , , , , 
A hunt on My Oracle Support didn't yield much apart from Bug 14230768 which looks like it's at a very early stage of discovery and being fixed. As far as I can tell from my own playing around, this only happens if you're using Interval Partitioning (which might explain why this hasn't been spotted at more sites) and the simple workaround is to split the constraint work into two steps.
CREATE TABLE TEST (PK_COLUMN NUMBER, NON_PK_COLUMN VARCHAR2(30)) PARTITION BY RANGE (PK_COLUMN) INTERVAL( 1) ( PARTITION RUN_0 VALUES LESS THAN (1) ); CREATE INDEX TEST_PK ON TEST (PK_COLUMN) LOCAL; ALTER TABLE TEST ADD CONSTRAINT TEST_PK PRIMARY KEY (PK_COLUMN)
Jul 14: OTHER_XML
Just a small tip that could make things a little easier for you one day when you are trying to work out the underlying cause of a SQL execution plan change that leads to degraded performance, after the problem has occurred.
There are plenty of more technical blog posts out there referring to the contents of the OTHER_XML column in DBA_HIST_SQL_PLAN and other dictionary views. For example, this Jonathan Lewis post and the follow-up comments focus on the peeked values of bind variables.
However, it occurred to me one day that the content of the OTHER_XML column, as well as containing potentially very useful information to help understand the plan difference is also, well, erm XML. Which means that rather than trying to decipher text output in sqlplus or TOAD or whatever you use, you can just spool it to a file and open it in a browser, where the implicit structure makes for an easier read. To show you a specific example from a real performance issue where I knew the SQL_ID of the problematic statement already (although I don't have the statement outputs any more) :-
1) Identify the various execution plans :-
SELECT snap_id, sql_id, plan_hash_value FROM dba_hist_sqlstat WHERE sql_id='a01f43qrd6a7g' ORDER BY snap_id;
2) Drag out the various contents of the OTHER_XML column for this statement :-
SELECT other_xml FROM dba_hist_sql_plan WHERE sql_id='a01f43qrd6a7g' AND other_xml is not null;
By spooling individual results to different XML files, you end up with a couple of files like this.
Opening these in two different browser tabs allows you to flick between the two and, as well as seeing useful information about peeked binds, optimiser version and configuration, you should be able to spot quite quickly that Cardinality Feedback was used in generating one of the execution plans. That was the cause of the performance degradation in this case.
Of course this is just a simple example, but the main point of the post was that there might be some pretty detailed information about the optimiser environment for old execution plans in your AWR repository and that viewing it in a browser might make it a little easier to interpret if you're not absolutely married to parsing text files.
P.S. Yes, that optimizer_index_cost_adj value is for real, too
Jul 12: Red Gate Webinars
There were a number of questions after the webinar that I didn't feel there was sufficient time to answer properly so if anyone who asked those questions wants to post them in the comments below, maybe I can attempt to give more detailed answers here or in a seperate post.
I'll be presenting the second part next Thursday which will focus on some of the new features of OEM 12c, particularly ASH Analytics. I heard from the James Murtagh of Red Gate that it looks like it's going to be a very well attended session so thanks to those who have already registered. I'm really looking forward to it.