Top 60 Oracle Blogs

Recent comments

August 2010


Over the last few days life has been giving me a little reminder about the usefulness of Homeostasis

On Wednesday afternoon I felt a little under the weather and by the evening I was throwing up in a big way. Eventually the effort of it all drained me to the point where I fell asleep. By the morning I was feeling much better, or so I thought. For the most part the vomiting had finished, but it seemed like my body had totally forgotten it had a homeostatic mechanism for regulating its temperature. I had the usual stuff you get when you are ill, like cold sweats one minute, then feeling really hot the next. The difference this time was I seemed to react really quickly to the temperature of food and drinks I consumed. If I ate some hot (temperature, not spicy) food I would quickly start sweating like a racehorse. Have a cold drink and it would be uncontrollable shivering. Kinda freaky.

For the last few days I’ve been cycling between bed, hot baths and the computer. I think I managed my body temperature pretty well if I do say so myself.

Today is the first day the whole temperature regulation thing seems back to normal, but my throat feels a little dodgy. The joys of secondary infections. :)



PS. This blog post is sponsored by Lemsip. The universal cure-all. :)

In-Memory Parallel Queryセッション数

In-Memory PQ続き、


セッション数=2                  1セッションと比べて約72%増えた。
セッション数=4                  2セッションと比べ21%程度しか増えない。
セッション数=6                  4セッションとほぼ同じ結果。
セッション数=7                  4セッションとほぼ同じ結果で急激なダウンが発生。
セッション数=8                   同時2セッションより下がった。


セッション数=2             まだ余裕が見られる。
セッション数=4             ほぼ100%。
セッション数=7             すべて100%。
セッション数=8              自動適にキューイング機能が働き、定期的にCPUが空く状況となった。


SQL> select status,queuing_time,sql_id
2 ,to_char(sql_exec_start,'HH24:MI:SS')
3 ,sql_exec_id
4 from v$sql_monitor
5 where sql_text is not null
6* order by sql_exec_start

------------------- ------------ ------------- -------- -----------
DONE (ALL ROWS) 2831511 gd9scwyt8v953 03:55:11 16777247
DONE (ALL ROWS) 2684562 814qvp0rkqug4 03:55:11 16777324
DONE (ALL ROWS) 1739474 f6jbr8wz0fv1t 03:55:12 16777216
DONE (ALL ROWS) 0 f7755257c288j 03:55:19 16777216
DONE (ALL ROWS) 0 5huqb0rhzf29q 03:55:19 16777681
DONE (ALL ROWS) 0 ayj1k1vnmp0f7 03:55:19 16777236
DONE (ALL ROWS) 0 du96rsyw3f7sz 03:55:21 16777229
DONE (ALL ROWS) 0 820qdj5r8cmrr 03:55:21 16777218
DONE (ALL ROWS) 0 6xrfbu2ts2pgk 03:55:22 16777220
DONE (ALL ROWS) 0 47s9vpct2js18 03:55:22 16777236
DONE (ALL ROWS) 46 8s85psf3jgy52 03:55:52 16777235
DONE (ALL ROWS) 3999283 64qpjd5t8rb40 03:55:52 16777221
DONE (ALL ROWS) 56 29rqwcj4cs31u 03:55:52 16777332
DONE (ALL ROWS) 3035084 gcuxbm6up4122 03:55:53 16777220
DONE (ALL ROWS) 669911 8dqsccvprfm78 03:55:56 16777217
DONE (ALL ROWS) 3506747 8s2krdc70vx18 03:55:57 16777216
DONE (ALL ROWS) 3517949 bf555m0vgxf0v 03:55:57 16777216
DONE (ALL ROWS) 42 cw42jzqxsm470 03:55:57 16777216
DONE (ALL ROWS) 3431230 av1rgj5v045d0 03:55:57 16777216
DONE (ALL ROWS) 3999450 7qqsm4251nu37 03:55:57 16777216
DONE (ALL ROWS) 3246401 2v03a3a51tdtv 03:55:58 16777216
DONE (ALL ROWS) 2701734 fkt8zyhzs9q59 03:55:58 16777216
DONE (ALL ROWS) 3248370 8w6atv1b7vyk6 03:55:58 16777216
DONE (ALL ROWS) 1105996 723j94mkj5vtk 03:56:00 16777216
DONE (ALL ROWS) 3999850 2rfpg0xh9m2j8 03:56:01 16777217
DONE (ALL ROWS) 2842527 66bx153zgukna 03:56:02 16777216
EXECUTING 2934971 4h393k5cvgyqz 03:56:02 16777217
DONE (ALL ROWS) 2934967 3msyu9tjy400d 03:56:02 16777216
EXECUTING 1816098 1xczk8apb4vpj 03:56:03 16777221
EXECUTING 1678900 8ycsk1j6bjq5p 03:56:04 16777223
EXECUTING 1657826 gbxbagzcd9xw9 03:56:04 16777221
QUEUED 3416073 chzq5gvh210th 03:56:06 16777230
QUEUED 2349656 7ukf2wrhkc5v1 03:56:07 16777216
QUEUED 1726668 6c2ma1t5gr2jy 03:56:07 16777218
QUEUED 1300262 ggp2tf8vm4tn2 03:56:08 16777230


