Single Hint SQL Profiles

Seems like all I ever write about these days is SQL Profiles. I do other stuff, honest! It just seems like getting Oracle to do what you want when you can’t touch the code is the closest thing to “Magic” that DBAs get to do. By the way, software developers get to create “Magic” all the time. They have the ability to create something out of nothing. I think it’s one of the coolest jobs ever. Of course a lot of art is like that too. But painters and sculptors start with some sort of raw material – programmers don’t. Musicians don’t really use raw material either, but music is much less tangible than software. Maybe one of these days I’ll write a little on the similarities between musicians and software developers, but right now I’m way off in the weeds. Back to the subject at hand.

A few weeks ago Jonathan Lewis called me to task on a couple of posts regarding SQL Profiles (in a very nice collegial sort of way). You can see the original dialog here. One of his main points was that SQL Profiles were not meant to be a generic mechanism for forcing a particular execution plan the way Outlines are. There is after all, no documented way (that I’m aware of) to directly create a SQL Profile on a statement. I had to agree with him that I was using them in a way that was not necessarily intended. Outlines were designed to lock execution plans, SQL Profiles were designed to overcome shortcomings in the optimizer with regards to statistics. Tom Kyte described SQL Profiles like this:

So, a SQL profile is sort of like gathering statistics on A QUERY – which involves many
tables, columns and the like….

In fact – it is just like gathering statistics for a query, it stores additional
information in the dictionary which the optimizer uses at optimization time to determine
the correct plan. The SQL Profile is not “locking a plan in place”, but rather giving
the optimizer yet more bits of information it can use to get the right plan.

Tom is referring to the documented way of creating a SQL Profile which is to use the SQL Tuning Advisor. The Tuning Advisor verifies the optimizer’s calculations and can create a SQL Profile that corrects the calculations, if they are found to be in error. The corrections most often come in the form of OPT_ESTIMATE hints which apply a scaling factor at various places in the optimizer’s calculations. But take note that the underlying mechanism of applying these corrections is hints. So a SQL Profile is ultimately a mechanism for applying a set of stored hints to a SQL statement (or set of statements) behind the scenes. And whether it was intended by the developers or not, this gives us a tremendously powerful tool. With this tool we can influence (and often times control) execution plans for statements coming from application code that is difficult or impossible to change.

By the way, all this discussion of SQL Profiles and whether they should be used as a generic mechanism for affecting plans without touching a SQL statement is probably going to be fairly short lived. SQL Baselines (introduced in 11g) are the latest revision of the “behind the scenes hint application” idea. They will most likely make SQL Profiles a less attractive option in the future. Fortunately, it is a simple matter to convert a SQL Profile into a Baseline. And SQL Profiles still work fine in 11g as well (so do Outlines for that matter), but on the off chance that Oracle decides to do away with SQL Profiles (or alter their behavior) in some future release, it’s comforting to know that we are not headed down a dead end street.

Back to the subject at hand. Jonathan expressed concern and doubt on a couple of points:

  1. That Profiles really were a generic mechanism to apply any hint behind the scenes. (i.e. that they could be used to apply any arbitrary hint, not just the hints created by the Tuning Advisor – like opt_estimate).
  2. Whether Profiles created by the Tuning Advisor ever had hints other than “Change the Optimizer Calculations” type hints (i.e. opt_estimate, index_stats, table_stats, etc…).

I think we finally agreed that they can be used to apply hints in a generic fashion, even if that was not the intention of the developers. Nevertheless, here is a simple test case to demonstrate that they can be used for that purpose. I created a little script to create a SQL Profile with a single manually typed hint called create_1_hint_sql_profile.sql. Please note that the syntax can be quite finicky with regards to query block names and aliases.

> !sql
sqlplus "/ as sysdba"
 
SQL*Plus: Release 11.2.0.1.0 Production on Mon Jan 25 15:23:02 2010
 
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
 
SYS@LAB112> @flush_pool
 
System altered.
 
SYS@LAB112> @avgskewi
 
AVG(PK_COL)
-----------
   15636133
 
SYS@LAB112> @find_sql
Enter value for sql_text: %skew%
Enter value for sql_id: 
 
