Sitemap

Amicuk Programming Answers

How to create outline from perfstat.stats$SQL_PLAN table

-0001-11-30   Views:0

Advertisement

How do I create outline (Plan stability in 10gR2database) from perfstat.stats$SQL_PLAN table? Current V$SQL_PLAN does not contain good but but week older snapshots have good plan. I can't modify query, it's vendor query so I need to create outline/pl

How do I create outline (Plan stability in 10gR2database) from perfstat.stats$SQL_PLAN table?
Current V$SQL_PLAN does not contain good but but week older snapshots have good plan.
I can't modify query, it's vendor query so I need to create outline/plan stability.
Thanks for help.

The replay answer
Advertisement
user7478143 wrote:
Thanks for reply.
I'm not saying I find it out doc but here is situation.
I have query which change execution plan week ago.
I have perfstat enable and I found out good execution plan from perfstat.stat$SQL_PLAN view.
If it's our query I can modify query and able to get good execution plan but it's vendor supplied query (which is Oracle - it used to be Portal now Oracle acquired it) so I can't modified it.
All I'm trying to do is how do I generate good plan and assigned to vendor query's hash_value/sql_id.If DBMS_ADVANCED_REWRITE is not an option for you and you want to use an outline, there is one significant problem. The Statspack captured data does not include quite enough information, in particular the OTHER_XML column, and the access predicates. So, what can you do? Note that this script is from page 214 of the book that I co-authored. Let's say you execute this SQL statement:
SQL> SELECT
  2    SQL_ID,
  3    COUNT(DISTINCT PLAN_HASH_VALUE) C
  4  FROM
  5    PERFSTAT.STATS$SQL_PLAN_USAGE
  6  GROUP BY
  7    SQL_ID
  8  HAVING
  9    COUNT(DISTINCT PLAN_HASH_VALUE)>1;
SQL_ID         C
0fr8zhn4ymu3v  2
0h6b2sajwb74n  2
1gu8t96d0bdmu  2
39m4sx9k63ba2  2
4b57myt9mpz37  3
52tr7ay85qwn0  5
…Interesting, SQL_ID has 2 different execution plans, let's take a look at the execution plans:
SQL> SET LINESIZE 150
SQL> SET PAGESIZE 10000
SQL> SPOOL StatspackPlan.txt
SQL> SELECT /*+ ORDERED */
  2    T.*
  3  FROM
  4    (SELECT DISTINCT
  5       PLAN_HASH_VALUE
  6     FROM
  7       PERFSTAT.STATS$SQL_PLAN_USAGE
  8     WHERE
  9       SQL_ID='0fr8zhn4ymu3v'
10     ORDER BY
11       PLAN_HASH_VALUE) SPU,
12    TABLE(DBMS_XPLAN.DISPLAY(
13      'PERFSTAT.STATS$SQL_PLAN',
14      NULL,
15      'TYPICAL -PREDICATE -NOTE',
16      'PLAN_HASH_VALUE='||SPU.PLAN_HASH_VALUE)) T;
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
|*  0 | SELECT STATEMENT            |            |       |       |     2 (100)|          |
|*  1 |  TABLE ACCESS BY INDEX ROWID| OPQTYPE$   |     1 |    27 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | I_OPQTYPE1 |     1 |       |     1   (0)| 00:00:01 |
| Id  | Operation             | Name     | Cost  |
|*  0 | SELECT STATEMENT      |          |       |
|*  1 |  SORT ORDER BY        |          |     0 |
|*  2 |   TABLE ACCESS CLUSTER| OPQTYPE$ |       |
|*  3 |    INDEX UNIQUE SCAN  | I_OBJ#   |       |
--------------------------------------------------As can be seen by the above, in one case the I_OBJ# index was used, and in another case the I_OPQTYPE1 index was used. Let's assume that I_OPQTYPE1 is the most efficient access path, so a hint like this needs to be inserted (assuming that the table does not have an alias in the SQL statement:
/*+ INDEX(OPQTYPE$ I_OPQTYPE1) */Now what (obviously, we would not want to actually fix the above plan for an internal SQL statement)? Take a look at the following, which shows how to build a private outline for the unhinted version of the SQL statement, an outline for a hinted version, and then swap the resulting outlines, followed by building a public outline from the hacked outline:
http://hoopercharles.wordpress.com/2009/12/18/tracking-performance-problems-inserting-a-hint-into-sql-in-a-compiled-program/
Charles Hooper
Co-author of "Expert Oracle Practices: Oracle Database Administration from the Oak Table"
http://hoopercharles.wordpress.com/
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.

Go to See the other 6 answers

How to create outline from perfstat.stats$SQL_PLAN table

Category:DefaultRelease time:-0001-11-30Views:130

How do I create outline (Plan stability in 10gR2database) from perfstat.stats$SQL_PLAN table? Current V$SQL_PLAN does not contain good but but week older snapshots have good plan. I can't modify query, it's vendor query so I need to create outline/pl[More]

Can't create outlines from a placed image?

Category:DefaultRelease time:2015-10-11Views:130

I'm using illustrator and I placed this image: http://i500.photobucket.com/albums/e406/Aelvir/G2_zps95ce8124.png and no matter what I do, it won't let me select "Create Outlines".aelvir wrote: ...and the Create Outlines thing is still unavailabl[More]

