Search

Top 60 Oracle Blogs

Recent comments

Uncategorized

Virtual Conference on Oracle Performance!

The E2SN Virtual Conferences are webinar-style online training sessions, but just like at conferences, you can listen to multiple top speakers within a single event.
The standard conference consists of 4 x 1.5 hour presentations with 30-minute Q & A sessions after each presentation. The four sessions are spread across two days, so the conference takes only four hours per day. So, in addition to not even having to leave your desk for learning from world’s top experts, you can still get your important work done in the office, too!
The first scheduled virtual conference is focusing on a walk-through on Systematic Oracle SQL Optimization, with emphasis on practical applications and real-life scenarios.

Systematic Oracle SQL Optimization virtual conference:

  • The speakers are: Cary Millsap, Jonathan Lewis, Kerry Osborne and Tanel Poder

  • The conference takes place on 18. and 19. November ( 2 x 0.5 days )
  • The time of the conference sessions is: 08:00am – 12:00pm Pacific Standard Time (PST) on both days
  • For this inaugural event we have a special launch price of 475 USD per attendee!

Click here for agenda, abstract and registration….

Interviewed for the November 2010 NoCOUG Journal

November 4, 2010 Almost two months ago I was interviewed for an article that was to appear in an upcoming NoCOUG Jornal newsletter.  It was a great opportunity to participate in the interview, and Chen (Gwen) Shapira provided to me a great set of thought provoking questions.  The interview went a bit long, so some of [...]

List partitions

Despite the title and content, this article is more about thinking and testing than it is about list partitions as such. It’s just based on a little thought experiment with list partitioning.

I have a list-partitioned table, with no indexes, defined as follows:


create table area_sales (
	area		varchar2(10)	not null,
	dated		date		not null,
	quantity	number(6),
	value		number(8,2),
	padding		char(100),
	constraint as_area_ck check (area in ('England','Ireland','Scotland','Wales'))
)
partition by list (area) (
	partition england values  ('England'),
	partition ireland values  ('Ireland'),
	partition scotland values ('Scotland'),
	partition wales values    ('Wales')
)
;

You’ll notice that there is no default partition, the partitioning column is declared as not nul and is limited by a check constraint to a very specific set of values, and that every partition is defined to hold exactly one of the legal partition key values. Given those restrictions you might like to think about which of the following queries will be able to do perfect partition elimination:


select	count(*)
from	area_sales
where	area = 'England'
;

select	count(*)
from	area_sales
where	area in ( 'England', 'Ireland', 'Wales')
;

select	count(*)
from	area_sales
where	area != 'England'
;

select	count(*)
from	area_sales
where	area not in ( 'England', 'Ireland', 'Wales')
;

select	count(*)
from	area_sales
where	area between 'England' and 'Ireland'
;

Once you’ve thought about what’s likely to happen, you might want to create and populate the table with a few thousand rows and run some tests to see if your thoughts were correct. Here’s a simple statement to spread some data evenly through the tables:


insert into area_sales
select
	decode(mod(rownum,4),
		0,'England',
		1,'Ireland',
		2,'Scotland',
		3,'Wales'
	),
	sysdate + 0.01 * rownum,
	rownum,
	rownum,
	rownum
from
	all_objects
where
	rownum <= 30000
;

Once you’ve done the tests, and satisfied yourself that you understand what’s going on, you might start thinking about a few variations that might make the behaviour change, such as:

    you add a default partition
    you add a default partiiton and remove the check constraint
    you add a default partition and remove the not null constraint
    you define partitions that hold multiple values
    you change the order of partitions so the key values don’t appear in alphabetical order
    you use a different version of Oracle

I’m not going to tell you the answers to these questions. The point I want to make is that this is the type of model, and these are the types of question you should ask yourself when you start to investigate a feature to see whether it will behave as well as you hope, and what special benefits you might get from it.

In fact, these are the questions I’m asking myself right now as I look at a system which is using list-partitioned tables to hold (lots of) sales data. I think it might be a good idea to restructure the table to disallow nulls, include a check constraintm, and hold just one key value per partition – and then modify some of the code to get rid of “not in” and “!=” predicates.

The trouble is it’s a massive table with lots of indexes, the number of partitions would jump from eight to 40, and the number of times that rows would migrate due to partition key updates would increase; so the cost of restructuring would be large and there would be some fairly undesirable impact on DML that would offset the benefits of more efficient queries.

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.

