Home » RDBMS Server » Performance Tuning » How to revert the work done by accept_sql_profile procedure & Issues to watch out with SPM (merged) (Oracle 11.2.0.4 on Linux)
How to revert the work done by accept_sql_profile procedure & Issues to watch out with SPM (merged) [message #618662] Mon, 14 July 2014 13:25 Go to next message
orausern
Messages: 826
Registered: December 2005
Senior Member
Hi Experts,


I have an issue with a sql that is taking more than 15 seconds which is not acceptable. I ran the SQL Tuning advisor (by DBMS_SQLTUNE.create_tuning_task and execute_tuning_task etc.) and I am getting a recommendation to accept a profile. It says that this will achieve 99.99% improvement. This is a critical issue and I need to be able to rollback if this does not work. Can someone help me with the steps that are involved in rolling back the work done by accept_sql_profile procedure?



I am not going into the exact issue that this sql has but would like to ask this generic question...if we accept_sql_Profile and then want to rollback, is it possible, and if yes how we do that?



Thanks,

Nirav
Re: How to revert the work done by accept_sql_profile procedure [message #618664 is a reply to message #618662] Mon, 14 July 2014 13:31 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
when all else fails Read The Fine Manual

http://docs.oracle.com/cd/E16655_01/appdev.121/e17602/d_sqltun.htm#ARPLS72932
Re: How to revert the work done by accept_sql_profile procedure [message #618665 is a reply to message #618662] Mon, 14 July 2014 13:35 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Have you studied SQL Profiles? If you look at the dbms_sqltune pckage, you'll see the procedures for working with them.

But really, profiles are terrible way to tune your SQL. You should be determining why the CBO is coming up with a poor plan. It is probably statistics. Most likely, lack of extended statistics. Fix that, and you have a real solution. Profiles are like hints: just a way of covering up your mistakes.
Re: How to revert the work done by accept_sql_profile procedure [message #618666 is a reply to message #618665] Mon, 14 July 2014 13:41 Go to previous messageGo to next message
orausern
Messages: 826
Registered: December 2005
Senior Member
Thank you both the experts! I understood now that the sql profile can be dropped as well. Thanks a lot!!

JohnWatson ,
about your point:
This sql is a messy 200+ line sql with about 9 table joins wiht 4 subqueries each having 8 to 9 table joins. Now from sqlplus it uses the right index and from app it doesn't. I ran the sql tuning advisor and it gave the accept profile recommendation. That is why I am exploring it. I know absolutely where Oracle is going wrong - it is not picking two of the absolutely required funciton based index from App and when I ran same from sqlplus, using same bind variables, it picks them up! This is a strange issue I am dealing with.

thanks a lot.
Re: How to revert the work done by accept_sql_profile procedure [message #618668 is a reply to message #618666] Mon, 14 July 2014 13:45 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Bind Variable Peeking may be cause of bad choice
Re: How to revert the work done by accept_sql_profile procedure [message #618669 is a reply to message #618666] Mon, 14 July 2014 13:46 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
OK, so why is the optimizer ignoring the index? Probably missing extended stats. Perhaps it used the index when you ran it second time because of cardinality feedback following te firsts run.
A hackers way that might fix it is to set optimizer_dynamic_sampling to at least 4. I do that on every database where there is not a decent DBA to tune properly. It never causes a problem, and sometimes produces spectacular benefits. But like profiles, all it is doing is covering up your mistakes.
Re: How to revert the work done by accept_sql_profile procedure [message #618671 is a reply to message #618669] Mon, 14 July 2014 13:54 Go to previous messageGo to next message
orausern
Messages: 826
Registered: December 2005
Senior Member
Thank you BlackSwan and JohnWatson.

1) About Bind variable peeking: When i ran the sql from sqlplus ,it is ALLWAYS fast. When the app runs it, it is ALLWAYS slow. What can I do to get app go fast?
2) About optimizer_dynamic_sampling : well the sql is running from jdbc app. from sqlplus , this sql is never an issue. from jdbc it is slow. So I can't do an alter session there. since the sql comes from jdbc, and it is an ntier architecture, how do I set this parameter to 4 for that sql to see this value?

Re: How to revert the work done by accept_sql_profile procedure [message #618672 is a reply to message #618671] Mon, 14 July 2014 13:57 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
I think you should know how to set an instance parameter.
Re: How to revert the work done by accept_sql_profile procedure [message #618675 is a reply to message #618669] Mon, 14 July 2014 13:59 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Quote:

I know absolutely where Oracle is going wrong - it is not picking two of the absolutely required funciton based index from App


How does the execution plan change when a SQL is executed from application? App just connects to DB, executes the SQL and rest should ideally remain same whether you execute it from SQL*Plus or App.

You also said that, App is not able to use the indexes, however, while executing through SQL*Plus optimizer uses required indexes. So, how did you find this, what are your observations. Please share.

Edit : fixed quote tags

[Updated on: Mon, 14 July 2014 14:00]

Report message to a moderator

Re: How to revert the work done by accept_sql_profile procedure [message #618677 is a reply to message #618671] Mon, 14 July 2014 14:33 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
When i ran the sql from sqlplus ,it is ALLWAYS fast. When the app runs it, it is ALLWAYS slow.


Often due to wrong variable type which to implicit conversion and prevent from index usage.

Re: How to revert the work done by accept_sql_profile procedure [message #618678 is a reply to message #618677] Mon, 14 July 2014 14:48 Go to previous messageGo to next message
orausern
Messages: 826
Registered: December 2005
Senior Member
Hi Lalit,

I found by checking the GV$SQL_PLAN and the AWR reports that the application sql is slow and that it is using the wrong index. I ran from sqlplus and generated the query plan using the dbms_xplan and found that it is using the right index from sqlplus. Now that is not supposed to happen but it does happen!

Hi Michel,
What do to here in this situation?

Thanks!
Re: How to revert the work done by accept_sql_profile procedure [message #618679 is a reply to message #618678] Mon, 14 July 2014 14:49 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/m/433888/#msg_433888
Please refer to URL above & be sure to provide the details requested:
1) DDL for all tables & indexes
2) EXPLAIN PLAN
3) output from SQL_TRACE & tkprof
Re: How to revert the work done by accept_sql_profile procedure [message #618680 is a reply to message #618678] Mon, 14 July 2014 15:01 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
nirav_hyd wrote on Tue, 15 July 2014 01:18

I found by checking the GV$SQL_PLAN and the AWR reports that the application sql is slow and that it is using the wrong index. I ran from sqlplus and generated the query plan using the dbms_xplan and found that it is using the right index from sqlplus.


One very important point to be taken care of, when you say that you are manually checking the execution plan of the query which is different from that used by the application, are you using the same environment for both. I have seen developers complaining about performance issue of a query and then realize they checked manually from test environment. Sounds silly, but makes sense.

Another thing, if bind value changes while getting it through application, then optimizer might decide to change the execution plan to make it an optimal pan.

Re: How to revert the work done by accept_sql_profile procedure [message #618705 is a reply to message #618678] Tue, 15 July 2014 00:15 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
Hi Michel,
What do to here in this situation?


Only one thing can be done: change the application code.

Re: How to revert the work done by accept_sql_profile procedure [message #618733 is a reply to message #618678] Tue, 15 July 2014 02:33 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
nirav_hyd wrote on Mon, 14 July 2014 20:48
Hi Lalit,

I found by checking the GV$SQL_PLAN and the AWR reports that the application sql is slow and that it is using the wrong index. I ran from sqlplus and generated the query plan using the dbms_xplan and found that it is using the right index from sqlplus. Now that is not supposed to happen but it does happen!

Hi Michel,
What do to here in this situation?

Thanks!
When you say that you "generated the query plan using the dbms_xplan and found that it is using the right index from sqlplus" how did you do this? With EXPLAIN PLAN or AUTOTARCE? If so, the plan you see may bare no relation to reality, because they do not use bind peeking or cardinality feedback.
Re: How to revert the work done by accept_sql_profile procedure [message #618809 is a reply to message #618733] Tue, 15 July 2014 14:42 Go to previous messageGo to next message
orausern
Messages: 826
Registered: December 2005
Senior Member
we decided that in the longer term the code should be changed to fix the issue (as Michel rightly pointed out). Also found an issue with SQL Profiles. Inspite of accepting the profiles oracle was using wrong plan! So instead I used the SPM and so far it is working ok. Thanks a lot all the experts for sharing your expertise!

[Updated on: Tue, 15 July 2014 14:43]

Report message to a moderator

Issues to watch out with SPM [message #618925 is a reply to message #618662] Wed, 16 July 2014 10:02 Go to previous messageGo to next message
orausern
Messages: 826
Registered: December 2005
Senior Member
Hi Experts,

I had a very tricky issue with a long 200+ line sql. The sql ideally should be simplified but that can't be done in short term -this is a given fact. The sql had a strange issue that when we run it from sqlplus it came back in less than a second and when we run from App. it took anywhere from 15 to 20 seconds. In the short term, so I decided to use SPM. I found good and bad plans and fixed the good plan and disabled the bad plans. (by using procedures like: DBMS_SPM.load_plans_from_cursor_cache, DBMS_SPM.alter_sql_plan_baseline and setting enabled attribute to NO for the bad plans, and setting the attribute 'fixed' to YES for the good plan etc.)


I wanted to ask there are there any known pitfalls with SPM - like even if the good plan is fixed it will not get used in some situations etc.? Has someone come across with a bad experience trying to use SPM and finding it was not worknig out? Bythe way, in our tesing (it has been just one day though) we are finding SPM to work thus far.

Thanks,
Re: Issues to watch out with SPM [message #618928 is a reply to message #618925] Wed, 16 July 2014 10:11 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
First tell/find the root cause for multiple execution plans. Are bind variables involved?
Re: Issues to watch out with SPM [message #618933 is a reply to message #618928] Wed, 16 July 2014 10:48 Go to previous messageGo to next message
orausern
Messages: 826
Registered: December 2005
Senior Member
Yes the sql uses about 65 bind variables. Changing the sql will be taken up but in a few weeks. For now we are usig SPM.
Re: Issues to watch out with SPM [message #618937 is a reply to message #618933] Wed, 16 July 2014 11:08 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
I feel this is just a workaround. Bind peeking has been a big problem, it was thought that adaptive cursor sharing would take care of it. Alas, it just does it work post the bind peeking problem when Oracle finds it. It is a kind of reactive technique. How/what made you to use SPM as a workaround technique temporarily, what other options did you consider?
Re: Issues to watch out with SPM [message #618939 is a reply to message #618937] Wed, 16 July 2014 11:16 Go to previous message
orausern
Messages: 826
Registered: December 2005
Senior Member
We tried profiles but did not work...and we are using SPM. Yes it is a workaround only. my point is, I wish to know if there are known isseus with using SPM. If it suddenly stops working even after a plan has been fixed etc.

Previous Topic: Oracle 11g on VMWARE
Next Topic: Performance Tuning
Goto Forum:
  


Current Time: Thu Mar 28 14:20:05 CDT 2024