SQL_ID         CHILD  PLAN_HASH      EXECS     AVG_ETIME      AVG_LIO SQL_TEXT
------------- ------ ---------- ---------- ------------- ------------ ------------------------------------------------------------
84q0zxfzn5u6s      0 3723858078          1           .05          190 select avg(pk_col) from kso.skew where col1 = 136133
 
SYS@LAB112> @dplan
Enter value for sql_id: 84q0zxfzn5u6s
Enter value for child_no: 
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  84q0zxfzn5u6s, child number 0
-------------------------------------
select avg(pk_col) from kso.skew where col1 = 136133
 
Plan hash value: 3723858078
 
------------------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |           |       |       |    35 (100)|          |
|   1 |  SORT AGGREGATE              |           |     1 |    24 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| SKEW      |    35 |   840 |    35   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | SKEW_COL1 |    35 |       |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("COL1"=136133)
 
 
20 rows selected.
 
SYS@LAB112> -- let's create a 1 hint profile to force a full table scan
SYS@LAB112> 
SYS@LAB112> @create_1_hint_sql_profile
Enter value for sql_id: 84q0zxfzn5u6s
Enter value for profile_name (PROFILE_sqlid_MANUAL): 
Enter value for category (DEFAULT): 
Enter value for force_matching (false): 
Enter value for hint: full(skew)
Profile PROFILE_84q0zxfzn5u6s_MANUAL created.
 
PL/SQL procedure successfully completed.
 
SYS@LAB112> @sql_profiles   
Enter value for sql_text: 
Enter value for name: 
 
NAME                           CATEGORY        STATUS   SQL_TEXT                                                               FOR
------------------------------ --------------- -------- ---------------------------------------------------------------------- ---
PROFILE_fgn6qzrvrjgnz          DEFAULT         DISABLED select /*+ index(a SKEW_COL1) */ avg(pk_col) from kso.skew a           NO
PROFILE_69k5bhm12sz98          DEFAULT         DISABLED SELECT dbin.instance_number,        dbin.db_name, dbin.instance_name,  NO
PROFILE_8js5bhfc668rp          DEFAULT         DISABLED select /*+ index(a SKEW_COL2_COL1) */ avg(pk_col) from kso.skew a wher NO
PROFILE_bxd77v75nynd8          DEFAULT         DISABLED select /*+ parallel (a 4) */ avg(pk_col) from kso.skew a where col1 >  NO
PROFILE_8hjn3vxrykmpf          DEFAULT         DISABLED select /*+ invalid_hint (doda) */ avg(pk_col) from kso.skew where col1 NO
PROFILE_7ng34ruy5awxq          DEFAULT         DISABLED select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.flags,i.prope NO
PROFILE_84q0zxfzn5u6s_MANUAL   DEFAULT         ENABLED  select avg(pk_col) from kso.skew                                       NO
 
7 rows selected.
 
SYS@LAB112> @sql_profile_hints
Enter value for profile_name: PROFILE_84q0zxfzn5u6s_MANUAL
 
HINT
------------------------------------------------------------------------------------------------------------------------------------------------------
full(skew)
 
1 rows selected.
 
SYS@LAB112> @avgskewi
 
AVG(PK_COL)
-----------
   15636133
 
1 row selected.
 
SYS@LAB112> @find_sql
Enter value for sql_text: 
Enter value for sql_id: 84q0zxfzn5u6s
 
SQL_ID         CHILD  PLAN_HASH      EXECS     AVG_ETIME      AVG_LIO SQL_TEXT
------------- ------ ---------- ---------- ------------- ------------ ------------------------------------------------------------
84q0zxfzn5u6s      0 3723858078          1           .01           86 select avg(pk_col) from kso.skew where col1 = 136133
 
1 row selected.
 
SYS@LAB112> @dplan
Enter value for sql_id: 84q0zxfzn5u6s
Enter value for child_no: 0
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  84q0zxfzn5u6s, child number 0
-------------------------------------
select avg(pk_col) from kso.skew where col1 = 136133
 
Plan hash value: 3723858078
 
