Top 60 Oracle Blogs

Recent comments


Subquery Factoring (4)

I’ve written before about the effects of subquery factoring (common table expressions – or CTEs) on the optimizer, and the way that the optimizer can “lose” some strategies when you start factoring out subquery expressions. Here’s another example I came across quite recently. It involved a join of about 15 tables so I’ve only extracted a few lines from the SQL and resulting execution plans.

We start with the original query, which had factored out an aggregate subquery then used it in place of an inline view:

with max_cust_comm as (
		customer_communications ccm
	group by
left join
	max_cust_comm	mcc
on	mcc.order_id = ord.order_id

The execution path for this query included the following lines:

|   6 |     HASH JOIN OUTER            |                            |     1 |
|   7 |      NESTED LOOP               |                            |     1 |
|  41 |      VIEW                      |                            |   798K|
|  42 |       HASH GROUP BY            |                            |   798K|
|  43 |        TABLE ACCESS FULL       | CUSTOMER_COMMUNICATIONS    |   798K|

You can see that the optimizer has created a result set (VIEW) at line 41 by scanning the entire customer_communications table, for a total of about 800,000 rows, aggregating the data by order_id. This is not very efficient becauase (a) I happen to have a very useful index on the customer_communications table that contains all the data I need, and (b) there are just a few input rows where I need to find this max(comm_date).

Hotsos Symposium 2010 Presentations

I got an email a few days ago asking if I would provide the scripts from my Hotsos Symposium 2010 presentations. I didn’t even realize the presentations had been posted anywhere but I managed to find them on my company’s website. So anyway, I decided to go ahead and post a link to the PDF’s and the scripts here as well. So click on the pretty pictures to get the PDFs and the cleverly titled text links to get the accompanying zip files with the scripts.

Controlling Execution Plans Zip File

My Favorite Scripts 2010 Zip File

Oh and Bob Sneed as “Disco Duck” (Thanks Marco)

Cardinalilty One

I think anyone who has read Wolfgang Breitling’s material about the optimizer will be familiar with the concept of Cardinality Feedback and one particular detail that when Oracle gets a cardinality estimate of one for a “driving” table then there’s a good chance that the execution plan will go wrong. (That’s not rule, by the way, just a fairly common observation after things have gone wrong.)

A recent note on OTN reminded me of a particular scenario where this specific problem can occur. It’s not particularly common, but it may hit people who are building data warehouses from multiple different sources. We start with an unlikely looking data set and very simple query:

drop table t1;

create table t1 as
	rownum id1,
	rownum id2
	rownum <= 10000

execute dbms_stats.gather_table_stats(user,'t1');

set autotrace traceonly

	id1 = id2

What do you think Oracle estimated cardinality will be for this predciate ? We know, because we saw the data being built, that we’re going to identify 10,000 rows. But the optimizer doesn’t see it that way – check line 2 of the execution plan. The optimizer thinks it will find just one row:

Exadata v2 Smart Scan Performance Troubleshooting article

I finally finished my first Exadata performance troubleshooting article.

This explains one bug I did hit when stress testing an Exadata v2 box, which caused smart scan to go very slow – and how I troubleshooted it:

Thanks to my secret startup company I’ve been way too busy to write anything serious lately, but apparently staying up until 6am helped this time! :-) Anyway, maybe next weekend I can repeat this and write Part 2 in the Exadata troubleshooting series ;-)

Enjoy! Comments are welcome to this blog entry as I haven’t figured out a good way to enable comments in the google sites page I’m using…


Oracle Support Sanctions Manually Created SQL Profiles!

I originally titled this post: “SQLT - coe_xfr_sql_profile.sql”

Catchy title huh? - (that’s why I changed it)

