Solution: - Oracle 11g nw features SQL Performance Analyzer (SPA) analyze the SQL performance impact of any type of system changes. SQL Performance Analyzer automates the process of assessing the overall effect of a change on the full SQL work
load by identifying performance divergence for each statement.
SPA report that shows the net impact on the workload performance due to the change is provided. For regressed SQL statements, SQL Performance Analyzer also provides appropriate executions plan details along with tuning recommendations. As a result, DBAs can remedy any negative outcome before their end users are affected and can validate, with significant time and cost savings, that the system change to the production environment will result in net improvemengt
The steps of the SQL Performance Analyzer workflow are as follows:
- Capture SQLs on Production Database
- Move SQLs to Test Database
- Create a SQL Performance Analyzer task on the test system
- Generate and store the query execution statistics before the change.
- Make changes (Database upgrade to 11g , parameter changes etc)
- Generate and store the query execution statistics after the change.
- Compare Performance
- Generate Report
1) Capture SQL ( Steps on production)
exec dbms_sqltune.drop_sqlset('STS_RAG_CMUL'); exec dbms_sqltune.create_sqlset('STS_RAG_CMUL'); BEGIN END; |
capture_cursor_cache_sqlset : The procedure captures a workload from the cursor cache into a SQL tuning set, polling the cache multiple times over a time period and updating the workload data stored there. It can execute over as long a period as required to capture an entire system workload.
- time_limit : 750000 ( The total amount of time, in seconds, to execute )
- repeat_interval : 1 ( The amount of time, in seconds, to pause between sampling )
- capture_option During capture, either insert new statements, update existing ones, or both.
- 'INSERT'
- 'UPDATE',
- 'MERGE'
- capture_mode capture Option (UPDATE and MERGE capture options).
MODE_REPLACE_OLD_STATS - Replace statistics when the number of executions seen is greater than that stored in the SQL tuning set
MODE_ACCUMULATE_STATS - Add new values to current values for SQL we already store. Note that this mode detects if a statement has been aged out, so the final value for a statistics will be the sum of the statistics of all cursors that statement existed under.
The CAPTURE_CURSOR_CACHE_SQLSET function enables the capture of the full
system workload by repeatedly polling the cursor cache over a specified interval.
This function is a lot more efficient than repeatedly using the SELECT_CURSOR_
CACHE and LOAD_SQLSET procedures to capture the cursor cache over an
extended period of time. This function effectively captures the entire workload, as
SQL Profiles opposed to the AWR—which only captures the workload of high-load SQL
statements—or the LOAD_SQLSET procedure, which accesses the data source only
once.
2) Move SQLs to test system
Steps on production database
Create staging table for STS and pack STS data into staging table and import the staging table
a) Create a staging table exec dbms_sqltune.create_stgtab_sqlset('SQLSET_TAB'); b) Pack the sqlset in a staging table exec dbms_sqltune.pack_stgtab_sqlset('STS_RAG_CMUL','APPS','SQLSET_TAB','APPS'); c) Export the staging table exp apps/apps file=sqlset_tab.dmp tables=sqlset_tab |
Steps on test database
Import staging table and unpack staged data in STS
d) Import the staging table in the target database imp apps/apps file=sqlset_tab.dmp full=y e) unpack the staging table into sqlset exec dbms_sqltune.drop_sqlset('STS_RAG_CMUL'); exec dbms_sqltune.create_sqlset('STS_RAG_CMUL'); |
3) Create a SQL Performance Analyzer task on the test system
Create a SQL Performance Analyzer task on the test system using the
SQL Tuning Set as its input source.
var l_task_id char(30) |
>
need to increase
exec dbms_sqltune.set_tuning_task_parameter('MY_REPLAY_TASK', |
The time_limit parameter specifies the global time limit to process all SQL
statements in a SQL Tuning Set beforetiming out.
The local_time_limit parameter specifies the time limit to process each SQL
statement in a SQL Tuning Set before timing out.
4)Generate and store the query execution statistics before the change
Use SQL Performance Analyzer to build the pre-change performance data by executing the
SQL statements stored in the SQL Tuning Set.
|
Check status of Analysis task
SQL> col TASK_NAME format a30 SQL> col EXECUTION_NAME for a30 SQL> l SQL> / EXECUTION_NAME STATUS EXECUTION_END -------------------- --------------------------- --------------- --Check time SQL> select TASK_ID,TASK_NAME,TASK_NAME , STATUS TASK_ID TASK_NAME TASK_NAME STATUS
SOFAR TOTALWORK |
5) Make database Changes
alter system set "_b_tree_bitmap_plans" = false; --- More application change |
6) Generate and store the query execution statistics after the change.
Use SQL Performance Analyzer to build the post-change performance data
by re-executing the SQL statements in the SQL Tuning Set on the post-change
test system.
Begin DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name => 'MY_REPLAY_TASK',execution_type => 'TEST EXECUTE', end; |
7) Compare execution plans
Use SQL Performance Analyzer to compare and analyze
the pre-change and post-change versions of performance data
Begin DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name => 'MY_REPLAY_TASK',execution_type => 'COMPARE PERFORMANCE', end; |
8) Compare the execution changes due to your database changes
Generate a report to identify the SQL statements in the
SQL workload that have improved, remained unchanged, or
regressed after the system change. Review the report and
interpret the results.
--Displaying the Results of a SQL Tuning Task set serveroutput on size 999999 select DBMS_SQLPA.REPORT_ANALYSIS_TASK('MY_REPLAY_TASK')from dual; -- [ Click Here for Sample Report of above command ]OR
SET LONG 100000 LONGCHUNKSIZE 100000 LINESIZE 130 PRINT :rep
|
>
exec dbms_sqlpa.DROP_ANALYSIS_TASK('MY_REPLAY_TASK'); |
>
Some more article on 11g
1. Oracle 11g TOP feature for DBA
2.11g Automatic Diagnostic Repository (ADR)
Posts
No comments:
Post a Comment