Search

OakieTags

Who's online

There are currently 0 users and 37 guests online.

Recent comments

Affiliations

October 2010

Conditional SQL – 3

I’ve spent the last week in Spain – sightseeing, rather than working – with a minimum amount of access to the Internet.

Inevitably I now have to work hard to catch up with my email. As a moment of light relief in an otherwise irritating chore I thought I’d respond to an emailed request for help. (Regular readers of the blog will know that I don’t usually respond to private email requests for solutions, but sometimes someone gets lucky.)

The question was basically this:  why do I get different execution plans for the following two statements:


select
        DISTINCT CLIENT_DISTRIBUTION_ID, BANK_SOURCE_SYSTEM_ID, CAS_CLIENT_ID,
        CAS_DEBT_ACCOUNT_ID, DESTINATION_ACCOUNT_NUMBER, DESTINATION_SORT_CODE, AMOUNT
FROM
        TRANSFERS_TO_BE_ISSUED
WHERE   CHARGING_SYSTEM_ID = 1
and     ( 1306290 IS NULL OR CLIENT_DISTRIBUTION_BATCH_ID = 1306290)

select
        DISTINCT CLIENT_DISTRIBUTION_ID, BANK_SOURCE_SYSTEM_ID, CAS_CLIENT_ID,
        CAS_DEBT_ACCOUNT_ID, DESTINATION_ACCOUNT_NUMBER, DESTINATION_SORT_CODE,
        AMOUNT
FROM
        TRANSFERS_TO_BE_ISSUED
WHERE   CHARGING_SYSTEM_ID =:Z2
and     ( :Z1 IS NULL OR CLIENT_DISTRIBUTION_BATCH_ID = :Z1 )

You’ll notice that the only difference between the two queries is the change from literals to bind variables. In fact the question goes on to point out that if the values used for the bind variables match the literal version, or even if the literal version is executed with parameter cursor_sharing set to force, the plan you get doesn’t match the plan from the literal code.

The explanation for this behaviour is related to an old posting about conditional SQL. I haven’t given you an explicit statement of the intent of this piece of SQL – but it’s basically an example of the form: “if I don’t supply a value I want everything, otherwise I want the rows associated with the value”.

In the example using literals the optimizer can see that the condition “1306290 IS NULL” is always false and (because of the or) reduce the where clause to: “CHARGING_SYSTEM_ID = 1 and CLIENT_DISTRIBUTION_BATCH_ID = 1306290″.

If you rewrite the query (whether explicitly or by fiddling with cursor_sharing) to use bind variables, it doesn’t matter to the optimizer that on its first call it can see an incoming value that is not null it’s possible that the next time you call the query you will supply a null for the bind variable Z1; so the optimizer has to create a plan that can get the right answer whether or not the bind variable is null – which can easily result in the need for a different execution path.

If you want to write this code to be as efficient as it can be in all cases, you probably need to write something like:

select
        DISTINCT CLIENT_DISTRIBUTION_ID, BANK_SOURCE_SYSTEM_ID, CAS_CLIENT_ID,
        CAS_DEBT_ACCOUNT_ID, DESTINATION_ACCOUNT_NUMBER, DESTINATION_SORT_CODE, AMOUNT
FROM
        TRANSFERS_TO_BE_ISSUED
WHERE   CHARGING_SYSTEM_ID = :Z2
and     :Z1 IS NULL
UNION ALL
select
        DISTINCT CLIENT_DISTRIBUTION_ID, BANK_SOURCE_SYSTEM_ID, CAS_CLIENT_ID,
        CAS_DEBT_ACCOUNT_ID, DESTINATION_ACCOUNT_NUMBER, DESTINATION_SORT_CODE, AMOUNT
FROM
        TRANSFERS_TO_BE_ISSUED
WHERE   CHARGING_SYSTEM_ID = :Z2
and     :Z1 IS NOT NULL
and     CLIENT_DISTRIBUTION_BATCH_ID = :Z1

