trusted online casino malaysia

SQL Tuning Advisor

I recently had a very positive experience with Oracle’s SQL Tuning Advisor. I was asked to assist with a 10gR2 RAC database that was performing badly. The database was spending a lot of time waiting on gc buffer busy waits and was doing a lot of physical i/o. These however turned out to be red herrings. A quick check showed transfer times on the inter-connect were not an issue. The disk access times were also not out of line. The real problem was that a handful of statements were doing an excessive amount of logical i/o.  We were able to identify the point in time when the performance issue began so we had a look at the AWR tables to see if there had been any plan changes for the “bad” statements around that time. Here is a snippet from the log showing changes in the plan_hash_value for one of the statements which produced substantially worse results than the previous plan(s).

SQL> @awr_plan_change
Enter value for sql_id: 2mkw0zbja4t6g

   SNAP_ID BEGIN_INTERVAL_TIME            SQL_ID        PLAN_HASH_VALUE        ETIME          LIO
---------- ------------------------------ ------------- --------------- ------------ ------------
     35519 18-JUL-08 09.00.35.521 AM      2mkw0zbja4t6g      3485252631        .0288        748.4
     35519 18-JUL-08 08.59.49.706 AM      2mkw0zbja4t6g                        .0288        748.4
     35519 18-JUL-08 09.00.35.521 AM      2mkw0zbja4t6g      4125764560       6.8601     86,933.8
     35519 18-JUL-08 08.59.49.706 AM      2mkw0zbja4t6g                       3.3464     66,720.2
     35519 18-JUL-08 09.00.35.521 AM      2mkw0zbja4t6g       593567772        .1594      8,232.7
     35519 18-JUL-08 08.59.49.706 AM      2mkw0zbja4t6g                        .1594      8,232.7
     35519 18-JUL-08 09.00.35.521 AM      2mkw0zbja4t6g      4125764560       3.3464     66,720.2
     35519 18-JUL-08 08.59.49.706 AM      2mkw0zbja4t6g                       6.8601     86,933.8
     35538 18-JUL-08 06.29.58.999 PM      2mkw0zbja4t6g       593567772        .0420      8,012.1
     35538 18-JUL-08 06.30.44.800 PM      2mkw0zbja4t6g                        .0420      8,012.1
     35539 18-JUL-08 07.00.05.767 PM      2mkw0zbja4t6g                        .0426      8,016.7
     35539 18-JUL-08 07.00.51.579 PM      2mkw0zbja4t6g                        .0426      8,016.7
     35540 18-JUL-08 07.30.12.654 PM      2mkw0zbja4t6g                        .0429      8,024.2
     35540 18-JUL-08 07.30.58.419 PM      2mkw0zbja4t6g                        .0429      8,024.2
     35545 18-JUL-08 10.00.32.175 PM      2mkw0zbja4t6g      3526031842      92.8685    203,058.3
     35545 18-JUL-08 09.59.46.365 PM      2mkw0zbja4t6g                      53.5655    136,190.0
     35545 18-JUL-08 10.00.32.175 PM      2mkw0zbja4t6g                      53.5655    136,190.0
     35545 18-JUL-08 09.59.46.365 PM      2mkw0zbja4t6g                      92.8685    203,058.3
     35546 18-JUL-08 10.30.39.168 PM      2mkw0zbja4t6g                     108.2104    220,259.1
     35546 18-JUL-08 10.29.53.369 PM      2mkw0zbja4t6g                     138.8346    217,732.0
     35546 18-JUL-08 10.30.39.168 PM      2mkw0zbja4t6g                     138.8346    217,732.0
     35546 18-JUL-08 10.29.53.369 PM      2mkw0zbja4t6g                     108.2104    220,259.1
     35547 18-JUL-08 11.00.00.396 PM      2mkw0zbja4t6g                     121.4556    217,735.0
     35547 18-JUL-08 11.00.46.148 PM      2mkw0zbja4t6g                     121.4556    217,735.0
     35548 18-JUL-08 11.30.04.018 PM      2mkw0zbja4t6g                     108.4236    217,739.0
     35548 18-JUL-08 11.30.50.076 PM      2mkw0zbja4t6g                     108.4236    217,739.0
     35557 19-JUL-08 04.00.10.812 AM      2mkw0zbja4t6g                     115.0718    217,731.0
     35557 19-JUL-08 04.00.56.631 AM      2mkw0zbja4t6g                     115.0718    217,731.0
     35558 19-JUL-08 04.29.17.277 AM      2mkw0zbja4t6g                     127.8277    217,731.0
     35558 19-JUL-08 04.30.03.100 AM      2mkw0zbja4t6g                     127.8277    217,731.0
     35559 19-JUL-08 04.59.24.163 AM      2mkw0zbja4t6g                     112.7978    198,768.6
     35559 19-JUL-08 05.00.09.945 AM      2mkw0zbja4t6g                     112.7978    198,768.6
     35560 19-JUL-08 05.29.31.269 AM      2mkw0zbja4t6g                     107.6025    193,642.2
     35560 19-JUL-08 05.30.17.076 AM      2mkw0zbja4t6g                     107.6025    193,642.2
     35561 19-JUL-08 06.00.24.094 AM      2mkw0zbja4t6g                     184.9285    217,598.2
     35561 19-JUL-08 05.59.38.289 AM      2mkw0zbja4t6g                     145.1714    204,952.2

