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