Ric Ramblings

Did someone get me this for Christmas?

2011-12-24T09:28:00.000-08:00

The speakers have been chosen for Sym ’12!!

2011-11-07T12:48:00.000-08:00

Check it out here. Looks like a super line up of speakers as always.

Make sure you sign up soon if you haven't already.

See you there!!

4th ANNUAL MICHIGAN ORACLE USER SUMMIT

2011-11-03T08:57:00.000-07:00

Everyone come on down to the 4th Annual Michigan Oracle User Summit

Wednesday, November 30th, 2011 8AM to 5PM

Schoolcraft College-VisTaTech Center, Livonia, MI

This event has it all:

Vendor Meet & Greet and Continental Breakfast

Sessions Galore with 50+ Presentations!

Presentations and Product Reviews Running throughout

Oracle User Groups Update

*LUNCH*

Wine & Cheese – Social Networking Hour

I'll be speaking there with many others. It's sure to be a good time and a great time to meet with other Oracle folks. See you there!!

Jonathan Lewis, for Hotsos Training Day 2012

2011-09-07T14:19:00.000-07:00

As if you really need another reason to come to the 10th anniversary Hotsos Symposium extravaganza, this has got to be way up there on the list: Jonathan Lewis is doing the training day on the 8th of March!!

Go here for details: Hotsos Training Day

This is going to be a great event. Be there.

Lex DeHaan

2011-09-01T18:03:00.000-07:00

Today I was leafing thru "Master Oracle SQL and SQL*PLUS" by Lex DeHaan looking up some SQLPlus command that I had forgotten the exact syntax for and in the back I found some printed notes of my last communications with Lex. One was a short chat we had via Skype and the other was a note that I sent just before he passed on.

It was January 2006 when Lex moved on to the next adventure. I had the extreme pleasure to call him both a friend and a colleague. He and I worked together at Oracle. My first contact with him was while I was an instructor and he sent me some scripts to demo something in class, I've long forgotten exactly what it was. Then I work with him while in Curriculum Development in the Server Technologies group. We were in different teams but worked together often.

I truly miss him and his every ready "I disagree" when we'd get off into slightly more theoretical talk, typically over tall glasses of dark brew!

He is still helping me today thru this book and his other ones.

Lex, as I said in my note to you back then, you will be missed in small and large places in small and large ways. Thanks for everything Lex.

Wanted: An Oracle Instructor

2011-08-30T12:41:00.000-07:00

Hotsos is looking for an instructor who can teach 5 days of intensive SQL optimization techniques to classes of up to 15 students anywhere in the world. Expect travel to be near 100% and to teach up to 3-4 weeks a month. Extensive Oracle SQL experience is a must as is a good understanding of basic Oracle instance and database architecture.

Do you think you have what it takes? Send a resume to jobs@hotsos.com

Registration is now open for Hotsos Symposium 2012!

2011-05-10T14:26:00.000-07:00

Don't wait!!

Visit the registration page today to sign up!

This will be a great one! Ten years in the making!!

If you haven't been to a Symposium before, check out this short video.

BE THERE!!

It's the little things....

2011-03-30T08:43:00.000-07:00

That can really mess up your day.

I'm using this little script:


declare
   v_flush varchar2(3) := '&1';
   err_mesg     varchar2(250);

begin
      if substr(upper(v_flush),1,2) = 'BP' then
      sys.hotsos_pkg.flush_bp;
      dbms_output.put_line ('*** Buffer Pool Flushed ***') ;
   end if;
   if substr(upper(v_flush),1,2) = 'SP' then
      sys.hotsos_pkg.flush_sp;
      dbms_output.put_line ('*** Shared pool flushed ***') ;      
   end if; 
exception
   when others then 
      err_mesg := SQLERRM;
      dbms_output.put_line ('****** Error! '||err_mesg) ;
end ;
/

And it's not working, as in, it's not flushing the pools. I get no errors. For the life of me I can't figure it out. I put in several DBMS_OUTPUT.PUT_LINE commands to print out what is going on as it runs. Eventually I notice that the variable v_flush is set to literally &1 while this thing runs. WHAT?

It turned out that DEFINE had some how gotten turned OFF in the session. How I'm not exactly sure, but now this script file has:


set define on

at the top of it.

New read events in 11G DIRECT PATH READ and DIRECT PATH READ TEMP

2011-03-17T08:18:00.000-07:00

In prior version of Oracle, Oracle used the SEQUENTIAL READ event to read temp objects into the PGA. With 11 Oracle seems to use some new read events. The DIRECT PATH READ appears to be used to read information from the data files into the temp segment, then DIRECT PATH READ TEMP will be used while manipulating the temp segment. There is also a DIRECT PATH WRITE TEMP event. This was always writing out a max of 31 blocks in my tests. (Which sure seemed like an strange number to me.)

In my simple test I have a query on a table with @2.3 Million rows and no indexes. Oracle version 11.2.0.1 on a Dell Inspiron. The query has a order by on the 3 columns I'm selecting and there is no choice other then to read the entire table via a full table scan and then sort the rows in temp.


SELECT OWNER, OBJECT_NAME, STATUS FROM AHWM ORDER BY 1,2,3;

I wanted to see if the setting of DB_FILE_MULTIBLOCK_READ_COUNT had any affect on the DIRECT PATH READ and DIRECT PATH READ TEMP events. Here is the findings:


With DB_FILE_MULTIBLOCK_READ_COUNT set to 8 I didn’t see a read more then 8 in my trace:
WAIT #8: nam='direct path read' ela= 9760 file number=4 first dba=5120 block cnt=8 obj#=76480 tim=102097583805