The log showed a couple of things:

  1. The “bad” plan first showed up around 10:00 PM on July 18th (plan hash value 3526031842).
  2. The “bad” plan did orders of magnitude more logical i/o than the previous plans.
  3. The plan for this statement had been experiencing instability even prior to the problem (this was due to bind variable peeking).

Since this was a production system, the goal was to return to the original performance as quickly as possible. The major difference between the two plans was that the “bad” plan had a full table scan on a 14M row table while the good plan used an index. My first thought was to force Oracle to use the plan I wanted via hints as the problem was severely impacting the users and it was a relatively simple query. Also, the majority of the query was buried in a view so it would be a simple matter to modify the view without having to roll out any new code. While I started down that path, an enterprising member of the team (Rick Miners), created a SQL Tuning Set out of one of the queries and let Oracle recommend a “fix”. This was done with the DBMS_SQLTUNE package. The recommendation offered was to create a SQL Profile that resulted in the exact plan we wanted – it eliminated the full table scan in favor of the index just as the “good” plan had. We accepted the proposed SQL Profile and watched as the long running versions completed while the new executions picked up the new plan. The same process was repeated for several other troublesome queries – all with the same excellent results.

The success came as quite a shock to me as my previous experiences with this advisor had not been good. Specifically, I was involved in an incident where a DBA had implemented several recommendations which brought a production server to its knees. Note that OEM has an easy point and shoot interface to implement recommendations (often a SQL Profile) with the push of a button. Unfortunately it seems that the shooting sometimes occurs before the pointing. It’s easy to see how that can happen. Imagine yourself in a high pressure production support situation where you know a certain SQL statement is behaving really badly and you’re looking at an OEM screen that says – yeah that SQL statement is a problem and I know how to fix it. Not only that but here’s a bunch of technical sounding supporting data and a 6 digit estimate of the percentage improvement you’ll get if you just push this button. And there is the button … just begging to be clicked. And you think to yourself, “Well it can’t get much worse”, so you click the button.

At any rate, this most recent experience has made me completely rethink my position on Oracle’s Tuning Advisors. While they are certainly not perfect, I think that they can serve a purpose – an extra wrench in the old tool kit if you will.

