Search

Top 60 Oracle Blogs

Recent comments

Oakies Blog Aggregator

Buffer Cacheにデータがない場合のparallel_degree_policy=autoの動き

前回、buffer cacheが足りなくなったら?というシナリオでテストをしたけど、なんとなく納得できない。
そもそもparallel_degree_policy=autoでIn-Memory PQが動くはず(動いているはず)と信じていいの?なんていう疑問が湧いてくる。
buffer cacheをflushしてbuffer cacheにデータがない状況で

parallel_degree_policy=auto:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0

SQL> alter system flush buffer_cache;
システムが変更されました。
SQL> alter system flush shared_pool;
システムが変更されました。
SQL> set timi on
SQL> set autot traceSQL> alter session set parallel_degree_policy=auto;
セッションが変更されました。
SQL> select c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice, sum(l_quantity)
2 from customer, orders, lineitem
3 where o_orderkey in
4 ( select l_orderkey
5 from lineitem
6 group by l_orderkey
7 having sum(l_quantity) > 313)
8 and c_custkey = o_custkey
9 and o_orderkey = l_orderkey
10 group by c_name, c_custkey
11 , o_orderkey
12 , o_orderdate
13 , o_totalprice
14 order by o_totalprice desc, o_orderdate;
12行が選択されました。
経過: 00:00:02.88
実行計画----------------------------------------------------------Plan hash value: 1448507623
---------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 15209 | 950K| | 20231 (2)| 00:04:03 | | | |
| 1 | PX COORDINATOR | | | | | | | | | |
| 2 | PX SEND QC (ORDER) | :TQ10005 | 15209 | 950K| | 20231 (2)| 00:04:03 | Q1,05 | P->S | QC (ORDER) |
| 3 | SORT GROUP BY | | 15209 | 950K| 1088K| 20231 (2)| 00:04:03 | Q1,05 | PCWP | |
| 4 | PX RECEIVE | | 15209 | 950K| | 20229 (2)| 00:04:03 | Q1,05 | PCWP | |
| 5 | PX SEND RANGE | :TQ10004 | 15209 | 950K| | 20229 (2)| 00:04:03 | Q1,04 | P->P | RANGE |
|* 6 | HASH JOIN | | 15209 | 950K| | 20229 (2)| 00:04:03 | Q1,04 | PCWP | |
| 7 | PX RECEIVE | | 3750 | 201K| | 13435 (3)| 00:02:42 | Q1,04 | PCWP | |
| 8 | PX SEND BROADCAST | :TQ10003 | 3750 | 201K| | 13435 (3)| 00:02:42 | Q1,03 | P->P | BROADCAST |
|* 9 | HASH JOIN | | 3750 | 201K| | 13435 (3)| 00:02:42 | Q1,03 | PCWP | |
| 10 | PX RECEIVE | | 3750 | 113K| | 13128 (3)| 00:02:38 | Q1,03 | PCWP | |
| 11 | PX SEND BROADCAST | :TQ10002 | 3750 | 113K| | 13128 (3)| 00:02:38 | Q1,02 | P->P | BROADCAST |
|* 12 | HASH JOIN | | 3750 | 113K| | 13128 (3)| 00:02:38 | Q1,02 | PCWP | |
| 13 | PX RECEIVE | | 73952 | 433K| | 11166 (3)| 00:02:14 | Q1,02 | PCWP | |
| 14 | PX SEND BROADCAST | :TQ10001 | 73952 | 433K| | 11166 (3)| 00:02:14 | Q1,01 | P->P | BROADCAST |
| 15 | VIEW | VW_NSO_1 | 73952 | 433K| | 11166 (3)| 00:02:14 | Q1,01 | PCWP | |
|* 16 | FILTER | | | | | | | Q1,01 | PCWC | |
| 17 | HASH GROUP BY | | 185 | 649K| 114M| 11166 (3)| 00:02:14 | Q1,01 | PCWP | |
| 18 | PX RECEIVE | | 5997K| 51M| | 6773 (1)| 00:01:22 | Q1,01 | PCWP | |
| 19 | PX SEND HASH | :TQ10000 | 5997K| 51M| | 6773 (1)| 00:01:22 | Q1,00 | P->P | HASH |
| 20 | PX BLOCK ITERATOR | | 5997K| 51M| | 6773 (1)| 00:01:22 | Q1,00 | PCWC | |
| 21 | TABLE ACCESS FULL| LINEITEM | 5997K| 51M| | 6773 (1)| 00:01:22 | Q1,00 | PCWP | |
| 22 | PX BLOCK ITERATOR | | 1500K| 35M| | 1953 (1)| 00:00:24 | Q1,02 | PCWC | |
| 23 | TABLE ACCESS FULL | ORDERS | 1500K| 35M| | 1953 (1)| 00:00:24 | Q1,02 | PCWP | |
| 24 | PX BLOCK ITERATOR | | 150K| 3515K| | 306 (1)| 00:00:04 | Q1,03 | PCWC | |
| 25 | TABLE ACCESS FULL | CUSTOMER | 150K| 3515K| | 306 (1)| 00:00:04 | Q1,03 | PCWP | |
| 26 | PX BLOCK ITERATOR | | 5997K| 51M| | 6773 (1)| 00:01:22 | Q1,04 | PCWC | |
| 27 | TABLE ACCESS FULL | LINEITEM | 5997K| 51M| | 6773 (1)| 00:01:22 | Q1,04 | PCWP | |
---------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