I’ve been promoting the use of SQL Profiles as a plan control mechanism for some time. The basic idea is to use the undocumented procedure dbms_sqltune.import_sql_profile to build a set of hints to be applied behind the scenes via a SQL Profile. The hints can be created anyway can think of, but one of my favorite ways to generate them is to pull the hints from the other_xml filed of v$sql. This is a technique suggested to me originally by Randolf Geist. I have used this approach several times in the past but occasionally I’ve had a few doubts as to whether this is a good idea or even if SQL Profiles can apply all valid hints (see Jonathan Lewis’s comments on this post, Why Oracle Isn’t Using My Profile, where he expresses some doubts as well - he’s also written a bit about SQL Profiles on his site as you might imagine).

Ignoring Hints

I’ve previously published a couple of notes (here and here) about the driving_site() hint. The first note pointed out that the hint was deliberately ignored if you write a local CTAS or INSERT that did a remote query. I’ve just found another case where the hint is ignored – this time in a simple SELECT [...]

double trouble

In the latest Quiz Night, I asked how you could make a query more efficient by changing a two table join into a three table join – with the clue that my third table was a repeat of the first table. Gary Myers, in comment 4,  provided the type of answer I was looking for. Sometimes [...]

Oracle Exadata V2 - Flash Cache

One of the things I didn’t really talk about in my first post on Exadata was the flash cache component of the storage servers. They are a key component of the “OLTP” claims that Oracle is making for the platform. So let’s talk about the hardware first. The storage servers have 4 of the Sun Flash Accelerator F20 PCIe cards. These cards hold 96G each for a total of 384G on each storage server. That’s well over a terabyte on the smallest quarter rack configuration. Here’s what they look like:

Note that they are only installed in the storage servers and not in the database servers. The cards are usually configured exclusively as Flash Cache, but can optionally have a portion defined as a “ram disk”.

Oracle has a White Paper here:

Exadata Smart Flash Cache and the Sun Oracle Database Machine

This white paper was published in late 2009 and it is specific to V2. It has some good information and is well worth reading. One of the comments I found interesting was the discussion of carving a piece of the Flash Cache out as a “disk”. Here’s the quote:

Cursor Sharing 3

Here’s a simple piece of code demonstrating an irritating problem. I’ve created a table, a function-based index, collected stats (without histograms), and then run a query that should use that index – but doesn’t. execute dbms_random.seed(0) create table t1 as with generator as ( select --+ materialize rownum id from dual connect by rownum <= [...]

Fun with Oracle Exadata V2

Well I’ve been holed up playing with an Exadata V2 machine for the past several weeks. Wow. Very interesting technology.

I must say that I believe the concept of offloading SQL processing to the storage layer is a game changer and I wouldn’t be at all surprised to see this as a standard feature a few years from now. What that means for other storage vendors is unclear at this point. So for this first post on the topic let me just describe the configuration (and some potential upgrades).

The basic architecture consists of a set of database severs and a set of storage servers.

Database Servers:

  • Sun x4170 (1RU 64x server)
  • 2 - Quad-core Intel Xeon E5540 2.53GHz processors
  • 72G Ram (18×4G Dimms - max of 144G using 8G DIMMs)
  • Dual-Port QDR InfiniBand Host Channel Adapter
  • HBA with 512MB Battery Backed Write Cache (only for internal disks???)
  • 4 - 146G internal drives (SAS 10,000 RPM)
  • dual hot swappable power supplies
  • no spare/empty slots!

Here’s what the Database Servers look like:

Storage Servers:

  • Sun x4275 (2RU 64x server)
  • 2 - Quad-core Intel Xeon E5540 (2.53GHz) processors
  • 24G Ram
  • Dual-Port QDR InfiniBand Host Channel Adapter
  • HBA with 512MB Battery Backed Write Cache (only for internal disks???)
  • dual hot swappable power supplies
  • 4 - 96G Sun Flash PCIe Cards (total of 384 GB)
  • 12 - 600 GB 15,000 RPM SAS or 2 TB 7,200 RPM SATA

Here’s what the Storage Servers look like: