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!
