Analyze the impact of change on SQL statements using the ...
Analyze the impact of change on SQL statements using the SQL Performance Analyzer
We can analyse the impact of upgrading from 10.2.0.4 to 11.1.0.6 at the SQL statement level by using the other component of 11g Real Application Testing which is called SQL Performance Analyzer or SPA..
We will use the following two SQL statements to create a SQL Tuning Set (STS) in the 10.2.0.4 environment. This STS is then transported to the 11g target environment and then we will use the GUI Database Control to replay the statements in the STS and generate a Performance Analysis report which will help us identify if any statements have improved or digressed in the changed environment.
SELECT /*+TEST_SPA*/ p.prod_name, s.time_id, t.week_ending_day, SUM(s.amount_sold)
FROM sales s, products p, times t
WHERE s.time_id=t.time_id
AND s.prod_id = p.prod_id
GROUP BY p.prod_name, s.time_id, t.week_ending_day;
SELECT /*+TEST_SPA*/ p.prod_category, t.week_ending_day, s.cust_id, SUM(s.amount_sold)
FROM sales s, products p, times t
WHERE s.time_id = t.time_id
AND s.prod_id = p.prod_id
AND p.prod_category = 'Peripherals and Accessories'
GROUP BY p.prod_category, t.week_ending_day, s.cust_id;
We are using a ‘tag’ TEST_SPA to help us easily identify these SQL statements in the shared pool.
Source 10.2.0.4 environment – Create and Export SQL Tuning Set
SQL> exec dbms_sqltune.drop_sqlset('SH_10204');
PL/SQL procedure successfully completed.
SQL> exec dbms_sqltune.create_sqlset('SH_10204','Demo Workload to test SPA');
PL/SQL procedure successfully completed.
SQL> DECLARE
2 stscur dbms_sqltune.sqlset_cursor;
3 BEGIN
4 OPEN stscur FOR
5 SELECT VALUE(P)
6 FROM TABLE(dbms_sqltune.select_cursor_cache(
7 'sql_text like ''SELECT /*+TEST_SPA%''',
8 null, null, null, null, null, null, 'ALL')) P;
9 -- populate the sqlset
10 dbms_sqltune.load_sqlset(:sts_name, stscur);
11 end;
12 /
PL/SQL procedure successfully completed.
SQL> select sql_id,plan_hash_value, substr(sql_text,1,40) text, executions
2 from dba_sqlset_statements
3 where sqlset_name ='SH_10204'
4 order by sql_id, plan_hash_value;
SQL_ID PLAN_HASH_VALUE TEXT EXECUTIONS
------------- --------------- -------------------------------------------------- ----------
5930m31ryy4b1 3901800458 SELECT /*+TEST_SPA*/ p.prod_category, t. 1
5tbr81fmrnyc0 1679814994 SELECT /*+TEST_SPA*/ p.prod_name, s.time 1
In order to transport the SQL tuning set that we have just created, we need to create a staging table to hold the contents of the STS and then we will use the export utility to generate a dump file which will then be copied to the target 11g environment.
SQL> execute DBMS_SQLTUNE.CREATE_STGTAB_SQLSET(table_name =>'SH_STS');
PL/SQL procedure successfully completed.
SQL> execute DBMS_SQLTUNE.PACK_STGTAB_SQLSET(sqlset_name => 'SH_10204',staging_table_name => 'SH_STS');
PL/SQL procedure successfully completed.
SQL> select count (*) from sh_sts;
COUNT(*)
----------
2
testdb:/u01/oracle/scripts> exp file=sts.dmp tables=sh_sts
Export: Release 10.2.0.4.0 - Production on Mon Sep 14 10:22:48 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Username: sys as sysdba
Password:
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)
About to export specified tables via Conventional Path ...
. . exporting table SH_STS 2 rows exported
. . exporting table SH_STS_CBINDS 0 rows exported
. . exporting table SH_STS_CPLANS 17 rows exported
Export terminated successfully without warnings.
testdb:/u01/oracle/scripts> scp -rp sts.dmp 10.1.210.21:/u01/oracle
oracle@10.1.210.21's password:
sts.dmp
Target 11.1.0.6 environment – Import the SQL Tuning Set and Perform Analysis
bwdba1:/u01/oracle> imp file=sts.dmp full=y
…….
. . importing table "SH_STS" 2 rows imported
. . importing table "SH_STS_CBINDS" 0 rows imported
. . importing table "SH_STS_CPLANS" 17 rows imported
SQL> execute DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET( sqlset_name => '%',-
replace => TRUE,-
staging_table_name => 'SH_STS');
> >
PL/SQL procedure successfully completed.
SQL> Select NAME,OWNER,CREATED,STATEMENT_COUNT FROM DBA_SQLSET;
NAME OWNER CREATED
------------------------------ ------------------------------ ---------
STATEMENT_COUNT
---------------
SH_10204 SYS 14-SEP-09
We launch the SQL Performance Analyzer page from the ‘Software and Support’ tab of Database Control. We are presented with three different options
Optimizer Upgrade Simulation
Parameter Change
Guided Workflow
For this example we will select the ‘Optimizer Upgrade Simulation’ option.
From the SQL Tuning Set dropdown list we will select the STS that we have just imported and unpacked – SH_10204
[pic]
[pic]
Automatically in this case, SPA has set up two trial runs simulating the optimizer behaviour in 10.2.0.4 and then 11.1.0.6 via the parameter optimizer_features_enable. We then click on the Create Replay Trial button and after a brief while we see that the status shows COMPLETED
[pic]
[pic]
[pic]
We then click on the button Run Replay Trial Comparison
[pic]
If we examine the report, we will find that out of the two SQL statements in the STS, one has remained unchanged in spite of the optimizer change, while the other statement has had a marginal improvement.
What is important is that this analysis has clearly shown us that no change in the SQL query execution plan has taken place in the 11.1.0.6 environment.
Thus while planning an upgrade from 10g to 11g, we can clearly see the impact of the change on key SQL statements and quickly identify if any regressions have taken place so that we can take corrective measures before we actually do and perform the database upgrade.
[pic]
[pic]
[pic]
Let us now use the other option Guided Workflow to test the impact of some optimizer related parameter changes on the two SQL statements which are included in the STS. We will use the same STS as in the earlier example.
We will make some changes in the 11g environment which will make the optimizer favour nested loops over hash joins and index scans over table scans.
At a high level, we will replay the SQL contained in the STS in the initial environment and execute the same in the ‘changed’ environment and finally compare the results of each ‘trial run’ via the Trial Comparison Report.
[pic]
[pic]
[pic]
[pic]
[pic]
We then make the following changes so as to influence the optimizer to potentially choose a different execution plan as compared to the original.
SQL> alter system set optimizer_mode=FIRST_ROWS;
System altered.
SQL> alter system set optimizer_index_cost_adj=20;
System altered.
SQL> alter system set optimizer_index_caching=80;
System altered.
[pic]
[pic]
While there are a number of metrics which can be used a basis for comparison like elapsed time, disk reads, optimizer cost etc, we will choose the metric buffer gets.
[pic]
[pic]
The Trial Comparison Report page shows us that one statement has regressed after making the optimizer changes while the other has remained unchanged.
The high value for buffer gets as shown in the second run is an indicator that inefficient indexes or sub-optimal join orders are being chosen by the optimizer.
Also, we find that execution plans have changed (as expected) and if we find that a majority of the SQL statements would have regressed due to the optimizer change, then it would not be a prudent decision to go ahead and make the change in a production environment.
[pic]
[pic]
[pic]
................
................
In order to avoid copyright disputes, this page is only a partial summary.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.
Related download
- theory and concept pcl soft
- getting the information you need from cdw sql starter
- sql university of oklahoma
- analyze the impact of change on sql statements using the
- sql class website
- sql select statement part one syracuse university
- solutions chapter 1 sql and data test your thinking
- select statement university of oklahoma
- sql select statement part one
Related searches
- impact of philosophy on education
- impact of education on society
- impact of technology on education
- impact of business on society
- the impact of technology on education article
- impact of education on individuals
- the impact of culture on education
- the impact of the scientific revolution
- analyze the effects of the neolithic revolution
- change the size of text on screen
- the impact of video games on children
- the impact of social media on society