QUEUING_TIME(micro sec)がキューイングされている時間だ。

8セッションからのキューイング管理は実装CPUのスレッド数から割り出されているのだろうけど、今回使用しているCore i7 860だと、管理が始まる8セッションは少し遅すぎるのではないかと感じる。


SQL> show parameter cpu

------------------------------------ ----------- -------
cpu_count integer 8
parallel_threads_per_cpu integer 2
resource_manager_cpu_allocation integer 8

In-Memory Parallel Query


hammeroraのスクリプトを編集して、In-Memory Parallel Queryのテストを行う:
set sql(1) "alter session set parallel_degree_policy=auto"



In-Memory Parallel Queryでは、Parallel度を明示的に指定できない。

SQL> alter system flush shared_pool;
set lines 250
set pages 200
col module format a20
col STIME format a8
col ETIME format a8
col PAST format a13
col PX format '99'
col CLWAIT format '99'
col APWAIT format '99'
col SORTS format '999'
col EXECS format '999'
select /*+ no_monitoring */
to_char(min(c.SQL_EXEC_START),'HH24:MI:SS') STIME
,to_char(max(c.SAMPLE_TIME),'HH24:MI:SS') ETIME
,substr(to_char(max(c.SAMPLE_TIME)-min(c.SQL_EXEC_START),'HH24:MI:SS'),11) PAST
-- ,round(max(a.CLUSTER_WAIT_TIME)) CLWAIT
,round(max(a.CPU_TIME)) CPU_TIME
from v$sql a, v$active_session_history C
where a.sql_id = c.sql_id
group by a.PLAN_HASH_VALUE, a.sql_id, a.MODULE
order by ELAPSED
-------- -------- ------------- ----- ---------- --- ----- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ------------- --------------------
00:47:24 04:50:15 04:02:51.629 1 1 4 4 2 0 0 141260 140400 2974 291 5huqb0rhzf29q wish85t.exe
04:54:49 04:54:50 00:00:01.404 1 75 4 2 744 0 0 411164 436804 28853 2903 arz3wfaba8da7 wish85t.exe
04:54:46 04:54:47 00:00:01.394 1 1 4 2 1 0 0 1038073 1029606 65835 6747 bk9sn1kbzs7yn wish85t.exe
04:54:43 04:54:44 00:00:01.384 1 1 4 2 2 0 0 1044030 1045207 74255 7614 akt0yxaa05ggd wish85t.exe
04:54:36 04:54:37 00:00:01.364 1 1152 4 2 11514 0 0 1209427 1201208 56191 9318 6c31ta3vgcwzh wish85t.exe
04:54:39 04:54:40 00:00:01.374 1 1 4 2 2 0 4 1473278 1513208 88811 9053 cruv5v9furgq5 wish85t.exe
04:54:38 04:54:39 00:00:01.364 1 1 4 4 4 0 0 1770111 1887612 41766 4239 5bgxrmr55wvb6 wish85t.exe
04:54:37 04:54:38 00:00:01.364 1 1 4 2 5 0 0 2055977 2090414 84879 8656 ampnk72s02dtp wish85t.exe
04:54:42 04:54:43 00:00:01.384 1 3798 4 2 37980 0 7 2152165 2293216 84405 12609 gwpu7myf17bz7 wish85t.exe
00:46:21 04:54:45 04:08:24.384 1 4 4 2 36 0 0 3714914 3759624 22537 7343 3ndjyxw801gnu wish85t.exe
04:54:40 04:54:42 00:00:02.374 1 18 4 4 175 0 0 4058806 4009226 100793 11871 3w4gvgbnb2tc3 wish85t.exe
04:54:33 04:54:36 00:00:03.364 1 1 3 2 4 0 0 4708541 4711230 62300 6534 3f14rpqjvdpz4 wish85t.exe
04:54:46 04:54:49 00:00:03.394 1 1 4 2 9 0 0 4984365 5070032 146284 19339 2xjra7wm4cryf wish85t.exe
04:54:49 04:54:52 00:00:03.405 1 78 4 2 780 0 0 5720865 5740836 143681 14808 bwynwaxkzqkxj wish85t.exe


