Breaking News: How Fast Can Your Query be With Exadata?

There has been a lot of buzz about the Oracle Exadata Storage Server these past few days. Did you know you can actually estimate the impact of it on some of your queries with SQL Performance Analyzer (SQLPA)? Here is the story.

First you need to install an 11.1.0.7 database! Then you must load your data and capture your queries in an SQL Tuning Set. Below is a very simple and short example:

connect / as sysdba

create table demo (x number,
                   y varchar2(4000));

begin
    for i in 1..10000 loop
        insert into demo (x,y) values (i,rpad('X',4000,'X'));
    end loop;
end;
/

commit;

begin
dbms_sqltune.create_sqlset(
           sqlset_name=>'DEMOSTS',
           description=>'SQL Tuning Set to evaluate Exadata');
end;
/

exec dbms_application_info.set_module('TUNING', null);

select x from demo  where x=1;

X
-
1

exec dbms_application_info.set_module(null,null);

DECLARE
   l_cursor DBMS_SQLTUNE.sqlset_cursor;
BEGIN
   OPEN l_cursor FOR
   SELECT VALUE(p)
      FROM TABLE (DBMS_SQLTUNE.select_cursor_cache (
                   'module = ''TUNING''', -- basic_filter
                   NULL, -- object_filter
                   NULL, -- ranking_measure1
                   NULL, -- ranking_measure2
                   NULL, -- ranking_measure3
                   NULL, -- result_percentage
                   1) -- result_limit
                 ) p;  

DBMS_SQLTUNE.load_sqlset (
               sqlset_name => 'DEMOSTS',
               populate_cursor => l_cursor);
END;
/ 

col sql_text format a50
SELECT sql_id, sql_text
    FROM TABLE(DBMS_SQLTUNE.select_sqlset ('DEMOSTS')); 

SQL_ID	      SQL_TEXT
------------- --------------------------------------------------
4ynqdxdhu08p1 select x from demo  where x=1

Once that is done, all you have to do is to run the tcellsim.sql script located in the $ORACLE_HOME/rdbms/admin directory of the 11.1.0.7 software and let it guide you through. Here’s an example of the use of that script:

(If you cannot see the output in an iframe, here it is: exadata.html.)

I guess it’s a bit optimistic. All I need now is 140k USD to make sure that’s the case. Can anyone send me the money?

Interested in working with Grégory? Schedule a tech call.