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
Hey guys, Wondering if anyone can urgently help me please. I've got a FLA file making an animated banner. When I export it as an animated GIF, the background (which originally is a solid color) has either dots or crosses making up the color. I've tri [More]
how can I populate a table reading the data from a TXT file? thanksHey Kevin! Using FORMS.TEXT_IO to bulk load data from a file strikes me as re-inventing the wheel. It is just about justifiable in a self-service environment, but I regard the EXTERNA [More]
Hello, I just received back my Mac-mini from Apple service. As other users reported, the fan was going mad, running at full speed all the time. By using: http://www.bresink.de/osx/TemperatureMonitor.html I could see that the temperature-sensor connec [More]
I swipe up words to get the tab to appear, On the tab there is no airplay link, just airdrop. I was hoping to mirror it on are new 32" Samsung Smart tv,  I am also now at this. ThanksGO to settings-messages and turn on iMessag, should work but you ne [More]
I went to use Onyx yesterday and it asks for my password. I enter my password and it says "Sorry, you entered an invalid password. Please try again. If the problem persists, quit OnyX, repair the permissions with Disk Utility then relaunch OnyX." [More]
Hi, How can i convert a spool into a pdf format and fill the atributes of the PDF document with title, author, subject and keywords from abap? Kind regards, H. HendriksCheck the Below code ...... REPORT ZMAILOUTPUT     MESSAGE-ID ZZ. *  CONSTANTS     [More]
The current time on my router keeps slipping by 15 minutes per week.  I can't find any place to change the time.  Changing the timezone is the only time related item on the router, but changing the timezone doesn't correct the time.  Any ideas?Try Up [More]
Hi Experts, Is is possible to default a transaction type XXX (Field: COBL-RMVCT)  for a GL Account when the user using transaction codes F.13( auto clearing) and F-03 (clearing GL account). Thanks in advance laxmi.Hi We can do that by using substitut [More]
I have a procedure that builds an Excel Spreadsheet from a parameter form, depending on the user's selected criteria. To make it easier for testing I am just selecting a range of 3 leases from this form. Each lease may have several entries according [More]
Firefox is misbehaving when I try to edit content locally so I've turned to Safari. The problem is the Error Console only displays errors for the current page. The problem with this is I have a link that's not behaving properly and when I call it it [More]