Search

OakieTags

more tags

Who's online

There are currently 0 users and 26 guests online.

Recent comments

Affiliations

Home » Monthly archive

September 2010

MOTS Mini – Moans The Magnificent

Submitted by marco on Thu, 2010-09-16 17:13

Michigan Oak Table Symposium 2010 (MOTS) in Ann Arbor, MI, USA

Submitted by joc on Thu, 2010-09-16 16:03

This morning we started with probably the biggest gathering of OakTable members ever. We gathered in Ann Arbor in Michigan for a two day event just before the Oracle Open World in San Francisco. The seminar is running in 3 parallel sessions.

OpenWorld 2010 Session Update. Room Change Again.

Submitted by kevinclosson on Thu, 2010-09-16 15:00

The OOW folks informed me that they needed to move our session to a different room–again. So, if you are interested here are the new details: ID#: S315110 Title: Optimizing Servers for Oracle Database Performance Track: Database Date: 20-SEP-10 Time: 17:00 – 18:00 Venue: Moscone South Room: Rm 102

MOTS Mini – A Great Start

Submitted by marco on Thu, 2010-09-16 14:07

Dinner was great last evening, I had a huuuuuuge steak (although Carol Dacko’s was bigger). Moans got his annual birthday gift, A great Arabian Nights kind of hat, and the 25+ people that attended the dinner had a good meal with great service at the  “Chop House” restaurant.
image

MOTS is about to start. After a good nights sleep (me: no extra alcohol infusion but straight to bed), I am now looking outside and really feel at home. Its cosy, its rainy and it will be fun despite, or probably because, the amount of attendees are approximately roundabout 50+ (Oakies not included). So this gives us the advantage to pinpoint on peoples work related, technical questions. If doable I will switch my demo session today with examples/demo’s explaining peoples the questions at hand.

image

Moans will open this years MOTS symposium. I am looking forward to it, if not only, because I already had some almost Dutch strong coffee in the lobby with a nice cone.

Captain Support and the International Printer Incident…

Submitted by oraclebase on Thu, 2010-09-16 13:37

When I was in Frankfurt airport I got an email from my mom telling me that my sister-in-law was having trouble with here printer. This sounds like a job for Captain Support…

Like any self respecting support super hero Captain Support ignored the email. When he woke up this morning Captain Support had an email from his sister-in-law telling him not to worry about it until he got back to the UK. Captain Support posted a couple of suggestions, just in case it was a simple issue.

When Captain Support gets back to the UK he may decide to switch from wireless printing to a good old-fashioned cable…

Cheers

Tim…

Update: It looks like the default printer was pointing to the wrong printer. Captain Support triumphs again…

Frankfurt to San Francisco…

Submitted by oraclebase on Thu, 2010-09-16 13:20

I posted yesterday about the start of my trip to OpenWorld. I arrived late, so I never got around to posting about the second half of my trip so here goes…

Frankfurt to San Francisco

I was in a pretty bad mood when it came to boarding. We were 4+ hours late and the boarding process was a complete disaster. Nobody could hear the announcements, so we were all in queues to ask what the hell was going on. Even so, nobody on the desk thought to just come and shout out what to do. As a result, boarding was like a rugby scrum, with people from all classes, seating zones and people who had not cleared security all thrown together. I don’t remember ever taking so long to board. Sigh.

I got on the plane and was instantly more miserable. It was one of those planes where you all have to watch the same films on shared screens. Sigh. I watched:

  • The Back-up Plan: Nauseating romantic comedy starring Jennifer Lopez.
  • Iron Man 2: In my opinion it wasn’t as good as Iron Man 1, but it was still pretty cool.
  • Just Wright: Chick-Flick. Would have hated it, except I had used up all my hate on the Jennifer Lopez film.

The food came along and guess what? I wasn’t on the list of vegetarians. Sigh. Luckily, they had extra so it wasn’t a big deal, just another thing to “brighten” my day.