bk9sn1kbzs7yn select sum(l_extendedprice) / 7.0 as avg_yearly from lineitem, part where p_partkey = l_partkey and p_brand = 'Brand#14' and p_container = 'JUMBO CAN' and l_quantity < ( select 0.2 * avg(l_quantity) from lineitem where l_partkey = p_partkey)
arz3wfaba8da7 select s_name, s_address from supplier, nation where s_suppkey in ( select ps_suppkey from partsupp where ps_partkey in ( select p_partkey from part where p_name like 'plum%') and ps_availqty > ( select 0.5 * sum(l_quantity) from lineitem where l_partkey = ps_partkey and l_suppkey = ps_suppkey and l_shipdate >= date '1997-01-01' and l_shipdate < date '1997-01-01' + interval '1' year)) and s_nationkey = n_nationkey and n_name = 'PERU' order by s_name
2xjra7wm4cryf select c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice, sum(l_quantity) from customer, orders, lineitem where o_orderkey in ( select l_orderkey from lineitem group by l_orderkey having sum(l_quantity) > 315) and c_custkey = o_custkey and o_orderkey = l_orderkey group by c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice order by o_totalprice desc, o_orderdate
5bgxrmr55wvb6 select supp_nation, cust_nation, l_year, sum(volume) as revenue from ( select n1.n_name as supp_nation, n2.n_name as cust_nation, extract(year from l_shipdate) as l_year, l_extendedprice * (1 - l_discount) as volume from supplier, lineitem, orders, customer, nation n1, nation n2 where s_suppkey = l_suppkey and o_orderkey = l_orderkey and c_custkey = o_custkey and s_nationkey = n1.n_nationkey and c_nationkey = n2.n_nationkey and ( (n1.n_name = 'SAUDI ARABIA' and n2.n_name = 'MOZAMBIQUE') or (n1.n_name = 'MOZAMBIQUE' and n2.n_name = 'SAUDI ARABIA')) and l_shipdate between date '1995-01-01' and date '1996-12-31') shipping group by supp_nation, cust_nation, l_year order by supp_nation, cust_nation, l_year
6c31ta3vgcwzh select l_orderkey, sum(l_extendedprice * (1 - l_discount)) as revenue, o_orderdate, o_shippriority from customer, orders, lineitem where c_mktsegment = 'MACHINERY' and c_custkey = o_custkey and l_orderkey = o_orderkey and o_orderdate < date '1995-03-14' and l_shipdate > date '1995-03-14' group by l_orderkey, o_orderdate, o_shippriority order by revenue desc, o_orderdate
akt0yxaa05ggd select l_shipmode, sum(case when o_orderpriority = '1-URGENT' or o_orderpriority = '2-HIGH' then 1 else 0 end) as high_line_count, sum(case when o_orderpriority <> '1-URGENT' and o_orderpriority <> '2-HIGH' then 1 else 0 end) as low_line_count from orders, lineitem where o_orderkey = l_orderkey and l_shipmode in ('REG AIR', 'AIR') and l_commitdate < l_receiptdate and l_shipdate < l_commitdate and l_receiptdate >= date '1997-01-01' and l_receiptdate < date '1997-01-01' + interval '1' year group by l_shipmode order by l_shipmode
3ndjyxw801gnu select c_count, count(*) as custdist from ( select c_custkey, count(o_orderkey) as c_count from customer left outer join orders on c_custkey = o_custkey and o_comment not like '%unusual%requests%' group by c_custkey) c_orders group by c_count order by custdist desc, c_count desc
3f14rpqjvdpz4 select l_returnflag, l_linestatus, sum(l_quantity) as sum_qty, sum(l_extendedprice) as sum_base_price, sum(l_extendedprice * (1 - l_discount)) as sum_disc_price, sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge, avg(l_quantity) as avg_qty, avg(l_extendedprice) as avg_price, avg(l_discount) as avg_disc, count(*) as count_order from lineitem where l_shipdate <= date '1998-12-01' - interval '68' day (3) group by l_returnflag, l_linestatus order by l_returnflag, l_linestatus
5huqb0rhzf29q select s_suppkey, s_name, s_address, s_phone, total_revenue from supplier, revenue0 where s_suppkey = supplier_no and total_revenue = ( select max(total_revenue) from revenue0) order by _suppkey
ampnk72s02dtp select n_name, sum(l_extendedprice * (1 - l_discount)) as revenue from customer, orders, lineitem, supplier, nation, region where c_custkey = o_custkey and l_orderkey = o_orderkey and l_suppkey = s_suppkey and c_nationkey = s_nationkey and s_nationkey = n_nationkey and n_regionkey = r_regionkey and r_name = 'ASIA' and o_orderdate >= date '1996-01-01' and o_orderdate < date '1996-01-01' + interval '1' year group by n_name order by revenue desc
bwynwaxkzqkxj select s_name, count(*) as numwait from supplier, lineitem l1, orders, nation where s_suppkey = l1.l_suppkey and o_orderkey = l1.l_orderkey and o_orderstatus = 'F' and l1.l_receiptdate > l1.l_commitdate and exists ( select * from lineitem l2 where l2.l_orderkey = l1.l_orderkey and l2.l_suppkey <> l1.l_suppkey) and not exists ( select * from lineitem l3 where l3.l_orderkey = l1.l_orderkey and l3.l_suppkey <> l1.l_suppkey and l3.l_receiptdate > l3.l_commitdate) and s_nationkey = n_nationkey and n_name = 'ARGENTINA' group by s_name order by numwait desc, s_name
3w4gvgbnb2tc3 select nation, o_year, sum(amount) as sum_profit from ( select n_name as nation, extract(year from o_orderdate) as o_year, l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity as amount from part, supplier, lineitem, partsupp, orders, nation where s_suppkey = l_suppkey and ps_suppkey = l_suppkey and ps_partkey = l_partkey and p_partkey = l_partkey and o_orderkey = l_orderkey and s_nationkey = n_nationkey and p_name like '%olive%') profit group by nation, o_year order by nation, o_year desc
gwpu7myf17bz7 select c_custkey, c_name, sum(l_extendedprice * (1 - l_discount)) as revenue, c_acctbal, n_name, c_address, c_phone, c_comment from customer, orders, lineitem, nation where c_custkey = o_custkey and l_orderkey = o_orderkey and o_orderdate >= date '1994-01-01' and o_orderdate < date '1994-01-01' + interval '3' month and l_returnflag = 'R' and c_nationkey = n_nationkey group by c_custkey, c_name, c_acctbal, c_phone, n_name, c_address, c_comment order by revenue desc
cruv5v9furgq5 select o_year, sum(case when nation = 'UNITED KINGDOM' then volume else 0 end) / sum(volume) as mkt_share from ( select extract(year from o_orderdate) as o_year, l_extendedprice * (1 - l_discount) as
volume, n2.n_name as nation from part, supplier, lineitem, orders, customer, nation n1, nation n2, region where p_partkey = l_partkey and s_suppkey = l_suppkey and l_orderkey = o_orderkey and o_custkey = c_custkey and c_nationkey = n1.n_nationkey and n1.n_regionkey = r_regionkey and r_name = 'EUROPE' and s_nationkey = n2.n_nationkey and o_orderdate between date '1995-01-01' and date '1996-12-31' and p_type = 'SMALL ANODIZED TIN') all_nations group by o_year order by o_year