------------------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |           |       |       |    35 (100)|          |
|   1 |  SORT AGGREGATE              |           |     1 |    24 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| SKEW      |    35 |   840 |    35   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | SKEW_COL1 |    35 |       |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("COL1"=136133)
 
Note
-----
   - SQL profile PROFILE_84q0zxfzn5u6s_MANUAL used for this statement
 
 
24 rows selected.
 
SYS@LAB112> -- didn't work - i.e. Profile got created and used, but the hint was ignored
SYS@LAB112> -- oh yeah, Query Block needed
SYS@LAB112> 
SYS@LAB112> @drop_sql_profile
Enter value for profile_name: PROFILE_84q0zxfzn5u6s_MANUAL
 
PL/SQL procedure successfully completed.
 
SYS@LAB112> -- must reload SQL statement for create_1_hint_sql_profile to work
SYS@LAB112> @avgskewi
 
AVG(PK_COL)
-----------
   15636133
 
1 row selected.
 
SYS@LAB112> @create_1_hint_sql_profile
Enter value for sql_id: 84q0zxfzn5u6s
Enter value for profile_name (PROFILE_sqlid_MANUAL): 
Enter value for category (DEFAULT): 
Enter value for force_matching (false): 
Enter value for hint: full( SKEW@SEL$1 )
Profile PROFILE_84q0zxfzn5u6s_MANUAL created.
 
PL/SQL procedure successfully completed.
 
SYS@LAB112> @sql_profile_hints
Enter value for profile_name: PROFILE_84q0zxfzn5u6s_MANUAL
 
HINT
------------------------------------------------------------------------------------------------------------------------------------------------------
full( SKEW@SEL$1 )
 
1 rows selected.
 
SYS@LAB112> @avgskewi
 
 
AVG(PK_COL)
-----------
   15636133
 
1 row selected.
 
SYS@LAB112> @dplan
Enter value for sql_id: 84q0zxfzn5u6s
Enter value for child_no: 
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  84q0zxfzn5u6s, child number 0
-------------------------------------
select avg(pk_col) from kso.skew where col1 = 136133
 
Plan hash value: 568322376
 
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       | 28360 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    24 |            |          |
|*  2 |   TABLE ACCESS FULL| SKEW |    35 |   840 | 28360   (1)| 00:05:41 |
---------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("COL1"=136133)
 
Note
-----
   - SQL profile PROFILE_84q0zxfzn5u6s_MANUAL used for this statement
 
 
23 rows selected.
 
SYS@LAB112> -- so that worked

Note that the syntax can be tricky requiring correct Query Block name (and aliases if there are any). SEL$1 is the default name for the query block of a simple select. (DEL$1 for deletes, UPD$1 for updates) As you can see from the example, the FULL hint was silently ignored without the QB name.

Since this post has gotten a little long, I’ll save my response to Johnathan’s second concern for the next post. Please let me know if you have any questions.