Sequence Smackdown

Submitted by ddelmoli on Thu, 2010-09-16 02:57

A short post where I kick myself for forgetting something basic…

In recent engagement, I come across a "smelly" construct (database smells) that looks like this:

Select max(errorid)+1 into newerrorid from errorlog;

"Why aren’t they using a sequence?", I wondered.

The reason, of course, is that the PL/SQL developers need to request the creation of each and every object from the production support DBAs, and since such requests require review by the central data architects for correctness before being approved for creation in development, the process can take 4-5 days. As a result, they took this "shortcut". (Reason #392 of why I don’t think production support DBAs should have any place in the development process, but that’s another story).

The good news is that they recognized this was bad after I pointed it out, and they went ahead and requested the sequence.

One week later, we get the sequence, correct the code and promote it to the integration environment.

Where we promptly get uniqueness violations when attempting to insert rows into the table because the sequence number was less than the max(errorid) already in the table.

"No problem!", I said – I didn’t want to re-create the sequence with a larger "start with" (due to the turnaround time), so I take a lazy shortcut:

Declare
I number;
J number;
begin
select erroridseq.nextval into I from dual;
select max(errorid) into J from errorlog;
while I <= J loop
select errorid_seq.nextval into I from dual;
end loop;
end;
/

Yes – I know this is all kinds of horrible, but I was in a hurry and didn’t think.

And the worst part is that it didn’t even work.

They still got uniqueness violations and came to me later saying that there were problems with the sequence – that when they selected maxval from the sequence in TOAD they got one value (1000), and when they selected maxval from the sequence via SQL Developer, they got another value (300).

What did I forget / do wrong?  What should I have done?

I eventually figured it out and "fixed" it.

There’s a coda to this – after I smacked the palm of my hand to my forehead and then explained the problem to the PL/SQL developers I thought they understood it. But later in the day they came to me and said they were having the same problem with a different sequence (getting different – and much smaller – values when selecting maxval from different tools)…

I should have done a better job of explaining it.J

MOTS Mini – Ann Arbor – Oakie invasion

Submitted by marco on Thu, 2010-09-16 01:54

image

Almost all speakers have arrived and it looks it will be a great gathering off techies and in-depth Oracle presentations. I think, guess, allthough it starts tomorrow, it will be cosy, special, and a great entree towards OOW.

Tomorrow I don’t have the same timeslot as Tanel. Thank god. This time is only a guy called Cary Millsap. Sounds doable.

;)

image

Now its time for a steak…

Quest vs Embarcadero : SQL Tuning

Submitted by khailey on Wed, 2010-09-15 23:07
I get asked regularly what does Embarcadero's SQL tuner have that is any different than Quest's SQL tuner. The HUGE difference between Quest and Embarcadero’s approach is the Visual SQL Tuning (VST). Without VST most automated SQL tuning turns into dead ends. Why dead ends? Because in general the databases are going to correctly optimize queries. Queries are rarely incorrectly optimized. In those rare instances where a database get’s it wrong, then the approach of blindly generating and testing alternative execution plans can be helpful but only helpful in the case where queries are small enough that many alternative cases can be tested.

If on the other hand the query is long running say, a day, then testing alternative cases will not lead to results for easily several days. On the other hand with VST diagram we can quickly identified the best execution path using the diagram and guide the database to use this path.

When is Exadata’s storage indexes used?

When is Exadata’s storage indexes used?

Exadata’s Storage indexes provides data pruning at the storage layer.  The storage indexes eliminating disk IO, by storing a summary of the data distribution on the disks.    MOS Note ID 1094934.1 provides the following summary:

Each disk in the Exadata storage cell is divided into equal sized pieces called storage regions (default 1MB). There is an index entry for every storage regions (1MB of data stored on disk). Each entry contains the minimum and maximum value for columns seen in ‘where’ clause predicates. Information for up to 8 columns can be stored. The index is then used to eliminate disk IO by identifying which storage regions don’t match the ‘where’ clause of a query.

