Tuesday, May 03, 2005

IOUG-A

It was a busy day yesterday - had two talks. The first was "All about Binds", talked for an hour and at the end, was asked to stay and talk more -- many people stayed. So, that went for about 1-3/4 hours. It is one of my favorite talks -- covering binds from a performance (wall clock) perspective, memory utilization, cpu consumption, scaling issues on multi-cpu machines, to security issues, bind variable peeking, cursor_sharing, and why isn't my SQL getting shared.

The second talk was at 8:15pm that night - all about "Why". It was just about a 15 minute talk followed by Q&A from the audience. I was frankly amazed at the turn out at that late time of night. It was a full room and got lots of good questions. All in all -- a pretty good session. I got out of there at about 10pm and went to sleep (easily)...

But, back to real life again, have a plane to catch and get back to work. Unfortunately, I cannot stay the week here at IOUG, had to do a hit and run this year.
POST A COMMENT

19 Comments:

Blogger Jeff Hunter said....

Your talks are so well attended because your opinion is highly respected. You could talk about chaning diapers scheduled for 04:30 and it would be attended by 90 people. You are the Warren Buffet of the Oracle world. Looking forward to hearing you at the NYOUG in July.

Tue May 03, 09:03:00 AM EDT  

Anonymous Anonymous said....

What happened to Question Authority slides?

Tue May 03, 09:10:00 AM EDT  

Blogger David Aldridge said....

How did the Q&A go? Will you be writing something up on the feedback you got?

Tue May 03, 10:20:00 AM EDT  

Anonymous Anonymous said....

Tom ,
Do you have any IOUG event planned for Toronto in new future ?

Will be looking forward to attend one whenever its available.

Tue May 03, 11:35:00 AM EDT  

Anonymous Anonymous said....

Tom,
Do you have any event planned at Toronto in near future ? We will be looking forward to attend whenever its available .

Your blog takes us inside Tom's personal world as well and its quite refreshing to read ur comments . The only problem is that I seems to be going less on asktom now and more on this blog ... :)

Tue May 03, 11:38:00 AM EDT  

Blogger Thomas Kyte said....

What happened to Question Authority slides?

I use those frequently, only had an hour and too much material for the All about Binds hour as it was :) Not even an intro slide for that one.

How did the Q&A go

It went well, as I recall had questions (this is not all of them I'm sure) about

o shared server, there was a concern that because it used s000 95% of the time, s001 4% of the time and s002 1% of the time it wasn't working right. They thought it would be best if it was spread out. For shared server, it doesn't really matter -- as long as the messages in the queue in the sga are getting processed as they arrive it really doesn't matter which one gets it and does it.

o merge and the inability to change the values in a given row twice in the same statement:

select 'T1', t1.* from t1 union all select 'T2', t2.* from t2;

'T X Y
-- ---------- ----------
T1 1
T2 1 1
T2 1 2

merge into t1
using t2
on (t1.x = t2.x)
when matched then update set y = t2.y
when not matched then insert values ( t2.x, t2.y );
using t2
*
ERROR at line 2:
ORA-30926: unable to get a stable set of rows in the source tables

The problem is it would not be deterministic, the order of the rows on disk from T2 would affect the final value in T1 (would or should Y be 1 or 2 at the end?)

The questioner said "well, what about order by", but the problem is -- you can order T2 -- sure, but when we MERGE t2 into t1 -- the concept of order disappears again (say we hash outer join -- in 10g that can go from either table -- and we pick T1 to drive the join?)

I should have (but didn't think about it, or at least didn't think of a way to state it on stage without sqlplus) said:

merge into t1
using
(select *
from ( select x, y,
row_number()
over ( partition by x
order by y ) rn
from t2 )
where rn = 1 ) t2
on (t1.x = t2.x)
when matched then update set y = t2.y
when not matched then insert values ( t2.x, t2.y );

1 row merged.


Use analytics to identify the row you wanted to keep by join key (partition by JOIN_KEY)


o questions about storage clauses -- I'm a fan of LMT's with system allocated extents for many tables, maybe using uniform extents in a data warehouse where you might have a better idea on the ultimate size of stuff.

o question on exp/imp and the storage clause -- specifically, how to get rid of it (since the initial extent from production was way too large). No good answer really -- not until 10g with data pump. dbsm_metadata.get_ddl was tossed out as something easier than using the indexfile=foo.sql option on IMP and editing the create table statements.

o questions on upgrades, going from 8i to 10g and should we rebuild or just upgrade.

o how to capture data from a transactional system, should we be looking at data guard, CDC (change data capture) or MV's... Said the order I would probably evaulate them would be logical standby, streams, mv's and then CDC (but only because I've no real experience with CDC to take that in mind when evaluating the advice!)

o a question on unbalanced freelists which went ignored :)

Tue May 03, 12:44:00 PM EDT  

Blogger Thomas Kyte said....

Do you have any event planned at Toronto in near future

October 3rd, 2005.

The event will be held on Monday October 3rd in the Canada's Oracle Headquarters in Mississauga (Toronto).

Tue May 03, 12:47:00 PM EDT  

Blogger melanie caffrey said....

a question on unbalanced freelists which went ignored :)