前回までのTPC-Hの結果と比較すると、やはりIn-Memory PQは一番速い。

hammeroraを使う場合は、OPTIMIZER_DYNAMIC_SAMPLING=0の環境にしないと、SQL統計情報の有無に関わらず発行されるRecursive Callがカウントされてしまうので注意です。

Fair Comparison

From time to time someone will post a question about query performance on the OTN database forum asking why one form of a query returns data almost immediately while another form of the query takes minutes to return the data.

Obviously there are all sorts of reasons – the optimizer is not perfect, and different transformations may take place that really do result in a huge differences in work done by two queries which return the same result set – but a very simple reason that can easily be overlooked is the front-end tool being used to run the query.

Because of its popularity, Toad is a common “culprit”. You type in your query, you hit the execute key or icon, the query runs, and the bottom of the screen shows a nice spreadsheet representation of your result set – but not necessarily immediately.

It’s important to remember that when you do this, Toad (or whatever your favourite GUI is) may only be showing you the first few rows of the result set. If you see a massive difference in apparent performance between two versions of a query, it may be that Oracle has used two different strategies to get the entire data set. For example – imagine your query is supposed to return 5,000 rows -

Version A unnests an aggregate subquery, does complex view merging, and aggregates late. No data appears for three minutes and then the first 20 rows appear in the spreadsheet.