With DB_FILE_MULTIBLOCK_READ_COUNT set to 16 I didn’t see a read more then 16 in my trace:
WAIT #8: nam='direct path read' ela= 9916 file number=4 first dba=5728 block cnt=16 obj#=76480 tim=104104097173


With DB_FILE_MULTIBLOCK_READ_COUNT set to 32 I didn’t see a read more then 32 in my trace:
WAIT #9: nam='direct path read' ela= 1965 file number=4 first dba=5600 block cnt=32 obj#=76480 tim=106136990814


With DB_FILE_MULTIBLOCK_READ_COUNT set to 0 (128) I didn’t see a read more then 128 in my trace:
WAIT #4: nam='direct path read' ela= 516 file number=4 first dba=29952 block cnt=128 obj#=76480 tim=109775067490

Ah! So YES the setting of good old DB_FILE_MULTIBLOCK_READ_COUNT will impact how many blocks are requested with each DIRECT PATH READ event.

Now for DIRECT PATH READ TEMP, the setting of DB_FILE_MULTIBLOCK_READ_COUNT made no difference. It was a max of 7 in all my tests, and only for very few events. For the most part it was reading 1 block at a time.

Did the changes have any performance impact? Well not really. Looking at the stat lines for each one they all did the same LIOs, PIOs and really ran in about the same amount of time:


Actual lines from extended SQL trace for a_sort:a_sort_8
----------------------------------------------------------------------------------------------------------------------------------
STAT #7 id=1 cnt=2291136 pid=0 pos=1 obj=0 op='SORT ORDER BY (cr=32542 pr=45112 pw=12575 time=9488819 us cost=31576 size=87063168 card=2291136)'
STAT #7 id=2 cnt=2291136 pid=1 pos=1 obj=76480 op='TABLE ACCESS FULL AHWM (cr=32542 pr=32537 pw=0 time=5905761 us cost=8895 size=87063168 card=2291136)'

Actual lines from extended SQL trace for a_sort:a_sort_16
----------------------------------------------------------------------------------------------------------------------------------
STAT #8 id=1 cnt=2291136 pid=0 pos=1 obj=0 op='SORT ORDER BY (cr=32542 pr=45112 pw=12575 time=9409541 us cost=29880 size=87063168 card=2291136)'
STAT #8 id=2 cnt=2291136 pid=1 pos=1 obj=76480 op='TABLE ACCESS FULL AHWM (cr=32542 pr=32537 pw=0 time=5615230 us cost=7199 size=87063168 card=2291136)'

Actual lines from extended SQL trace for a_sort:a_sort_32
----------------------------------------------------------------------------------------------------------------------------------
STAT #9 id=1 cnt=2291136 pid=0 pos=1 obj=0 op='SORT ORDER BY (cr=32542 pr=45112 pw=12575 time=8508505 us cost=29032 size=87063168 card=2291136)'
STAT #9 id=2 cnt=2291136 pid=1 pos=1 obj=76480 op='TABLE ACCESS FULL AHWM (cr=32542 pr=32537 pw=0 time=5264382 us cost=6351 size=87063168 card=2291136)'

Actual lines from extended SQL trace for a_sort:a_sort_0
----------------------------------------------------------------------------------------------------------------------------------
STAT #4 id=1 cnt=2291136 pid=0 pos=1 obj=0 op='SORT ORDER BY (cr=32542 pr=45112 pw=12575 time=9435964 us cost=28396 size=87063168 card=2291136)'
STAT #4 id=2 cnt=2291136 pid=1 pos=1 obj=76480 op='TABLE ACCESS FULL AHWM (cr=32542 pr=32537 pw=0 time=4780372 us cost=5715 size=87063168 card=2291136)'

MBRC and DB_FILE_MULTIBLOCK_READ_COUNT

2011-02-08T16:31:00.000-08:00

Maybe you have this down but I found out today that I had understood this completely backwards. It was my understanding that DB_FILE_MULTIBLOCK_READ_COUNT (if set) was only used to COST the plan but MBRC (if set) would be used for each scattered read. This doesn’t appear to be the case, in fact it appears I have this exactly opposite.

I have on my test box collected workload system stats and MBRC is set to 8.

I tried it with DB_FILE_MULTIBLOCK_READ_COUNT set to 0, in which case the system sets it to 128. I expected to get scattered reads of 8. But I got my first read on the table at 128, and then next got what was left. (I created the table with 1024 blocks in the first extent.)


WAIT #10: nam='db file scattered read' ela= 16051 file#=4 block#=290066 blocks=128 obj#=77649 tim=891367221237

WAIT #10: nam='db file scattered read' ela= 1341 file#=4 block#=290194 blocks=24 obj#=77649 tim=891367282208

OK, not what I expected. Then I set DB_FILE_MULTIBLOCK_READ_COUNT to 16 and dag-nab-bit, I got a bunch of 16 block reads.


file scattered read' ela= 903 file#=4 block#=290082 blocks=16 obj#=77649 tim=891493998398

file scattered read' ela= 807 file#=4 block#=290098 blocks=16 obj#=77649 tim=891493999573

file scattered read' ela= 839 file#=4 block#=290114 blocks=16 obj#=77649 tim=891494000751

file scattered read' ela= 824 file#=4 block#=290130 blocks=16 obj#=77649 tim=891494001988

file scattered read' ela= 815 file#=4 block#=290146 blocks=16 obj#=77649 tim=891494003176

file scattered read' ela= 23783 file#=4 block#=290162 blocks=16 obj#=77649 tim=891494027343

...

