Joe -- Thanks for the question regarding "10g SQL Profiles", version 10.2

Submitted on 24-Mar-2008 12:24 Central time zone

Tom's latest followup | Bookmark | Bottom

Last updated 3-Mar-2011 7:49

You Asked

Can you explain how SQL profiles work when it comes to recommendations from the SQL Tuning Advisor in 10g?

Thanks in advance.

and we said...

simply put:

sql profiles work by taking the query itself and doing partial executions of various predicates - and remembering the ACTUAL card= values (cardinality values) and hinting the query so that it *KNOWS* "this step will return about 5 records, not the 50,000 you guessed at, but only 5"

and the next time the query is hard parsed, we use those card= values to come up with the best plan.

the key is to get the CORRECT card=value numbers, when we get the right number - you get the right plan. When we get the wrong number - that is when you see suboptimal plans.

So, sql profiles simply take the query - execute bits of it, figure out how many rows are actually returned (or look at the execution HISTORY to see how many rows are actually flowing from the row sources as well) and STORE that information as extended statistics - and use that information the next time the query is hard parsed

Reviews

March 24, 2008 - 8pm Central time zone

Bookmark | Bottom | Top

Reviewer: A reader

Execution plan from tuning advisor March 25, 2008 - 2am Central time zone

Bookmark | Bottom | Top

Reviewer: Dhairyasheel Ttawde from India.(Mumbai).

HI Tom,
when we run tuning advisor it sometimes recommends sql execution plans promising 60% + benefit. One 
query we recently tuned showed 'bitmap conversion from rowids'
in its new execution plan, is it that this bitmap is stored in the profile & accessed the next time 
the query is run, won't it then make sense to covert all b-tree indexes to bitmap, if that is what 
oracle is doing internally.

Followup March 25, 2008 - 6am Central time zone:

bitmap conversion from rowids is a plan step, an execution plan step - like "full scan" or "table access by index rowid"

it is something done at runtime, at query execution time - there is no bitmap built and stored persistently, it is entirely transient - the bitmap is created at execution (query run time) time, used and discarded - each execution of the query will generate a unique bitmap based on the unique data it retrieves.

we are not converting anything, we are creating a temporary bitmap on the fly, using it, discarding it.

do NOT convert all of your indexes to bitmaps

Profiles March 25, 2008 - 3pm Central time zone

Bookmark | Bottom | Top

Reviewer: Dana from Phoenix, AZ USA

Are SQL Profiles an extension of sql outlines? 
Many of the features sound similar or same.

Followup March 26, 2008 - 8am Central time zone:

similar, but not the same.

stored outlines are a set of hints that say "use this index, do this table first, do that next, use this access path, perform this filter then that filter"....

sql profiles are more like extended statistics - they are the result of "analyzing a query", the information provided to the optimizer is not HOW to perform the query - but rather better information about how many rows will flow out of a step in the plan, how selective something is.

say you have a query, you generate a stored outline for it. You now add an index to the underlying table. This index would be GREAT for the query. A stored outline won't use it, the stored outline says "use this index - query that table - then do this". Since the GREAT index did not exist when the outline was generated - it won't be used.

say you have a query, you generate a profile for it. You now add an index to the underlying table. This index would be GREAT for the query. A profile can use it - since the profile is just more information for the CBO - better cardinality estimates.

Thanks for the Insight. March 26, 2008 - 2am Central time zone

Bookmark | Bottom | Top

Reviewer: Dhairyasheel Ttawde from India.(Mumbai.)

Hi Tom,

Thanks for the gr8 insight on bitmap conversion from rowids.
Is there any doc which lists such runtime enhancements
& the way to influence them.

Followup March 26, 2008 - 8am Central time zone:

gr8?

gr8 explained March 27, 2008 - 12am Central time zone

Bookmark | Bottom | Top

Reviewer: Dhairyasheel Ttawde from India(Mumbai.)

Hi Tom,

gr8 = short form for great.

Think of it as an optimized way of communication.
less bytes to upload,store..

Followup March 27, 2008 - 10am Central time zone:

Think of it as "I'm talking to a twelve year old"

That is what it looks like to me.

Profiles explanation March 27, 2008 - 11am Central time zone

Bookmark | Bottom | Top

Reviewer: Dana from Phoenix, AZ USA

Thank you sir. That is the most consise and clear explanation I've seen. Your work is invaluable.

May 22, 2008 - 6am Central time zone

Bookmark | Bottom | Top

Reviewer: Alexei from Amsterdam, The Netherlands

Hi Tom,

Thanks a lot for a good explanation! Not much not less, just straight to the point.

