For edition (Enterprise or Standard) of the Oracle software (engine) installed, if still not created a database (you can remove the issue from the catalog of the base) and if the person who installed it can not remember which option chosen, you can run the following command from Unix / Linux you are looking for in the inventory: $ grep-w $ s_serverInstallType ORACLE_HOME/inventory/Components21/oracle.server / * / context.xml of statistics and system objects. This new feature is very useful to analyze the impact of change as it allows to "play" with the environment and easily make comparison reports, regression testing, impact load, etc.
Now I will build a test scenario in 11g to compare a simple count of the table T using rule-Optimizer (RBO) against the same count using the CBO optimizer. It is clear that this desoportado from 10g RBO and that the example does not represent a real case, or maybe yes, but will serve to show how it works step SPA and show how "blind" is RBO in some cases that seem intuitively trivial. I will create a table with 1M records T and PCTFREE 90% to consume many blocks such that the difference between the performances that I compare it more noticeable. Then I'll create a PK for the id field. With RBO will do a FULL SCAN on the table T, which does not realize that has a PK and I could do a full index scan is faster. CBO obviously realizes this as being just PK has the same amount of records to the table and therefore serves to answer the question of how many records have the table T.
create table t (id int, val varchar2 (10)) PCTFREE 90;
insert into t select rownum as, dbms_random.string ('a', 10)
from dual connect by rownum
alter
table t add primary key (id); test carried out the sentence, use a hint to be more easily located in the dynamic view and get your sqlid:
select count (1) / * + * Test SPA / from t;
sql_id select from v $ sql WHERE
sql_text like '% SPA% Proof' and sql_text NOT LIKE '% sql_text%';
5r2ufj2vqkk4pThe STS test you create is called, now has charge of the sentence from memory cursor: DECLARE
I have the sqlid
already, so I'm going to do is build a SQL Tuning Set (STS) using DBMS_SQLTUNE package (that has existed for 10g, so I could do in 10g and then migrate it to 11g, for example, to evaluate an upgrade between those versions)
begin
dbms_sqltune.create_sqlset (sqlset_name => 'Test', description => 'Test STS');
end;
l_cursor DBMS_SQLTUNE.sqlset_cursor ;FOR SELECT VALUE (p) FROM TABLE
BEGIN OPEN l_cursor <= 100000; 100000 filas suprimidas.
(
DBMS_SQLTUNE.select_cursor_cache (
'5r2ufj2vqkk4p sql_id =''''', - basic_filtersqlset_name => 'test',
NULL, - object_filter
NULL, - NULL
ranking_measure1 - ranking_measure2
NULL, - NULL
ranking_measure3 - result_percentage
1) - result_limit ) p;
DBMS_SQLTUNE.load_sqlset (
populate_cursor => l_cursor)
END;
user_sqlset select * from WHERE name = 'Test';
actually verified that the STS has been created and contains the sentence :
----------------------------- NAME ID - ---------- ------------------------------------
Test STS 12-AUG-10 12-AUG-10 1
DESCRIPTION CREATED
------------------------------------------------ STATEMENT_COUNT LAST_MODI
Test --------- --------- --------------- 10
select sqlset_name, sql_id from user_sqlset_statements WHERE r2ufj2vqkk4p sql_id = '5 ';
SQLSET_NAME
SQL_ID
------------- Test ------------- -----------------
5r2ufj2vqkk4p
At this point, and having created the STS, which contains the but the sentence context information to evaluate it, we can begin to use the package DBMS_SQLPA (there from 11g R1) for comparison. For example DBMS_SQLTUNE and DBMS_SQLPA packages are complementary. With the first harmonized the workload (which may contain one or more sentences obtained from the AWR, from a cursor, from another STS or even from a trace file) and the second conducted comparative analysis (benchmarking). Then see how to perform this analysis: First
created a task analysis:
l_out declare char (50);
begin l_out: = dbms_sqlpa.create_analysis_task (
sqlset_name => 'test',
task_name => 'Prueba_TSK');
end;
alter session set optimizer_mode = RULE;
begin DBMS_SQLPA.EXECUTE_ANALYSIS_TASK (
task_name => 'Prueba_TSK'
execution_type => 'TEST EXECUTE',
execution_name = > 'Prueba_EXEC_antes');
end; 
I do the same to compare the "after" Setting Up the optimizer to its default value in 11g:
alter session set optimizer_mode = ALL_ROWS;
begin
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK
(
task_name => 'Prueba_TSK'
execution_type => 'TEST EXECUTE ',
execution_name =>' Prueba_EXEC_despues');
end;
executed on the master for the sp Comparison: BEGIN
To compare, you can set focus on that metric, if not clarified anything, it is used as a metric of comparison: "elapsed_time." In this example I chose to use "buffer_gets", as this metric is one that most changes between the two cases to compare and therefore makes more compelling the final report. BEGIN
DBMS_SQLPA.set_analysis_task_parameter ('Prueba_TSK'
'comparison_metric' <= 1000000 ;
'buffer_gets');
END;
DBMS_SQLPA.execute_analysis_task (
task_name => 'Prueba_TSK'
execution_type => 'compare performance',
execution_params => dbms_advisor.arglist (
'execution_name1'
'Prueba_EXEC_antes'
' execution_name2 '
' Prueba_EXEC_despues')
)
END;
Once we ran the analysis using a summary report of the differences:
LONGCHUNKSIZE SET 200
SET LONG 1000000 SET PAGESIZE 0 SET LINESIZE 200
SET TRIMSPOOL ON
rop@DESA11G> SELECT DBMS_SQLPA.REPORT_ANALYSIS_TASK('Prueba_TSK', 'TEXT', 'TYPICAL', 'SUMMARY') from
dual;
General Information
---------------------------------------------------------------------------------------------
Task Information: Workload Information:
--------------------------------------------- ---------------------------------------------
Task Name : Prueba_TSK SQL Tuning Set Name : Prueba
Task Owner : ROP SQL Tuning Set Owner : ROP
Description : Total SQL Statement Count : 1
Execution Information:
---------------------------------------------------------------------------------------------
Execution Name : EXEC_7692 Started : 08/12/2010 16:27:22
Execution Type : COMPARE PERFORMANCE Last Updated : 08/12/2010 16:27:22
Description : Global Time Limit : UNLIMITED
Scope : COMPREHENSIVE Per-SQL Time Limit : UNUSED
Status : COMPLETED Number of Errors : 0
Analysis Information:
---------------------------------------------------------------------------------------------
Comparison Metric: BUFFER_GETS ------------------ Workload Impact Threshold: 1%
--------------------------
SQL Impact Threshold: 1%Improvement Impact : 92.05%
----------------------
Before Change Execution: After Change Execution:
--------------------------------------------- ---------------------------------------------
Execution Name : Prueba_EXEC_antes Execution Name : Prueba_EXEC_despues
Execution Type : TEST EXECUTE Execution Type : TEST EXECUTE
Scope : COMPREHENSIVE Scope : COMPREHENSIVE
Status : COMPLETED Status : COMPLETED
Started : 08/12/2010 16:26:29 Started : 08/12/2010 16:27:04
Last Updated : 08/12/2010 16:27:13 Last Updated : 08/12/2010 16:27:13
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
Report Summary
---------------------------------------------------------------------------------------------
Projected Workload Change Impact:
-------------------------------------------
Overall Impact : 92.05%
Regression Impact : 0%
SQL Statement Count
-------------------------------------------
SQL Category SQL Count Plan Change Count
Overall 1 1---------------------------------------------------------------------------------------------
Improved 1 1
Top SQL Statements Sorted by their Absolute Value of Change Impact on the Workload
--------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
'TEXT', 'TYPICAL', 'FINDINGS') from dual;
General Information
Task Information: Workload Information:
--------------------------------------------- ---------------------------------------------
Task Name : Prueba_TSK SQL Tuning Set Name : PruebaStatus : COMPLETED Number of Errors : 0
Task Owner : ROP SQL Tuning Set Owner : ROP
Description : Total SQL Statement Count : 1
Execution Information:
---------------------------------------------------------------------------------------------
Execution Name : EXEC_7692 Started : 08/12/2010 16:27:22
Execution Type : COMPARE PERFORMANCE Last Updated : 08/12/2010 16:27:22
Description : Global Time Limit : UNLIMITED
Scope : COMPREHENSIVE Per-SQL Time Limit : UNUSED
Analysis Information:
---------------------------------------------------------------------------------------------
Comparison Metric: BUFFER_GETSScope : COMPREHENSIVE Scope : COMPREHENSIVE
------------------
Workload Impact Threshold: 1%
--------------------------
SQL Impact Threshold: 1%
----------------------
Before Change Execution: After Change Execution:
--------------------------------------------- ---------------------------------------------
Execution Name : Prueba_EXEC_antes Execution Name : Prueba_EXEC_despues
Execution Type : TEST EXECUTE Execution Type : TEST EXECUTE
Status : COMPLETED Status : COMPLETED
Started : 08/12/2010 16:26:29 Started : 08/12/2010 16:27:04
Last Updated : 08/12/2010 16:27:13 Last Updated : 08/12/2010 16:27:13
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
Report Details: Statements Sorted by their Absolute Value of Change Impact on the Workload
---------------------------------------------------------------------------------------------
SQL Details:
-----------------------------
Object ID : 4
Schema Name : ROP
SQL ID : 5r2ufj2vqkk4p
Execution Frequency : 1
SQL Text : select count(1) /*+ Prueba SPA */ from t
Execution Statistics:
-----------------------------
------------------------------------------------------------------------------------------------
Elapsed: 00:00:00.59
The trivial example a comparison of the SPA procedure using sqlplus only (you could use MS to a more visual comparison), but serve to illustrate its usefulness. Using a similar procedure could make a pre-upgrade analysis to help ensure the stability of post-upgrade critical judgments on a 11g. To do so would have to perform the following steps before the analysis with SPA:
1. Create the STS at the base to Upgrade
If the upgrade is based on 10g you can create the STS from AWR determining a representative range of the load. If the upgrade is based on 9i STS can be obtained from a previous trace 9i generated during a real load range.
then show an example, which is in the official documentation 10g to create a STS
from AWR, using a previously established baseline for a range with maximum load "peak baseline ', and filtered for the STS only include statements that are executed more than 10 times and a ratio between disk reads and buffer gets over 50%. It also specifies that TOP collect the 30 sentences ordered by disk_reads / buffer_gets: DECLARE
baseline_cursor DBMS_SQLTUNE.SQLSET_CURSOR;
BEGIN OPEN baseline_cursor
FOR SELECT VALUE (p) FROM TABLE
(DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY (
'peak baseline',
'Executions> = 10 AND disk_reads / buffer_gets> = 0.5', NULL
,
'disk_reads / buffer_gets'
NULL, NULL, NULL,
30)) p;
DBMS_SQLTUNE.LOAD_SQLSET (
sqlset_name => 'my_sql_tuning_set'
populate_cursor => baseline_cursor)
END;
If not I create a baseline, also can be configured using two AWR snapshosts id to specify the range to be processed.
2. STS migrate to the new base (11g)
- Create table to store the STS stage and then transferred to the new base
begin DBMS_SQLTUNE.create_stgtab_sqlset (table_name => 'TBL_STG_STS'
schema_name => user);
end;
- Record STS in the table begin
DBMS_SQLTUNE.pack_stgtab_sqlset
stage (sqlset_name => 'test',
staging_table_name => 'TBL_STG_STS');
end;
Once created and charged the stage table, subtract pass to the new base. Here you can use data pump or exp / imp conventional.
- Creates the STS generated in 10g from the stage table in 11g
begin
DBMS_SQLTUNE.unpack_stgtab_sqlset (sqlset_name => 'test',
staging_table_name => 'TBL_STG_STS'
replace => TRUE);
end;
In short, we may use this procedure for quickly assessing the impact of changes on the sentences, and therefore in the implementation plans, due to changes in the environment, for example, change of equipment, disks, cpu add, change database version changes in parameterization, etc.
can "play" with different settings and see how it sentences, perform benchmarking and analysis with different strategies and parameterizations, etc and so to infer the pre-change behavior and prevent instability of the applications when it is too late to turn back implies a high cost.
