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?
4 Comments. Leave new
Just wait a month. $140K USD might be like 10 euros by then.
Don, in Europe, you’ll have to pay more than 100K€ I think. It’s like on amazon, you cannot specify a delivery address that’s not in northern america.
So how believable are those figures.100% improvement seems a nice round figure.
I must confess that I’ve built this example to get to that “nice” figure