The easiest/quickest way to use this advisor is of course with OEM/Grid control. However, I don’t always have access to that tool. Also, since I am a performance addict, I don’t really want to be tempted to click the button without doing my homework first. So here are the basic steps to do it from the command line (i.e. sqlplus):

  1. Create a tuning task
    • Creating a task for a single sql statement makes the most sense to me.
    • There are actually 3 parts to this – creating the tuning task, executing it, and then reporting on it
      • DBMS_SQLTUNE.CREATE_TUNING_TASK
      • DBMS_SQLTUNE.EXECUTE_TUNING_TASK
      • DBMS_SQLTUNE.REPORT_TUNING_TASK
    • See create_tuning_task.sql
    • Note the default time limit for the execute part is 30 minutes so you may want to reduce that (the create_tuning_task script prompts you for a value in seconds). By the way, the reason it can take so long is that the advisor can actually execute parts of the statement to get better estimates of the number of rows returned by a particular step.
    • Primary recommendations are often to accept a SQL Profile but there may be other recommendations such as creating indexes, etc..
    • The report output shows the old plan and the proposed new plan.
  2. Review recommendations
    • Never, ever, ever (I really mean it) blindly accept a recommendation – Duh!
    • Look at the new plan as proposed by REPORT_TUNING_TASK
    • Specifically – evaluate the proposed plan and if possible and time permits, test it
  3. Prepare a script to disable the SQL Profile that will be created.
    • You do have a back out plan right?
    • It is very easy to disable a profile, however once a plan is selected it will not be changed. That execution will continue to use that plan until it finishes, or hell freezes over, which ever happens first.
    • See disable_sql_profile.sql
  4. Accept the SQL Profile
  5. Confirm the performance improvement
    • Existing active cursors will continue processing
    • A new child will be created using the profile and subsequent executions will use it
    • You can compare the performance of the old plan and the new
    • See find_sql.sql, find_sql_stats.sql, dplan.sql
  6. Consider creating an Outline for the statement
    • SQL Profiles do not lock the execution plan
    • Outlines do lock the plan
    • You can create an outline on a plan that is using a profile
    • You should disable the profile after enabling the outline
    • See create_outline.sql
  7. Figure out why the plan went crazy in the first place

Jonathan Lewis has a couple of notes about SQL Profiles on his site here SQL Profiles (10g) and here SQL Profiles – 2 that are worth a look. I totally agree with his advice to make sure you know what the profile is going to do before implementing it. You can do this by looking at the proposed plan and the hints to be applied to get there (see tuning_task_hints.sql or sql_profile_hints.sql). Also be aware that profiles will tend to sour on you over time. As things change, the estimates that the CBO comes up with for particular operations can and will change. One of the main techniques used by profiles is to modify cardinality estimates of operations by applying a scaling factor. Once the factor is defined it remains fixed, however, the CBO’s estimate may change for many reasons – new stats, histograms, etc… Therefore, I don’t believe that profiles are a good choice as a long term solution.