Basically you’ve got to give the optimizer two queries – of which only one will execute at run time. (And if you want your code to play towards the strengths of the database you’ll put the choice into the front-end code as an “if .. then .. else” test, rather than hiding the choice in a UNION ALL.

Now back to the email.

11.2 New Features - Subtle Restrictions

This is just a short note that some of the new features added in 11.2 have some subtle restrictions that are not documented properly.

1. Hybrid Columnar Compression and Function-Based Indexes

The Hybrid Columnar Compression (HCC) introduced in 11.2 that is only enabled in tablespaces residing on Exadata Storage in 11.2.0.1 has the subtle restriction that the mere presence of a function-based index either explicitly created or indirectly via an index on a virtual column disables HCC completely on that segment with direct path / parallel DML inserts. It silently falls back to no compression at all, rather than using for example basic or advanced oltp compression instead.

This can be easily reproduced using the following simple test case:

set echo on timing on

spool hcc_function_based_indexes_testcase.log

drop table t_hcc_fbi purge;

-- Create table with HCC enabled
create table t_hcc_fbi compress for query low as select * from dba_objects where 1 = 2;

truncate table t_hcc_fbi;

-- Direct path inserts support HCC by default
insert /*+ append */ into t_hcc_fbi select * from dba_objects;

commit;

-- This is supposed to return DBMS_COMPRESSION.COMP_FOR_QUERY_LOW = 8
select distinct dbms_compression.get_compression_type(USER, 'T_HCC_FBI', rowid) from t_hcc_fbi;

-- So it is supported to have an FBI on HCC data
create index t_hcc_fbi_idx on t_hcc_fbi (lower(object_type));

truncate table t_hcc_fbi;

-- Let's make the FBI unusable, so it doesn't have to be maintained by the load
alter index t_hcc_fbi_idx unusable;

insert /*+ append */ into t_hcc_fbi select * from dba_objects;

commit;

-- But this will return DBMS_COMPRESSION.COMP_NOCOMPRESS = 1
select distinct dbms_compression.get_compression_type(USER, 'T_HCC_FBI', rowid) from t_hcc_fbi;

-- So the mere existence of a FBI, no matter if usable or unusable will prevent HCC with direct-path inserts / parallel DML
-- It is however fully supported to have them on HCC data, but you need to drop / re-create them in order to load the data
-- via direct-path inserts / parallel DML with HCC enabled

spool off

And this is the output I get from 11.2.0.1:

SQL>
SQL> drop table t_hcc_fbi purge;

Table dropped.

Elapsed: 00:00:00.13
SQL>
SQL> -- Create table with HCC enabled
SQL> create table t_hcc_fbi compress for query low as select * from dba_objects where 1 = 2;

Table created.

Elapsed: 00:00:00.09
SQL>
SQL> truncate table t_hcc_fbi;

Table truncated.

Elapsed: 00:00:00.01
SQL>
SQL> -- Direct path inserts support HCC by default
SQL> insert /*+ append */ into t_hcc_fbi select * from dba_objects;

72545 rows created.

Elapsed: 00:00:01.24
SQL>
SQL> commit;

Commit complete.

Elapsed: 00:00:00.00
SQL>
SQL> -- This is supposed to return DBMS_COMPRESSION.COMP_FOR_QUERY_LOW = 8
SQL> select distinct dbms_compression.get_compression_type(USER, 'T_HCC_FBI', rowid) from t_hcc_fbi;

DBMS_COMPRESSION.GET_COMPRESSION_TYPE(USER,'T_HCC_FBI',ROWID)
-------------------------------------------------------------
8

Elapsed: 00:00:09.51
SQL>
SQL> -- So it is supported to have an FBI on HCC data
SQL> create index t_hcc_fbi_idx on t_hcc_fbi (lower(object_type));

Index created.

Elapsed: 00:00:00.23
SQL>
SQL> truncate table t_hcc_fbi;

Table truncated.

Elapsed: 00:00:00.03
SQL>
SQL> -- Let's make the FBI unusable, so it doesn't have to be maintained by the load
SQL> alter index t_hcc_fbi_idx unusable;

Index altered.

Elapsed: 00:00:00.02
SQL>
SQL> insert /*+ append */ into t_hcc_fbi select * from dba_objects;

72546 rows created.

Elapsed: 00:00:01.01
SQL>
SQL> commit;

Commit complete.

Elapsed: 00:00:00.01
SQL>
SQL> -- But this will return DBMS_COMPRESSION.COMP_NOCOMPRESS = 1
SQL> select distinct dbms_compression.get_compression_type(USER, 'T_HCC_FBI', rowid) from t_hcc_fbi;

DBMS_COMPRESSION.GET_COMPRESSION_TYPE(USER,'T_HCC_FBI',ROWID)
-------------------------------------------------------------
1

Elapsed: 00:00:08.93
SQL>

I've been told that this restriction is going to be lifted in some future version, but I haven't had the chance yet to test this with the 11.2.0.2 patch set, in particular because the patch set is not yet released for existing Exadata installations as upgrade - it is only available for new Exadata deployments.

2. Deferred Segment Creation and Parallel DML

11.2 introduced the deferred segment creation that interestingly is enabled by default when doing a fresh installation. I haven't done a migration from previous versions to 11.2 yet so I don't know if this is also enabled in migrated environments but I assume so.

Note that the 11.2.0.2 patch set extends the deferred segment creation to partitioned objects which wasn't supported in the initial 11.2.0.1 release.

The deferred segment creation has some subtle side effects, for example sequences seem not to start with the defined START WITH value because the recursive transaction fails initially, the segment gets created and the next sequence value will be used for the actual DML insert operation.

Another side effect that is not properly documented is the fact that parallel DML is not supported on "segment-less" objects.

So if you have a freshly created segment that is supposed to be populated via parallel DML inserts then this will silently fall back to serial direct-path inserts for the first time executed.

Once the segment has been created from the next time on parallel DML is going to be used which might help to confuse the issue since it is only reproducible under particular circumstances.

So if you have migrated an application to 11.2 and wonder why sometimes load operations take significantly longer than before but mostly do not then this might be a viable explanation.

This behaviour is not part of the deferred segment creation restrictions described in the official documentation, but it is mentioned towards the end of the MOS note "11.2 Database New Feature Deferred Segment Creation [ID 887962.1]"

According to the mentioned MOS note this still seems to apply to 11.2.0.2, but I haven't tested this yet myself. So it looks like this significant restriction is not yet lifted with the current patch set.

Update July 2011: See comment below by Radoslav, the problem seems to be fixed in 11.2.0.2.

Here is a simple test case demonstrating the issue:

set echo on timing on

set linesize 160 trimspool on tab off pagesize 0

spool deferred_segment_creation_parallel_dml_testcase.log

drop table t_source purge;

drop table t_deferred_parallel_dml purge;

-- Create a simple table as source for the insert
create table t_source parallel nologging
as
select * from dba_objects;

exec dbms_stats.gather_table_stats(null, 't_source')

-- No AUTO DOP for this session to prevent any side-effects of this feature
alter session set parallel_degree_policy = manual;

-- Note that even a CTAS operation can be segment-less
-- if the query doesn't return any rows
create table t_deferred_parallel_dml
segment creation deferred
as
select * from t_source where 1 = 2;

-- No segment although CTAS has been used
select count(*) from user_segments where segment_name = 'T_DEFERRED_PARALLEL_DML';

alter session enable parallel dml;

explain plan for
insert /*+ append parallel(t) */ into t_deferred_parallel_dml t select * from t_source;

-- This plan does not show parallel DML
select * from table(dbms_xplan.display);

drop table t_deferred_parallel_dml purge;

create table t_deferred_parallel_dml
segment creation immediate
as
select * from t_source where 1 = 2;

-- One segment
select count(*) from user_segments where segment_name = 'T_DEFERRED_PARALLEL_DML';

explain plan for
insert /*+ append parallel(t) */ into t_deferred_parallel_dml t select * from t_source;

-- This plan does show parallel DML
select * from table(dbms_xplan.display);

spool off

And this is the output I get from 11.2.0.1:

SQL>
SQL> drop table t_source purge;

Table dropped.

Elapsed: 00:00:00.04
SQL>
SQL> drop table t_deferred_parallel_dml purge;

Table dropped.

Elapsed: 00:00:00.01
SQL>
SQL> -- Create a simple table as source for the insert
SQL> create table t_source parallel nologging
2 as
3 select * from dba_objects;

Table created.

Elapsed: 00:00:03.32
SQL>
SQL> exec dbms_stats.gather_table_stats(null, 't_source')

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.64
SQL>
SQL> -- No AUTO DOP for this session to prevent any side-effects of this feature
SQL> alter session set parallel_degree_policy = manual;

Session altered.

Elapsed: 00:00:00.00
SQL>
SQL> -- Note that even a CTAS operation can be segment-less
SQL> -- if the query doesn't return any rows
SQL> create table t_deferred_parallel_dml
2 segment creation deferred
3 as
4 select * from t_source where 1 = 2;

Table created.

Elapsed: 00:00:00.01
SQL>
SQL> -- No segment although CTAS has been used
SQL> select count(*) from user_segments where segment_name = 'T_DEFERRED_PARALLEL_DML';
0

Elapsed: 00:00:00.00
SQL>
SQL> alter session enable parallel dml;

Session altered.

Elapsed: 00:00:00.00
SQL>
SQL> explain plan for
2 insert /*+ append parallel(t) */ into t_deferred_parallel_dml t select * from t_source;

Explained.

Elapsed: 00:00:00.01
SQL>
SQL> -- This plan does not show parallel DML
SQL> select * from table(dbms_xplan.display);
Plan hash value: 1453990251

------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 73251 | 6938K| 117 (1)| 00:00:02 | | | |
| 1 | LOAD AS SELECT | T_DEFERRED_PARALLEL_DML | | | | | | | |
| 2 | PX COORDINATOR | | | | | | | | |
| 3 | PX SEND QC (RANDOM)| :TQ10000 | 73251 | 6938K| 117 (1)| 00:00:02 | Q1,00 | P->S | QC (RAND) |
| 4 | PX BLOCK ITERATOR | | 73251 | 6938K| 117 (1)| 00:00:02 | Q1,00 | PCWC | |
| 5 | TABLE ACCESS FULL| T_SOURCE | 73251 | 6938K| 117 (1)| 00:00:02 | Q1,00 | PCWP | |
------------------------------------------------------------------------------------------------------------------------------

12 rows selected.

Elapsed: 00:00:00.06
SQL>
SQL> drop table t_deferred_parallel_dml purge;

Table dropped.

Elapsed: 00:00:00.03
SQL>
SQL> create table t_deferred_parallel_dml
2 segment creation immediate
3 as
4 select * from t_source where 1 = 2;

Table created.

Elapsed: 00:00:00.01
SQL>
SQL> -- One segment
SQL> select count(*) from user_segments where segment_name = 'T_DEFERRED_PARALLEL_DML';
1

Elapsed: 00:00:00.01
SQL>
SQL> explain plan for
2 insert /*+ append parallel(t) */ into t_deferred_parallel_dml t select * from t_source;

Explained.

Elapsed: 00:00:00.01
SQL>
SQL> -- This plan does show parallel DML
SQL> select * from table(dbms_xplan.display);
Plan hash value: 4111574378

------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 73251 | 6938K| 117 (1)| 00:00:02 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 73251 | 6938K| 117 (1)| 00:00:02 | Q1,00 | P->S | QC (RAND) |
| 3 | LOAD AS SELECT | T_DEFERRED_PARALLEL_DML | | | | | Q1,00 | PCWP | |
| 4 | PX BLOCK ITERATOR | | 73251 | 6938K| 117 (1)| 00:00:02 | Q1,00 | PCWC | |
| 5 | TABLE ACCESS FULL| T_SOURCE | 73251 | 6938K| 117 (1)| 00:00:02 | Q1,00 | PCWP | |
------------------------------------------------------------------------------------------------------------------------------

12 rows selected.

Elapsed: 00:00:00.06
SQL>
SQL> spool off

In general I would advise to disable deferred segment creation and enable it only in environments where a clear benefit can be seen and the side effects are known, understood and tested.

This can be accomplished by simply setting "deferred_segment_creation = false" on instance level.

MS Query Teases You – Excel will Not Display Text Contained in Long Raw/Blob Column

October 30, 2010 Older versions of the ERP package that I work with stored lengthy text data in LONG RAW columns.  Newer versions of the ERP package store lengthy text data in BLOB columns.  When tables containing those columns are queried using the Microsoft Query tool, which allows the data stored in tables to be retrieved into [...]

OT: Movember

Lord help me and eternal hell on Graeme Pitt for badgering me into this
...

"This Movember I've decided to donate my face to raising
awareness about prostate cancer.  My donation and commitment is the
growth of a moustache for the entire month of Movember, which I know
will generate conversation, much laughter and ritual abuse. As I’m sure
you can imagine, the thought of growing a pitiful imitation of a
moustache fills me with horror and there is every prospect that I will
chicken out at some stage unless sponsorship commitments mean I have too
much to lose. However, in the spirit of full disclosure, I plan to
shave it off on Monday 29th Movember at the UKOUG conference in
Birmingham.

Prostate cancer is the most common cancer in men. One
man dies every hour from the disease in the UK. I'm asking you to
support my efforts by making a donation to The Prostate Cancer Charity.
To help, you can either:
-    Click this link http://uk.movember.com/donate/your-details/member_id/763450/
and donate online using your credit card or PayPal account . Or,
-   
Send cheques and CAF vouchers (made payable to 'The Prostate Cancer
Charity Re Movember') directly to The Prostate Cancer Charity, First
Floor, Cambridge House, Cambridge Grove, London W6 0LE. Be sure to
include the person's name on the back of the cheque.

The Prostate
Cancer Charity will use the money raised by Movember for the
development of programs related to awareness, public education,
advocacy, support of those affected, and research into the prevention,
detection, treatment and cure of prostate cancer.

For more
details on the impact Movember is having please visit
http://uk.movemberfoundation.com/research-and-programs
.

Thank
you in advance for helping me to support men's health and putting me
through a month of miserable embarrassment.

Cheers,

Doug
Burns

P.S. How on earth did I allow myself to be talked into
this?
P.P.S. Perhaps I could run a marathon instead?"

Scribd… Publisher of all things stolen…

In recent weeks it seems not a day goes by without me getting a notification of a stolen article that has been uploaded into Scribd (http://www.scribd.com/). To be fair to them, every time I’ve written to ask them to remove the content they have done, but I’m failing to see the business model here. From what I can see the whole site is just made up of stolen PowerPoint, PDF, Word and HTML files. There seems to be little if any original content present at all.

As far as my own slides and html files are concerned, the users uploading them seem to make a habit of uploading anything they can get their hands on. I’ve also seen copies of Oracle books in PDF format, which look like they have been scanned in manually, and even the whole Oracle documentation in PDF format uploaded.

I just don’t see the incentive for the users of this site. What is the point of uploading documents that are already freely available on the net. Surely just a link in your favorites list is all you need. In the case of plagiarists, they are trying to pass work off as their own, but that is not what Scribd users seem to do. They just upload the content as-is. Why? I just don’t get it.

Anyway, if you publish any content and you are bored one day, have a search through Scribd (using your domain name, blog URL or real name) and you will probably find something of yours that has been stolen. If you do, follow the Copyright link at the bottom of the page and it will tell you how to send a DCMA takedown notice. They usually respond pretty quickly. I guess if everyone did this the site would be empty and they would probably move on to some other form of organised crime. :)

Cheers

Tim…

What’s in a voting disk?

Introduction

In RAC, CSSD processes (Cluster Services Synchronization Daemon) monitor the health of RAC nodes employing two distinct heart beats: Network heart beat and Disk heart beat. Healthy nodes will have continuous network and disk heartbeats exchanged between the nodes. Break in heart beat indicates a possible error scenario. There are few different scenarios possible with missing heart beats:

  1. Network heart beat is successful, but disk heart beat is missed.
  2. Disk heart beat is successful, but network heart beat is missed.
  3. Both heart beats failed.

In addition, with numerous nodes, there are other possible scenarios too. Few possible scenarios:

Possibly Interesting Execution Plan

October 29, 2010 I recently found what I believe to be an interesting execution plan, but then maybe I am just having difficulty thinking of blog article topics.  Here is the execution plan: Plan hash value: 904523798 -------------------------------------------------------------------------------------------------------------------- | Id  | Operation                      | Name                      | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | -------------------------------------------------------------------------------------------------------------------- |   [...]

Post Trip Crash…

Well, the excitement and stress of my recent trip is all behind me, so I have the inevitable post trip crash. Now I’ve got to deal with stuff like money, washing and playing catch-up on all the stuff I’ve missed since I was gone. It’s all so Rock’n'Roll… Sigh…

I went to visit some friends yesterday evening and slept through two films on the couch they use for their dogs. I then got up, drove home and have not been able to sleep since. On the positive side you can get a lot done in the middle of the night as there are no distractions. On the negative side it’s now 08:00 and I feel like I belong in a mortuary.

Having said that, there is a little sparkle of excitement here because I’ve got something new from Oracle to play with. More may be revealed in the near future on that front. It’s all so terribly cloak and dagger. :)

Cheers

Tim…

Oracle XMonth

I spent most of the last week in California at Oracle’s XMonth (it was really a week so I’m not sure why they called it XMonth). Which reminds me of a tuning engagement I did a few years ago. I showed up at the client’s facilities and we started discussing the problem. They told me that their daily job was running too slow and it was causing major problems. So I asked them what time the daily job ran each day. And they told me that it ran every 20 minutes. Ha! “And why do you call it the daily job?”, I asked. They weren’t really sure. The job was taking about an hour to complete as I recall, but I digress.

At XMonth, they had three tracks: Exadata (and two others that I can’t remember). I did learn a few things I thought were worth sharing.

  1. Exalogic is not shipping yet, but we did cover it in some detail. Exalogic’s biggest advantage appears to be the Infiniband fabric. It can be connected directly to any DB server using IB (Exadata for example) and can communicate with the extremely low latency RDS protocol.
  2. Oracle has relaxed their “no changes to the Exadata configuration” stance (but only very slightly). They said that it was OK to change out the Cisco switch and replace it with some other equivalent switch.
  3. A competitive analysis of Sun’s server line was provided. It included T series, X series, and M series servers along with Exadata. Exadata pretty much kicks all other options in the teeth (in my opinion). M series are still suitable for very large applications that are unable to scale out via RAC – such are Oracle’s own MRP package which uses the dbms_pipe package limiting its ability to scale in a RAC environment. But in general, the advice to the sales team was that if you are in a competitive situation, in most cases you should lead with Exadata.

So that’s about it. Oh they also let me talk about our experiences with Exadata. That was fun and I got to try my hand at a virtual presentation, as there were participants all over the world following along with Webex. The software has gotten pretty good for doing these kinds of presentations by the way. It was good practice for the Virtual Oracle Conference we have coming up next month. (note that there are only a couple of days left to sign up at the discounted rate)

Hong Kong Update

As always, you never know what you’re going to get when you do an Oracle University class. Originally the Hong Kong class was cancelled, then got rescheduled at the last minute. I was expecting a very small class, but as it turned out I had 19 people, so it was the biggest class I did on this trip.

The room was a little on the small side and a bit warm in the morning of the first day, but we managed to get through OK.

Doing the same course 4 times in such quick succession was a little odd. On a couple of occasions I caught myself thinking, “I’m sure I’ve already said this before”. :)

The first leg of the flight back home was “interesting”. There was an old guy on my row who cycled between snorting half his brain down into the back of his throat, then coughing up his lungs up into the back of his throat, followed by a good chew and swallow. It was less than a pleasant experience. Added to that, there was a small kid who was intermittently making a noise which kept waking me up. I kinda forgive kids on planes because it’s not a natural environment for them, but this kid had fallen out of the ugly tree and hit every branch on the way down, twice. When they don’t look cute I’m much less sympathetic.

The second leg of the trip seemed a lot quicker because I was chatting to an Aussie lady about life, the universe and everything. Seven hours flew by.

So now I’m home and trying to deal with the washing, backlog of mail and yet another time zone. I think it’s two weeks until the next trip, assuming there are no cancellations. :)

Cheers

Tim…