Search

OakieTags

more tags

Who's online

There are currently 0 users and 34 guests online.

Recent comments

Affiliations

Home

Oakies Blog Aggregator

dba_tab_modifications

Submitted by Jonathan Lewis on Tue, 2010-07-06 22:01

In case you don’t follow the link to Martin Widlake’s blog (see right) very often, he’s done a couple of recent posts on dba_tab_modifications that are worth reading. (And I’ve just discovered the ‘gutter=”false”;’ option for the ‘sourcecode’ tag in one of the comments on Martin’s blog – and that’s also a  helpful feature.) Filed [...]

Oracle's SQL Tuning Pack - part 3

Submitted by khailey on Tue, 2010-07-06 21:18

How does the STA work in 11gR2 with the query from "Oracle's SQL Tuning Pack - part 2" ?

Here is the view on the Top Activity Page:
The first spike is when I ran the query. I pass the gray window over the spike and selected the first query and submitted it to the tuner. The rest of the load, the next 30 minutes is the STA running trying to find a better plan. After 30 minutes the STA times out saying not better plan found.
On 10gR2 the STA finishes in less than 2 minutes and finds a profile though the profile puts a higher load on the database for this query. On 11gR2, the STA runs for 30 and times out without finding any suggestions.

Here is a video on Vimeo of the tuning with DB Optimizer which takes less than 60 seconds to find a faster plan:

Oracle's SQL Tuning Pack, part 2

Submitted by khailey on Tue, 2010-07-06 19:17

Above its the load produced by the same load. Its a query being run by 10 users 20 times with a sleep of random interval between 1-2 seconds.
The graph measures the load on the database. Green is CPU, blue is IO and the redish brown is concurrency.
First, on the left is the load of the original default execution plan.
Second, in the middle is the load after applying a profile from the SQL Tuning Advisor (STA).
Third, on the right is the load after having been tuned by DB Optimizer.
All this goes to show that the STA's profile can be sub-optimal even when a better plan exists.
Here is the view of the same load from the "Statistics" tab of the SQL Details in OEM 10g.
First, dark blue, is load from the default path.
Second, light blue is the load from the STA suggested profile.
Third is the load after tuning the query with DB Optimizer.
Here is the query, actually a Peoplesoft query, 
SELECT
A.COMPANY,
A.PAYGROUP,
E.OFF_CYCLE,
E.SEPCHK_FLAG,
E.TAX_METHOD,
E.TAX_PERIODS,
C.RETROPAY_ERNCD,
SUM (C.AMOUNT_DIFF) SUM_AMOUNT
FROM
PS_PAY_CALENDAR A,
WB_JOB B,
WB_RETROPAY_EARNS C,
PS_RETROPAY_RQST D,
PS_RETROPAYPGM_TBL E
WHERE
A.RUN_ID = 'PD2' AND
A.PAY_CONFIRM_RUN = 'N' AND
B.COMPANY = A.COMPANY AND
B.PAYGROUP = A.PAYGROUP AND
E.OFF_CYCLE = A.PAY_OFF_CYCLE_CAL AND
B.EFFDT = (SELECT MAX (F.EFFDT)
FROM WB_JOB F
WHERE
  F.EMPLID = B.EMPLID AND
  F.EMPL_RCD# = B.EMPL_RCD# AND
  F.EFFDT < = A.PAY_END_DT) AND
       B.EFFSEQ = (SELECT MAX (G.EFFSEQ)
FROM WB_JOB G
WHERE
    G.EMPLID = B.EMPLID AND
    G.EMPL_RCD# = B.EMPL_RCD# AND
    G.EFFDT = B.EFFDT) AND
C.EMPLID = B.EMPLID AND
C.EMPL_RCD# = B.EMPL_RCD# AND
C.RETROPAY_PRCS_FLAG = 'C' AND
C.RETROPAY_LOAD_SW = 'Y' AND
D.RETROPAY_SEQ_NO = C.RETROPAY_SEQ_NO AND
E.RETROPAY_PGM_ID = D.RETROPAY_PGM_ID
GROUP BY
A.COMPANY,
A.PAYGROUP,
E.OFF_CYCLE,
E.SEPCHK_FLAG,
E.TAX_METHOD,
E.TAX_PERIODS,
C.RETROPAY_ERNCD;

For the courageous or bored with idle time, the dmp and SQL are available at

and for kicks, the query's VST diagram
Couple of thoughts questions in my mind.
First if the reason for picking sub-optimal path in the first place is due to a bug, then it would make some sense that the the STA would still have the same bug and make the same mistakes.
Second, I wonder if the STA is really trying to find better plans, or if it is trying to pull the statistics used by the optimizer more in line with the actual statistics that would happen when running the query along the lines of TCF or tuning by cardinality feedback. When I look at the hints in profile the are of the nature of skewing stats one way or another:
OPT_ESTIMATE(@"SEL$485D066A", TABLE, "C"@"SEL$1", SCALE_ROWS=4.412144743)
OPT_ESTIMATE(@"SEL$485D066A", INDEX_SKIP_SCAN, "C"@"SEL$1", WBBRETROPAY_EARNS, SCALE_ROWS=3.642257471e-005)
OPT_ESTIMATE(@"SEL$485D066A", JOIN, ("B"@"SEL$1", "A"@"SEL$1"), SCALE_ROWS=12.28434396)
OPT_ESTIMATE(@"SEL$485D066A", JOIN, ("B"@"SEL$1", "VW_SQ_2"@"SEL$09936D23"), SCALE_ROWS=3.049755426)
OPT_ESTIMATE(@"SEL$485D066A", INDEX_SKIP_SCAN, "C"@"SEL$1", WB_RETROPAY_EARNS_IDX1, SCALE_ROWS=3.642257471e-005)
OPT_ESTIMATE(@"SEL$485D066A", INDEX_FILTER, "C"@"SEL$1", WB_RETROPAY_EARNS_IDX1, SCALE_ROWS=3.642257471e-005)
OPT_ESTIMATE(@"SEL$485D066A", JOIN, ("C"@"SEL$1", "B"@"SEL$1", "A"@"SEL$1", "VW_SQ_2"@"SEL$09936D23"), SCALE_ROWS=15.63082791)
OPT_ESTIMATE(@"SEL$485D066A", JOIN, ("C"@"SEL$1", "B"@"SEL$1", "A"@"SEL$1"), SCALE_ROWS=5.133606627)
OPT_ESTIMATE(@"SEL$485D066A", JOIN, ("B"@"SEL$1", "A"@"SEL$1", "VW_SQ_2"@"SEL$09936D23"), SCALE_ROWS=37.50606343)
OPT_ESTIMATE(@"SEL$B186933D", TABLE, "C"@"SEL$1", SCALE_ROWS=4.412144743)
OPT_ESTIMATE(@"SEL$B186933D", INDEX_SKIP_SCAN, "C"@"SEL$1", WBBRETROPAY_EARNS, SCALE_ROWS=3.642257471e-005)
OPT_ESTIMATE(@"SEL$B186933D", JOIN, ("B"@"SEL$1", "A"@"SEL$1"), SCALE_ROWS=9.626410845)
OPT_ESTIMATE(@"SEL$B186933D", INDEX_SKIP_SCAN, "C"@"SEL$1", WB_RETROPAY_EARNS_IDX1, SCALE_ROWS=3.642257471e-005)
OPT_ESTIMATE(@"SEL$B186933D", JOIN, ("E"@"SEL$1", "D"@"SEL$1", "C"@"SEL$1", "B"@"SEL$1", "A"@"SEL$1"), SCALE_ROWS=3.896066538)
OPT_ESTIMATE(@"SEL$B186933D", INDEX_FILTER, "C"@"SEL$1", WB_RETROPAY_EARNS_IDX1, SCALE_ROWS=3.642257471e-005)
OPT_ESTIMATE(@"SEL$B186933D", JOIN, ("E"@"SEL$1", "B"@"SEL$1", "A"@"SEL$1"), SCALE_ROWS=9.626410845)
OPT_ESTIMATE(@"SEL$6E19F182", TABLE, "C"@"SEL$1", SCALE_ROWS=4.412144743)
OPT_ESTIMATE(@"SEL$6E19F182", INDEX_FILTER, "C"@"SEL$1", WB_RETROPAY_EARNS_IDX1, SCALE_ROWS=3.642257471e-005)
OPT_ESTIMATE(@"SEL$6E19F182", INDEX_SKIP_SCAN, "C"@"SEL$1", WBBRETROPAY_EARNS, SCALE_ROWS=3.642257471e-005)
OPT_ESTIMATE(@"SEL$6E19F182", INDEX_SKIP_SCAN, "C"@"SEL$1", WB_RETROPAY_EARNS_IDX1, SCALE_ROWS=3.642257471e-005)
The above profile "hints" are mostly concerning scaling the expected rows.
Speaking of TCF it's interesting to not that in DB Optimizer the TCF values of estimate verses actual come out closer in the optimize query verses the original.
Below are the explain plans for the default plan, plan with profile and the DB Optimizer tuned plan. Notice the "Orders of Magnitude" column. This column shows the orders of magnitude that the estimated rows differed from the actual rows per line in the execution plan. The larger the difference the bigger the discrepancy between what the optimizer expected and what actually happened
Default Plan and TCF
Profile Explain and TCF
DB Optimized Plan and TCF
Ironically the plan using the profile has the worst TCF discrepancies