17 Comments

  1. […] me back up for a minute. I posted about Oracle’s SQL Tuning Advisor a while back. It’s a feature that was added to Oracle in version 10g. It basically looks a […]

  2. rnm1978 says:

    This is a great article, very clearly written. Thanks.
    I humbly offer a minor correction – create_tuning_task time_limit is documented as being in seconds, not minutes as you state. Ref: http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28419/d_sqltun.htm#CHDGBCDB

  3. osborne says:

    Oops. Your right about the time units in my script. I think I was playing with my script using minutes but ended up posting text in minutes and script in seconds. I’ve fixed the text to match the script. Thanks for pointing out that inconsistency.

    Thanks also for the positive feedback.

    Kerry

  4. sachu says:

    hi,
    create script failing with error ..

    SQL> SET LONG 10000;
    SQL> SET PAGESIZE 9999
    SET LINESIZE 155
    SQL> SQL> set verify off
    SQL> col recommendations for a150
    SQL> accept task_name –
    > prompt ‘Task_Name: ‘
    Task_Name: DECLARE ret_val VARCHAR2(4000);
    SQL> BEGIN
    2 ret_val := dbms_sqltune.create_tuning_task(task_name=>’&&Task_name’, sql_id=>’&sql_id’, time_limit=>&time_limit);
    3 dbms_sqltune.execute_tuning_task(‘&&Task_name’);
    4 END;
    5 /
    Enter value for sql_id: dt7vf07d0kj4n
    Enter value for time_limit: 10
    ret_val := dbms_sqltune.create_tuning_task(task_name=>’ DECLARE ret_val VARCHAR2(4000);’, sql_id=>’dt7vf07d0kj4n’, time_limit=>10);
    *
    ERROR at line 2:
    ORA-06550: line 2, column 1:
    PLS-00201: identifier ‘RET_VAL’ must be declared
    ORA-06550: line 2, column 1:
    PL/SQL: Statement ignored

  5. osborne says:

    Hi Sachu,

    Try pasting the script into a file and executing it with @.

    @create_tuning_task — for example

    Kerry

  6. sachu says:

    Hi, Kerry
    after running Automatic SQL Tuning I got the below recommendation but , applying recommendation not helping to tune the query ..
    what can be the reason ?

    select * from ( select comm_dates.*, ROWNUM rnum from (select comm_id, comm_date_utc from communi where comm_id in ( select
    distinct cp.comm_id from comm_parti cp, com_add ca where ca.comm_address_id = cp.comm_address_id and
    ca.lwr_mail_add = :1 and cp.CREA_DATE >= :2 and cp.CREA_DATE < :3 ) and mkplace_id in ( :4 ) and
    response_status_type_code in ( :5 ) and (parent_comm_id is null or parent_comm_id = 0) order by comm_date_utc desc ) comm_dates
    where ROWNUM :7

    ——————————————————————————-
    FINDINGS SECTION (4 findings)
    ——————————————————————————-

    1- Restructure SQL finding (see plan 1 in explain plans section)
    —————————————————————-
    The predicate “CP”.”CREA_DATE”>=:B1 used at line ID 12 of the execution
    plan contains an implicit data type conversion on indexed column
    “CREA_DATE”. This implicit data type conversion prevents the optimizer
    from selecting indices on table “SACHIN”.”comm_parti_T”.

    Recommendation
    ————–
    – Rewrite the predicate into an equivalent form to take advantage of
    indices.

    Rationale
    ———
    The optimizer is unable to use an index if the predicate is an inequality
    condition or if there is an expression or an implicit data type conversion
    on the indexed column.

    changed the column predicate but explain plan shows no improvement, what I am missing here ?

    select * from ( select comm_dates.*, ROWNUM rnum from (select comm_id, comm_date_utc from communi where comm_id in ( select
    distinct cp.comm_id from comm_parti cp, com_add ca where ca.comm_address_id = cp.comm_address_id and
    ca.lwr_mail_add = :1 and cp.CREA_DATE >= TO_DATE(:2) and cp.CREA_DATE < TO_DATE(:3) ) and mkplace_id in ( TO_NUMBER(:4) ) and
    response_status_type_code in ( TO_NUMBER(:4) ) and (parent_comm_id is null or parent_comm_id = 0) order by comm_date_utc desc ) comm_dates
    where ROWNUM :7

    thanks in advance ..

  7. osborne says:

    Sachu,

    The to_date function should have allowed the the index on CRE_DATE to be used. Perhaps the variables passed to the query were not selective enough to make the optimizer want to use the index. You can try running a 10053 trace to see what decisions the optimizer made, including if it evaluated the index at all and why it didn’t decide to use it.

    Kerry

  8. sachu says:

    Hi Kerry,
    thanks for your time ..here is index list on comm_parti table right now, I_COMMPART_T_CAID_CRDT_PRC_CID index is being used in explain plan ..

    INDEX_OWNER INDEX_NAME COLUMN_NAME COLUMN_POSITION
    —————————— —————————————- —————————————- —————
    sachin I_COMMPART_T_CAID_CRDT_PRC_CID COMM_ADDRESS_ID 1
    sachin I_COMMPART_T_CAID_CRDT_PRC_CID CREA_DATE 2
    sachin I_COMMPART_T_CAID_CRDT_PRC_CID PARTY_ROLE_TYPE_CODE 3
    sachin I_COMMPART_T_CAID_CRDT_PRC_CID COMM_ID 4

    sachin I_COMMPART_T_CAID_MKTPL_ID COMM_ADDRESS_ID 1
    sachin I_COMMPART_T_CAID_MKTPL_ID MKPLACE_ID 2

    sachin I_COMMPART_T_COMM_ID COMM_ID 1

    sachin I_COMMPART_T_CREA_DATE CREA_DATE 1

    sachin I_COMMPART_T_LAST_UPDATED_DATE LAST_UPDATED_DATE 1

    sachin PK_COMM_PARTICIPANTS_T COMM_PARTICIPANT_ID 1

    sachin U_COMMPART_T_CID_CAID_PRID_CD COMM_ID 1
    sachin U_COMMPART_T_CID_CAID_PRID_CD COMM_ADDRESS_ID 2
    sachin U_COMMPART_T_CID_CAID_PRID_CD PARTY_ROLE_ID 3
    sachin U_COMMPART_T_CID_CAID_PRID_CD COMM_PARTICIPANT_TYPE_CODE 4

    and explain plan

    ——————————————————————————————————————————————
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
    ——————————————————————————————————————————————
    | 0 | SELECT STATEMENT | | 10 | 350 | 114 (3)| 00:00:02 | | |
    |* 1 | VIEW | | 10 | 350 | 114 (3)| 00:00:02 | | |
    |* 2 | COUNT STOPKEY | | | | | | | |
    | 3 | VIEW | | 249 | 5478 | 114 (3)| 00:00:02 | | |
    |* 4 | SORT ORDER BY STOPKEY | | 249 | 7968 | 114 (3)| 00:00:02 | | |
    | 5 | NESTED LOOPS | | 249 | 7968 | 113 (2)| 00:00:02 | | |
    | 6 | VIEW | VW_NSO_1 | 249 | 1992 | 62 (2)| 00:00:01 | | |
    | 7 | SORT UNIQUE | | 249 | 13944 | | | | |
    |* 8 | FILTER | | | | | | | |
    | 9 | NESTED LOOPS | | 249 | 13944 | 62 (2)| 00:00:01 | | |
    | 10 | TABLE ACCESS BY INDEX ROWID | COMM_ADDRESSES | 1 | 33 | 1 (0)| 00:00:01 | | |
    |* 11 | INDEX UNIQUE SCAN | UNIQ_COMMADDR_LOWERADDR | 1 | | 1 (0)| 00:00:01 | | |
    |* 12 | INDEX RANGE SCAN | I_COMMPART_T_CAID_CRDT_PRC_CID | 249 | 5727 | 61 (2)| 00:00:01 | | |
    |* 13 | TABLE ACCESS BY GLOBAL INDEX ROWID| COMMUNI | 1 | 24 | 1 (0)| 00:00:01 | ROWID | ROWID |
    |* 14 | INDEX UNIQUE SCAN | PK_COMMUNI | 1 | | 1 (0)| 00:00:01 | | |
    ——————————————————————————————————————————————
    1 – filter(“RNUM”>:7)
    2 – filter(ROWNUM<=:6)
    4 – filter(ROWNUM<=:6)
    8 – filter(:2=:2 AND INTERNAL_FUNCTION(“CP”.”CREA_DATE”)<:3)
    13 – filter(“MKPLACE_ID”=:4 AND “RESPONSE_STATUS_TYPE_CODE”=:5 AND (“PARENT_COMM_ID” IS NULL OR “PARENT_COMM_ID”=0))
    14 – access(“COMM_ID”=”$nso_col_1”)

  9. Sameer says:

    Kerry ,

    Thanks for such a wonderfull post and the valuable scripts 🙂

    can you please let me know what is the issue in the below query .

    SNAP_ID   NODE BEGIN_INTERVAL_TIME            SQL_ID        PLAN_HASH_VALUE        EXECS    AVG_ETIME        AVG_LIO
    ---------- ------ ------------------------------ ------------- --------------- ------------ ------------ --------------
         38002      1 04-JUL-12 05.00.06.808 PM      7k2gqcxd80bsb               0            1    2,416.740      134,240.0
         38024      1 05-JUL-12 03.00.17.933 PM      7k2gqcxd80bsb                            1      376.491        3,000.0
         38064      1 07-JUL-12 07.00.10.732 AM      7k2gqcxd80bsb                            1    3,698.977        2,091.0
         38066      1 07-JUL-12 09.00.33.386 AM      7k2gqcxd80bsb                            1      778.336        2,091.0
         38087      1 08-JUL-12 06.00.47.197 AM      7k2gqcxd80bsb                            1      913.786        3,171.0
         38089      1 08-JUL-12 08.00.09.674 AM      7k2gqcxd80bsb                            4      538.307      431,436.3
         38090      1 08-JUL-12 09.00.21.045 AM      7k2gqcxd80bsb                            1      501.806            6.0
         38119      1 09-JUL-12 02.00.01.787 PM      7k2gqcxd80bsb                            1    1,346.570        2,548.0
         38143      1 10-JUL-12 02.00.45.118 PM      7k2gqcxd80bsb                            1    3,437.140       13,954.0
    

    the actual query is

    DELETE FROM GBC_CORE.SPI_ELEMENT_ID TRGT WHERE 
    (TRGT.URI) NOT IN ( SELECT DISTINCT FROMTOURI.URI FROM ( SELECT SERVICEACCESSNAME AS URI, SUBSTR(
    SERVICEACCESSNAME, 1, INSTR( SERVICEACCESSNAME , '_')-1) 
    AS FROM_URI, SUBSTR( SERVICEACCESSNAME, INSTR( 
    SERVICEACCESSNAME , '_')+1, LENGTH(SERVICEACCESSNAME)) 
    AS TO_URI FROM TRPT.V_TRPT_SPI_VIEW@DBLNK_FCE_TRPT ) 
    FROMTOURI, BFG_OWNER.FCE_SPI_V@DBLNK_FCE_BFG URIA 
    WHERE FROMTOURI.FROM_URI IS NOT NULL AND 
    FROMTOURI.TO_URI IS NOT NULL AND FROMTOURI.URI IS NOT 
    NULL AND FROMTOURI.FROM_URI=URIA.URI_STRING AND 
    TO_URI IN ( SELECT URI_STRING FROM 
    BFG_OWNER.FCE_SPI_V@DBLNK_FCE_BFG ) )
    

    After running sql tuning advisor OEM recommends to use the SQL PROFILE which 99.99% benifit.

    Request your comments.

  10. osborne says:

    I’d go ahead and create the Profile using a non-default category and see what hints it wants to apply. This will probably give you enough information to see what you need to do. You can accept a recommended Profile using a non-default category using my accept_sql_profile script and look at the hints included in the Profile using my sql_profile_hints script. (just search this site for them and they should pop up). Also, if the hints are all opt_estimate (which will probably will be), you might want to look at the directive hints which you should be able to do by running the statement with the Profile and then using my sql_hints script which will prompt you for a sql_id and child_no. This site has several examples of using these scripts.

    By the way, the plan_hash_value value of 0 is disturbing. Delete’s generally have plan’s and plan_hash_values are non-0. I suspect you have a couple of plans that have very different performance characteristics. Perhaps trying to capture the plan while it’s running would show you a more definitive pattern. More frequent snapshots or increasing the size of the shared pool may also allow you catch the plan before it’s been aged out.

  11. max says:

    Hi Kerry,

    First of all thank you for all the awesome work you do for the community and may GOD bless you..

    i know this is a very old blog but I recently started using profiles, and love all the advise you have given on it. I mostly attach the bad plans to the good ones from AWR but still am afraid to use the profiles recommended by the SQL Tuning advisor. When i do run the advisor it gives me the below recommendation….to accept the tuning task(16quupasdf1_task)…but my question to you is…how do i get the SQL hints before accepting the task? I do not want to accept the task without looking at the hints first…

    in your blog you mention below…
    ” You can do this by looking at the proposed plan and the hints to be applied to get there (see tuning_task_hints.sql or sql_profile_hints.sql).”

    but looks like the link on tuning_task_hints.sql is also going to sql_profile_hints.sql…. can you please publish tuning_task_hints or tell me how can i see all the hints related to a profile that was recommended by a tuning task…

    Recommendation (estimated benefit: 98.46%)
    ——————————————
    – Consider accepting the recommended SQL profile.
    execute dbms_sqltune.accept_sql_profile(task_name =>
    ’16quupasdf1_task’, task_owner => ‘SYSTEM’, replace => TRUE);

    Validation results
    ——————
    The SQL profile was tested by executing both its plan and the original plan
    and measuring their respective execution statistics. A plan may have been
    only partially executed if the other could be run to completion in less time.

    Original Plan With SQL Profile % Improved
    ————- —————- ———-
    Completion Status: COMPLETE COMPLETE
    Elapsed Time (s): .170587 .001552 99.09 %
    CPU Time (s): .166666 .002 98.79 %
    User I/O Time (s): 0 0
    Buffer Gets: 2272 35 98.45 %
    Physical Read Requests: 0 0
    Physical Write Requests: 0 0
    Physical Read Bytes: 0 0
    Physical Write Bytes: 0 0
    Rows Processed: 5 5
    Fetches: 5 5
    Executions: 1 1

  12. osborne says:

    Hi Max,

    Sorry about that. I have fixed the link to point to the correct script (tuning_task_hints.sql). It is a very simple script by the way, so you may want to enhance it. 🙂 Also, you may find it more useful to simply create the profile in a non-default category and test the performance, look at the plan,, etc… This can be done by modifying the suggested command to create the profile:

    Recommendation (estimated benefit: 98.46%)
    ——————————————
    – Consider accepting the recommended SQL profile.
    execute dbms_sqltune.accept_sql_profile(task_name =>
    ’16quupasdf1_task’, task_owner => ‘SYSTEM’, replace => TRUE);

    to something like this:

    exec dbms_sqltune.accept_sql_profile(task_name => ‘&task_name’,category => ‘&category’);

    Use something other than DEFAULT for the category (TEST for example). Then you can use an alter session to set your category to the same string.

    alter session set sqltune_category=&category;

    This will allow you to test the profile without enabling it for the general public. Hope that helps.

    Kerry

  13. Jon Adams says:

    I have used STA myself on occasion successfully in 10g and 11g. As a result of using it more than once, I developed a ‘quick and easy’ STA routine:

    –Setting up the tuning task
    DECLARE
    my_task_name varchar2(30);
    my_sqlid varchar2(20);
    BEGIN

    my_sqlid := ‘8rvsfmuyskbcu’;

    my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
    sql_id => my_sqlid,
    scope => ‘COMPREHENSIVE’,
    task_name => ‘scnprd_tuning_task’,
    description => ‘jadams_tuning’,
    time_limit => 99999);

    END;
    /

    –Executing the tuning task
    BEGIN
    dbms_sqltune.Execute_tuning_task (task_name => ‘scnprd_tuning_task’);
    END;
    /

    –Viewing the output of the tuning task
    set long 1000000
    set longchunksize 1000
    set linesize 200 pages 999
    select dbms_sqltune.report_tuning_task(‘scnprd_tuning_task’) from dual;

    –Removing the tuning task
    –NOTE – DO NOT EXECUTE THIS BEFORE IMPLEMENTING A SUGGESTED SQL PROFILE
    — AND EXECUTE THIS BEFORE PERFORMING ANOTHER TUNING TASK
    BEGIN
    dbms_sqltune.Drop_tuning_task (task_name => ‘scnprd_tuning_task’);
    END;
    /

  14. Mayank says:

    Hi Kerry,
    Most of the hyperlinks for scripts location is not working , it seems to be pointing to wrong location http://www.oracle-guy.com/scripts/disable_sql_profile.sql instead of pointing to http://kerryosborne.oracle-guy.com/scripts/disable_sql_profile.sql and also its not opening may be due to some permoission issue in Scripts path , please fix it

    Thanks much

  15. osborne says:

    Sorry about that. Should be fixed now.

    Kerry

  16. Tushar says:

    Hi Kerry,
    Thanks a lot for sharing your knowledge with all of us along with scripts. 🙂

Leave a Reply