I too had to do a hit and run at IOUG and leave on Tuesday ...

... but I *do* remember the unbalanced freelists question. :)

Wed May 04, 08:55:00 PM EDT  

Blogger Thomas Kyte said....

Melanie,

I too did the hit and run, I left tuesday morning myself. Good to bump into you there!

Next time Tanel is on stage speaking, I am so going to ask him a question...

Revenge is sweet ;)

Wed May 04, 09:01:00 PM EDT  

Blogger melanie caffrey said....

Next time Tanel is on stage speaking, I am so going to ask him a question...

Revenge is sweet ;)


Too true.

Just hope I'm there to see it. :D

Wed May 04, 09:54:00 PM EDT  

Anonymous rbaraer said....

Tom, just for information, have you ever come to France for a conference or seminar ? Are you by any chance planning to do so in the near future ?

It would be a great pleasure to come to one of your talks.

Thu May 05, 10:42:00 AM EDT  

Blogger Thomas Kyte said....

have you ever come to France for a conference or seminar ?

Yes, but it was a while ago (when 8.1.5 came out)...

I could see it happening again in the future -- I've been lucky enough to visit many places in Europe recently and don't see and "end" to that coming.

Thu May 05, 10:56:00 AM EDT  

Anonymous Anonymous said....

Tom, I also had the opportunity to catch your talk at IOUG this year. It was great, I can't believe I missed your Bind talk...! Anyway I've been writing to you for about 8 yrs & just wanted to say thanks!

Vinnie from Orlando

BTW, what again was your responce on the BLOB inserts?

Thu May 05, 12:56:00 PM EDT  

Anonymous Anonymous said....

What's wrong with 80's rock....I graduated in 77 so I am partial to 70s rock. But there was still some good music in the 80's!!

Vinnie:)

Thu May 05, 12:59:00 PM EDT  

Blogger Thomas Kyte said....

I also had the opportunity to catch your talk at IOUG this year

The blob question was about a system dealing with lots of BLOB items in jdbc -- what could be done for performance.

With not too much to go on -- I suggested they look at the CACHE option of the lob, and perhaps try a separate block size for the LOBs.

By default, lobs are not cached:

ops$tkyte@ORA10G> create table t ( x blob );
Table created.

ops$tkyte@ORA10G> select dbms_metadata.get_ddl('TABLE','T') from dual;

DBMS_METADATA.GET_DDL('TABLE','T')
-------------------------------------------------------------------------------

CREATE TABLE "OPS$TKYTE"."T"
( "X" BLOB
) PCTFREE 10 PCTUSED 40 LOGGING
...
LOB ("X") STORE AS (
TABLESPACE "USERS"
ENABLE STORAGE IN ROW
CHUNK 8192
PCTVERSION 10
NOCACHE
....
)

so, every retrieval and modification was a direct path read/write. We could "apparently" speed this up by caching the writes and reads. But in order to do it in a fashion that didn't push other useful bits out of the cache *this might be* a good use of a different block size. I'd want to (and have in my mind to do so) test that.

Thu May 05, 01:04:00 PM EDT  

Blogger jk said....

Oracle oracle better be careful, or AskTom will end up here ;)

Trying to find good instructional conference to attend, is OpenWorld too much marketing?

Fri May 06, 12:27:00 PM EDT  

Blogger Thomas Kyte said....

Trying to find good instructional conference to attend

what are you trying to learn?

Fri May 06, 12:40:00 PM EDT  

Blogger jk said....

Have been picking up Oracle DB piecemeal for 5 years. Passed the 1st SQL cert. test. Do a lot of installs and migrations, patches, backup and recovery. Senior DBA/ architect of our system got promoted to a CTO, Sounds like I'm going to need to learn Database Administration and Tuning quickly. So that I can deal our rapid growth. Working my way through Effective Oracle by Design and read the Oracle Concepts.

Mon May 09, 02:42:00 PM EDT  

Blogger Prasad Gunaratne said....

Tom,

Since lots of people don't even have a chance to attend your tech-talks, would it be possible to record them and publish on the Internet?

I am sure your talks will be highly valuable to the Oracle Community.

Prasad Gunaratne

Fri May 13, 05:35:00 AM EDT  

POST A COMMENT

<< Home