實戰(zhàn):ORACLESQLPerformanceAnalyzer的使用
通過 SPA，您可以根據(jù)各種更改類型（如初始化參數(shù)更改、優(yōu)化器統(tǒng)計刷新和數(shù)據(jù)庫升級）播放特定的
讓客戶滿意是我們工作的目標，不斷超越客戶的期望值來自于我們對這個行業(yè)的熱愛。我們立志把好的技術(shù)通過有效、簡單的方式提供給客戶，將通過不懈努力成為客戶在信息化領(lǐng)域值得信任、有價值的長期合作伙伴，公司提供的服務(wù)項目有：主機域名、虛擬主機、營銷軟件、網(wǎng)站建設(shè)、花垣網(wǎng)站維護、網(wǎng)站推廣。
SQL 或整個 SQL 負載，然后生成比較報告，幫助您評估它們的影響.
在 Oracle Database 11g 之前的版本中，我必須捕獲所有 SQL 語句，通過跟蹤運行這些語句，
然后得到執(zhí)行計劃 — 這是一項極其耗時又極易出錯的任務(wù)。有了新版本之后，我不需要再那樣做了，
我改用非常簡單而有效的 SQL Performance Analyzer。
---使用場景
1.數(shù)據(jù)庫升級
2.實施優(yōu)化建議
3.更改方案
4.收集統(tǒng)計信息
5.更改數(shù)據(jù)庫參數(shù)
6.更改操作系統(tǒng)和硬件
create tablespace test
datafile 'E:\APP\ADMINISTRATOR\ORADATA\ORCL\test01.DBF'
size 5000m
autoextend on
next 100m maxsize unlimited
extent management local autoallocate
segment space management auto;
create table t1
(
sid int not null ,
sname varchar2(10)
)
tablespace test;
-2.-循環(huán)導(dǎo)入數(shù)據(jù)
declare
maxrecords constant int:=1000000;
i int :=1;
begin
for i in 1..maxrecords loop
insert into t1 values(i,'ocpyang');
end loop;
dbms_output.put_line(' 成功錄入數(shù)據(jù)！ ');
commit;
end;
/
update t1 set sname='蘇州' where sid=500001;
update t1 set sname='南京' where sid=600001;
---3.收集統(tǒng)計信息
exec dbms_stats.gather_table_stats(USER,'T1',CASCADE=>TRUE)
alter system flush shared_pool;
---4.執(zhí)行查詢
select count(*) from t1 where sid<=100;
select count(*) from t1 where sid<=500;
select count(*) from t1 where sid>50000;
---5.新建STS
BEGIN
DBMS_SQLTUNE.DROP_SQLSET(
sqlset_name => 'OCPYANG_STS'
);
END;
/
BEGIN
DBMS_SQLTUNE.CREATE_SQLSET(
sqlset_name => 'OCPYANG_STS',
sqlset_owner => 'SYS',
description => 'ocpyangtest');
END;
/
---6.加載sql優(yōu)化集
set serveroutput on
DECLARE
cur01 dbms_sqltune.sqlset_cursor;
BEGIN
open cur01 for select value(a) from table(dbms_sqltune.select_cursor_cache
(
basic_filter => 'sql_text like ''%t1%'' and parsing_schema_name =''SYS''',
attribute_list => 'ALL'
)
) a;
dbms_sqltune.load_sqlset(
sqlset_name => 'OCPYANG_STS',
populate_cursor => cur01);
close cur01;
END;
/
/*********有兩個參數(shù)值得特別說明：
1）SELECT_CURSOR_CACHE的第一個參數(shù)是basic_filter ，它可以取的值有：
sql_id VARCHAR(13),
force_matching_signature NUMBER,
sql_text CLOB,
object_list sql_objects,
bind_data RAW(2000),
parsing_schema_name VARCHAR2(30),
module VARCHAR2(48),
action VARCHAR2(32),
elapsed_time NUMBER,
cpu_time NUMBER,
buffer_gets NUMBER,
disk_reads NUMBER,
direct_writes NUMBER,
rows_processed NUMBER,
fetches NUMBER,
executions NUMBER,
end_of_fetch_count NUMBER,
optimizer_cost NUMBER,
optimizer_env RAW(1000),
priority NUMBER,
command_type NUMBER,
first_load_time VARCHAR2(19),
stat_period NUMBER,
active_stat_period NUMBER,
other CLOB,
plan_hash_value NUMBER,
sql_plan sql_plan_table_type,
bind_list sql_binds
2）SELECT_CURSOR_CACHE的最后一個參數(shù)是attribute_list
BASIC (default) -all attributes (such as execution statistics and binds) are returned except the plans The execution context is always part of the result.
TYPICAL - BASIC + SQL plan (without row source statistics) and without object reference list
ALL - return all attributes
Comma separated list of attribute names this allows to return only a subset of SQL attributes: EXECUTION_STATISTICS, BIND_LIST, OBJECT_LIST, SQL_PLAN,SQL_PLAN_STATISTICS: similar to SQL_PLAN + row source statistics
*********/
---7.查詢sql優(yōu)化集
select sql_id,sql_text from dba_sqlset_statements
where sqlset_name='OCPYANG_STS' and sql_text like '% from t1%';
---8.新建SPA
var v_task varchar2(64);
begin
:v_task:=dbms_sqlpa.create_analysis_task(
sqlset_name => 'OCPYANG_STS',
task_name => 'SPA01'
);
end;
/
/**********語法
Syntax
SQL text format. This form of the function is called to prepare the analysis of a single statement given its text.
DBMS_SQLPA.CREATE_ANALYSIS_TASK(
sql_text IN CLOB,
bind_list IN sql_binds := NULL,
parsing_schema IN VARCHAR2 := NULL,
task_name IN VARCHAR2 := NULL,
description IN VARCHAR2 := NULL)
RETURN VARCHAR2;
SQL ID format. This form of the function is called to prepare the analysis of a single statement from the cursor cache given its identifier.
DBMS_SQLPA.CREATE_ANALYSIS_TASK(
sql_id IN VARCHAR2,
plan_hash_value IN NUMBER := NULL,
task_name IN VARCHAR2 := NULL,
description IN VARCHAR2 := NULL)
RETURN VARCHAR2;
Workload Repository format. This form of the function is called to prepare the analysis of a single statement from the workload repository given a range of snapshot identifiers.
DBMS_SQLPA.CREATE_ANALYSIS_TASK(
begin_snap IN NUMBER,
end_snap IN NUMBER,
sql_id IN VARCHAR2,
plan_hash_value IN NUMBER := NULL,
task_name IN VARCHAR2 := NULL,
description IN VARCHAR2 := NULL)
RETURN VARCHAR2;
SQLSET format. This form of the function is called to prepare the analysis of a SQL tuning set.
DBMS_SQLPA.CREATE_ANALYSIS_TASK(
sqlset_name IN VARCHAR2,
basic_filter IN VARCHAR2 := NULL,
order_by IN VARCHAR2 := NULL,
top_sql IN VARCHAR2 := NULL,
task_name IN VARCHAR2 := NULL,
description IN VARCHAR2 := NULL
sqlset_owner IN VARCHAR2 := NULL)
RETURN VARCHAR2;
**********/
---9.執(zhí)行SPA
begin
dbms_sqlpa.execute_analysis_task
(
task_name => 'SPA01',
execution_type => 'test execute',
execution_name => 'before_change'
);
end;
/
/*********語法
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
task_name IN VARCHAR2,
execution_type IN VARCHAR2 := 'test execute',
execution_name IN VARCHAR2 := NULL,
execution_params IN dbms_advisor.argList := NULL,
execution_desc IN VARCHAR2 := NULL)
RETURN VARCHAR2;
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
task_name IN VARCHAR2,
execution_type IN VARCHAR2 := 'test execute',
execution_name IN VARCHAR2 := NULL,
execution_params IN dbms_advisor.argList := NULL,
execution_desc IN VARCHAR2 := NULL);
*********/
---10.改變
create index index_01 on t1(sid,sname)
tablespace test;
exec dbms_stats.gather_table_stats(USER,'T1',CASCADE=>TRUE)
---11.改變后執(zhí)行
begin
dbms_sqlpa.execute_analysis_task
(
task_name => 'SPA01',
execution_type => 'test execute',
execution_name => 'after_change'
);
end;
/
col TASK_NAME format a30
col EXECUTION_NAME for a30
select execution_name,
status,
execution_end
from DBA_ADVISOR_EXECUTIONS
where task_name='SPA01'
order by execution_end
/
EXECUTION_NAME STATUS EXECUTION_END
------------------------------ ----------- -------------------
before_change COMPLETED 2014-05-28 15:43:58
after_change COMPLETED 2014-05-28 15:44:58
---12.執(zhí)行任務(wù)比較
begin
dbms_sqlpa.EXECUTE_ANALYSIS_TASK(
task_name => 'SPA01',
execution_type => 'compare performance',
execution_params => dbms_advisor.arglist(
'execution_name1',
'before_change',
'execution_name2',
'after_change'));
end;
/
---13.生產(chǎn)報告
set serveroutput on size 999999
set long 100000000
set pagesize 0
set linesize 200
set longchunksize 200
set trimspool on
spool e:\report.txt
select DBMS_SQLPA.REPORT_ANALYSIS_TASK('SPA01') from dual;
spool off;
分享文章：實戰(zhàn):ORACLESQLPerformanceAnalyzer的使用
網(wǎng)頁URL：http://muchs.cn/article4/gedgoe.html
成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供做網(wǎng)站、微信小程序、搜索引擎優(yōu)化、網(wǎng)站設(shè)計、網(wǎng)站排名、
聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)
- 品牌網(wǎng)站設(shè)計制作過程中要注意哪些問題？ 2020-12-03
- 品牌網(wǎng)站設(shè)計都需要一個“新聞工具包” 2023-03-02
- 網(wǎng)站制作專業(yè)公司更相符公司的成長請求 2022-06-04
- 品牌網(wǎng)站設(shè)計有哪些問題要注意？ 2020-12-26
- 品牌網(wǎng)站設(shè)計定制解決方案 2021-08-16
- 我要做的是品牌網(wǎng)站設(shè)計，實際上是品牌網(wǎng)頁優(yōu)化設(shè)計，可懂? 2022-05-23
- 品牌網(wǎng)站設(shè)計如何做好用戶體驗 2021-10-03
- 企業(yè)品牌網(wǎng)站設(shè)計思路介紹 2023-01-29
- 品牌網(wǎng)站設(shè)計的頁腳樣式是什么 2021-06-20
- 品牌網(wǎng)站設(shè)計怎樣做更加高端？ 2022-11-15
- 常見的營銷網(wǎng)站類型你了解嗎 企業(yè)品牌網(wǎng)站設(shè)計思路 2021-05-11
- 吳江高端網(wǎng)站建設(shè)制作品牌網(wǎng)站設(shè)計如何布局 2020-11-23