Speaking about previous review ("less bytes to upload and store¿") let me say my opinion:
1. Never have heard that besides in mathematics, engineering, science or architecture the number 
"8" is used in correspondence. (by  the way, "8" must be written as "eight", not "eat")
2. In your answers, Tom, I found much less bytes than in others, even if they would try to used all 
kind of combinations numbers and letters.

Hints from sql profile March 17, 2010 - 7pm Central time zone

Bookmark | Bottom | Top

Reviewer: Ummati from Los Angeles CA

Hi Tom,

I want to know what is Oracle using in the sql profile. I want to see the hints or outlines that I 
am going to accept. I looked through the dba_sql_profiles but there is no refrence as to what is 
used or how the query is going to be handled. In a production system its hard to accept the profile 
without actually able to examine it. Please let me know.

Followup March 18, 2010 - 8am Central time zone:

look at the resulting plan - the profile is a bunch of selectivity/cardinality hints.

Look at the plan without a profile, look at the plan with the profile - look at the difference in estimated cardinalities, that tells you what impact the profile has.

SQL Profile for a View February 15, 2011 - 5am Central time zone

Bookmark | Bottom | Top

Reviewer: Sarvindu from London

Hi Tom,

I am using SQL Profile for following sql statement
select * from ELLA_V_ENG_PORTFOLIO;

The above statement runs fine but when I use the owner qualifier the sql statement select * from 
PARA.ELLA_V_ENG_PORTFOLIO; is not using the sql profile.

Is there any way to use sql profile for underlying text of the view or convert the execution plan 
generated by SQL profile in equivalent hint so that we can run the different version of same sql 
text using the view or just the underlying text of the view.

1- Original With Adjusted Cost
------------------------------
Plan hash value: 2374821689

----------------------------------------------------------------------------------------------------
---------------
| Id  | Operation                         | Name                  | Rows  | Bytes |TempSpc| Cost 
(%CPU)| Time     |
----------------------------------------------------------------------------------------------------
---------------
|   0 | SELECT STATEMENT                  |                       |   117K|    37M|       |    28M  
(1)| 96:30:31 |
|*  1 |  HASH JOIN RIGHT OUTER            |                       |   117K|    37M|  8200K|    28M  
(1)| 96:30:31 |
|   2 |   VIEW                            | V_TEMP_ANNOTATION     | 29657 |  7848K|       |    28M  
(1)| 96:29:07 |
|*  3 |    FILTER                         |                       |       |       |       |         
   |          |
|   4 |     HASH GROUP BY                 |                       | 29657 |  5126K|   206G|    28M  
(1)| 96:29:07 |
|*  5 |      HASH JOIN                    |                       |  1056M|   174G|   148M| 74740  
(11)| 00:14:57 |
|*  6 |       INDEX FAST FULL SCAN        | PAR_ANNOTATION_PK     |  3628K|   107M|       |  6790   
(2)| 00:01:22 |
|*  7 |       TABLE ACCESS FULL           | PAR_ANNOTATION        |  3628K|   505M|       | 25773   
(1)| 00:05:10 |
|*  8 |   HASH JOIN                       |                       |   117K|  7006K|  2872K|  6230   
(1)| 00:01:15 |
|   9 |    VIEW                           | V_PARAGON_PORTFOLIO   |   117K|  1493K|       |  4082   
(2)| 00:00:49 |
|  10 |     SORT UNIQUE                   |                       |   117K|  3101K|  9264K|  4082  
(30)| 00:00:49 |
|  11 |      UNION-ALL                    |                       |       |       |       |         
   |          |
