Monday, September 21, 2009

Exadata Simulation test

As you know, 11gR2 came with new feature that you can simulate performance effects
from Exadata Storage Server on your workload.

Just to check that functionality I installed Oracle 11g Release 2 on my local VM
(Oracle EL 5.2 32bit) and created test database called o1102

I created a user, a table with 5000000 entries, tuning set after that and executed analysis
using the script provided in $ORACLE_HOME/rdbms/admin

That's what I got:


create table t1 (c1 int, c2 varchar2(100), c3 date);

insert into t1 select rownum, dbms_random.string('X', 100),
sysdate + dbms_random.value(-100, 100) from dual
connect by level <= 5000000;

commit;
SQL> select count(*) from t1
2 where t1.c2 like '___AB%'
3 union all
4 select count(*) from t1
5 where t1.c2 like '___AC%'
6 /

COUNT(*)
----------
3789
3803

SQL> select sql_id, sql_text from v$sqlarea where sql_text like 'select count(*) from t1%';

SQL_ID SQL_TEXT
------------- --------------------------------------------------------------------------------
g64qhr1vp8zwr select count(*) from t1 where t1.c2 like '___AB%' union all select count(*) from
t1 where t1.c2 like '___AC%'

5d28bd9s5pc21 select count(*) from t1, t1 t2 where t1.c2 like '___AB%' or t2.c2 like '___AC%'



SQL> var
variable refcur
datatype REFCURSOR



SQL> exec DBMS_SQLTUNE.CREATE_SQLSET(sqlset_name => 'my_sts1', description => 'to test exadata');

PL/SQL procedure successfully completed.



SQL> exec open :refcur for SELECT value(P) FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('sql_id = ''g64qhr1vp8zwr''')) p;

PL/SQL procedure successfully completed.

SQL> exec DBMS_SQLTUNE.LOAD_SQLSET(sqlset_name => 'my_sts1', populate_cursor => :refcur, load_option => 'INSERT')

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> @$ORACLE_HOME/rdbms/admin/tcellsim.sql

10 Most active SQL tuning sets
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

NAME OWNER SQL_COUNT DESCP
------------------------------ ------------------------------ ---------- --------------------
my_sts1 AG 1 to test exadata

Specify the name and owner of SQL tuning set to use
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for sts_name: my_sts1
Enter value for sts_owner: AG

>> SQL tuning set specified: my_sts1 owned by AG

Run Cell simulation
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

>> 1. create a spa analysis task to test cell simulation
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

>> Name of SPA analysis task: TASK_16

>> 2. Test execute statements with cell simulatin DISABLED
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

>> 3. Test execute statements with cell simulation ENABLED
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

>> 4. Compare peformance and generate analysis report
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

General Information
---------------------------------------------------------------------------------------------

Task Information: Workload Information:
--------------------------------------------- ---------------------------------------------
Task Name : TASK_16 SQL Tuning Set Name : my_sts1
Task Owner : AG SQL Tuning Set Owner : AG
Description : Total SQL Statement Count : 1

Execution Information:
---------------------------------------------------------------------------------------------
Execution Name : EXEC_12 Started : 09/21/2009 16:12:00
Execution Type : COMPARE PERFORMANCE Last Updated : 09/21/2009 16:12:00

Description : Global Time Limit : UNLIMITED
Scope : COMPREHENSIVE Per-SQL Time Limit : UNUSED
Status : COMPLETED Number of Errors : 0

Analysis Information:
---------------------------------------------------------------------------------------------
Before Change Execution: After Change Execution:
--------------------------------------------- ---------------------------------------------
Execution Name : cell_simulation_DISABLED Execution Name : cell_simulation_ENABLED
Execution Type : TEST EXECUTE Execution Type : TEST EXECUTE
Scope : COMPREHENSIVE Scope : COMPREHENSIVE
Status : COMPLETED Status : COMPLETED
Started : 09/21/2009 16:11:05 Started : 09/21/2009 16:11:43

Last Updated : 09/21/2009 16:11:43 Last Updated : 09/21/2009 16:11:59
Global Time Limit : UNLIMITED Global Time Limit : UNLIMITED
Per-SQL Time Limit : UNUSED Per-SQL Time Limit : UNUSED
Number of Errors : 0 Number of Errors : 0

---------------------------------------------
Comparison Metric: IO_INTERCONNECT_BYTES
------------------
Workload Impact Threshold: 1%
--------------------------
SQL Impact Threshold: 1%
----------------------


Report Summary
---------------------------------------------------------------------------------------------

Projected Workload Change Impact:
-------------------------------------------
Overall Impact : 0%
Improvement Impact : 0%
Regression Impact : 0%

SQL Statement Count
-------------------------------------------
SQL Category SQL Count Plan Change Count
Overall 1 0

Unchanged 1 0

Top 1 SQL Sorted by Absolute Value of Change Impact on the Workload
---------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------
| | | Impact on | Execution | Metric | Metric | Impact |
| object_id | sql_id | Workload | Frequency | Before | After | on SQL |
----------------------------------------------------------------------------------------
| 6 | g64qhr1vp8zwr | -.01% | 1 | 1120731136 | 1120845824 | -.01% |
----------------------------------------------------------------------------------------


---------------------------------------------------------------------------------------------


It seems that there is no performance improvement for the query
that I executed but at least functionality was tested and might be it will show
something more encouraging to another queries.

Have a good day!