In praise of bloggers (along with sql_profiles and RAC interconnect pings)
Posted by John Hallas on March 12, 2014
Today I am going to mention two articles which I came across in the last few days whilst investigating problems and talk about the background to the problems but also praise the articles. I am sure many of us have run the SQL Tuning advisor from within OEM or using the DBMS_SQLTUNE package from the command line and often it recommends a sql_profile that should be applied, (invariably giving 99.98 perceived benefits). Now I see this as both a good thing and a bad thing. Yes it is nice to apply the profile and hopefully fix the problem but I would also like to know what the profile is doing and that is well-hidden. I agree that there is the option to look at current and new explain plans but sometimes it is difficult to really understand what hints have been used to get the new plan, especially on a statement that has many lines in the explain plan. So why is this important might you ask, just go and select the apply button and forget about it. Well I have two very good reasons why it is important.
- I want to know so that I develop my tuning skills for next time
- If you are using any sort of ITIL process or change management function then you should be able to raise a change which documents what you have done and in this case all you can say is that you have ‘performed magic’ and the regression path is to drop the profile using a command similar to the one below. I do not think that is being fully in control of the situation.
BEGIN DBMS_SQLTUNE.drop_sql_profile ( name => ‘SYS_SQLPROF_014c4f70d130c001’, ignore => TRUE); END; /
To that end I spent some time researching all the views to see where the hints used were documented within the data dictionary and believe me there are many associated Advisor information views, such as DBA_ADVISOR_TASKS, DBA_ADVISOR_EXECUTIONS, DBA_ADVISOR_FINDINGS, DBA_ADVISOR_RECOMMENDATIONS, and DBA_ADVISOR_RATIONALE views.SQL tuning information views, such as DBA_SQLTUNE_STATISTICS, DBA_SQLTUNE_BINDS, and DBA_SQLTUNE_PLANS views.SQL tuning set views, such as DBA_SQLSET, DBA_SQLSET_BINDS, DBA_SQLSET_STATEMENTS, and DBA_SQLSET_REFERENCES views. Not forgetting DBA_SQL_PROFILES.
I tracked down the key views which made up the dba_sql_profile view as being sqlobj$, sql$text,sql$,sqlobj$auxdata with SQLOBJ$AUXDATA being the key view and I really did not get much further than that. However once I had the correct keyword to search on, Google pointed me at a blog by Christian Antognini that gave the exact query I was looking for. How he got to that information I do not know, probably by working through the source code of various packages, which I had assumed were wrapped. However it was done it is a fantastic piece of analysis and that is one of the two sites I am giving kudos to today.
The query to show the hints used in the profile is below and I think it would be an under-estimate if I said that I could have worked full-time for the next month and not managed to produce the 3rd line of the script below.
SELECT extractValue(value(h),'.') AS hint FROM sys.sqlobj$data od, sys.sqlobj$ so, table(xmlsequence(extract(xmltype(od.comp_data),'/outline_data/hint'))) h where so.signature = od.signature AND so.category = od.category AND so.obj_type = od.obj_type AND so.plan_id = od.plan_id /
OPT_ESTIMATE(@”SEL$58A6D7F6″, NLJ_INDEX_SCAN, “OI”@”SEL$1”, (“MI”@”SEL$1”), “DWR_OCD_SKU_ITEM_IDX2″, SCALE_ROWS=0.01815231016)
OPT_ESTIMATE(@”SEL$58A6D7F6”, NLJ_INDEX_FILTER, “OI”@”SEL$1”, (“MI”@”SEL$1”), “DWR_OCD_SKU_ITEM_IDX2”, SCALE_ROWS=0.01815231016)
PS Since I posted this blog entry Anju Garg pointed me to a post she had written on sql_profiles and it actually gives more information than the one from Christian that I highlighted so if you are interested in the hints that make up a sql profile please read her blog on sql_profiles. However the main point of the post was the variety and depth of knowledge that bloggers post these days and how it helps us all gain more information and to do our jobs in a better manner.
On the same day (oh what joy my life is) I was looking at a problem where we had been seeing client timeouts on a RAC cluster and I had ended up looking at the Ping responses in the AWR global report. That drove me to look at a view I was not familiar with – DBA_HIST_INTERCONNECT_PINGS. Once again searching Google I came across a 2013 Openworld presentation entitled “RAC 11gR2 performance with OEM 12c and AWR data “ by Tim Quinlan. Now I am not familiar with that name and cannot find anything else by him but this presentation is a really good overview of how to look at RAC problems, especially around node evictions and it has a really good set of slides detailing the process they went through along with associated scripts .
I know I am showing my age now but when I started (V7.3) the only options you had to fix a problem was to have seen it before and made a note, own a book which covered the issue or ring Oracle support. These days with the wonder of Google, MoS documents and the willingness of people to blog about what they have found, being an Oracle DBA is much easier. On the other hand DBAs do not log as many MoS calls now as they used to do and yet the response and support from Oracle, does not, in my view, reflect that. Perhaps as a balance the breadth of functionality that Oracle now support has grown dramatically so perhaps I am being a bit harsh. It is also fair to say that the MoS calls we log now are probably much more complex because the information available does reduce the need to log minor calls.
So once again, thank you to Christian Antognini and Michael Quinlan and all the other bloggers around the world who make the DBA life much easier.