分类： Oracle
2008-04-22 19:37:38
/*
|| Oracle 11g SQL Performance Analyzer Listing 1
||
|| Demonstrates Oracle 11g SQL Performance Analyzer (SPA) basic techniques,
|| including:
|| - Setup and configuration of required sample database objects
|| - Creation of SQL Tuning Sets for "before" and "after" image comparson
|| - Execution of SQL Performance Analyzer "before" test image
|| - Execution of SQL Performance Analyzer "after" test image
|| - Execution of SQL Performance Analyzer comparison reports
||
|| Author: Jim Czuprynski
||
|| Usage Notes:
|| These examples are provided to demonstrate various features of Oracle 11g
|| SQL Performance Analyzer features, and they should be carefully proofread
|| before executing them against any existing Oracle database(s) to avoid
|| potential damage!
*/
/*
|| Listing 1.1:
|| Create a new table (SH.SALES_AGENTS) and corresponding objects for
|| simulated performance tuning
*/
DROP TABLESPACE lmt_data INCLUDING CONTENTS AND DATAFILES;
CREATE SMALLFILE TABLESPACE lmt_data
DATAFILE '/u01/app/oracle/oradata/orcl/lmt_data01.dbf'
SIZE 60M
AUTOEXTEND ON
LOGGING
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;
DROP TABLESPACE lmt_idx INCLUDING CONTENTS AND DATAFILES;
CREATE SMALLFILE TABLESPACE lmt_idx
DATAFILE '/u01/app/oracle/oradata/orcl/lmt_idx01.dbf'
SIZE 60M
AUTOEXTEND ON
LOGGING
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;
DROP TABLE sh.sales_agents PURGE;
CREATE TABLE sh.sales_agents(
salesperson_id NUMBER(10) NOT NULL
,last_name VARCHAR2(32) NOT NULL
,first_name VARCHAR2(32) NOT NULL
,address_line_1 VARCHAR2(40)
,address_line_2 VARCHAR2(40)
,address_line_3 VARCHAR2(40) NOT NULL
,city VARCHAR2(40) NOT NULL
,locale VARCHAR2(40)
,state VARCHAR2(2)
,postal_code VARCHAR2(10)
,country VARCHAR2(32)
)
TABLESPACE lmt_data
STORAGE (INITIAL 1M)
;
DROP INDEX sh.sales_agents_pk_idx;
CREATE UNIQUE INDEX sh.sales_agents_pk_idx
ON sh.sales_agents (salesperson_id)
TABLESPACE lmt_idx
STORAGE (INITIAL 1M)
;
ALTER TABLE sh.sales_agents
ADD CONSTRAINT salesperson_pk
PRIMARY KEY (salesperson_id)
;
/*
|| Initial Data Load
*/
@LoadSalesAgents.sql;
/*
|| Index Creation and Statistics Gathering
*/
-----
-- Create alternate indexes for reporting
-----
DROP INDEX sh.sales_agents_la_idx;
CREATE INDEX sh.sales_agents_la_idx
ON sh.sales_agents (last_name ASC)
TABLESPACE lmt_idx
STORAGE (INITIAL 1M)
;
DROP INDEX sh.sales_agents_sa_idx;
CREATE INDEX sh.sales_agents_sa_idx
ON sh.sales_agents (state ASC)
TABLESPACE lmt_idx
STORAGE (INITIAL 1M)
;
DROP INDEX sh.sales_agents_lafa_idx;
CREATE INDEX sh.sales_agents_lafa_idx
ON sh.sales_agents (last_name ASC, first_name ASC)
TABLESPACE lmt_idx
STORAGE (INITIAL 1M)
;
DROP INDEX sh.sales_agents_saca_idx;
CREATE INDEX sh.sales_agents_saca_idx
ON sh.sales_agents (state ASC, city ASC)
TABLESPACE lmt_idx
STORAGE (INITIAL 1M)
;
-----
-- Gather statistics on table and its indexes, and
-- then lock them to prevent unexpected recalculation
-- during nightly statistics regathering job
-----
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(
ownname => 'SH'
,tabname => 'SALES_AGENTS'
,cascade => TRUE
,force => TRUE
);
END;
/
/*
|| Listing 1.2: Load Generation
|| While the SQL Tuning Set task in Listing 1.x is "watching,"
|| generate a load on the database that can be captured into a
|| SQL Tuning Set for later analysis with the SQL Performance Analyzer (SPA)
*/
CONNECT ldgn/ldgn;
SET ECHO ON
SET TIMING ON
-----
-- Scenario 1.0:
-- Let the Cost-Based Optimizer choose the best query plan
-----
SELECT /*LDGN_1.0*/
SA.last_name
,SA.first_name
,SA.city
,SA.state
,SA.postal_code
FROM
sh.sales_agents SA
WHERE ((SA.city = 'Chicago' AND SA.state = 'IL') OR SA.state = 'IN')
OR SA.last_name LIKE 'Crystal%'
;
-----
-- Scenario 1.1:
-- Force the query to ignore all indexes
-----
SELECT /*+ NO_INDEX(SA) LDGN_1.1*/
SA.last_name
,SA.first_name
,SA.city
,SA.state
,SA.postal_code
FROM
sh.sales_agents SA
WHERE ((SA.city = 'Chicago' AND SA.state = 'IL') OR SA.state = 'IN')
OR SA.last_name LIKE 'Crystal%'
;
-----
-- Scenario 1.2:
-- Force the query to ignore at least one of the obvious
-- "best choice" indexes
-----
SELECT /*+ NO_INDEX(SA sales_agents_la_idx) LDGN_1.2*/
SA.last_name
,SA.first_name
,SA.city
,SA.state
,SA.postal_code
FROM
sh.sales_agents SA
WHERE ((SA.city = 'Chicago' AND SA.state = 'IL') OR SA.state = 'IN')
OR SA.last_name LIKE 'Crystal%'
;
-----
-- Scenario 1.3:
-- Force the query to use a wrong index
-----
SELECT /*+INDEX(SA sales_agents_pk_idx) LDGN_1.3*/
SA.last_name
,SA.first_name
,SA.city
,SA.state
,SA.postal_code
FROM
sh.sales_agents SA
WHERE ((SA.city = 'Chicago' AND SA.state = 'IL') OR SA.state = 'IN')
OR SA.last_name LIKE 'Crystal%'
;
-----
-- Scenario 2.0:
-- Let the Cost-Based Optimizer choose the best query plan
-----
SELECT /*LDGN_2.0*/
SA.state
,COUNT(SA.city) tot_cities
FROM
sh.sales_agents SA
WHERE (SA.last_name,SA.first_name) IN (('Wells','Billy'),('Alexander','Kirk'))
GROUP BY SA.state
ORDER BY SA.state;
;
-----
-- Scenario 2.1:
-- Force the query to use a poorer index (SALES_AGENTS_SACA_IDX)
-- even though a better index (SALES_AGENTS_LAFA_IDX) exists
-----
SELECT /*+INDEX(SA, SALES_AGENTS_SACA_IDX) LDGN_2.1*/
SA.state
,COUNT(SA.city) tot_cities
FROM
sh.sales_agents SA
WHERE (SA.last_name,SA.first_name) IN (('Wells','Billy'),('Alexander','Kirk'))
GROUP BY SA.state
ORDER BY SA.state;
;
-----
-- Scenario 3.0:
-- Let the Cost-Based Optimizer choose the best query plan
-----
SELECT /*LDGN_3.0*/
SA.state
,SA.city
,COUNT(SA.last_name) namecnt
,MIN(SA.last_name) min_lname
,MAX(SA.last_name) max_lname
,MIN(SA.first_name) min_fname
,MAX(SA.first_name) max_fname
FROM
sh.sales_agents SA
WHERE (SA.city,SA.state) IN (
('Milpitas','CA')
,('Schaumburg','IL')
,('El Paso','TX')
,('Oshkosh','WI'))
GROUP BY SA.state, SA.city
ORDER BY SA.state, SA.city;
;
-----
-- Scenario 3.1:
-- Force the query to use a poorer index (SALES_AGENTS_LAFA_IDX)
-- even though a better index (SALES_AGENTS_SACA_IDX) exists
-----
SELECT /*+INDEX(SA, SALES_AGENTS_LAFA_IDX) LDGN_3.1*/
SA.state
,SA.city
,COUNT(SA.last_name) namecnt
,MIN(SA.last_name) min_lname
,MAX(SA.last_name) max_lname
,MIN(SA.first_name) min_fname
,MAX(SA.first_name) max_fname
FROM
sh.sales_agents SA
WHERE (SA.city,SA.state) IN (
('Milpitas','CA')
,('Schaumburg','IL')
,('El Paso','TX')
,('Oshkosh','WI'))
GROUP BY SA.state, SA.city
ORDER BY SA.state, SA.city;
;
SET ECHO OFF
SET TIMING OFF
DISCONNECT;
/*
|| Listing 1.3: SQL Tuning Set Creation
|| Create and prepare to populate a SQL Tuning Set (STS)
|| for selected SQL statements. Note that this STS will capture
|| all SQL statements which are executed by the LDGN user account
|| within a 5-minute period, and Oracle will check every 5 seconds
|| for any new statements
*/
BEGIN
DBMS_SQLTUNE.CREATE_SQLSET(
sqlset_name => 'STS_SPA_100'
);
DBMS_SQLTUNE.CAPTURE_CURSOR_CACHE_SQLSET(
sqlset_name => 'STS_SPA_100'
,basic_filter=> q'#sql_text LIKE '%LDGN_%' AND parsing_schema_name = 'LDGN'#'
,time_limit => 300
,repeat_interval => 5
);
END;
/
/*
|| Listing 1.4: Generate SQL Performance Analysis Task
|| In the following steps:
|| 1.) A new SQL Performance Analysis (SPA) task is generated
|| 2.) The SPA task is executed in test execution mode to
|| produce a "before" image
*/
-----
-- Create a SQL Performance Analysis Task
-----
DECLARE
spa_task_name VARCHAR2(64);
BEGIN
spa_task_name :=
DBMS_SQLPA.CREATE_ANALYSIS_TASK(
sqlset_name => 'STS_SPA_100'
,basic_filter => NULL
,order_by => NULL
,top_sql => NULL
,description => 'Comparison of LDGN-executed SQL statements'
,sqlset_owner => 'SYS'
);
END;
/
-----
-- Execute the SQL Performance Analysis Task to capture the "before"
-- image of the SQL Tuning Set's performance
-----
BEGIN
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
task_name => 'TASK_69'
,execution_type => 'test execute'
,execution_name => 'SQR_100_BEFORE'
,execution_desc => 'SPA Before Image'
,execution_params => DBMS_ADVISOR.ARGLIST('TEST_EXECUTE','FULL')
);
END;
/
/*
|| Listing 1.5: Change the Database Environment
|| Drop some of the indexes that were utilized heavily
|| by the previously-run SQL statements, and then regather
|| optimizer statistics
*/
DROP INDEX sh.sales_agents_la_idx;
DROP INDEX sh.sales_agents_saca_idx;
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(
ownname => 'SH'
,tabname => 'SALES_AGENTS'
,cascade => TRUE
,force => TRUE
);
END;
/
/*
|| Listing 1.6: Generate An "After" Performance Image
|| The existing the SQL Performance Analysis Task is re-executed
|| to capture the "after" image of the SQL Tuning Set's performance
*/
BEGIN
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
task_name => 'TASK_69'
,execution_type => 'test execute'
,execution_name => 'SQR_100_AFTER'
,execution_desc => 'SPA After Image'
,execution_params => DBMS_ADVISOR.ARGLIST('TEST_EXECUTE','FULL')
);
END;
/
/*
|| Listing 1.7: Compare Before vs. After Performance
*/
BEGIN
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
task_name => 'TASK_69'
,execution_type => 'compare performance'
,execution_name => 'SPA_110_PC'
,execution_desc => 'SPA 110 Performance Comparison'
,execution_params =>
DBMS_ADVISOR.ARGLIST(
'EXECUTION_NAME1','SQR_BEFORE_110'
,'EXECUTION_NAME2','SQR_AFTER_110'
,'COMPARISON_METRIC','ELAPSED_TIME'
)
);
END;
/
/*
|| Listing 1.8: Generate Comparison Report
|| Now that the comparison is completed, generate the "delta" report
|| using DBMS_SQLPA.REPORT_ANALYSIS_TASK
*/
SET PAGESIZE 0
SET LINESIZE 1000
SET SERVEROUTPUT ON SIZE UNLIMITED
DECLARE
tLOB CLOB;
BEGIN
SELECT DBMS_SQLPA.REPORT_ANALYSIS_TASK('TASK_69')
INTO tLOB
FROM DUAL;
DBMS_OUTPUT.PUT_LINE(tLOB);
END;
/