Well then I looked at the COST of the plan, I get the same COST for a setting of 8, 16, and 32 for DB_FILE_MULTIBLOCK_READ_COUNT (it's kinda hard to read, the cost is 50 in the plan below):


SQL> get zz_test1
1  select *
2    from MBRC_TEST
3*  where object_id between 1 and 4

SQL> @hxplan

Enter .sql file name (without extension): zz_test1

Enter the display level (TYPICAL, ALL, BASIC, SERIAL) [TYPICAL]  :

Plan hash value: 3059191348


-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |   503 | 52312 |    50   (0)| 00:47:06 |
|*  1 |  TABLE ACCESS FULL| MBRC_TEST |   503 | 52312 |    50   (0)| 00:47:06 |
-------------------------------------------------------------------------------

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

1 - filter("OBJECT_ID"<=4 AND "OBJECT_ID">=1)

So there you have it, the MBRC value is used to COST the plan but NOT for the scattered read event. Honestly reading the docs it was less then clear, hence my confusion. Nothing beats a test! (or more then one really, I did this several times just to make sure I was seeing it right.)

This number keeps coming up....

2011-02-07T11:25:00.000-08:00

Happy Holidays! Hawaiian style...

2010-12-23T16:09:00.000-08:00

Baby Blues BBQ in Philadelphia

2010-12-19T14:34:00.000-08:00

I visited Baby Blues BBQ in Philly this past week and had the “Mason Dixon” (1/2 RACK of MEMPHIS RIBS, 1/4 of a CHICKEN). The Mac and Cheese is great with the hot sauce by the way. If you're in Philly and want some ribs instead of a cheese stake sandwich, try them out. Great folks working the grill and super servers.

I sat where I was looking right on to the grill and watched the guys making the food. Makes ya more hungry watching the food get prepared. They were cooking up ribs and chicken and shrimp and corn on the cob all on the grill! It was great.

The place is a little of the beaten path, and worth the time to find:

3402 Sansom St
Philadelphia, PA 19104

Error Logging in SQLPlus

2010-11-23T10:08:00.000-08:00

This is pretty cool. You can now log errors in to table within SQLPlus. I think when this feature first came out it was just for DML (insert, update and delete) but now it seems to work for any error that is raised within a SQLplus session.

The command to turn this on is a SQLPlus SET command, it's simplest format is:

SET ERRORLOGGING on

With this SQLPlus will start putting errors into a table called sperrorlog. What's cool about this statement is that if the table doesn't exist it will created it. In the more verbose format it would look something like this:

SET ERRORLOGGING on TABLE hsperrorlog TRUNCATE IDENTIFIER HARNESS

With this I have to create a table named hsperrorlog. The TRUNCATE option truncates the contents of the table before it starts logging anything into it. The IDENTIFIER option allows you to populate a column in the table, with the name of (you guessed it) IDENTIFIER. (Pretty cleaver eh?)

The table has these columns (all can be null):

USERNAME data type VARCHAR2(256) the user running the command.

TIMESTAMP data type TIMESTAMP(6) when the error was encountered.

SCRIPT data type VARCHAR2(1024) the name of the script being run. If this is an interactively entered command this will be null.

IDENTIFIER data type VARCHAR2(256) the optional id used when turning on error logging. You can change the identifier by just doing a set command like this:

set errorlogging ON IDENTIFIER RVD

This doesn't change the table that the errors are being logged into.

MESSAGE data type CLOB this is the error message.

STATEMENT data type CLOB this is the statement that raised the error.

I've been using it to add some needed functionality to the Hotsos Harness and it's already helped me clean up a few minor errors in the harness that have been quite hard to track down with out this.

Here is a very simple example, the output was reformatted to make this easier to read:

OP@ORCL112> SET ERRORLOGGING on TABLE hsperrorlog TRUNCATE IDENTIFIER HARNESS
OP@ORCL112> select * from XYZ;
select * from XYZ
*
ERROR at line 1:
ORA-00942: table or view does not exist

OP@ORCL112> select * from hsperrorlog;

USERNAME
-----------
OP

TIMESTAMP
----------------------------
23-NOV-10 12.59.39.000000 PM

SCRIPT
----------------------------

IDENTIFIER
----------
HARNESS

MESSAGE
---------------------------------------
ORA-00942: table or view does not exist

STATEMENT
-----------------
select * from XYZ

Check this out, it should make debugging SQL scripts a lot easier!! There are some limitations to it of course, it's not on for recursive SQL which makes a lot of sense since it could easily get into an infinite loop. Also if your scripts do reconnections, you'll have to turn it back on each time you reconnect. It is off by default.

Oh and to turn it off it looks like:

OP@ORCL112> SET ERRORLOGGING off
OP@ORCL112>
OP@ORCL112> show errorl
errorlogging is OFF

Kerry Osborne - Keynote Speaker for Hotsos Symposium 2011!!

2010-10-12T19:23:00.000-07:00

WOW! The 2011 Sym is lining up to be a great one!! Clear your schedule for March 6-11 2011 and be Omni Mandalay Hotel in Las Colinas (Dallas) Texas, USA!

Kerry Osborne has just been named the Keynote Speaker, and Karen Morton is doing the one day of training.

This is certainly a sym you don't want to miss. Have you signed up yet? Click here and do it now!

So who will Oracle buy next.?

2010-08-20T10:30:00.000-07:00

Here's an interesting look forward. This is a survey by Stephen Jannise.

Oracle Mergers & Acquisitions: Who’s Next?

I worked with Teradata years ago and have some familiarity with their architecture (at least what it was like back then) and I'm not so sure they would such a "Fairly Straightforward Ideas" at least from a technical point of view. They have a shared nothing approach to the data storage and Oracle is a shared everything. Merging systems like that would be tricky at best. But from a market share point of view, that's a whole different beast.

It will be interesting to see the results.

UPDATE!! The results are in! Click here to see the results.

Branded Steer

2010-07-13T06:42:00.001-07:00

Yes I do still eat ribs. This was at a place call Branded Steer. Great fall off the bone ribs and the beans were nice and sweet, maybe a touch too sweet. The place certainly had a "Texas" feel to it, even my waitress gave a decent shot at a Taxes drawl. Not bad considering I'm in Wisconsin.

Postings

2010-07-08T05:58:00.000-07:00

I have been trying to make some posts recently but my HTML skills are apparently not where they need to be. When trying to post code or an explain plan they just come out unreadable. And given that my goal is to talk about Oracle SQL (oh and food too) this really hampers my ability to post things. I may just have to find a different hosting service, or I might just have to buckle down and really learn HTML so I can make readable posts here. I know it can be done, I see lots of other folks posting code and such. Some I know are using screen shots which are OK, but I'm really hoping to not have to do that. I hope every one is having a good summer here in the Northern Hemisphere, and that the winter is kind to you in the Southern.

What’s going on with STAT lines in 10046 trace data?

2010-05-11T07:05:00.000-07:00

I’ve long been teaching folks about the value of using the STAT lines in 10046 Trace data to help in optimizing queries. With STAT lines it’s been quite easy to see which part of a plan takes up the most time and does the most LIOs (Logical IOs).

This is a simple plan from 10.2:

STAT #8 id=1 cnt=348 pid=0 pos=1 obj=0

op='SORT GROUP BY (cr=494 pr=19 pw=0 time=603190 us)'

STAT #8 id=2 cnt=2399 pid=1 pos=1 obj=0
op='HASH JOIN (cr=494 pr=19 pw=0 time=595897 us)'

STAT #8 id=3 cnt=437 pid=2 pos=1 obj=57036
op='TABLE ACCESS FULL ORD2 (cr=184 pr=11 pw=0 time=24947 us)'

STAT #8 id=4 cnt=70975 pid=2 pos=2 obj=57039
op='TABLE ACCESS FULL ORD_ITEM2 (cr=310 pr=8 pw=0 time=212985 us)'

Each of the above is one line; I’ve broken it into two to make it a bit easier to read.

The ID and PID show the relationship between the steps. The ID shows the order of the steps in the plan, and the PID shows which step is the parent of a step. Hence the last two lines are children of the second line, they each have a PID of 2. And the second step is the only child of the first step. The first step has no parent since its PID is 0 (zero).

The numbers in the parenthesis are the really cool ones. These numbers tell us about how much work was done for each row source operation, including its children. So for example, the CR value (which is the LIOs) in the HASH JOIN line of 494 includes the 184 and the 310 of the next two lines. So the HASH JOIN didn’t really do any LIOs. All the LIOs were in the two other steps. 184 + 310 = 494

Of particular interest can be the TIME value. It also includes the children of a step. Here we can see which step took up the most time. The HASH JOIN shows a TIME of 595, 897, if we subtract out of that the TIME of the next two steps 24,947 + 212,985 = 237,932 so 595,897 – 237,932 = 357,965. This tells us that even though the HASH JOIN didn’t do any LIOs it did take the longest time to run.

These TIME values are in Micro Seconds (that’s what the “us” is after the time, it’s really a µs.)

This is great stuff and I use this to drill right to the problem step of a plan. However things are not looking good with 11.

Here is a simple plan in 11.1.0.6 (side note, this is the good old EMP - DEPT join, which has been a NESTED LOOP join forever, notice in 11 it’s now a SORT MERGE plan):

STAT #6 id=1 cnt=13 pid=0 pos=1 obj=0
op='MERGE JOIN (cr=6 pr=0 pw=0 time=144 us cost=5 size=767 card=13)‘

STAT #6 id=2 cnt=4 pid=1 pos=1 obj=71198
op='TABLE ACCESS BY INDEX ROWID DEPT (cr=4 pr=0 pw=0 time=44 us cost=2 size=80 card=4)'

STAT #6 id=3 cnt=4 pid=2 pos=1 obj=71199
op='INDEX FULL SCAN DEPT_DEPTNO_PK (cr=2 pr=0 pw=0 time=26 us cost=1 size=0 card=4)'

STAT #6 id=4 cnt=13 pid=1 pos=2 obj=0
op='SORT JOIN (cr=2 pr=0 pw=0 time=22 us cost=3 size=507 card=13)'

STAT #6 id=5 cnt=13 pid=4 pos=1 obj=71200
op='TABLE ACCESS BY INDEX ROWID EMP (cr=2 pr=0 pw=0 time=72 us cost=2 size=507 card=13)'

STAT #6 id=6 cnt=13 pid=5 pos=1 obj=71205
op='INDEX FULL SCAN EMP_DEPT_IDX (cr=1 pr=0 pw=0 time=24 us cost=1 size=0 card=13)'

Well it may not be clear from just looking at the stat lines but the timings are a miss. The SORT JOIN step (ID 4) shows a TIME of 22 and it has one child (ID 5). This 22 can't included the time of step 5 which is 72 (the 24 in step 6 is part of the 72.) There seems to be a pattern of any time there is a sort in the plan, the timings seem to be off.

But wait it gets worse!

In 11.1.0.7 we see this:

STAT #17 id=1 cnt=13 pid=0 pos=1 obj=0
op='MERGE JOIN (cr=6 pr=0 pw=0 time=0 us cost=5 size=767 card=13)'

STAT #17 id=2 cnt=4 pid=1 pos=1 obj=77595
op='TABLE ACCESS BY INDEX ROWID DEPT (cr=4 pr=0 pw=0 time=0 us cost=2 size=80 card=4)'

STAT #17 id=3 cnt=4 pid=2 pos=1 obj=77596
op='INDEX FULL SCAN DEPT_DEPTNO_PK (cr=2 pr=0 pw=0 time=0 us cost=1 size=0 card=4)'

STAT #17 id=4 cnt=13 pid=1 pos=2 obj=0
op='SORT JOIN (cr=2 pr=0 pw=0 time=0 us cost=3 size=507 card=13)'

STAT #17 id=5 cnt=13 pid=4 pos=1 obj=77597
op='TABLE ACCESS BY INDEX ROWID EMP (cr=2 pr=0 pw=0 time=0 us cost=2 size=507 card=13)'

STAT #17 id=6 cnt=13 pid=5 pos=1 obj=77602
op='INDEX FULL SCAN EMP_DEPT_IDX (cr=1 pr=0 pw=0 time=0 us cost=1 size=0 card=13)'

Now all the TIME values are Zero!! Well maybe this is better, at least we don’t have numbers that don’t add up! Oh and before you ask, yes STATISTICS_LEVEL was set to ALL for these tests.

I’m not sure what is going on but this is a bad sign. We at one time had a great way to see what was happening for each line in a SQL statement. Now it appears that we are getting either bad data or no data. This is quite distressing.

I don’t have an 11.2 database currently to test on. Maybe it’s fixed, but I’m doubtful. Based on the bugs I’ve seen on Metalink that address this issue, the problem still seems to be there in 11.2.

In 11.1.0.6 the data in the view V$SQL_PLAN_STATISTICS and $SQL_PLAN_STATISTICS_ALL seemed to be unaffected by this. But in 11.1.0.7 this “feature” has gotten into those views as well.

My Oracle Book list

2010-03-22T06:06:00.001-07:00

This is a list of books that I've pulled together over the years. I hand out this list in most of my classes and several folks have mentioned that I should post it here as well. They are not in any particular order.

These are books that every Oracle professional should have on hand. It's not intended to be the "be all end all" list, just a list of books to start with. With these and the Oracle Documentation, you will be well on your way to understanding how the Oracle Database works. Some are a little old but those still have great information in them.

No I don't get any kick back on sales of these books, maybe I should... Hummmm....

· Cost-Based Oracle Fundamentals (Apress) By: Jonathon Lewis

· Practical Oracle8i(TM): Building Efficient Databases (Addison-Wesley) By:
Jonathon Lewis

· Optimizing Oracle Performance By: Cary Milsap, Jeff Holt

· Relational Database Index Design and the Optimizers By: Tapio Lahdenmaki,
Mike Leach

· Effective Oracle by Design (Osborne ORACLE Press Series) By: Tom Kyte

· Expert Oracle, Signature Edition Programming Techniques and Solutions for
Oracle 7.3 through 8.1.7 (Expert One-On-One) By: Tom Kyte

· The Art of SQL (Art of) [Illustrated] By: Stephane Faroult, Peter Robson

· Database in Depth: Relational Theory for Practitioners By: C.J. Date

· Expert Oracle Database Architecture: 9i and 10g Programming Techniques and
Solutions By: Tom Kyte

· Oracle Database 10g High Availability with RAC, Flashback, and Data Guard
(Osborne ORACLE Press Series) By: Matthew Hart, (and others)

· Oracle Dataguard : Standby Database Failover Handbook (Oracle In-Focus series)
By: Bipul Kumar

· Mastering Oracle SQL and SQL*Plus By: Lex de Haan

· Oracle Insights: Tales of the Oak Table By: Dave Ensor, (and others)

· Oracle Wait Interface: A Practical Guide to Performance Diagnostics & Tuning
(Osborne ORACLE Press Series) By: Richmond Shee, (and others)

· For PL/SQL – anything by Steven Feuerstein

Oak Table Comes to Michigan!

2010-02-14T11:02:00.000-08:00

The first Michigan Oak Table Symposium (MOTS) is happening this September! On the 16th and 17th.

Go here NOW to see more info and sign up, only 300 attendees will be allowed -> http://michigan.oaktable.net

There is a great set of speaker for this event and you wouldn't want to miss this!!

A partial list of speakers includes: Jonathan Lewis, Cary Millsap, Christian Antognini, Riyaj Shamsudeen, Randolf Geist, Charles Hooper, and many more.

(Find the Oak Tree in the above picture for a prize! Hint: There isn't one....)

Ho-ho-ho!

2009-12-18T12:13:00.001-08:00

Check it out! All of us at Hotsos have some seasons greetings for you at the Hotsos web site!

Happy holidays everyone!

-------------------------------------------------------------------------------

The 2010 Hotsos Symposium

2009-11-24T09:33:00.000-08:00

Hey everyone the Hotsos Sym is coming soon! March 7^thto the 11^th of 2010. There is a great group off speakers, including me! (Wow, how’d I slip in again, someone must not have been watching carefully! Hehehe.. ) Make sure you are there and sign up now.

Click here for a link to the official page.

Make sure to check out the videos on the page.

Tom Kyte is the Keynote and is sure to deliver an awesome presentation he will also do two more presentations during the Symposium. Tanel Põder as the trainer for the optional training day on the 11^th. Make sure to sign up for the training day when you sign up. Seating is limited for the training day, don’t miss it. Tanel is a fantastic presenter and trainer. His presentations are standing room only around the world. Don't miss this opportunity.

I hear the party night is schedule to be Disco!

Where are my white bell-bottom slacks, vest, big collar shirt and jacket.... Staying-alive-ah-ah-ah!

2009-11-02T15:03:00.000-08:00

I was recently in Houston TX teaching for two weeks and had the excellent opportunity to eat at a TEXAS ROADHOUSE while I was there. Yes it is a chain and sort of more know for stakes rather then ribs, but these were darn good ribs. I'm adding them to my list of restaurants worth going to if I find one in a town I'm in. And heck with free peanuts that you toss the shells on the floor, yeehaw! Count me in! :-)

Yes, this is the performance problem.

2009-10-03T11:32:00.000-07:00

Recently in a class of mine we were all reviewing a trace file (10046) of a transaction that ran on the client’s system. There was one SQL statement that consumed about 70% of the total time for the run. The statement was rather simple one table and one predicate. The predicate was a concatenation of several columns (each had an index) with an equality operator to a concatenation of a couple of binds.

The predicate looked something like this (it was longer and slightly more complicated, then this but this captures the flavor):

where COL1 || '-' || COL2 = :B1 || '-' || :B2

Pretty clearly the Optimizer can’t use the indexes on the columns because they are concatenated together in the predicate. The STAT lines weren’t in the trace data so we couldn’t see for sure but it was likely doing a full table scan which was driving about 60,000 LIOs for the statement. After some discussion several options were bantered about on how to fix the SQL, with a new index and rewriting the predicate being the top two.

As we were finishing up the discussion, the person who brought in the trace said “But this statement isn’t the performance problem.”

To which I replied “Oh yes it is.”

This is something that I’ve experienced before. Folks will think another SQL statement is the problem before they start and when it isn’t they still focus on the statement they think is the problem. We all do it. We have a preconceived idea of what the problem is and when faced with something else, we have a hard time getting over our preconceived idea.

This is the root of why having some one with little knowledge of the system can seem to have such great insight. Really it’s just that they have no preconceived idea of what is wrong. They can then follow the clues without prejudice and see what everyone else may have missed.

Cardinal Thomas Wosley (1471-1530) was an advisor to King Henry the VIII. He gave the King the advice of "Be very, very careful what you put into that head, because you will never, ever get it out." Several 100 years later this advice still rings true.

Temperatures

2009-08-13T10:02:00.001-07:00

Living in the Great State of Michigan in the USA gives me the opportunity to experience weather in all its ways. This is my thermometer on my back porch.

Ribs In New Jersey

2009-07-23T13:34:00.000-07:00

I'm here in New Jersey the Garden state of the USA. Went out for some good ribs at Houston's (181 Riverside Sq Mall Hackensack, NJ). Quite good. Very nice atmosphere and friendly staff. Although I do prefer a dry rub on the ribs the sauce they used was excellent. I also really liked the shoe string fries, they were nice and crunchy. The coleslaw was different from my norm and quite tasty as well. Great meal!

Were you there?

2009-05-23T09:14:00.000-07:00

Click here for a video of the 2009 Hotsos Symposium.

Will you be there next year?

BBQ in Phoenix AZ

2009-05-14T10:24:00.000-07:00

Ahhh... a great lunch at the BBQ Company here in Phoenix AZ. I got a 1/3 rack of ribs for lunch with some sweet beans and a corn medley. Excellent sauce, and the corn medley was particularly good. The only bummer about this place is that it's only open for lunch. This is now added to the list of places to get to while in Phoenix.

OK, now back to some SQL optimization... How many LIOs is that thing doing?!?!

Breakfast at Matt's

2009-05-13T09:48:00.001-07:00

One of my other favorite foods is Breakfast Food and in particular Waffles. Matt's Big Breakfast has the best Waffles I've ever had. And the other food there is excellent as well. I try to get to Matt's at least once each time I'm in the Phoenix/Tempe area in Arizona. This morning was the day for this week and my waffle was excellent as always!!

They open at 06:30 and the best thing to do is to be there at opening if you want a seat. There aren't many and they go fast!

The SQL runs fast but....

2009-05-12T07:41:00.000-07:00

For the most part I don't think any one would get to excited about dealing with a SQL statement that runs in .3 seconds. Most of us are worried about the statements that run for hours and we tend to focus on them. Which is not necessarily a bad thing. But how many statements on your system fit into this category I'm about to describe?

This statement on a per execution runs in .336 seconds, and does 1,627 Logical IOs. It's a pretty simple 3 way table join. All the predicates are simple equality ones that are ANDed together. The kicker is that this statement was called 5,935 times in a 40 minute window for a total of 9,658,869 LIOs and 275,527 Physical IOs and didn't return any rows. For this 40 minute trace this statement consumed 69% of the total response time and nearly all the PIOs via Sequential Read events. All this to get back nothing.

Maybe the statement can be optimized to do less work, but the real question is why do this at all? Is there a way to avoid running this statement at all?

It's easy to see why no one would have even looked at this SQL in the conventional tuning type engagement. It's fast, the LIOs per execution aren't necessarily bad either given that the tables involved are rather large. Looking at it from a per execution basis, there isn't much reason to get excited. But when looked at with in the context of a running application, it's easy to see that this statement is doing a lot of work for nothing.

Use the Force!

2009-05-02T17:24:00.000-07:00

I was out this morning and saw this group at a local comic book store. I just couldn't resist getting a picture with them. They didn't have any BBQ...

Intelligent Cursor Sharing in 11.1.0.7 More testing

2009-04-08T12:27:00.000-07:00

I’m continuing my research on Intelligent Cursor Sharing (Adaptive Cursor Sharing). At the symposium this year one thing I pointed out was the range in the view v$sql_cs_selectivity view. There is a high and low to each range. Looking at these values for a set of queries there is a pattern to what is going on but exactly what it means is a bit less then clear to me.

It starts out pretty simple. When I run my simple query:

select /* RVD */ count(*) from big_tab where object_type = :obj_typ

With a couple of values is seems pretty simple. I run it with the values “RULE”, then “SYNONYM” twice, this kicks in Cursor Sharing. And I see this in the v$sql_cs_selectivity view:

LOW = 0.351151
HIGH = 0.429185

Doing a bit of arithmetic and it is easy to find that the midpoint of this range is .390168. The selectivity for SYNONYM in this table is 0.390168 so it’s very clear that this is the midpoint of this range. The span of the range is .078034 from the low to the high values.

I run another query with the value set to INDEX, and a new range is created with:

LOW = 0.044807
HIGH = 0.054764

Doing the arithmetic again the selectivity of INEX (.049785470) falls on the mid point, .0497855. But the span of the range from low to high is different: .009957.

If I run with several other values I end up with 6 child cursors and the low high look like this:

A couple of interesting points about this, the span between high and low generally fluctuates, child number 5 and 4 are nearly the same, but the others differ by quite a bit. Only cursor number 5 and 6 are sharable from V$SQL which means they are the only ones that are useable.

Cursor 5 is uses and INDEX RANGE SCAN and cursor 6 uses a INDEX FAST FULL SCAN. Cursor 5 has a Span from low to high of 0.000013 to 0.077194
The selectivity for the queries that use cursor 5 are:

0.000015 (RULE)
0.049785 (INDEX)
0.000073 (LOB)
0.039517 (TABLE)
0.070177 (VIEW)
0.000102 (CONTEXT)
0.000160 (JOB)
0.000145 (CLUSTER)
0.000015 (EDITION)
0.018602 (PACKAGE)

Cursor 6 has a span from low to high of 0.290623 to 0.429185. The selectivity for the queries that use cursor 6 are:

0.390168 (SYNONYM)
0.322915 (JAVA CLASS)

So there seems to be some correlation between the selectivity and the range. It appears that after a few runs the Cursor Sharing is able to figure out it doesn’t need more cursors. Somehow it also seems to come up with a decent ranger for each, how the range is calculated is still a bit of a mystery. Also there is a gap between the ranges used for 5 and 6. So far this is just with one BIND in the statement. Next to move on to multiple BINDs and see what happens there.

Note: If you are looking at doing some test like this on your own, the high and low values are stored as varchar2 data. And at least on my windows box were stored in a multi-byte char set. To do any arithmetic with them I had to do this to get the values as a number:

(TO_NUMBER(TO_SINGLE_BYTE(HIGH))

BINDs and Selectivity Calculations

2009-04-02T08:59:00.000-07:00

One of the issues with doing an explain plan vs an execution plan is that if at explain time you use a bind the optimizer will "assume" a selectivity for the bind. If it's an equality operator it will use the density value from the stats, which is 1/NDV (NDV - Number of district values).

(Note: These test runs on a 11.1.0.7 database, but I have seen the same values on a 10.2.0.1 database as well.)

Given this SQL:
select /* RVD */ count(*) from big_tab where object_type = :obj_typ1
/

An explain plan shows:

INDEX RANGE SCAN on BIG_OBJTYPE_IDX with estimated rows of 59464

The table has 2,200,160 rows in it, the desity is 1/37 = .02702702702703

Doing the arithmetic: 2200160*.02702702702703 = 59463.78378379 so it sure looks like that is the calculation going on.

Interesting note is that when there is a histogram on the column, the stored density in the stats shows a .00000, not the .02070207 number. The optimizer must just do the calculation during the parse of the statement.

OK, how about when we use other operators? What selectivity does it use for them?

With <, <=, >,>=, LIKE, and BETWEEN I got this:

INDEX RANGE SCAN on BIG_OBJTYPE_IDX with estimated rows of 110K

Doing a bit of arithmetic, this is 5% (2200160*.05=110008).

This seems to prove that with a BIND it will go with 5% selectivity, except for the equality operator where it will use the 1/NDV calculation.

BBQ in Vancouver BC Canada

2009-04-01T14:12:00.001-07:00

The other night I went to Dix BBQ and Brewery here in Vancouver. The food was good, but the beer is really their trade. I had ordered a Margareta, mostly because I really like tequila. Beer and I haven't been getting along as well these days... I'll head back there again and try some more of the beer, I did have a small sample the other night and it was very good.

Fogo de Chão - Chicago

2009-03-27T05:51:00.000-07:00

OK not exactly the BBQ I usually get, but some darn good meat for sure! I think I eat enough meat last night to feed a village some where. Excellent cuts and salty which I really like. Had great conversion with a SQL Server guy, whom I'm working on converting to the light side. Great place for a good dinner for those coming to Chi-Town, Fogo de Chão.

Count(*)

2009-03-26T08:28:00.001-07:00

For years I've heard folks promote the idea that COUNT(1) is better then COUNT(*). This is not true, and I'm pretty sure this has never been true. Here is a very simple test in 11.1.0.7. If some one can prove to me that this works different in a different version of Oracle, please let me know.

Here is a count(*):

Here is count of a constant:

Both plans do exactly the same thing, a fast full index scan on the primary key column of the table. Oracle will use an index on a non-null column for the count, it doesn't have to be the primary key.

Intelligent Cursor Sharing in 11.1.0.7

2009-03-20T11:28:00.000-07:00

I've installed 11.1.0.7 and it does seem that Intelligent Cursor Sharing is smarter in this version. When I did the testing in 11.1.0.6, I had a SQL statement that generated 10 child cursors, with 11.1.0.7 the same test only generates 6 child cursors. So it's better.

The test case is this SQL:

select /* RVD */ count(*) from big_tab where object_type = :obj_typ
/

The object type column has some good skew to it, some values only appear tens of times and others many thousands. The bind (:obj_typ) is then set to 12 different values.

With 11.1.0.6 I got the following when I looked at V$SQL:

With 11.1.0.7 I got the following when I looked at V$SQL:

I know the code here is hard to read, an important column is the first one which is the "IS_SHARABLE" column. This more of less says will this cursor be used in the future. With it set to N it wouldn't be. So in both these versions only the last two cursors are sharable (usable).

There are only two different plan for each version. An index range scan, and an index fast full scan.

For the 11.1.0.6 version the index range scan is use for child cursor 0, 2, 3, 4, 5, 6, 7, 8, and 9. Only child cursor 1 and 10 have the index fast full scan.

For the 11.1.0.7 version the index range scan is used for child cursors 0, 2, 3, 4, and 5. Again only the second and last (child number 1 and 6) have the index fast full scan.

This is definitely an improvement. I hope to see that we get to a point with a simple query like this that we only have 3 plans, cursor 0 and then the two plans that are used. Because of the nature of how Intelligent Cursor Sharing works, cursor 0 will always be an unused cursor once Intelligent Cursor Sharing kicks in.

Windows....

2009-03-19T13:17:00.000-07:00

Last night I was trying to apply the 11.1.0.7 patch to my 11 Database on my laptop. I kept getting this error:

I checked and checked, all services were shutdown, no other programs were running. I check MetaLink for anything like this, I search with Google. Nothing.

So out of desperation I reboot my laptop. Sure enough the install runs without a hitch...

Intelligent Cursor Sharing in 11.1.0.6

2009-03-16T07:36:00.000-07:00

In my presentation at the 2009 Hotsos Symposium I talked about a new 11g feature called Intelligent Cursor Sharing. It was originally called Adaptive Cursor Sharing. The basic premises of the new feature is that a cursor with a bind might have more then one best path depending on which value the bind happens to be. This is a classic problem that we all have had to deal with at one level or another. We have a data column with just enough skew in it that a maybe a full table scan is the right thing to do for some values and that an index scan is right for others.

A histogram will let the optimizer know about the skew but with bind peeking, that histogram will only be used at the initial hard parse. So who ever gets there first will get the right plan of them, however if the next run would be better with a different plan then the performance will suffer for queries wanting a different plan.

Intelligent Cursor Sharing to the rescue! Now with this feature you will still suffer some pain, but over time the pain will subside, that’s the plan anyway. Now the sequence of event should go like this.

Someone runs a plan with a value that is best done with an index scan for example.

The next person’s value would be best with a full table scan. This person will still suffer the pain of having the wrong plan for this initial run. But the next time they run this Oracle will know that it wasn’t such a good idea and re-parse the plan. There will now be a couple of child cursors for the statement. The idea being based on which value comes in Oracle will pick the right plan for the given values.

The problem appears to be that the optimizer doesn’t seem to know that the plan it came up with might be the same one it just used. I showed a very simple statement, ran it with 12 different values, and ended up with 11 cursors, but only 2 plans. 8 of the cursors had one plan (an index range scan), only the second one and that last one had a different plan (both were an index fast full scan). One member of the audience told me he had a client with over 32,000 child cursors! Clearly this isn’t working quite as expected.

I had a few folks tell me this is fixed in 11.1.0.7, I have just pulled that patch set down and will be testing this over the next few days.

BBQ in Rochester NY

2009-03-16T06:28:00.000-07:00

Speaking of BBQ, the week before I was in Dallas for the Sym I was in Rochester NY. I hit two quite good BBQ places. Dinosaurs BBQ and Sticky Lips.

This is pulled pork at Dinosaurs:

And a full rack of ribs at Sticky Lips, one half with sauce and the other with a dry rub. I really like a good dry rub and this was a good one!

Both are good. I'd recommend both to anyone wanting some BBQ. Sticky Lips was a bit harder to find. But not that hard. Neither one has parking really close by. Dinosaurs is right down town on the river so if your lucky (like I was) you can parallel park on the street out front, or you parallel parking gives you the shakes, across the bridge there is a lot you can park in. Sticky Lips there is a large parking lot just a short walk behind the restaurant.

Hotsos Sym 2009

2009-03-14T08:23:00.000-07:00

Once again a great symposium has come to an end. Outstanding speakers great friends and coworkers, super food and of course a great Pirate Party on Tuesday night!

Chris Date started us off with a drop back to academia to let us all know what we are doing wrong with our databases. Always good to get back to the Fundamentals. Just like in sports if your team doesn't do the fundamentals right, then it unlikely that your team will reach the playoffs.

I had the honor of working directly with Chris to help him set things up and keep things moving smoothly for him. We had the opportunity to just sit and chat a couple of times. He was quite interested in talking about many other things outside of the database world and that was refreshing to step out of the professional world for a moment.

I did a presentation on the new 11g feature "Intelligent Cursor Sharing", I must admit I was a bit hung over from the party the night before so I did get off to a rough start. But we had a lively conversation with about what appears to be a bug with this in 11.1.0.6, hopefully this is fixed in the .7 patch set and I will be checking that shortly.

Jonathan Lewis gave the one day of training on Thursday and it was great as always. Great talk on troubleshooting.

One disappointment was there was not much on the BBQ fount for food this year. I'll have to make a comment about that...

Do what you can to get there next year!