The only thing you should NOT ask Icelanders (and maybe Norwegians) to do...

Submitted by mno on Tue, 2010-07-06 17:51

I love to visit Iceland. Not because of the landscape (We Do Not Use landscapes), but because of the people there. Man, they're funny and good to talk to.

This time (last week, for two days only) I detected a deep, slow-burning anger in the folks up there, that I haven't felt before.

One of them - a very stylish, suit-dressed bartender in his 40's, who broke his principle about not talking politics with guests (hey, the bar was empty), said that he suspected there would be real riots in the fall of 2010 if the few guilty bastards were not punished for real in court.

The handful of real bastards have been driven out of Iceland, by the way: People simply spat on them when they met them on the street AND painted their houses and cars red at night. One of the bastards re-painted his house in its original color. Guess what happened the following night.

They all now live in the UK or Florida.

Fine and good. Now you know.

But this post is about the one thing you should never ask an Icelander to do: A list.

You see, back around year 900 several Norwegian vikings sailed to Iceland. They pretty much remembered everything: Clothes. Food. The ship. Oares. Live animals. Tools. Pen & Paper. You know - all the usual stuff for a 400 years voyage.

But whoever was in charge of The Norwegian Iceland Travel List forgot one thing, and whether they discovered it quickly (i.e. on the journey) or when they had settled in in their small mud huts, I do not know. But man, they must have told The List Guy a thing or two upon suddenly remembering what they had forgot:

Women.

Anyway, they apparently decided to do something about it, because DNA-tests of Icelandic women some years ago confirmed that they originate from the British isles.

The British scientist who found this out, and who was interviewed on Danish Radio, commented on the fact that Icelandic women look Pretty Damn Good by saying (rather drily): "They probably didn't take the ugly ones."

So now you know.

Cloning prior to patching/upgrading/CPU: what do you do?

Submitted by fritshoogland on Mon, 2010-07-05 09:07

In addition to my previous post about cloning, please tell me your experiences and practices. What do you do when altering your Oracle database software? I think a home name should include a FULL version number (so 11.2.0.1 instead of 11.2.0) to indicate the version of the home. If you strictly clone the home before [...]

Oracle datafile IO latency – Part 1

Submitted by karlarao on Mon, 2010-07-05 08:13

On my post about observing the Exadata V1 I had an interesting comment posted by Mark Seger (author of collectl and collectl utilities) about the correlation of activities across a system, the sample and snap time, and seeing the state of the subsystem before and after…

The comment made me curious about the effect of snap intervals on the performance numbers of the datafiles and block devices.. especially on the latency numbers.. so I made a few test cases and created some scripts that would give me 5 seconds, 10 minutes, and 60 minutes output of latency numbers on the database. Also running 5 seconds interval of OSWatcher to give me a view on the block devices.

