Oracle 11g：SQL性能分析器（三）(5)

作者: Jim Czuprynski/黄永兵 译 出处:51CTO.com　2008-04-21 15:30　   砖    好    评论  条 　进入论坛

阅读提示：Oracle数据库11g第一版（11gR1）新的SQL性能分析器许诺较大地减轻了忙碌的Oracle DBA的工作量，因为它提供了一个精确地评估前后性能变化的完整的数据库工作负载的方法。

列表一

/*
|| 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;
/

共7页: 上一页 [1] [2] [3] [4] 5 [6] [7] 下一页

【内容导航】  第 1 页：准备工作  第 2 页：情景#1  第 3 页：情景#2  第 4 页：情景#3  第 5 页：列表一  第 6 页：列表二  第 7 页：列表三