Search

OakieTags

Who's online

There are currently 0 users and 29 guests online.

Recent comments

Oakies Blog Aggregator

Sqoop /*+ parallel */

It’s often useful to keep one’s ear to the ground when one is responsible for database performance and/or efficiency — it’s a talent that I wish more DBAs had: a sense of curiosity about how the database is being used to support particular application use cases.

Today’s post deals with something rather basic, but it speaks to this idea of “agile” collaboration between developers and DBAs around something that has a lot of buzz right now: processing “Big Data” with Hadoop / MapReduce. In particular, a process that someone has already deemed to be “too big” to be accomplished within an existing Oracle database.

Opinions aside about the appropriate tool for the job, I happen to overhear some application developers explaining that their process to extract data from the database for Hadoop processing was the “long pole” in the time their entire process was taking. Of course, having only basic database and SQL knowledge, this time delay was attributed to the database being “slow”.

Normally this is enough to set off even the most mild-mannered DBAs (at least the ones who care anyway), but in my case I decided to just take a look and see if I could help them.

Turns out that they were using Sqoop to pull data out of Oracle for several tables, and then using the MapReduce framework to “correlate” them (i.e., join them).

Of course, there were limits to this approach as doing joins in MapReduce required custom code to map and hash column values, or they could always use Hive (which they actually ended up using — it could have worse: they might have used Pig). But it was a distraction from what they were actually trying to do.

So, I volunteered to help them — first by creating a view that joined everything they needed together, complete with an id column that uniquely identified each row. Bingo! No more Hive code necessary.

While this helped them by eliminating the extra code in their project, they were still disappointed with the speed at which they were able to extract the data using Sqoop. They even tried “adding threads” by using Sqoop with parallelism.

From the Sqoop User Guide:

Sqoop imports data in parallel from most database sources. You can specify the number of map tasks (parallel processes) to use to perform the import by using the -m or –num-mappers argument. Each of these arguments takes an integer value which corresponds to the degree of parallelism to employ. By default, four tasks are used. Some databases may see improved performance by increasing this value to 8 or 16. Do not increase the degree of parallelism greater than that available within your MapReduce cluster; tasks will run serially and will likely increase the amount of time required to perform the import. Likewise, do not increase the degree of parallism higher than that which your database can reasonably support. Connecting 100 concurrent clients to your database may increase the load on the database server to a point where performance suffers as a result.

Of course at this point the competent performance DBA can see a problem right away — specifically in the following description of how parallelism is achieved:

When performing parallel imports, Sqoop needs a criterion by which it can split the workload. Sqoop uses a splitting column to split the workload. By default, Sqoop will identify the primary key column (if present) in a table and use it as the splitting column. The low and high values for the splitting column are retrieved from the database, and the map tasks operate on evenly-sized components of the total range. For example, if you had a table with a primary key column of id whose minimum value was 0 and maximum value was 1000, and Sqoop was directed to use 4 tasks, Sqoop would run four processes which each execute SQL statements of the form SELECT * FROM sometable WHERE id >= lo AND id < hi, with (lo, hi) set to (0, 250), (250, 500), (500, 750), and (750, 1001) in the different tasks.

In the absence of an index on the id column, the example above would require 5 full-table scans — my view was actually making things worse as I had assumed they were simply dumping ALL of the results from the view from one connection, not 4 attempts to slice it up.

In the end I simply materialized the results of the view into a table and indexed the id column — resulting in much happier developers and a blameless (for the time being) database.

Of course, the next thing they wanted was the full result set as an array of JSON objects, but that’s another story…

How much does IT cost?

One of my friends used to own a sandwich bar. He knew the exact profit margin on each product. He knew the impact of a price change from one supplier on each of the products he sold, as well as the overall affect on his profits.

So compare that situation with your average IT department, where to be frank, nobody has a bloody clue about costs. Yes, we all know the headline grabbers like licensing cost for Oracle and you can probably find the bit of paper that tells you the yearly hardware maintenance fee, but I’ve not encountered many companies that have a handle on the real cost of projects. If a company can’t say, “Project X cost £Y to complete and costs £Z a year to maintain and this is the breakdown of costs”, with a reasonable level of accuracy then they’ve failed.

You need this sort of data in order to make a valid judgement about new projects. When someone starts extolling the virtues of the latest and greatest database/language/framework, how can you make a judgement on the relative savings you can make if you don’t know your true costs? Free software is not free if you have to pay people to integrate it into your existing systems and hire/train staff with the relevant skills. Conversely, paying ridiculous licensing costs may not be sensible compared to hiring/training skills to allow you to use cheaper alternatives.