As I was doing all of this, I had an interesting discovery about how the latency output of the datafiles are being computed and I was able to quantify by having the performance numbers how average could be misleading and mask the problem on the datafile IO latency.

I did a one-take (amateur :) ) clip straight from my iPhone3gs to give you an overview about it..

On the next post I will detail on the following:
- where and how to get datafile IO latency
- how is it computed
- how does the long average can affect the latency output
- how does this affect performance tuning?
- what can you do about it?

 

Cloning your Oracle database software installation

Submitted by fritshoogland on Sat, 2010-07-03 12:49

With the coming of the binary version of the installer repository (with Oracle 9 if my memory serves me well), some things changed which did not make everybody happy. The biggest disappointment of most people: it was not supported to tar (or zip, cpio, whatever) your Oracle installation and put it in another place. This [...]

Unrecoverable

Submitted by Jonathan Lewis on Fri, 2010-07-02 19:38

A recent question on the OTN database forum asked: “What’s the difference between index rebuild unrecoverable and nologging?” The most important difference, of course, is that unrecoverable is a deprecated option so you shouldn’t be using it even though it still works. Another important difference is that unrecoverable tells you exactly the risk you are [...]

Oracle's SQL Tuning Pack - how well does it work?

Submitted by khailey on Fri, 2010-07-02 00:52

Have you used Oracle's SQL Tuning Pack? What were your experiences like?
At my last consulting gig 2 out 3 statements I "tuned" with the SQL tuning pack actually ran significantly more slowly after applying the profiles recommended. I thought it was just my bad karma and luck of the draw until recently. Recently I've started asking other DBAs what their experiences were and generally finding less than enthusiastic responses. In the classes on Oracle 10g and OEM, I've always cautioned that profiles should not be blindly trusted and give an example using a Swingbench query where the SQL runs more slowly after the profile.If applying the profile, I recommend to jump immediately to the SQL performance page and to verify the effect of the profile on the load of the query.

Above is a page from OEM after the SQL Tuning Advisor has been run, a profile is suggested, and now I can implement the profile.

After the profile has been implemented, I'd recommend clicking on the SQL_ID to see go to the SQL details page and see what the new execution plan looks like on the load chart

In the above SQL details we can see the new execution plan hash 467377872 and that it's load is now higher. The number of executions by the application, swingbench remained the same, but the load by the SQL statement with the new profile is higher.
At this point I'd click on "Tuning Information" and back out the profile.

I thought the above example was a fluke along with the couple of bad experiences I had at a customer site but after asking a number of other DBAs who have used the SQL Tuning Advisor and who have responded less than enthusiastically, I'm now curious to poll a wider audience.

A couple of thoughts:
One, I think the reason the STA's profile created more load is because the application, swingbench, actually only uses one row from the query result set and ignores the rest of the result set. I'm guessing that the STA tuned the query for the entire result set.
Two, it seems to be a serious flaw that the STA doesn't actually run the query with the new profile to compare the before and after. At least this should be offered as an option.
It seems that Oracle 11 will address some of this issues in a round about way with SQL baselines and the evolve. See http://www.oracle.com/technology/oramag/oracle/09-mar/o29spm.html
From the above link, for example


                     Baseline Plan       Test Plan   Improv. Ratio
                     --------------      ---------   -------------
Execution Status:          COMPLETE       COMPLETE
Rows Processed:                   0              0
Elapsed Time(ms):              5036           1033         4.88
CPU Time(ms):                   254            700          .36
Buffer Gets:                   1728          43945          .04
Disk Reads:                     254             22        11.55
Direct Writes:                    0              0
Fetches:                         49             22         2.23
Executions:                       1              1

Though I haven't investigated how the SQL Baselines and the evolve option are incorporated in OEM 11g.

Index branches

Submitted by Jonathan Lewis on Thu, 2010-07-01 19:45

Some time ago I published a little script that showed you how to read an index treedump and summarise the leaf block usage in the logical order of the index leaf blocks – allowing you to see fairly easily if the index had any areas where the blocks were poorly filled. Here’s another  way of looking [...]