Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Joe.

Asked: March 24, 2008 - 12:24 pm UTC

Last updated: March 03, 2011 - 7:49 am UTC

Version: 10.2

Viewed 1000+ times

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 Tom 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

Rating

  (12 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, March 24, 2008 - 8:30 pm UTC


Execution plan from tuning advisor

Dhairyasheel Ttawde, March 25, 2008 - 2:28 am UTC

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.
Tom Kyte
March 25, 2008 - 6:43 am UTC

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

Dana, March 25, 2008 - 3:53 pm UTC

Are SQL Profiles an extension of sql outlines?
Many of the features sound similar or same.
Tom Kyte
March 26, 2008 - 8:43 am UTC

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.

Dhairyasheel Ttawde, March 26, 2008 - 2:58 am UTC

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.


Tom Kyte
March 26, 2008 - 8:57 am UTC

gr8?

gr8 explained

Dhairyasheel Ttawde, March 27, 2008 - 12:50 am UTC

Hi Tom,

gr8 = short form for great.

Think of it as an optimized way of communication.
less bytes to upload,store..
Tom Kyte
March 27, 2008 - 10:41 am UTC

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

That is what it looks like to me.

Profiles explanation

Dana, March 27, 2008 - 11:04 am UTC

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

Alexei, May 22, 2008 - 6:48 am UTC

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

Ummati, March 17, 2010 - 7:41 pm UTC

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.


Tom Kyte
March 18, 2010 - 8:33 am UTC

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

Sarvindu, February 15, 2011 - 5:23 am UTC

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')

-------------------------------------------------------------------------------
Tom Kyte
February 15, 2011 - 8:38 am UTC

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?

A reader, March 01, 2011 - 12:38 pm UTC

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.
Tom Kyte
March 01, 2011 - 12:43 pm UTC

did you create a profile for this query:

select * from PARA.ELLA_V_ENG_PORTFOLIO;

SQL Profile Contents

Abhisek, March 02, 2011 - 4:59 pm UTC

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?
Tom Kyte
March 02, 2011 - 5:46 pm UTC

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

Abhisek, March 03, 2011 - 1:33 am UTC

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.
Tom Kyte
March 03, 2011 - 7:49 am UTC

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.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.