SVG and OEM on windows 64 bit

Anyone try to use OEM from a windows 64bit machine? Looks like there is no SVG install for windows 64bit so the charts on the performance pages are all just gifs.
Seems surprising but alas with Oracle and their dealings with UI, its not that surprising.
I wanted Oracle to use flash in OEM 10g but that got nixed. I was glad to have SVG in OEM 10g rather than nothing, but SVG has been EOLed and so unless Oracle does something then there will be more and more cases where OEMs SVG components don’t work in browsers as OS’s advance.
On a good note though, in 11g I was happy to see some FLEX components but word has it that these will be pulled – hmpf!
I’m happy to see that my current company, Delphix, is using FLEX (not that FLEX is the only way to go but it’s a nice way, at least until HTML5) . Here’s an example of Delphix’s interface

http://www.delphix.com/resources.php?tab=product-demo


After working at Oracle for 13 years , UI seemed to be some of the worst in the valley often harking back to web pages from 1992, it’s nice to be at Delphix, a company that seems to appreciate UI and aesthetics. I never understood why Oracle’s UI wasn’t better since Larry was such good friends with Steve Jobs. Steve Jobs and Apple are so good at look and feel. I figured Steve Jobs would have influenced Larry and Oracle to have great UI, but alas there has never been any indication of that other than the fun demos Larry and Steve use to do around Oracle and the NEXT machine back in the day.

Moving from ASM to a Filesystem

ASM BUG DUPLICATE

ASHMON on 64bit Oracle 11gR2

It’s been years since I’ve changed any ASHMON code though I have used ASHMON whenever I didn’t have access to DB Optimizer or when I wanted to specifically access v$active_session_history graphically. Yesterday, though, I just got a new laptop that is 64bit and I couldn’t get ASHMON to run! So after only sleeping 4 hours last night I finally got it to run. (a lot of time was just getting Oracle to install which is a separate story – suffice it to say the 11gR2 full database download installed where as the 11gR2 client gave an error that main wasn’t found).
The fix to make ASHMON run on 64 bit turns out to be pretty easy. To make ASHMON run on 64bit requires two 32bit dlls as well as forcing TNS_ADMIN to the directory with the tnsnames.ora (unfortunately oratcl doesn’t seem to accept explicit database addresses , ie name:port:sid, that could bypass the tnsnames.ora) ASHMON for some reason wasn’t picking up the tnsnames.ora in the Oracle 11gR2 directory. I could have forced ASHMON to use the Oracle 11 tnsnames.ora with TNS_ADMIN but figured it was easier to just point ASHMON to a tnsnames in the ASHMON install with TNS_ADMIN. You can edit ashmon/funcs/begin.tcl to change TNS_ADMIN to point to a different tnsnames.ora.
I have now made a new compressed file of ASHMON including the two dlls, oci.dll and oraocius10.dll, as well as changing the variables ORACLE_HOME and TNS_NAMES to point to ./ashmon and ./ashmon/network/admin respectively.
Here is the new “rar” file (I don’t have gzip on my new laptop so just downloaded rar)
This version of ASHMON should run on any windows box within reason. The only “gotcha” is that you have to edit the tnsnames.ora file in the ./ashmon/network/admin to add your targets.

Cricinfo All Time World XI

The Cricinfo all time world 11 is now out. Unsurprisingly there are a few differences (though I was expecting more). Hutton ahead of Greenidge is on pure style terms pretty straightforward. Similarly Wasim would make a great replacement for Freddie Trueman. I personally don’t subscribe to the view that an all-rounder is needed in a world XI [...]

UKOUG Conference Series 2010

The annual UKOUG Technology and E-Business Suite Conference takes place from November 29th to December 1st in Birmingham (UK not Alabama). You can book here. One of the neat features of this event is that you can pre-register your interest in sessions and build a handy one page personalized agenda. This saves carting around the [...]

October 2010 Security Patches

This is just a quick note to list a few of the interesting issues I’ve run into applying the October 2010 Security Patches to Windows 11.1.0.7 databases (32 and 64bit). For *nix platforms Oracle Security Patches come in 2 main flavours a Critical Patch Update and a Patch Set Update -the latter includes more than [...]