Monday, November 7, 2011

The speakers have been chosen for Sym ’12!!

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!!

Thursday, November 3, 2011

4th ANNUAL MICHIGAN ORACLE USER SUMMIT

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!!

Wednesday, September 7, 2011

Jonathan Lewis, for Hotsos Training Day 2012

 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.

Thursday, September 1, 2011

Lex DeHaan

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. 

Tuesday, August 30, 2011

Wanted: An Oracle Instructor

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

Tuesday, May 10, 2011

Registration is now open for Hotsos Symposium 2012!

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!!

Wednesday, March 30, 2011

It's the little things....

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.

Thursday, March 17, 2011

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

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)'

Tuesday, February 8, 2011

MBRC and DB_FILE_MULTIBLOCK_READ_COUNT

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.)