Creating Outlined Text in Pages 09

Category:DefaultRelease time:2015-10-11Views:130

Having read the manual for Pages 09, i've learned how to create Outlined Text, but i want to know if i can keep the colour of the text as it was before creating the outline. For example, a black bold headline with a red outline? Instead what i get is[More]

How do you create outlines from strokes in cs5

Category:DefaultRelease time:-0001-11-30Views:130

how do you create outlines from strokes in cs5Sure. Roughen effect. This is interesting for some "vintage" look of lines. Roughen effect with Outline first.Read other 8 answers[More]

How do I create outlines for created pattern (or swatch) for laser cut?

Category:DefaultRelease time:-0001-11-30Views:130

Hi, I can't figure out how to create outlines (vector lines) for my patterns , as I need to laser cut the pattern, and all the lines have to be seen, Please help! I use Illustrator CS6.mer, What happens if you Object>Expand?Read other 4 answers[More]

"Create outlines" grayed out for text-- what is the RIGHT option??

Category:DefaultRelease time:-0001-11-30Views:130

Hey all, So here's what happened. I created an image for a print template in PS (which I use all the time) and then used file-place to bring the image into the AI version of the template. (I don't use AI very often...) I knew that it might not be a g[More]

Create Outlines not working

Category:DefaultRelease time:-0001-11-30Views:130

I'm running ID CS6, v. 8.0.1 on Mac OSX 10.6.8. When I try to convert type to outlines, the Create Outlines option is grayed out. Also, it says 'Create Outlines menu' instead of just 'Create Outlines'. I tried selecting the text box as well as just h[More]

Some text after creating outlines fills with gadient

Category:DefaultRelease time:-0001-11-30Views:130

I have a few files in Illustrator CS that when I select all text and "create outlines" of them, about half of the text fills with black (normal), and the other half fill with a black to white gradient. Has anyone else seen this? Thank you very m[More]

Creating Outline from textPath

Category:DefaultRelease time:-0001-11-30Views:130

Hi, Going follow one of first sentence from "Adobe_intro_to_scripting" which says: ...Each scripting item corresponds to a tool or a palette or menu item in an Adobe application... I am trying to discover how to reach effect which is obtained fr[More]

Type, create outlines and merging questions

Category:DefaultRelease time:-0001-11-30Views:130

Hi, I have type which I then I created outlines. Then I have this shape (a rectangle frame), I want to merge the two so I can bring an image into the combined shape. I cannot figure out how to merge the outlines text with this frame - is this even po[More]

How to Create Outline Text in OS X 10.10 Preview

Category:DefaultRelease time:-0001-11-30Views:130

Just installed OS X 10.10 and, all in all, I'm very happy with the upgrade. Preview really has some nice enhancements, but one thing that seems to be missing is the ability to create outline text. It was present in Mavericks, but I can't find any way[More]

Hot
Hi - I recently upgraded to FCP 5.1.2 then tried to capture footage with my Sony FX1 HDV camcorder, but the software indicates that it cannot initialize my device. The only way it will work is when I change the capture settings to HDV Apple Intermedi [More]
I rented a movie and downloaded it to my iPad.  Yesterday my iPad was doing some strange things so I went to the Apple Store and they said it was a software problem.  I had everything saved to my laptop. so we cleared the iPad and set it up as new.  [More]
Hi Experts, I've a requirement in which I'm looking for help. Consider that I've one DSO with line item detail and I wish to display on a report for each line item (in the same row) the value of the header which has to be the sum of all line items fo [More]
Weird problem just appeared. Home computer has two accounts (A and B). I allow ssh login to both accounts via public key login (ssh-keygen). Two remote computers with accounts A' and B' on one, and A" and B" on the other. I can ssh into the home [More]
apple is not accepting my payment method ,I also used many mastercards but it is always declining it is very annoying what should I do?The quickest way to get an answer to this may be to contact your card issuer directly, it may be that they are havi [More]
Working on a 200 page pdf file and need it converted quickly into a ppt presentation.  Any advice?You can use Acrobat or the PDF Pack online service.Read other 3 answers [More]
I am trying to extract the xmlsample jar. I get a exception raised, can anybody help? created: xmlnews/admin/ extracted: xmlnews/admin/AdminServletHTML.java extracted: xmlnews/admin/MultipartHandler.java extracted: xmlnews/admin/NewsAdmin.jpr extract [More]
How to make attachment mandatory in E-Separation in ESS screen while submitting the resignation?Hello, Apologies for delayed response. Please find the below steps : 1) Extend ReviewCO (pkg - oracle.apps.per.selfservice.review.webui.ReviewCO) 2) In PF [More]
Hi, about a month ago, i go to my phone update, and update my phone, aftet that i hope to see FM radio, data sence, ... But i don't have any of theme. i.e. I think i just have the os update and no amber updates. my phone is lumia 520 , soudi arabia o [More]
I have restarted the computer several times since installing Java.I have saw this question posted quite a lot on the support page. I think that there are some compatibility issues with Firefox, Windows 8, and Java. I new version of Firefox was just r [More]