6 - access("O_ORDERKEY"="L_ORDERKEY")
9 - access("C_CUSTKEY"="O_CUSTKEY")
12 - access("O_ORDERKEY"="L_ORDERKEY")
16 - filter(SUM("L_QUANTITY")>313)

Note
-----
- automatic DOP: Computed Degree of Parallelism is 2


統計
----------------------------------------------------------
1079 recursive calls
0 db block gets
156736 consistent gets
89285 physical reads
0 redo size
1563 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
21 sorts (memory)
0 sorts (disk)
12 rows processed

automatic DOP: Computed Degree of Parallelism is 2と出た。
あれ?Parallelで動いてる?
間違ったことを書いちゃった!
そんなはずはない:

SQL> select n.name, m.value
2 from sys.v_$mystat m, sys.v_$statname n
3 where n.name in ('CPU used by this session','DB time','physical reads direct')
4 and n.statistic# = m.statistic#
5 /

NAME VALUE
---------------------------------------------------------------- ----------
CPU used by this session 511
DB time 1608
physical reads direct 0

「良かった。間違ってない」ちゃんとdirect path readはしていない。

確認のため、同じことをParallel Queryでやってみる(念のためbuffer_cacheもflushして)

force prallel query 2:

SQL> alter system flush buffer_cache;
システムが変更されました。
SQL> alter system flush shared_pool;
システムが変更されました。
SQL> set timi on
SQL> set autot trace
SQL> alter session force parallel query parallel 2;
セッションが変更されました。
SQL> select c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice, sum(l_quantity)
2 from customer, orders, lineitem
3 where o_orderkey in
4 ( select l_orderkey
5 from lineitem
6 group by l_orderkey
7 having sum(l_quantity) > 313)
8 and c_custkey = o_custkey
9 and o_orderkey = l_orderkey
10 group by c_name, c_custkey
11 , o_orderkey
12 , o_orderdate
13 , o_totalprice
14 order by o_totalprice desc, o_orderdate;

12行が選択されました。

経過: 00:00:02.77
....
.... same plan ...
....
統計
----------------------------------------------------------
1009 recursive calls
0 db block gets
156652 consistent gets
156241 physical reads
0 redo size
1563 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
21 sorts (memory)
0 sorts (disk)
12 rows processed

SQL> set autot off
SQL> select n.name, m.value
2 from sys.v_$mystat m, sys.v_$statname n
3 where n.name in ('CPU used by this session','DB time','physical reads direct')
4 and n.statistic# = m.statistic#
5 /

NAME VALUE
---------------------------------------------------------------- ----------
CPU used by this session 460
DB time 1532
physical reads direct 153219

physical reads direct=153219とphysical reads数とほぼ同じになっている。

実行時間を比べてみると、やはりdirect path readを行うParallel Queryの方が速い。