I sometimes feel the IT industry is like some cowboy building firm. When someone asks for a price you scratch your chin, suck in some air then pull a random figure out of the ether. Don’t even get me started on the sales people, with their astronomical list prices that nobody ever pays, just so they can make you feel like you’ve got a “good deal”. It’s an industry in dire need of a change.

[/RANT]

Cheers

Tim…




Oracle Database Appliance – Urgent Patch 2.1.0.3.1

Just a quick heads up to the ODA customers — there is a critical patch 2.1.0.3.1 out that is applied on top of ODA patch bundle 2.1.0.3.0. This patch has an important fix for a bug causing ODA servers to shutdown in some situations when an ASM disk is lost. The patch number is 13817532 [...]

Oracle Database Appliance — Storage expansion with NFS (dNFS, HCC)

The biggest objection to Oracle Database Appliance (ODA) we hear from customers is about 4TB usable space limit (tripple mirrored 12TB of raw storage). I think most of the times this is more a perceived barrier rather than objective — more along the lines of being afraid to hit the limit if the system grows [...]

OUGN 2012 Third Day

The last day of the three (and second on the ferry and of the conference proper) had a lot of talks I wanted to see, especially Dan Morgan talking about “Insanely large Databases”. It was a good talk, with an interesting interlude when a very loud announcement let us know we had docked at Kiel. Dan handled it with aplomb. Dan was talking about one specific experience he had suffered recently and he covered quite a few things I did and some I planned to but never got that far – but he had more technical details about the issues he had encountered, so all in all probably of more immediate use to the audience than my talk. It was a very good session. I have to confess, there were times I laughed out loud as memories flooded in, prompted by his wise words – I fear others may have interpreted differently but, honestly, I was laughing tears of shared pain.

I was also looking forward to seeing Uwe Hesse talk about Dataguard. I’d had the pleasure of spending a lot of time and a few beers chatting with Uwe over the last few days. His presentation was very well done (as it should be, he’s a professional trainer! He exceeded my expectations, though). And I loved the last bit, where he demonstrated how, under 11G R2 (R1 as well???), if you have a physical standby, a block corruption can be fixed “on the fly” and invisibly to the end user. I just love that feature and, though I knew about it already, seeing it demonstrated and the errors appearing in the alert log – though the user query runs fine – was sweet.

The rest of the sessions I saw were also good {Maria Colgan on preventing sub-optimal plans which was, mostly, about avoiding implicit data conversions, which I think all developers and designers should have drummed into their heads with rubber hammers; Doug Burns on those OEM performance graphs which continue to get better and better} – but I had to given in and go for a sleep. These anti-seasickness pills seem to work but make me dozy. I’d love it if those anti-travel-sickness pills were really placebos and I had a placebo side effect :-)

The last day was rounded off with a nice meal and one or two (or three, or four) beers in a bar and some excellent times. I of course spent time with the Other Martins (we could not disband the cluster too easily), Doug, Holger, Maria, our Norwegian hosts and many more of the other people there. If only I had managed to fit in the other 10, 15 people I wanted to see but I’m getting old and I was very, very, very tired.

I have to say, it was one of the best conferences I have ever been to. OUGN 2013? Get yourself on that boat.

The fourth use-case for triggers

In our previous post we talked about three of the four use-cases we introduced. Triggers can:

  1. Assign/modify (row) column values.
  2. Execute insert/update/delete statements.
  3. Execute non-transactional stuff.
We've also discussed why indeed using triggers for above use-cases, should be considered harmful. Their automagic side-effects are often not expected by other programmers / users of the database application.

In this post we continue with use-case four. Triggers can:

  1. Execute select statements.

