New create_1_hint_sql_profile.sql
I modified my create_1_hint_sql_profile.sql script (which I blogged about here: Single Hint Profiles) to allow any arbitrary text sting including quote characters. This is a script that I use fairly often to apply a hint to a single SQL statement that is executing in a production system where we can’t touch the code for some reason. For example, it’s sometimes useful to add a MONITOR hint or a GATHER_PLAN_STATISTICS hint to a statement that’s behaving badly so we can get more information about what the optimizer is thinking. I recently updated the script to allow special characters in the hint syntax. This feature is useful when you want to add something like an OPT_PARAM hint that takes quoted arguments. The change makes use of the q-Quote feature which I blogged about here: q-Quote. (the original version just barfed on quotes being input as part of the hint)
Here’s an example of how to use it:
SYS@SANDBOX1> alter session set cell_offload_processing=false;
Session altered.
Elapsed: 00:00:00.00
SYS@SANDBOX1> select avg(pk_col) from kso.skew3 where col1 < 0;
AVG(PK_COL)
-----------
1849142.5
1 row selected.
Elapsed: 00:00:28.08
SYS@SANDBOX1> @fsx
Enter value for sql_text: select avg(pk_col) from kso.skew3 where col1 < 0
Enter value for sql_id:
SQL_ID CHILD PLAN_HASH EXECS AVG_ETIME AVG_PX OFFLOAD IO_SAVED_% SQL_TEXT
------------- ------ ---------- ------ ---------- ------ ------- ---------- ----------------------------------------------------------------------
a6j7wgqf84jvg 0 2684249835 1 28.07 0 No .00 select avg(pk_col) from kso.skew3 where col1 < 0
1 row selected.
Elapsed: 00:00:00.02
SYS@SANDBOX1> @create_1_hint_sql_profile.sql
Enter value for sql_id: a6j7wgqf84jvg
Enter value for profile_name (PROFILE_sqlid_MANUAL):
Enter value for category (DEFAULT):
Enter value for force_matching (false):
Enter value for hint_text: opt_param('cell_offload_processing' 'true')
Profile PROFILE_a6j7wgqf84jvg_MANUAL created.
Elapsed: 00:00:00.07
SYS@SANDBOX1> @sql_profile_hints
Enter value for profile_name: PROFILE_a6j7wgqf84jvg_MANUAL
HINT
------------------------------------------------------------------------------------------------------------------------------------------------------
opt_param('cell_offload_processing' 'true')
1 rows selected.
Elapsed: 00:00:00.04
SYS@SANDBOX1> select avg(pk_col) from kso.skew3 where col1 < 0;
AVG(PK_COL)
-----------
1849142.5
1 row selected.
Elapsed: 00:00:05.11
SYS@SANDBOX1> @fsx
Enter value for sql_text: select avg(pk_col) from kso.skew3 where col1 < 0
Enter value for sql_id:
SQL_ID CHILD PLAN_HASH EXECS AVG_ETIME AVG_PX OFFLOAD IO_SAVED_% SQL_TEXT
------------- ------ ---------- ------ ---------- ------ ------- ---------- ----------------------------------------------------------------------
a6j7wgqf84jvg 0 2684249835 1 28.07 0 No .00 select avg(pk_col) from kso.skew3 where col1 < 0
a6j7wgqf84jvg 1 2684249835 1 5.10 0 Yes 99.99 select avg(pk_col) from kso.skew3 where col1 < 0
In the example I turned off cell offload processing with the ALTER SESSION and ran a SQL statement that took 28 seconds. Then I used my fsx.sql script to verify that the statement was not offloaded and to find the SQL_ID. Next I created a 1 hint Profile with an OPT_PARAM hint that set the cell_offload_processing parameter back to TRUE using the new version of my create_1_hint_sql_profile.sql script. Next I used my sql_profile_hints.sql script to verify the text of the hint that was added to the Profile. It looked good including the quotes. When I executed the statement a second time it ran in 5 seconds. I then used fsx.sql again to see that the statement was offloaded for the second execution (child 1).
Hi Kerry,
I have heard about the exadata’s performance. But recently, I came across an issue where in a report running in exadata executed for a long time, when compared to the same reports running in oracle DB. Report structure is same, though there is a minor difference in the result. It would be more helpful if you could clarify me on this?
Regards,
Kavitha
Sorry, time taken by the exadata installed server is more, but when the query is executed in exa DB it produces the results in 4s.
Kavitha,
I’m not sure I understand your question. Query running on Exadata is slower than the same query running on non-Exadata with the same set of data? If that’s it then there are many reasons that it could happen. First guess would be different plans so I would check to see if they have the same plan hash value (it’s in v$sql).
Kerry
Hi Kerry,
Thanks for publishing the scripts related to profiles. I am using those extensively. Quick question on the 1 hint profile. If my sql_id is already running with a profile and I want to grab the full execution statistics (gather_plan_statistics), would it affect my current plan or it would just add the hint to my current plan? Thank you.
–Kumar Ramalingam
Kumar,
I previously wrote a script called gps.sql that adds a gather_plan_statistics hint to a statement via a SQL Profile. This would not work in your situation because your statement already has a SQL Profile attached to it. However, I recently modified the gps.sql script to use the SQL Patch facility which allows it to be used on a statement which also has a SQL Profile already attached. (just search this blog for gps.sql for more info) It only works on 11g though. So if you are using 10g you may have to do something a little more tricky like using the SQLT script (I blogged about here) to create a sql script which contains the hints, drop the existing SQL Profile, modify the hints in the script created by SQLT, and then create a new Profile with the modified hints.
Kerry
Kumar,
I have now fixed the gps.sql script to work on 10 or 11. Should work whether there is a SQL Profile already in use or not. Let me know if you have any questions or issues.
Kerry
Hi Kerry,
I am a big fan of your blog entries. Thank you very much for sharing your wonderful research.
Recently, I am working on fixing a performance issue of Oracle EBS Forms. Form allows the user to input some data(for example date criteria) which will be appended to the where conditions of the main sql. Somehow for each user Oracle is generating different sql_id’s even though the sql_text is same(with white spaces as well). These sql’s are using wrong index and I tried to create profile for one of the sql_id. Only one of the user is able to use the profile. Because of the different sql_id’s it’s not using the profile for other sql_id’s(or other users). We are on 11gR2. Could you please help me in solving this problem?
Appreciate all your help
If the SQL_TEXT is exactly the same, the SQL_ID should be the same. Can you email me output that shows this phenomenon?
If they the statements only differ by having different literals, then setting the FORCE_MATCHING argument to TRUE should do the trick. One caveat on that. If a statement has bind variables and literals, FORCE_MATCHING won’t work.
Looking forward to hearing back from you.
Thank you very much for responding back Kerry.
Sorry, I didn’t notice that it has the literal value. What can we do if we have bind variables and literals in a statement? In our case the literal value is being generated dynamically(it’s an optional value which a user might enter or not). But how can we make sure when the user enters this value, profile should be picked up and not in the other cases? Any help would be greatly appreciated.
Once again thanks for your time.
-Raj
If a statement has both literals and bind variables, the force_matching parameter will not work. I think the Oracle developers figured that if someone is smart enough to mix literals and bind variables they probably know what they are doing. At any rate, there is not much you can do other than change the code itself or figure out why the optimizer is sometimes getting it wrong. That is, solve the underlying problem whether it be with stats, histograms, bind variables peeking, etc… I should mention that I view SQL Profiles as a quick fix in most situations. They can provide almost instantaneous relief, but they don’t solve the underlying problem.
Kerry