I was also asked a couple of questions that I wanted to follow up on. First, someone asked about the use of dbms_sqltune. As it turns out, it does require a license for the Oracle Tuning Pack in order to be able to legally use that package. That means that SQL Monitor reports are off limits without that license. Sigh...
Another person asked about how to create a SQL Profile and I included a script I use that was created by Kerry Osborne called create_sql_profile_awr.sql. You can get that script and several others along with a great overview of SQL Profiles at Kerry's blog.
Again, thanks to everyone who attended and I hope you found it interesting and helpful. Thanks also to the folks at Red Gate and particularly James Murtaugh who leads the charge at AllThingsOracle.com. Keep your eyes on the site for upcoming webinars, great articles and more!
6 comments:
Karen, thanks for the great webinar !
Since now, I enjoyed SQL Monitoring only via OEM, during the webinar I was playing around with dbms_sqltune.report_sql_monitor (on production 11.2.0.2 linux/x64 )
about 20 minutes later, we received an
ORA-600 [kdifind:kcbget_24]
which we didn't get before in the last 18 months on this system.
The ORA-600 occurred while inserting into a partitioned IOT.
The ORA-600 could be reproduced with the same values several times.
we did an
alter system flush shared_pool;
alter system flush shared_pool;
alter system flush buffer_cache;
alter system flush buffer_cache;
, after which the Exception disappeared and didn't came up again since then.
because of the coincidence we conject the ORA-600 could have be caused by me playing with dbms_sqltune.report_sql_monitor
Are you aware of any bugs here ?
I couldn't find something about it on Note 1380544.1
I was in a conference session where an Oracle employee explained the reason sql monitor is licensed under the Tuning Pack in spite of the fact that it is a diagnostic tool. The "tuning" group developed the tool. At that point politics trumped reason and the "tuning" group got credit for their work via licensing. ugh.
Chris - I have to wonder if the "reason" is really more like the "excuse". :) The tools are key elements and when they license them separately - causing it to be an issue for some people to purchase - I think that's a mistake. If people have the tools needed to help them find and fix problems, that seems to me that's a good thing and would engender only good results.
Anyway, thanks for that bit of first-hand info!
Karen
Is querying v$sql_monitor view also off limits if we don't have Tuning pack license?
Marogel -
Be very careful and don't confuse correlation with causation. Just because two things (in this case, the appearance of ORA-600 around the time you were playing with dbms_sqltune) occur in proximity to one another, it doesn't necessarily imply that one caused the other.
For what you describe, the ORA-600 occurred while inserting into a partitioned IOT and was reproducible. Why not relate the problem to that and pick dbms_sqltune instead? It seems to me there is more direct correlation between the insert and the ORA-600 than with dbms_sqltune.
But, the bottom-line is that further investigation needs to be done to determine the actual root cause. Until you have data to support your root cause analysis, you're guessing. And, in my experience, I'd rather know than guess.
Also, all that's happening when you produce a SQL Monitor report is that some dynamic views (like v$sql_monitor) are queried. It's not really any different than querying v$sql, v$session and so on.
I am not aware of any bugs related to using dbms_sqltune. I'd simply suggest you guys do a deeper root cause analysis and not leap to any conclusions.
Good luck!
Karen
Prashant, Without the Tuning pack license you are legally not supposed to query that view. You can do it, as there isn't an "off" switch for all the features if you don't own the licenses, but it would be illegal to do so.
Karen
Post a Comment