This use-case, where we have the need to execute queries inside triggers, is a very common use-case, and it is one that always (re)surfaces, in almost every project. And there's a reason for this: a very important database-concept requires you to query other data from within triggers.
This concept is called: data integrity constraints. And it's exactly this use-case where, in my opinion, triggers form the means to an end.
Now beware though, and we give you this warning beforehand: using triggers to implement data integrity constraints, is by far not easy. In fact it's extremely complex (as we will detail in future posts). But to me that is no reason to not use triggers. And this use-case will not suffer from the automagic side-effects the other three use-cases had. So I disagree here when Tom Kyte says that even for this use-case 'thou shall not use triggers'.
Recall the 'business rule' that we were trying to implement using triggers: a department cannot employ a manager without a clerk in the same department. That rule is in fact a data integrity constraint. Now, few people know of the documented feature in the SQL standard called: assertions. SQL assertions have been part of the SQL standard since 1992. See this link for the syntax/grammar (it's the eighth 'key sql statement' from the top of the list). Had database vendors supplied us with support for the two-decades old SQL assertion feature, then we could have straightforwardly implemented the data integrity constraint with one CREATE ASSERTION statement:

Translating the above assertion into somewhat awkward english, it goes like this: there cannot exist a department such that this department employs a manager, and such that this department does not employ a clerk.

Once the assertion is created, it's up to the DBMS to enforce the integrity constraint in a correct and (hopefully) efficient manner. Just like you expect the DBMS to correctly and efficiently enforce a uniqueness constraint, or a foreign key. Conceptually these constraints are no different than assertions: it's just that these two represent (constraint) patterns that occur so frequently in every database design, that we've been provided with dedicated SQL language constructs to declare them. But theoretically the UNIQUE / PRIMARY KEY and FOREIGN KEY language constructs are redundant, when assertions are available: both can be rewritten using a CREATE ASSERTION statement.

Would you use assertions, if Oracle provided them? I certainly would, and you would probably too. You are using the other declarative means to implement data integrity (check constraints, primary key, foreign key) right now too, aren't you?

Implementing data integrity constraints in a declarative manner, enables a kind of separation of concerns: you implement your integrity constraints once-and-forall while you create your tables. And then you build business logic on top of the database design without having to worry about validating integrity constraints: all you need to cater for is error-handling, in case your business logic tries to modify/store data in such a way that it violates one or more of the declared integrity constraints. If you want to read up on this way of implementing data-centric applications theHelsinkiDeclaration.blogspot.com is a good starting point.

By the way, there's a reason why DBMS vendors have yet to supply us with support for the CREATE ASSERTION statement:

Developing a correct and efficient implementation for an arbitrary complex assertion (which is what we're asking for), is very hard. By 'correct' we mean, the implementation must properly deal with concurrent transactions manipulating data that is involved in the assertion. By 'efficient' we mean, the implementation must not just run the SQL-predicate that was asserted in the create assertion command, but it must be able to a) detect when a transaction might potentially violate the assertion, and then b) run a minimal check to ensure the continued validity of the assertion.

As far as I know the problem areas described above, haven't been fully researched yet by the scientific community: for one I'm unable to find this research, and had it been researched fully, it would have been fairly easy for a database vendor like Oracle to 'stand on the shoulders' of those researchers and provide us support for create assertion.

So there we are: the fourth use-case for triggers being implementing data integrity constraints.

To be continued...

2001: Oracle Database Appliance by Dell — Déjà vu

Stumbled upon this Dell’s article from 2001 — The Oracle Database Appliance by Dell: Architecture and Features. The idea of appliance-like platform for Oracle database is obviously not new but the latest implementation of Oracle Database Appliance makes the most sense from all previous attempts (comments are open below to disagree if you’d like). What [...]

OUGN Spring meeting 2012

I had the great pleasure to spend the better part of last week at the Norwegian Oracle User Group’s spring conference. Martin Nash and I helped promote the Real Application Cluster platform on the attendees’ laptop in a program called RAC Attack.  RAC Attack has its home on the wikibooks website http://racattack.org where the whole program is documented and available for self-study. The purpose of the hands-on labs which Jeremy Schneider started a few years ago is to allow users to get practical experience installing Oracle Linux, Grid Infrastructure and the RDBMS binaries before creating a two node database. Following the database creation a practical session ensues which explains certain HA concepts with RAC such as session failover. We are planning on greatly enhancing the lab session as we go along. If you have any suggestions about what you would like to see covered by us then please let us know!

Now what we were really surprised about was the number of attendees. I have helped at RAC Attack at last year’s UKOUG annual conference and we had a good turnout. This was nothing in comparison to Oslo! You can get an idea how busy it was from the picture shown below.

The entire workshop went really well and we had lots of interesting discussions with the audience. What was really great was the preparation of the attendees. The organisers of the event send a number of emails out to all indicating which software to download. We saw a mixed field of VMWare Server and Virtual Box users, with Virtual Box clearly in the majority. Martin Nash has prepared supplemental instructions for Virtual Box in a PDF which made the installation process a lot easier, which I have distributed on this weblog on Wednesday. Not having to download the software on the spot was very useful. Towards the end of the training day I wanted to demonstrate the usefulness of RAC and demonstrated my lab environment in full swing. I started a two node RAC cluster, two more VMs for OEM 12.1 plus an application server for Tomcat. Those were obviously not running on my laptop. The tomcat instance created a universal connection pool to both RAC instances. The implementation of UCP using FAN and FCF allowed the pool to tolerate the forced failure of one instance: instead of 10 sessions spread over both instances, it immediately created 5 more on the surviving instance. And by the way-fewer sessions in your connection pool will most often result in better performance! During all the time I had the OEM 12.1performance page open as well. As you can see I really like UCP, and have a few blog posts about it and RAC.

Wednesday evening we went to Holmenkollen, just above Oslo where daredevils jump from a ski-ramp. This was very impressive, and I believe any contestant must possess a certain lack of fear before going down that slope. The best of Wednesday was the dinner in the evening. The OUGN board took us speakers out for a VIP event, which was not only 5 courses, but also the best I have had for a long time. I felt really appreciated and well looked after, which continued for the rest of the event. The Norwegians certainly knew how to look after their guests!

Thursday, Friday and Saturday morning we spent on the boat from Oslo to Kiel and back, which was a wonderful experience. The calm seas certainly contributed to that feat, but it was again the great work of the organisers (especially Oyvind-many thanks to you) who proved great company. RAC Attack continued, and we might actually extend the supported platforms to VMWare Workstation which one of the participants used. I even managed to go out for lunch on Friday in Kiel which turned out to be a very pretty city. I especially had a nice time experimenting with a few additions to the database scheduler, and found an interesting anomaly with a merge statement on Linux x86-64 in 11.2.0.3. Maria Colgan was great fun and very eager to have a look, let’s see if something will come out of that.

The evenings were spent with many geeky conversations, a few drinks and more good discussions. I have to thank Doug once more for his open comments which were very much to the point (mostly ;) The conference speaker lineup was great, including lots of familiar names from the Oracle community. When asked I left a few recommendations who else could be invited to complement the good lineup-I’m curious as to whether that’s going to happen or not.

On Saturday morning we were taken back to the central station in taxis, also organised by OUGN. Have I said already that I never felt that well looked after during a user event? Thanks for the hospitality and being looked after so well, I really enjoyed it and so did everyone I talked to.

Oh, and by the way, Oyvind you need to come to the UK once to see the state of the trains there. I didn’t find a fault with your trains at all spending about 20 minutes at the platform!

Coalesce Subquery Transformation - COALESCE_SQ

Oracle 11.2 introduced a set of new Query Transformations, among others the ability to coalesce subqueries which means that multiple correlated subqueries can be merged into a number of less subqueries.

Timur Akhmadeev already demonstrated the basic principles in a blog entry, but when I was recently involved into supporting a TPC-H benchmark for a particular storage vendor I saw a quite impressive application of this optimization that I would like to share here.

In principle the TPC-H benchmark is simple and attempts to simulate typical DWH query workloads (or what was assumed to be a typical DWH workload when it was designed many years ago) with only a rather limited amount of DML in the mix. The query part consists of 22 queries that have to be run one after the other in order to measure the so called "Power" component of the benchmark. Similar 22 queries will then be run concurrently by at least 5 "streams", where each stream runs them in a different (pseudo-randomized) order to measure the so called "Throughput" part of the benchmark. There is also a DML part but it is almost negligible compared to the query load generated.

One of the most demanding queries out of the 22 is called "Suppliers Who Kept Orders Waiting Query (Q21)" and looks like this in Oracle SQL:

select
*
from (
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
null
from
lineitem l2
where
l1.l_orderkey = l2.l_orderkey
and l2.l_suppkey <> l1.l_suppkey
)
and not exists (
select
null
from
lineitem l3
where
l1.l_orderkey = l3.l_orderkey
and l3.l_suppkey <> l1.l_suppkey
and l3.l_receiptdate > l3.l_commitdate
)
and s_nationkey = n_nationkey
and n_name = 'SAUDI ARABIA'
group by
s_name
order by
numwait desc
, s_name
)
where
rownum <= 100
;

The demanding part of the query is that it accesses the by far largest table LINEITEM three times: Once in the main query and twice as part of the correlated subqueries (EXISTS (...L2...) / NOT EXISTS (...L3...)).

A minimal setup to reproduce the execution plans can be found at the end of this post.

I've deliberately kept the complexity of the setup at the bare minimum - usually the actual table definitions include parallelism, partitioning, compression and other options like freelist and freelist groups for MSSM setups.

Let's have a look at the execution plan produced by pre-11.2 optimizer versions:

Plan hash value: 1997471497

-----------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc|
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 4000 | |
|* 1 | COUNT STOPKEY | | | | |
| 2 | VIEW | | 375K| 14M| |
|* 3 | SORT ORDER BY STOPKEY | | 375K| 79M| 86M|
| 4 | HASH GROUP BY | | 375K| 79M| 86M|
|* 5 | HASH JOIN ANTI | | 375K| 79M| 68M|
|* 6 | HASH JOIN SEMI | | 375K| 64M| 59M|
|* 7 | HASH JOIN | | 375K| 54M| 53M|
|* 8 | HASH JOIN | | 375K| 48M| 3848K|
| 9 | NESTED LOOPS | | 37500 | 3405K| |
|* 10 | TABLE ACCESS FULL| NATION | 1 | 40 | |
|* 11 | TABLE ACCESS FULL| SUPPLIER | 30000 | 1552K| |
|* 12 | TABLE ACCESS FULL | LINEITEM | 7500K| 314M| |
|* 13 | TABLE ACCESS FULL | ORDERS | 37M| 572M| |
| 14 | TABLE ACCESS FULL | LINEITEM | 150M| 3719M| |
|* 15 | TABLE ACCESS FULL | LINEITEM | 7500K| 314M| |
-----------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

1 - SEL$1
2 - SEL$A317D234 / from$_subquery$_001@SEL$1
3 - SEL$A317D234
10 - SEL$A317D234 / NATION@SEL$2
11 - SEL$A317D234 / SUPPLIER@SEL$2
12 - SEL$A317D234 / L1@SEL$2
13 - SEL$A317D234 / ORDERS@SEL$2
14 - SEL$A317D234 / L2@SEL$3
15 - SEL$A317D234 / L3@SEL$4

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

1 - filter(ROWNUM<=100)
3 - filter(ROWNUM<=100)
5 - access("L1"."L_ORDERKEY"="L3"."L_ORDERKEY")
filter("L3"."L_SUPPKEY"<>"L1"."L_SUPPKEY")
6 - access("L1"."L_ORDERKEY"="L2"."L_ORDERKEY")
filter("L2"."L_SUPPKEY"<>"L1"."L_SUPPKEY")
7 - access("O_ORDERKEY"="L1"."L_ORDERKEY")
8 - access("S_SUPPKEY"="L1"."L_SUPPKEY")
10 - filter("N_NAME"='SAUDI ARABIA')
11 - filter("S_NATIONKEY"="N_NATIONKEY")
12 - filter("L1"."L_RECEIPTDATE">"L1"."L_COMMITDATE")
13 - filter("O_ORDERSTATUS"='F')
15 - filter("L3"."L_RECEIPTDATE">"L3"."L_COMMITDATE")

And this is what you get from 11.2:

Plan hash value: 823100515

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc|
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 4000 | |
|* 1 | COUNT STOPKEY | | | | |
| 2 | VIEW | | 7500 | 292K| |
|* 3 | SORT ORDER BY STOPKEY | | 7500 | 197K| |
| 4 | HASH GROUP BY | | 7500 | 197K| |
| 5 | VIEW | VM_NWVW_2 | 7500 | 197K| |
|* 6 | FILTER | | | | |
| 7 | HASH GROUP BY | | 7500 | 1794K| 189M|
|* 8 | HASH JOIN | | 749K| 175M| 76M|
|* 9 | HASH JOIN | | 375K| 71M| 66M|
|* 10 | HASH JOIN | | 375K| 61M| 4728K|
|* 11 | HASH JOIN | | 37500 | 4284K| |
|* 12 | TABLE ACCESS FULL| NATION | 1 | 52 | |
| 13 | TABLE ACCESS FULL| SUPPLIER | 750K| 46M| |
|* 14 | TABLE ACCESS FULL | LINEITEM | 7500K| 400M| |
|* 15 | TABLE ACCESS FULL | ORDERS | 37M| 1001M| |
| 16 | TABLE ACCESS FULL | LINEITEM | 150M| 6294M| |
--------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

1 - SEL$1
2 - SEL$DD8F533F / from$_subquery$_001@SEL$1
3 - SEL$DD8F533F
5 - SEL$11CEEA77 / VM_NWVW_2@SEL$DD8F533F
6 - SEL$11CEEA77
12 - SEL$11CEEA77 / NATION@SEL$2
13 - SEL$11CEEA77 / SUPPLIER@SEL$2
14 - SEL$11CEEA77 / L1@SEL$2
15 - SEL$11CEEA77 / ORDERS@SEL$2
16 - SEL$11CEEA77 / L3@SEL$4

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

1 - filter(ROWNUM<=100)
3 - filter(ROWNUM<=100)
6 - filter(SUM(CASE WHEN "L3"."L_RECEIPTDATE">"L3"."L_COMMITDATE" THEN 1 ELSE 0 END
)=0)
8 - access("L3"."L_ORDERKEY"="L1"."L_ORDERKEY")
filter("L3"."L_SUPPKEY"<>"L1"."L_SUPPKEY")
9 - access("O_ORDERKEY"="L1"."L_ORDERKEY")
10 - access("S_SUPPKEY"="L1"."L_SUPPKEY")
11 - access("S_NATIONKEY"="N_NATIONKEY")
12 - filter("N_NAME"='SAUDI ARABIA')
14 - filter("L1"."L_RECEIPTDATE">"L1"."L_COMMITDATE")
15 - filter("O_ORDERSTATUS"='F')

Let's ignore the difference in cardinality estimates for the moment, in particular when swapping the left and right side of the correlation predicate in the subqueries, since this is obviously caused by the incomplete fake statistics of my test setup. Whereas the first execution plan looks like a rather expected one, including a SEMI and ANTI join for the two unnested, correlated subqueries and accessing the LINEITEM table three times in total, the 11.2 execution plan looks dramatically different. This is particularly noticeable as my minimal setup doesn't include any fancy primary key/unique or foreign key constraints declarations that could support some of the more recent transformations that Oracle offers. The only constraints that are included are NOT NULL column constraints.

There is no SEMI or ANTI join, and the third instance of LINEITEM is gone, too. When I saw this execution plan for the first time, my initial reaction was: "What a cunning optimization!" - shortly followed by "But it seems to be illegal! (too good to be true)". It turns out that the former is true whereas the latter isn't - the results are correct, although the transformation introduces a potential overhead that can be significant.

If you look closely at the two correlated subqueries it becomes obvious that they share the correlation criteria, but one is an EXISTS clause, and the other one a NOT EXISTS that adds an additional filter predicate.

So one possible idea for a rewrite of the query was to transform the EXISTS clause into a join that allowed filtering the "RECEIPTDATE greater than COMMITDATE" condition checked in the NOT EXISTS clause - thereby getting rid of the third instance of LINEITEM and saving a tremendous amount of work.

But then, these are correlated subqueries and when transforming them into a join care has to be taken that the transformation is semantically equivalent and the result still correct - here transforming the [NOT] EXISTS check into a regular join could potentially lead to duplicates that need to be eliminated introducing overhead again.

And here is what happens internally, the final transformed query from the optimizer trace file looks like this:

SELECT "from$_subquery$_001"."S_NAME" "S_NAME",
"from$_subquery$_001"."NUMWAIT" "NUMWAIT"
FROM
(SELECT "VM_NWVW_2"."$vm_col_1" "S_NAME",
COUNT(*) "NUMWAIT"
FROM
(SELECT
/*+ UNNEST */
"SUPPLIER"."S_NAME" "$vm_col_1"
FROM "CBO_TEST"."LINEITEM" "L3",
"CBO_TEST"."SUPPLIER" "SUPPLIER",
"CBO_TEST"."LINEITEM" "L1",
"CBO_TEST"."ORDERS" "ORDERS",
"CBO_TEST"."NATION" "NATION"
WHERE "SUPPLIER"."S_SUPPKEY"="L1"."L_SUPPKEY"
AND "ORDERS"."O_ORDERKEY" ="L1"."L_ORDERKEY"
AND "ORDERS"."O_ORDERSTATUS"='F'
AND "L1"."L_RECEIPTDATE" >"L1"."L_COMMITDATE"
AND 0 <1
AND "SUPPLIER"."S_NATIONKEY"="NATION"."N_NATIONKEY"
AND "NATION"."N_NAME" ='SAUDI ARABIA'
AND "L3"."L_ORDERKEY" ="L1"."L_ORDERKEY"
AND "L3"."L_SUPPKEY" <>"L1"."L_SUPPKEY"
GROUP BY "NATION".ROWID,
"ORDERS".ROWID,
"L1".ROWID,
"SUPPLIER".ROWID,
"SUPPLIER"."S_NAME"
HAVING SUM(
CASE
WHEN "L3"."L_RECEIPTDATE">"L3"."L_COMMITDATE"
THEN 1
ELSE 0
END )=0
) "VM_NWVW_2"
GROUP BY "VM_NWVW_2"."$vm_col_1"
ORDER BY COUNT(*) DESC,
"VM_NWVW_2"."$vm_col_1"
) "from$_subquery$_001"
WHERE ROWNUM<=100;

So Oracle eliminates the L2 instance of LINEITEM (and the corresponding subquery) by coalescing the EXISTS and the NOT EXISTS subquery which can be seen from the OUTLINE where two COALESCE_SQ hints show up. Finally it transforms the remaining subquery into a regular join that requires an additional aggregation step in order to eliminate potential duplicates (the inner GROUP BY ...ROWID). The HAVING clause applies the additional filter from the NOT EXISTS subquery.

If you run the query with Row Source Statistics enabled using the minimum set of data provided you'll notice that the second join to LINEITEM in fact generates duplicates. So this execution plan is a cunning optimization that allows getting rid of the third instance of LINEITEM, but depending on the number of duplicates generated a significant amount of excess work might be introduced instead.

This might explain why this transformation seems at present only to be applied when dealing with queries that include aggregations anyway. If you change the COUNT(*) ... GROUP BY into a regular query without aggregation, the transformation will not be applied and the traditional SEMI / ANTI join execution plan will show up.

Of course you could also argue that possibly the optimization was particularly aimed at benchmarks like this, but then there are certainly similar real-life queries out there that can benefit from such potential workload reductions via query transformations.

The setup script:

drop table lineitem purge;
drop table orders purge;
drop table supplier purge;
drop table nation purge;

CREATE TABLE lineitem (
l_shipdate DATE NULL,
l_orderkey NUMBER NOT NULL,
l_discount NUMBER NOT NULL,
l_extendedprice NUMBER NOT NULL,
l_suppkey NUMBER NOT NULL,
l_quantity NUMBER NOT NULL,
l_returnflag CHAR(1) NULL,
l_partkey NUMBER NOT NULL,
l_linestatus CHAR(1) NULL,
l_tax NUMBER NOT NULL,
l_commitdate DATE NULL,
l_receiptdate DATE NULL,
l_shipmode CHAR(10) NULL,
l_linenumber NUMBER NOT NULL,
l_shipinstruct CHAR(25) NULL,
l_comment VARCHAR2(44) NULL
)
;

CREATE TABLE orders (
o_orderdate DATE NULL,
o_orderkey NUMBER NOT NULL,
o_custkey NUMBER NOT NULL,
o_orderpriority CHAR(15) NULL,
o_shippriority NUMBER NULL,
o_clerk CHAR(15) NULL,
o_orderstatus CHAR(1) NULL,
o_totalprice NUMBER NULL,
o_comment VARCHAR2(79) NULL
)
;

CREATE TABLE supplier (
s_suppkey NUMBER NOT NULL,
s_nationkey NUMBER NULL,
s_comment VARCHAR2(101) NULL,
s_name CHAR(25) NULL,
s_address VARCHAR2(40) NULL,
s_phone CHAR(15) NULL,
s_acctbal NUMBER NULL
)
;

CREATE TABLE nation (
n_nationkey NUMBER NOT NULL,
n_name CHAR(25) NULL,
n_regionkey NUMBER NULL,
n_comment VARCHAR2(152) NULL
)
;

/*
CREATE INDEX i_l_orderkey
ON lineitem (
l_orderkey
)
;

CREATE UNIQUE INDEX i_o_orderkey
ON orders (
o_orderkey
)
;
*/

exec sys.dbms_stats.set_table_stats(null, 'lineitem', numblks=> 6450000, numrows=> 150000000)

exec sys.dbms_stats.set_table_stats(null, 'orders', numblks=> 3750000, numrows=> 75000000)

exec sys.dbms_stats.set_table_stats(null, 'nation', numblks=> 375, numrows=> 25)

exec sys.dbms_stats.set_table_stats(null, 'supplier', numblks=> 37500, numrows=> 750000)

--exec sys.dbms_stats.set_index_stats(null, 'i_l_orderkey', numlblks=> 645000, numrows=> 150000000, numdist=> 75000000, indlevel => 4, clstfct => 150000000)

--exec sys.dbms_stats.set_index_stats(null, 'i_o_orderkey', numlblks=> 375000, numrows=> 75000000, numdist=> 75000000, indlevel => 3, clstfct => 75000000)

exec sys.dbms_stats.set_column_stats(null, 'lineitem', 'l_orderkey', distcnt => 75000000)

exec sys.dbms_stats.set_column_stats(null, 'lineitem', 'l_suppkey', distcnt => 750000)

exec sys.dbms_stats.set_column_stats(null, 'orders', 'o_orderkey', distcnt => 75000000)

exec sys.dbms_stats.set_column_stats(null, 'orders', 'o_orderstatus', distcnt => 2)

exec sys.dbms_stats.set_column_stats(null, 'supplier', 's_suppkey', distcnt => 750000)

exec sys.dbms_stats.set_column_stats(null, 'supplier', 's_nationkey', distcnt => 25)

exec sys.dbms_stats.set_column_stats(null, 'supplier', 's_name', distcnt => 750000)

exec sys.dbms_stats.set_column_stats(null, 'nation', 'n_nationkey', distcnt => 25)

exec sys.dbms_stats.set_column_stats(null, 'nation', 'n_name', distcnt => 20)

insert into nation (n_nationkey, n_name) values (1, 'SAUDI ARABIA');

insert into supplier (s_suppkey, s_name, s_nationkey) values (1, 'SUPPLIER1', 1);

insert into supplier (s_suppkey, s_name, s_nationkey) values (2, 'SUPPLIER2', 1);

insert into orders (o_orderkey, o_custkey, o_orderstatus) values (1, 1, 'F');

insert into orders (o_orderkey, o_custkey, o_orderstatus) values (2, 1, 'A');

insert into lineitem (l_orderkey, l_discount, l_extendedprice, l_suppkey, l_quantity, l_linenumber, l_partkey, l_tax, l_receiptdate, l_commitdate) values (1, 0, 0, 1, 0, 1, 1, 0, sysdate + 1, sysdate);

insert into lineitem (l_orderkey, l_discount, l_extendedprice, l_suppkey, l_quantity, l_linenumber, l_partkey, l_tax, l_receiptdate, l_commitdate) values (1, 0, 0, 1, 0, 2, 1, 0, sysdate + 1, sysdate);

insert into lineitem (l_orderkey, l_discount, l_extendedprice, l_suppkey, l_quantity, l_linenumber, l_partkey, l_tax, l_receiptdate, l_commitdate) values (1, 0, 0, 1, 0, 3, 1, 0, sysdate, sysdate);

insert into lineitem (l_orderkey, l_discount, l_extendedprice, l_suppkey, l_quantity, l_linenumber, l_partkey, l_tax, l_receiptdate, l_commitdate) values (1, 0, 0, 2, 0, 1, 1, 0, sysdate, sysdate);

insert into lineitem (l_orderkey, l_discount, l_extendedprice, l_suppkey, l_quantity, l_linenumber, l_partkey, l_tax, l_receiptdate, l_commitdate) values (1, 0, 0, 2, 0, 2, 1, 0, sysdate, sysdate);

Another batch of Linux articles (RHCSA)…

I’ve just put another batch of Linux articles live.

As before, they are focussing on the RHCSA exam objectives, so a lot of it is pretty basic information.

The Firewall and SELinux objectives are the only ones left now. These two objectives were the main reasons I decided to start this process. I left them until last because I figured if I started with them, I might never get round to doing the other articles. :)

As far as the Linux firewall goes, if it can’t be done with the point and click GUI (or TUI), I don’t do it, so taking a look at iptables from the command line has been on my list for a very long time. The RHCSA objective suggests using the GUI/TUI interface should be sufficient, since it says, “using system-config-firewall or iptables”. In contrast, the RHCE objectives explicitly mention iptables, possibly suggesting tasks that may not be possible from the GUI? The question is, how much do I trust my own judgement on this matter? I would prefer go in to the RHCSA exam with too much information, rather than not enough, so I guess I’ll take a look at iptables from the command line before attempting the RHCSA exam.

I know even less about SELinux than I do about the firewall. For Oracle installations I typically disable it. :) So I guess this objective is going to be a magical mystery tour. :)

If anyone has sat the RHCSA exam for RHEL6, I would be interested to hear your thoughts on the Firewall and SELinux objectives. I think I’ve got a pretty good handle on the other objectives, but I’m kinda shooting in the dark with these two. It would be a shame to waste £400 on a failed exam. :)

Cheers

Tim…