The question I’ll try help answer in this blog post is, when Oracle can take advantage of the storage indexes, by sharing some of the test results and findings I came across when testing storage indexes.  The data used for these tests are simple versions of a dba_tab_columns including an id column.   The tests were performed on a quarter rack ExaData database machine (2 db nodes – with 16 cores each and 3 storage servers)

Create sorted and unsorted Test data for storage index testing
 SQL> create table pba_order AS SELECT rownum pba_id, column_name, owner, table_name
            from (SELECT column_name, owner, table_name
                          from dba_tab_columns where rownum <= 10000),
                      (select rownum from dual CONNECT BY LEVEL <=20000)
          order by pba_id;
Table created.

SQL> select count(*) from pba_order;
          COUNT(*)  --  200,000,000
 
SQL> select count(distinct(pba_id)) from pba_order;
           COUNT(DISTINCT(PBA_ID))   --      200,000,000
 
SQL> create table pba_rnd as  select * from pba_order order by DBMS_RANDOM.RANDOM;
Table created.
 
Create a few null value entries for id column
SQL> update pba_rnd set PBA_ID = null where pba_id between 100 and 120;
21 rows updated.
 
SQL> update PBA_ORDER set PBA_ID = null where pba_id between 100 and 120;
21 rows updated.
SQL> commit;
 
Collect CBO statistics for tables
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS('JA2','PBA_RND');
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS('JA2','PBA_ORDER');
PL/SQL procedure successfully completed.

 SQL> select TABLE_NAME, NUM_ROWS, SAMPLE_SIZE, LAST_ANALYZED from user_tables where table_name like '%PBA%'; 

TABLE_NAME     NUM_ROWS SAMPLE_SIZE LAST_ANALYZED
PBA_RND 200000000 200000000 13-SEP-10
PBA_ORDER  200000000 200000000 13-SEP-10


Check the storage session statistics before testing

Name Value
cell physical IO bytes saved by storage index 0
 
Storage indexes works on the columns in your where clause, so let’s check out a few different predicates, please notice that I have not created any indexes on either of the tables.
SQL> select count(*) from PBA_RND where pba_id is null;     -- IS NULL on random sorted data
  COUNT(*)  --    21
 Elapsed: 00:00:00.04
 
Name Value
cell physical IO bytes saved by storage index  10303389184
 
SQL>  select count(*) from PBA_ORDER where pba_id is null;    -- IS NULL on sorted data
    COUNT(*)   --     21
Elapsed: 00:00:00.04 
 
Name Value
cell physical IO bytes saved by storage index  20866007040
 So the entry for the storage index column can contain a NULL value unlike regular b-tree indexes, which is also what Kerry Osborne blog post on Exadata offload confirms http://kerryosborne.oracle-guy.com/2010/06/exadata-offload-the-secret-sauce/#more-2531

Check the storage session statistics before testing

Name Value
cell physical IO bytes saved by storage index  72634597376


SQL> select avg(pba_id) from pba_order where pba_id between 1000000 and 1000999;   - Range scan on sorted data.

    AVG(PBA_ID)  --    1000499.5
1 row selected.
Elapsed: 00:00:00.05 
 
Name Value
cell physical IO bytes saved by storage index  82996625408
 
 SQL> select avg(pba_id) from pba_rnd where pba_id between 1000000 and 1000999;  -- Range scan on random sorted data.
AVG(PBA_ID)  --    1000499.5
 Elapsed: 00:00:05.68 
 
Name Value
cell physical IO bytes saved by storage index  82996625408

I tried a few more test scenarios, with forcing direct reads, using parallel query but it did not make any difference for this SQL.  For the between test, I could only get the storage index to kick in for the ordered data.

Check the storage session statistics before testing

Name Value
cell physical IO bytes saved by storage index  93359243264