|* 12 |       HASH JOIN                   |                       | 71857 |  1894K|  2744K|  2354   
(1)| 00:00:29 |
|* 13 |        TABLE ACCESS FULL          | PAR_PORTFOLIO         |   116K|  1370K|       |  1378   
(1)| 00:00:17 |
|* 14 |        TABLE ACCESS FULL          | PAR_PORTFOLIO_DETAILS |   136K|  1992K|       |   665   
(2)| 00:00:08 |
|* 15 |       HASH JOIN                   |                       | 45759 |  1206K|       |   826   
(2)| 00:00:10 |
|* 16 |        TABLE ACCESS BY INDEX ROWID| PAR_PORTFOLIO         |  3786 | 45432 |       |   161   
(0)| 00:00:02 |
|* 17 |         INDEX RANGE SCAN          | PAR_PORTFOLIO_IDX2    |  6971 |       |       |     8   
(0)| 00:00:01 |
|* 18 |        TABLE ACCESS FULL          | PAR_PORTFOLIO_DETAILS | 54544 |   798K|       |   664   
(2)| 00:00:08 |
|  19 |    TABLE ACCESS FULL              | PAR_PORTFOLIO         |   221K|    10M|       |  1375   
(1)| 00:00:17 |
----------------------------------------------------------------------------------------------------
---------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("PP"."PORTFOLIO_ID"="VTA"."OWNER"(+))
   3 - filter("ANNOTATION_DATE"=MAX("PA2"."ANNOTATION_DATE") AND 
              "ANNOTATION_TIME"=MAX("PA2"."ANNOTATION_TIME"))
   5 - access("PA2"."OWNER"="PA"."OWNER" AND "PA2"."ANNOTATION_TYPE"="PA"."ANNOTATION_TYPE")
   6 - filter("PA2"."ANNOTATION_TYPE"='4')
   7 - filter("ANNOTATION_TYPE"='4')
   8 - access("PP"."PORTFOLIO_ID"="VPP"."PORTFOLIO_ID")
  12 - access("PP"."PORTFOLIO_ID"="PPD"."PORTFOLIO_ID")
  13 - filter(("PP"."PORTFOLIO_STATUS"=1 OR "PP"."PORTFOLIO_STATUS"=3) AND "PP"."IS_CLUSTERED"=2)
  14 - filter("PPD"."CLUSTER_PORTFOLIO_LINK" IS NULL AND "PPD"."STATE"=1 AND 
("PPD"."PRPT_CLUSTER_FLAG"=2 
              OR "PPD"."PRPT_CLUSTER_FLAG"=3))
  15 - access("PP"."PORTFOLIO_ID"="PPD"."PORTFOLIO_ID")
  16 - filter("PP"."PORTFOLIO_STATUS"=3 OR "PP"."PORTFOLIO_STATUS"=5 OR "PP"."PORTFOLIO_STATUS"=6)
  17 - access("PP"."IS_CLUSTERED"=1)
  18 - filter("PPD"."CLUSTER_PORTFOLIO_LINK" IS NOT NULL AND ("PPD"."STATE"=2 OR "PPD"."STATE"=4) 
AND 
              "PPD"."PRPT_CLUSTER_FLAG"=2)

2- Using SQL Profile
--------------------
Plan hash value: 1933970793

----------------------------------------------------------------------------------------------------
---------------
| Id  | Operation                         | Name                  | Rows  | Bytes |TempSpc| Cost 
(%CPU)| Time     |
----------------------------------------------------------------------------------------------------
---------------
|   0 | SELECT STATEMENT                  |                       |   117K|    37M|       |   102K  
(1)| 00:20:35 |
|*  1 |  HASH JOIN OUTER                  |                       |   117K|    37M|  8392K|   102K  
(1)| 00:20:35 |
|*  2 |   HASH JOIN                       |                       |   117K|  7006K|  2872K|  6230   
(1)| 00:01:15 |
|   3 |    VIEW                           | V_PARAGON_PORTFOLIO   |   117K|  1493K|       |  4082   
(2)| 00:00:49 |
|   4 |     SORT UNIQUE                   |                       |   117K|  3101K|  9264K|  4082  
(30)| 00:00:49 |
|   5 |      UNION-ALL                    |                       |       |       |       |         
   |          |
