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
I just made the leap to a Mac Pro 8 Core from a 350MHz G3. I was running 10.3.9 on the G3, but I was still using the old Outlook mail program because I was comfortable with it. Now I can't figure out how to import the address book into Mail 3.3. I kn [More]
i have a 3 minute movie....i want to make it sendable via the internet....it is current HD with 1440 x 1081 aspect ratio. It seems to me the Export - Using QuickTime Conversion option would be a great solution....however every setting i choose render [More]
I did the new download 7.1.2 and my iMessages don't go through and no one can receive them!! I tried restarting my phone and turning off for a couple hours and nothing works!!!! I still have wifi which works and my apple id works on the apple site bu [More]
I am interested to understand if you can easily salvage the data files if the log files corrupt for some reason. Thus far I cannot see how this can be easilly achieved as db_backup requires a working environment, and since the log files may have vani [More]
I am trying to make an HTML webpage. But I am having a hard time changing the appearance of my menu buttons. The starting point of my work could be seen on this address: http://www.batzorig.com/gallery2 As you can see, at the moment there are only tw [More]
hello! i have a problem with the PKGBUILD of ots-plugin for gedit. here you can get more information about ots: http://debaday.debian.net/2007/06/06/ots-command-line-text-auto-summary/ this is the PKGBUILD: # Contributor: iggy <[email protected]> pk [More]
According to Speedtest, my Internet speeds have dropped to .74 Download and .69 Upload.  I've done everything suggested on a previous page like rebooting the modem and having virus and spyward protection.  Why am I not getting the 2.7 speed I'm payin [More]
I now get a dull grey title area where before my appearances was showing up. This happens no matter what appearance I select. Including the default one. Is there a master setting that turns this feature on/off?This was updating from 19 to 20, I assum [More]
Hi, I have a fresh installation of 11.5.10.2 My customer wants Discoverer 10g to be configured on separate machine and integrated with ebiz. I have not worked on discoverer earlier but have very little knowledge abt discover. Does 11.5.10.2 come with [More]
I have a nokia 701 with symbian belle on it and the video editor program is not working I searched  for a solution on the internet and its written that I just can open the video and edit it but it didn't work with me and thier is no edit option on th [More]