Version B runs the same aggregate subquery as a filter subquery, the first 20 rows appears instantly.

Oracle Exadata Database Machine I/O Bottleneck Revealed At… 157 MB/s! But At Least It Scales Linearly Within Datasheet-Specified Bounds!

It has been quite a while since my last Exadata-related post. Since I spend all my time, every working day, on Exadata performance work this blogging dry-spell should seem quite strange to readers of this blog. However, for a while it seemed to me as though I was saturating the websphere on the topic and [...]

Guess Who Got Inducted into the OakTable Network

I am very proud to be one of the newest OakTable Network members!

(That My Happy Face)

PL/SQL Vs. Oracle JVM: Speed Comparison for Mathematical Operations…

I remember hearing someone talking about this years ago and I never actually took the time to check it out. It looks like the real answer is “it depends”. For the basic loop processing and maths the JVM does look a little faster. It was just a curiosity thing, but I thought I might as well write it up as an article on the website.



The illustrated guide to a Ph.D.

A nice graphical story of the PhD and perspective from

here is the article (click on above link to get original)

By the time you finish elementary school, you know a little:Imagine a circle that contains all of human knowledge:

By the time you finish high school, you know a bit more:

With a bachelor's degree, you gain a specialty:

A master's degree deepens that specialty:

Reading research papers takes you to the edge of human knowledge:

Once you're at the boundary, you focus:

You push at the boundary for a few years:

Until one day, the boundary gives way:

And, that dent you've made is called a Ph.D.:

Of course, the world looks different to you now:

So, don't forget the bigger picture:

Keep pushing.

Quiz Night.

I was on a customer site recently where I needed to add a NOT NULL constraint to a table of 200 million rows – without taking any downtime. It’s not difficult (provided you are happy with a check constraint rather than a column definition.)

alter table t1
	add constraint t1_ck_colX_nn check (colX is not null)
	enable novalidate

The first step creates the constraint and enables it – but doesn’t validate it. This means that future data (and changes) will obey the constraint, but there may be illegal data already in the table that will not be checked. You will have an interruption to service doing this, as your session will wait to lock the table in share mode (mode 4) to add the constraint – so will be blocked by current update transactions, and will block new update transactions. In a typical OLTP system this should result in just a brief pause.

The second step validates the constraint, which needs a slightly more complex piece of code – perhaps something like the following:

	resource_busy exception;
	pragma EXCEPTION_INIT(resource_busy, -54);
			execute immediate
			'alter table t1 modify constraint t1_ck_colX_nn validate';
			when resource_busy then
	end loop;

This code tries to validate the constraint and goes into a loop, sleeping for 1/100 second, if it hits the “resource busy” error. Unlike the call to add the constraint, the call to validate it doesn’t wait for a lock – it requests an immediate lock, so it’s going to fail immediately if there are any active transactions on the table. Once it has the lock it drops it, so you’re not going to shut your users out while the validation takes place.

Linux Thinks It’s a CPU, But What Is It Really – Part III. How Do Intel Xeon 7500 (Nehalem EX) Processors Map To Linux OS Processors?

Last year I posted a blog entry entitled Linux Thinks It’s a CPU, But What Is It Really – Part I. Mapping Xeon 5500 (Nehalem) Processor Threads to Linux OS CPUs where I discussed the Intel CPU Topology Tool. The topology tool is most helpful when trying to quickly map Linux OS processors to physical processor [...]