|*  6 |       HASH JOIN                   |                       | 71857 |  1894K|  2744K|  2354   
(1)| 00:00:29 |
|*  7 |        TABLE ACCESS FULL          | PAR_PORTFOLIO         |   116K|  1370K|       |  1378   
(1)| 00:00:17 |
|*  8 |        TABLE ACCESS FULL          | PAR_PORTFOLIO_DETAILS |   136K|  1992K|       |   665   
(2)| 00:00:08 |
|*  9 |       HASH JOIN                   |                       | 45759 |  1206K|       |   826   
(2)| 00:00:10 |
|* 10 |        TABLE ACCESS BY INDEX ROWID| PAR_PORTFOLIO         |  3786 | 45432 |       |   161   
(0)| 00:00:02 |
|* 11 |         INDEX RANGE SCAN          | PAR_PORTFOLIO_IDX2    |  6971 |       |       |     8   
(0)| 00:00:01 |
|* 12 |        TABLE ACCESS FULL          | PAR_PORTFOLIO_DETAILS | 54544 |   798K|       |   664   
(2)| 00:00:08 |
|  13 |    TABLE ACCESS FULL              | PAR_PORTFOLIO         |   221K|    10M|       |  1375   
(1)| 00:00:17 |
|  14 |   VIEW                            | V_TEMP_ANNOTATION     | 54381 |    14M|       | 95512   
(1)| 00:19:07 |
|* 15 |    HASH JOIN                      |                       | 54381 |  8550K|   204M| 95512   
(1)| 00:19:07 |
|  16 |     VIEW                          | VW_SQ_1               |  3628K|   162M|       | 37904   
(1)| 00:07:35 |
|  17 |      HASH GROUP BY                |                       |  3628K|   107M|   333M| 37904   
(1)| 00:07:35 |
|* 18 |       INDEX FAST FULL SCAN        | PAR_ANNOTATION_PK     |  3628K|   107M|       |  6790   
(2)| 00:01:22 |
|* 19 |     TABLE ACCESS FULL             | PAR_ANNOTATION        |  3628K|   394M|       | 25773   
(1)| 00:05:10 |
----------------------------------------------------------------------------------------------------
---------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("PP"."PORTFOLIO_ID"="VTA"."OWNER"(+))
   2 - access("PP"."PORTFOLIO_ID"="VPP"."PORTFOLIO_ID")
   6 - access("PP"."PORTFOLIO_ID"="PPD"."PORTFOLIO_ID")
   7 - filter(("PP"."PORTFOLIO_STATUS"=1 OR "PP"."PORTFOLIO_STATUS"=3) AND "PP"."IS_CLUSTERED"=2)
   8 - filter("PPD"."CLUSTER_PORTFOLIO_LINK" IS NULL AND "PPD"."STATE"=1 AND 
("PPD"."PRPT_CLUSTER_FLAG"=2 
              OR "PPD"."PRPT_CLUSTER_FLAG"=3))
   9 - access("PP"."PORTFOLIO_ID"="PPD"."PORTFOLIO_ID")
  10 - filter("PP"."PORTFOLIO_STATUS"=3 OR "PP"."PORTFOLIO_STATUS"=5 OR "PP"."PORTFOLIO_STATUS"=6)
  11 - access("PP"."IS_CLUSTERED"=1)
  12 - filter("PPD"."CLUSTER_PORTFOLIO_LINK" IS NOT NULL AND ("PPD"."STATE"=2 OR "PPD"."STATE"=4) 
AND 
              "PPD"."PRPT_CLUSTER_FLAG"=2)
  15 - access("PA"."ANNOTATION_DATE"="VW_COL_1" AND "PA"."ANNOTATION_TIME"="VW_COL_2" AND 
              "OWNER"="PA"."OWNER" AND "ANNOTATION_TYPE"="PA"."ANNOTATION_TYPE")
  18 - filter("PA2"."ANNOTATION_TYPE"='4')
  19 - filter("ANNOTATION_TYPE"='4')

-------------------------------------------------------------------------------

Followup February 15, 2011 - 8am Central time zone:

why wouldn't you just generate the profile on the target query in question? Just like you did for the one without the username on it?

March 1, 2011 - 12pm Central time zone

Bookmark | Bottom | Top

Reviewer: A reader

I tried the same on original text of view and the select statement runs fine but when I compile the 
view and select from view it doesn't run finr. I tried hint /*+ OUTLINE(@"SEL$0A1A5A0C") */  with 
the view then it works fine but when I create the view with simliar hint and again it is not 
performing well.

SELECT /*+ OUTLINE(@"SEL$0A1A5A0C") */ * from ella_v_eng_portfolio

This view is being used in many of my application module.

Please help.

I will give you all details as soon as you require.

Followup March 1, 2011 - 12pm Central time zone:

did you create a profile for this query:

select * from PARA.ELLA_V_ENG_PORTFOLIO;

SQL Profile Contents March 2, 2011 - 4pm Central time zone

Bookmark | Bottom | Top

Reviewer: Abhisek

Hi Tom,

Can we see the contents of the SQL Profile that we accept? I want to see what it actually contains? 
Can it be seen through any package or table?

Followup March 2, 2011 - 5pm Central time zone:

when you get the profile, it shows you the plan that would result as a side effect of using the profile - that is what you get to see.

otherwise a profile is just extra statistics - undocumented in format. It is stuff as a piece of XML (I believe you'll see it in the plan table if you explain the query) somewhere - but isn't really something we need to 'see'.

The resulting plan is what counts.

SQL Profile March 3, 2011 - 1am Central time zone

Bookmark | Bottom | Top

Reviewer: Abhisek

Hi Tom,

Thanks for the explanation.

But it should be storing the SQL Profile somewhere? Can I see what was the actual contents? jsut 
curious. if not possible, I take it that way only.

Followup March 3, 2011 - 7am Central time zone:

it does store it, but it isn't like it is a readable bit of information. It is nothing we ever need to look at, it wouldn't be useful to us. We "see it" via the explain plans and the estimated cardinality. That is the manifestation of it that means something to us - the corrected estimated cardinality values.

Write a Review