SQL> select avg(pba_id) from pba_order where pba_id <100;      -- LESS than on sorted data

AVG(PBA_ID)  --    50
Elapsed: 00:00:00.04
 
Name Value
cell physical IO bytes saved by storage index  103721861120
 
 SQL> select avg(pba_id) from pba_rnd where pba_id <100;       -- LESS than on unsorted data
    AVG(PBA_ID)  --        50
 Elapsed: 00:00:00.13
 
Name Value
cell physical IO bytes saved by storage index 113980342272
 
 SQL> select avg(pba_id) from pba_order where pba_id > 199999000;   -- greater than on sorted data
    AVG(PBA_ID)  --     199999501
Elapsed: 00:00:00.06
 
Name Value
cell physical IO bytes saved by storage index 144962977792
 
 SQL> select avg(pba_id) from pba_rnd where pba_id > 199999000;  -- greater than on unsorted data.
AVG(PBA_ID)  --    199999501
 Elapsed: 00:00:00.62
 
Name Value
cell physical IO bytes saved by storage index 154317127680

So we can confirm that Oracle can use storage indexing for both less and greater than predicates.

Check the storage session statistics before testing

Name Value
cell physical IO bytes saved by storage index 174033272832
 SQL> select pba_id from pba_order where pba_id = 100000000;   -- equal sign on sorted data
    PBA_ID  --   100000000
 Elapsed: 00:00:00.05 
 
Name Value
cell physical IO bytes saved by storage index 184395268096
 
SQL> select pba_id from pba_rnd where pba_id = 100000000;    -- equal sign on unsorted data
    PBA_ID  -- 100000000
 Elapsed: 00:00:05.66
 
Name Value
cell physical IO bytes saved by storage index 184395268096

Again I tried a few more test scenarios, with forcing direct reads, using parallel query but it did not make any difference in this case.  For the equal sign predicate test, I could only get the storage index to kick in for the ordered data.

Before finishing up, let’s check if storage indexes are kicking in on tables compressed with EHCC.   

Segment Name Size MB
PBA_ORDER 9,937
PBA_HCC 856

 

Table Name Last Analyzed Par Flash_C Cell_fl Compressed Compressed_for
PBA_HCC 14-SEP-10 No Default Default Enabled Query High Default
PBA_ORDER 14-SEP-10 No Default Default Disabled Default

Check the storage session statistics before testing 

Name Value
cell physical IO bytes saved by storage index 0

SQL>  select avg(pba_id) from pba_order where pba_id <100;

     AVG(PBA_ID) -- 50
 Elapsed: 00:00:00.13 
 
Name Value
cell physical IO bytes saved by storage index 10362617856
 

SQL> select avg(pba_id) from pba_hcc  where pba_id <100;
      AVG(PBA_ID)  --  50
Elapsed: 00:00:00.04  
 
Name Value
cell physical IO bytes saved by storage index 11097702400
 

I you like to read more on EHCC; I have blog post on EHCC with a few test-case and conclusions on http://www.oaktable.net/blog/what-can-ehcc-do-you

Conclusion

In Exadata, the Storage Index can evaluate predicates like <, <=, =, !=, >=, >, is NULL, is NOT NULL, as you saw above I was not able to get the storage indexes to kick in for the between and equal predicate for the random sorted data.    It may work in other circumstances or test-cases; currently it is very hard to evaluate, as there is no public tracing tool available. 

In general we saw marginal better IO savings on the test-case with sorted data and that storage indexes also worked on EHCC compressed tables.    I have also performed the same tests on a global temp table and the storage indexes did work with global temp tables as well.

Kerry Osborne has a good blog post, confirming that Storage indexes also works with bind variables: http://kerryosborne.oracle-guy.com/2010/09/do-storage-indexes-work-with-bind-variables/

The performance gained from the storage indexes really rocks my world and it made me review our design and index strategy for our warehouse.

Submitted by Pbach on Wed, 2010-09-15 20:26