27 Comments

  1. Balakrishna says:

    Hi,

    You got a very good scripts written for a daily use , is that possible to share all your scripts by any chance ?

    Regards

    Bala

  2. osborne says:

    Thanks. I’m working on a scripts page. But for now, most of the scripts in the posts are on the site.
    You should be able to find them by searching on the site. Alternativey, many of them are stored in the following location:

    http://kerryosborne.oracle-guy.com/scripts/

    While browsing that directory is not allowed, you can try putting in the directory followed by the explicit file name you are looking for. Like so:

    http://kerryosborne.oracle-guy.com/scripts/find_sql.sql

    Kerry

  3. joel garry says:

    The raw material programmers start with is the language (or language generating tool) they use.

    Some limited browsing of the scripts directory is available.

  4. osborne says:

    Joel,

    On the limited browsing:

    Well what do you know. Google is amazing! Thanks for sharing.

    On the raw materials:

    This is really an interesting topic (to me anyway). I would like to spend some time writing about it. Anyway, I don’t think of the language as a raw material, like clay or stone or wood or paint. I think of it as a tool like a chisel or a paint brush. Using these tools (which are not consumed in the process) a thought can be transformed into something real and tangible. Music is very similar in that a tool (instrument) is used, and basically a thought can be transformed into something at least semi tangible. It’s the closest thing to magic I can think of. Not to dismiss the fact that we are all standing on the shoulders of countless men and women that went before us. Without the advanced tools we have at our disposal, it would be very difficult to do what we do. Here’s to the pioneers!

    Kerry

  5. joel garry says:

    I for one look forward to your thoughts on these matters. I remember talking to people in the early ’80s, remarking on the apparent links between programming and music and maybe even religious studies. We all knew people who could barely write but were seemingly excellent programmers and/or musicians. Even now, people who can do our stuff well, and write or present well, are a rare treat. Some (human) language theories posit that thought is limited, or enhanced, by language.

    odd tangent: hitting next blog link a few times from Noon’s blog landed me an interesting useless music tidbit, I know not why (date posted, maybe): http://bertnews.blogspot.com/

    One of my current tasks is to write a front end for some data entry, to move the effort from one group closer to the group that generates the data (and gets some of it bar-codable). So I’m taking the data entry program and hacking away, I actually did think of it like molding clay before you said anything about it.

  6. osborne says:

    Thanks Joel,

    I’ve got it on my list to write about the similarities between musicians and programmers. It’s of interest to me because I am also a musician and have known a relatively large number of people that were accomplished programmers as well as accomplished musicians.

    Interesting link. I feel for the guy. Sadly I traded off my 62 Gibson SG in the late 70′s for a piece of %$#& Ovation acoustic. I’ve made a couple of horrendous decisions in the past that cost me dearly. But honestly, the decision to trade that guitar is the only one I think back on and wish I could undo.

    Interesting that you think about your current project as molding clay. I find I often start out with that frame of mind on a project, but that often shifts to more of a sculpture metaphor. (i.e. “I just chip away everything that doesn’t look like a horse.”) I had a friend that told me he viewed it as if he was going into battle (and spent several minutes describing what he meant, in excruciating detail).

    Thanks again for the comments.

    Kerry

  7. [...] closest thing to ‘Magic’ that DBAs get to do.” Here’s Kerry’s post on single-hint SQL profiles, inspired by a discussion with Jonathan [...]

  8. [...] 21-SQL Tuning Advisor generated SQL Profiles and manual sql profile (comments) Kerry Osborne-Single Hint SQL Profiles Kerry Osborne-SQL Tuning Advisor Profiles 22-How to do pivoting pre-11G? Marc Billette-Easy Pivot [...]

  9. [...] 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. The change makes use of the q-Quote [...]

  10. oracle_dba says:

    Hi,

    Very good article!!!

    Can you please let me know how to create sql profile for the hints such as RESULT_CACHE? I want to create the profile without touching the code.

    Appreciate all your help!!!

    Thanks,
    Pranav

  11. osborne says:

    Well that’s interesting. The RESULTS_CACHE hint appears not to work with SQL Profiles. It also doesn’t work with Baselines, at least as of 11.2.0.3. There are a couple of other ways to enable Results Cache as documented here: http://kerryosborne.oracle-guy.com/2011/04/how-oracle-results-cache-and-smart-scans-work-together/

    Kerry

  12. Arun says:

    Hi Kerry,

    I have this hint “NO_CONNECT_BY_FILTERING” which need to be added to a query. Problematic query in question is from application and could not be modified to add this hint manually.

    I tried to create sql profile(with this hint) on same query and switch the plans but it didn’t work.

    Recently I have been coming across these kind of hints where ORACLE didn’t provide alternative ways for creating SQL PROFILES on.

    Please provide your valuable suggestions.

    Thanks!!

  13. osborne says:

    If you are sure the Profile is getting picked up, then it may be because the hint is not being applied at the right place in the statement. The hint uses only one parameter which is the query block where it should be applied. The hint should look something like this: NO_CONNECT_BY_FILTERING(@”SEL$1″) You might try looking at the outline hints in v$sql_plan.other_xml for your test statement to see which query block it’s being applied to. (see my sql_hints.sql script to look at that).

    Kerry

    • Arun says:

      Thanks for the quick response Kerry!!

      That’s excellent idea. Catch here is query block name. My bad, I just used NO_CONNECT_BY_FILTERING (without qb_name) in the original sql and tried to switch the plans. So it never shown up in the plan.

      Do you know if we can add parallel hint through profiles? I know we can define parallel degree on table that could enable parallelism. But I am interested in enabling parallelism to only particular queries.

      Appreciate your help!!

  14. osborne says:

    Hi Arun,

    Yes – parallel hints should work fine as long as the syntax is valid.

    Kerry

  15. Arun says:

    Hi Kerry,

    I checked the parallel hints. I don’t think there is a way to impose parallel hints through profiles. From the below example, it doesn’t have any parallel hint details in Outline Data. Do you know what is the exact syntax we can use for imposing parallel hints while creating profile? Haven’t seen it in oracle documentation.

    Thanks as always!!!

    explain plan for
    select /*+ FULL(a) PARALLEL(a,8) */ count(*) from tab_a a;

    ——————————————————————————————————————————————
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
    ——————————————————————————————————————————————
    | 0 | SELECT STATEMENT | | 13M| 4519M| 83869 (5)| 00:04:12 | | | | | |
    | 1 | PX COORDINATOR | | | | | | | | | | |
    | 2 | PX SEND QC (RANDOM)| :TQ10000 | 13M| 4519M| 83869 (5)| 00:04:12 | | | Q1,00 | P->S | QC (RAND) |
    | 3 | PX BLOCK ITERATOR | | 13M| 4519M| 83869 (5)| 00:04:12 | 1 | 32 | Q1,00 | PCWC | |
    | 4 | TABLE ACCESS FULL| TAB_A | 13M| 4519M| 83869 (5)| 00:04:12 | 1 | 32 | Q1,00 | PCWP | |
    ——————————————————————————————————————————————

    Query Block Name / Object Alias (identified by operation id):
    ————————————————————-

    1 – SEL$1
    4 – SEL$1 / A@SEL$1

    Outline Data
    ————-

    /*+
    BEGIN_OUTLINE_DATA
    FULL(@”SEL$1″ “A”@”SEL$1″)
    OUTLINE_LEAF(@”SEL$1″)
    ALL_ROWS
    OPT_PARAM(‘optimizer_index_caching’ 90)
    OPT_PARAM(‘optimizer_index_cost_adj’ 30)
    OPT_PARAM(‘optimizer_dynamic_sampling’ 6)
    OPT_PARAM(‘_fast_full_scan_enabled’ ‘false’)
    OPT_PARAM(‘_b_tree_bitmap_plans’ ‘false’)
    DB_VERSION(’11.2.0.2′)
    OPTIMIZER_FEATURES_ENABLE(’11.2.0.2′)
    IGNORE_OPTIM_EMBEDDED_HINTS
    END_OUTLINE_DATA
    */

  16. osborne says:

    Hi Arun,

    Yes – it’s interesting that the outline hints stored in the OTHER_XML field do not include anything to force parallel or a certain degree of PX. The SQL Profile mechanism of applying hints behind the scene will work for the PARALLEL hint though if you properly specify the hint – which means you’ll have to tell the optimizer where to apply the hint via the query block name. Here is an example:

    SYS@dbm1> !cat junk.sql
    select avg(pk_col) from kso.skew a
    where col1 > 0
    /
    
    SYS@dbm1> @junk
    
    AVG(PK_COL)
    -----------
     16093749.3
    
    1 row selected.
    
    SYS@dbm1> @x
    
    PLAN_TABLE_OUTPUT
    -----------------------------------------------------------------------------------------------------------------------------------------------------------
    SQL_ID  93356j5juvwpz, child number 1
    -------------------------------------
    select avg(pk_col) from kso.skew a where col1 > 0
    
    Plan hash value: 568322376
    
    -----------------------------------------------------------------------------------
    | Id  | Operation                  | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    -----------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT           |      |       |       | 44398 (100)|          |
    |   1 |  SORT AGGREGATE            |      |     1 |    11 |            |          |
    |*  2 |   TABLE ACCESS STORAGE FULL| SKEW |    32M|   335M| 44398   (1)| 00:00:02 |
    -----------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - storage("COL1">0)
           filter("COL1">0)
    
    
    20 rows selected.
    
    SYS@dbm1> @create_1_hint_sql_profile.sql
    Enter value for sql_id: 93356j5juvwpz
    Enter value for profile_name (PROFILE_sqlid_MANUAL): 
    Enter value for category (DEFAULT): 
    Enter value for force_matching (false): 
    Enter value for hint_text: PARALLEL(@"SEL$1" A@"SEL$1" 8)
    
    Profile PROFILE_93356j5juvwpz_MANUAL created.
    
    SYS@dbm1> @sql_profile_hints
    Enter value for profile_name: PROFILE_93356j5juvwpz_MANUAL
    
    HINT
    ------------------------------------------------------------------------------------------------------------------------------------------------------
    PARALLEL(@"SEL$1" A@"SEL$1" 8)
    
    1 rows selected.
    
    SYS@dbm1> @junk
    
    AVG(PK_COL)
    -----------
     16093749.3
    
    1 row selected.
    
    SYS@dbm1> @x
    
    PLAN_TABLE_OUTPUT
    -----------------------------------------------------------------------------------------------------------------------------------------------------------
    SQL_ID  93356j5juvwpz, child number 1
    -------------------------------------
    select avg(pk_col) from kso.skew a where col1 > 0
    
    Plan hash value: 578366071
    
    ------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                      | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
    ------------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT               |          |       |       |  6158 (100)|          |        |      |            |
    |   1 |  SORT AGGREGATE                |          |     1 |    11 |            |          |        |      |            |
    |   2 |   PX COORDINATOR               |          |       |       |            |          |        |      |            |
    |   3 |    PX SEND QC (RANDOM)         | :TQ10000 |     1 |    11 |            |          |  Q1,00 | P->S | QC (RAND)  |
    |   4 |     SORT AGGREGATE             |          |     1 |    11 |            |          |  Q1,00 | PCWP |            |
    |   5 |      PX BLOCK ITERATOR         |          |    32M|   335M|  6158   (1)| 00:00:01 |  Q1,00 | PCWC |            |
    |*  6 |       TABLE ACCESS STORAGE FULL| SKEW     |    32M|   335M|  6158   (1)| 00:00:01 |  Q1,00 | PCWP |            |
    ------------------------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       6 - storage(:Z>=:Z AND :Z< =:Z AND "COL1">0)
           filter("COL1">0)
    
    Note
    -----
       - SQL profile PROFILE_93356j5juvwpz_MANUAL used for this statement
    
    
    28 rows selected.
    
  17. osborne says:

    By the way, the general format for a hint includes a QB Name where the hint should be inserted as the first arguement (@”SEL$1″ – where SEL$! is the QB Name). Then you’ll notice that objects are fully specified by including the QB name like so – A@”SEL$1″. In the particular example I showed you could actually leave out the first arguement as long as you fully specify the object. Also the double quotes are not required in this case. So a simplified version be like so:

    parallel(a@sel$1 8)

    One other note: I have rewritten the create_1_hint_sql_profile.sql script to use the q-Quote feature which is a little more flexible in terms of what it will allow you to type as a hint (i.e. it doesn’t blow up if you put in single quotes). I did a blog post about it here: New create_1_hint_sql_profile.sql

    Kerry

  18. Arun says:

    Great. You are awesome. Thanks a lot Kerry!!!

  19. Arun says:

    I regularly follow your blog. Thanks for sharing your knowledge. There are very few who does this.

  20. osborne says:

    Thanks for the kind words Arun. Glad I could help.

    Kerry

  21. Rajeev says:

    How do I extract sql_text from SQL_profile. I tried one of your posting on this and it did not work. If you have an example you could post it.

  22. [...] very good review of what STA does was written by Tim Hall. Worth checking is also what Kerry Osborne writes about SQL Profiles. Cool [...]

  23. [...] script dont il s’agit est disponible sur Internet. Il appartient à Kerry Osborne, lequel à un blog avec quelques articles exceptionnels. Il s’appuie essentiellement sur [...]

  24. [...] script dont il s’agit est disponible sur Internet. Il appartient à Kerry Osborne, lequel à un blog avec quelques articles exceptionnels. Il s’appuie essentiellement sur [...]

Leave a Reply