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 18.104.22.168 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 22.214.171.124 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 Responses to “Breaking News: How Fast Can Your Query be With Exadata?”
Leave a Reply