まとめると、
buffer cacheにデータが全くない場合parallel_degree_policy=autoだと、

  • Parallel Queryをしたのと同じ実行計画が出てくる
  • automatic DOP: Computed Degree of Parallelism is 2とも出る
  • でもParallel実行はされていない
  • Oracle Data Mining Primer (3 of 3): Scoring Models

    Part 3 of this Oracle Data Mining Primer series will demonstrate how mining models are scored.

    The Oracle SQL language has been extended to include data mining functions that apply persisted models to new data.  Classification, regression, and anomaly detection models can be used to predict likely outcomes.  Clustering models can be used to assign rows to clusters.  Feature extraction models can be used to transform the input data to a new space.

    The data mining SQL functions are single row functions and are evaluated as any other built-in SQL function.  Parallel operations are fully supported without restriction as each input row can be evaluated independently.  The model information necessary to produce the scores (predictions, probabilities, etc.) is often loaded into shared memory as part of the shared cursor, enabling caching of the model content across query executions.

    ODM scores the classification model that was built in part 1 of this primer by leveraging the PREDICTION* family of functions.  Let's take the following query as a starting point for further understanding:
    select sum(decode(INCOME, 
                      PREDICTION(PREDICT_INCOME using *), 
                      1, 0))
               *100/count(*) as accuracy
    from adult_test;

    The query produces a measure of overall accuracy for the classification model when applied to the ADULT_TEST dataset.  The query reads in all rows from the input table, and for each row, uses a decode expression to compare the actual income level value, INCOME, with the result of applying the PREDICTION data mining function to the input row attributes.  As with a * on the select list, the * represents a wildcard which expands to all columns in the underlying data flow.  The input attributes are matched based on name to the attributes in the model.  The sum will count up how many correct predictions were made, and the *100/count(*) piece will translate this to a percentage of correct predictions.

    Overall accuracy provides a very coarse measure.  It is often mode interesting to investigate how the model predicts the individual income level values.  The following query will produce the confusion matrix associated with the PREDICT_MODEL for the ADULT_TEST dataset:

    select INCOME, predicted_income, count(*) from
    (select INCOME, 
            PREDICTION(PREDICT_INCOME using *) predicted_income
     from adult_test)
    group by INCOME, predicted_income;

    In addition to producing the most likely income level value for a given row, Oracle Data Mining can also produce a probability associated with a prediction.  Such a probability can differentiate very likely predictions from marginal ones.  The following query retrieves the ten adults that are most likely to earn >50K:

    select * from
    (select a.*, 

      rank() 
       over (order by 
             PREDICTION_PROBABILITY(PREDICT_INCOME, '>50K' using *)
             desc) rnk
     from adult_test a)
    where rnk <= 10;

    This query uses the rank analytic function to rank the rows, where the input for ranking is the probability that an individual is likely to have an income level of >50K.  The PREDICTION_PROBABILITY function takes as input the mining model, the input data, and, optionally, the class value of interest.

    The above examples just scratch the surface of how data mining results can be embedded within the context of SQL queries.

    Michigan Oak Table Symposium 2010 (MOTS) in Ann Arbor, MI, USA

    This morning we started with probably the biggest gathering of OakTable members ever. We gathered in Ann Arbor in Michigan for a two day event just before the Oracle Open World in San Francisco. The seminar is running in 3 parallel sessions.
    Cary Millsap, one of the founders of OakTable, told a short story how OakTable was founded and then he introduced Mogens Norgard - "Moans The Magnificent" who was answering to the secret questions even before he knew for them. Believe, we had a lot of fun with the answers and questions as well. On the picture you can see Mogens answering the questions and wearing the magic hat.
    As Doug Burns couldn't make to this event I offered to have another presentation which I had already in the past and was proposed by Carol - "Optimizing Access Paths".

    Tomorrow I'll be talking about "SQL Plan Baselines, Adaptive Cursor Sharing , Automatic Cardinality Feedback SQL Tuning in Oracle 11gR2".


    OpenWorld 2010 Session Update. Room Change Again.

    The OOW folks informed me that they needed to move our session to a different room–again. So, if you are interested here are the new details: ID#: S315110 Title: Optimizing Servers for Oracle Database Performance Track: Database Date: 20-SEP-10 Time: 17:00 – 18:00 Venue: Moscone South Room: Rm 102

    MOTS Mini – A Great Start

    Dinner was great last evening, I had a huuuuuuge steak (although Carol Dacko’s was bigger). Moans got his annual birthday gift, A great Arabian Nights kind of hat, and the 25+ people that attended the dinner had a good meal with great service at the  “Chop House” restaurant.
    image

    MOTS is about to start. After a good nights sleep (me: no extra alcohol infusion but straight to bed), I am now looking outside and really feel at home. Its cosy, its rainy and it will be fun despite, or probably because, the amount of attendees are approximately roundabout 50+ (Oakies not included). So this gives us the advantage to pinpoint on peoples work related, technical questions. If doable I will switch my demo session today with examples/demo’s explaining peoples the questions at hand.

    image

    Moans will open this years MOTS symposium. I am looking forward to it, if not only, because I already had some almost Dutch strong coffee in the lobby with a nice cone.

    Captain Support and the International Printer Incident…

    When I was in Frankfurt airport I got an email from my mom telling me that my sister-in-law was having trouble with here printer. This sounds like a job for Captain Support…

    Like any self respecting support super hero Captain Support ignored the email. When he woke up this morning Captain Support had an email from his sister-in-law telling him not to worry about it until he got back to the UK. Captain Support posted a couple of suggestions, just in case it was a simple issue.

    When Captain Support gets back to the UK he may decide to switch from wireless printing to a good old-fashioned cable…

    Cheers

    Tim…

    Update: It looks like the default printer was pointing to the wrong printer. Captain Support triumphs again…

    Frankfurt to San Francisco…

    I posted yesterday about the start of my trip to OpenWorld. I arrived late, so I never got around to posting about the second half of my trip so here goes…

    Frankfurt to San Francisco

    I was in a pretty bad mood when it came to boarding. We were 4+ hours late and the boarding process was a complete disaster. Nobody could hear the announcements, so we were all in queues to ask what the hell was going on. Even so, nobody on the desk thought to just come and shout out what to do. As a result, boarding was like a rugby scrum, with people from all classes, seating zones and people who had not cleared security all thrown together. I don’t remember ever taking so long to board. Sigh.

    I got on the plane and was instantly more miserable. It was one of those planes where you all have to watch the same films on shared screens. Sigh. I watched:

    • The Back-up Plan: Nauseating romantic comedy starring Jennifer Lopez.
    • Iron Man 2: In my opinion it wasn’t as good as Iron Man 1, but it was still pretty cool.
    • Just Wright: Chick-Flick. Would have hated it, except I had used up all my hate on the Jennifer Lopez film.

    The food came along and guess what? I wasn’t on the list of vegetarians. Sigh. Luckily, they had extra so it wasn’t a big deal, just another thing to “brighten” my day.

    I was seated next to the galley so I got to hear a lot of the gossip from the cabin crew. They do talk rather loud. :) One woman seemed to be hitting the assistance button continually, which was getting on their nerves (and mine because I could hear the dinging). It seems the maintenance crew had left one of the internal panels off, which was mentioned by a passenger 2 hours before landing. HELLO! Don’t you think you might want to mention that to someone before you take off ? You are sitting next to a ruddy great hole on an 11+ hour flight! The cabin attendant told the woman to keep her seat belt on. :) I’m sure it was just a cosmetic thing.

    All in all, the flight was ok. The cabin crew were pretty good, which offset most of my gripes.

    In the update to yesterdays post I mentioned Hajo Normann and Andrejus Baranovskis were on an later flight from Frankfurt. I think it was scheduled to leave about 3 hours after mine. Their flight wasn’t delayed, so they actually left over an hour before me. When I landed at SFO I saw Hajo in the customs queue. It turns out they were in a holding pattern for over an hour so we landed at about the same time. :)

    It has been mentioned several times by friends and family that I never seem to have a straight forward trip anywhere. My response is, this is the reality of international travel. There is no enjoyment involved. It’s a matter of endurance and anger management. Can someone hurry up and invent a teleport system already!

    Cheers

    Tim…

    Sequence Smackdown

    A short post where I kick myself for forgetting something basic…

    In recent engagement, I come across a "smelly" construct (database smells) that looks like this:

    Select max(errorid)+1 into newerrorid from errorlog;

    "Why aren’t they using a sequence?", I wondered.

    The reason, of course, is that the PL/SQL developers need to request the creation of each and every object from the production support DBAs, and since such requests require review by the central data architects for correctness before being approved for creation in development, the process can take 4-5 days. As a result, they took this "shortcut". (Reason #392 of why I don’t think production support DBAs should have any place in the development process, but that’s another story).

    The good news is that they recognized this was bad after I pointed it out, and they went ahead and requested the sequence.

    One week later, we get the sequence, correct the code and promote it to the integration environment.

    Where we promptly get uniqueness violations when attempting to insert rows into the table because the sequence number was less than the max(errorid) already in the table.

    "No problem!", I said – I didn’t want to re-create the sequence with a larger "start with" (due to the turnaround time), so I take a lazy shortcut:

    Declare
    I number;
    J number;
    begin
    select erroridseq.nextval into I from dual;
    select max(error
    id) into J from errorlog;
    while I <= J loop
    select error
    id_seq.nextval into I from dual;
    end loop;
    end;
    /

    Yes – I know this is all kinds of horrible, but I was in a hurry and didn’t think.

    And the worst part is that it didn’t even work.

    They still got uniqueness violations and came to me later saying that there were problems with the sequence – that when they selected maxval from the sequence in TOAD they got one value (1000), and when they selected maxval from the sequence via SQL Developer, they got another value (300).

    What did I forget / do wrong?  What should I have done?

    I eventually figured it out and "fixed" it.

    There’s a coda to this – after I smacked the palm of my hand to my forehead and then explained the problem to the PL/SQL developers I thought they understood it. But later in the day they came to me and said they were having the same problem with a different sequence (getting different – and much smaller – values when selecting maxval from different tools)…

    I should have done a better job of explaining it.J

    MOTS Mini – Ann Arbor – Oakie invasion

    image

    Almost all speakers have arrived and it looks it will be a great gathering off techies and in-depth Oracle presentations. I think, guess, allthough it starts tomorrow, it will be cosy, special, and a great entree towards OOW.

    Tomorrow I don’t have the same timeslot as Tanel. Thank god. This time is only a guy called Cary Millsap. Sounds doable.

    ;)

    image

    Now its time for a steak…