oracle sql调优集
来源:互联网 发布:伴奏制作的软件 编辑:程序博客网 时间:2024/04/30 06:40
************************************************************1.新建调优集对象************************************************************---授权grant ADMINISTER ANY SQL TUNING SET to scott;---删除存在的STSBEGIN DBMS_SQLTUNE.DROP_SQLSET( sqlset_name => 'OCPYANG_STS' );END;/---新建STSBEGIN DBMS_SQLTUNE.CREATE_SQLSET( sqlset_name => 'OCPYANG_STS', sqlset_owner => 'SCOTT', description => 'ocpyangtest');END;/---查看数据库已经创建的SQLSETselect owner, name, id, created, statement_count from dba_sqlset;************************************************************2.查看AWR资源密集型SQL语句************************************************************---2.1查看可用的快照范围SELECT snap_id, instance_number, end_interval_timeFROM dba_hist_snapshotORDER BY snap_id;---2.2 查看快照编号820-840之间磁盘使用率前10的sqlSELECT sql_id,substr(sql_text,1,100),disk_reads, cpu_time, elapsed_timeFROM table(DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(820,841, null, null, 'disk_reads',null, null, null, 10))ORDER BY disk_reads DESC;---2.3查看没有被sys用户解析的sqlSELECT sql_id, substr(sql_text,1,100),disk_reads, cpu_time, elapsed_time, parsing_schema_nameFROM table(DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(820,841,'parsing_schema_name <> ''SYS''',NULL, NULL,NULL,NULL, 1, NULL, 'ALL'));---2.4 查看快照编号820-840之间非sql用户排序的前10的sqlSELECT sql_id, substr(sql_text,1,100),disk_reads, cpu_time, elapsed_time, buffer_gets, parsing_schema_nameFROM table(DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY( begin_snap => 820,end_snap => 841,basic_filter => 'parsing_schema_name <> ''SYS''',ranking_measure1 => 'buffer_gets',result_limit => 10));COL bsnap NEW_VALUE begin_snapCOL esnap NEW_VALUE end_snap--SELECT MAX(snap_id) bsnapFROM dba_hist_snapshotWHERE begin_interval_time < sysdate-7;--SELECT MAX(snap_id) esnapFROM dba_hist_snapshot;--COL sql_text FORMAT A40COL sql_id FORMAT A15COL parsing_schema_name FORMAT A15COL cpu_seconds FORMAT 999,999,999,999,999SET LONG 10000 LINES 132 PAGES 100 TRIMSPOOL ON--SELECT sql_id, sql_text,disk_reads, cpu_time cpu_seconds, elapsed_time, buffer_gets, parsing_schema_nameFROM table(DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY( begin_snap => &begin_snap,end_snap => &end_snap,basic_filter => 'parsing_schema_name <> ''SYS''',ranking_measure1 => 'cpu_time',result_limit => 10));************************************************************3.使用AWR中高资源消耗的SQL来填充优化集:************************************************************---3.1新建STSBEGIN DBMS_SQLTUNE.CREATE_SQLSET( sqlset_name => 'OCPYANG_STS', sqlset_owner => 'SCOTT', description => 'ocpyangtest');END;/---3.2查看AWR快照起止select snap_id, begin_interval_timefrom dba_hist_snapshot order by 1;---3.3使用AWR中高资源的sql来填充sql优化集DECLARE test_cur dbms_sqltune.sqlset_cursor;BEGIN OPEN test_cur FOR SELECT value(x) FROM table(dbms_sqltune.select_workload_repository( 820,841, null, null,'disk_reads', null, null, null, 15)) x; -- dbms_sqltune.load_sqlset( sqlset_owner =>'SCOTT', sqlset_name => 'OCPYANG_STS', populate_cursor => test_cur);END;/*------------------常见错误第 1 行出现错误:ORA-13774: 权限不足, 无法从工作量资料档案库中选择数据 ORA-06512: 在"SYS.DBMS_SQLTUNE", line 4715ORA-06512: 在 line 10使用sys账户即可,在DBMS_SQLTUNE.LOAD_SQLSET指定sqlset_ownerDBMS_SQLTUNE.LOAD_SQLSET ( sqlset_name IN VARCHAR2, populate_cursor IN sqlset_cursor, load_option IN VARCHAR2 := 'INSERT', update_option IN VARCHAR2 := 'REPLACE', update_condition IN VARCHAR2 := NULL, update_attributes IN VARCHAR2 := NULL, ignore_null IN BOOLEAN := TRUE, commit_rows IN POSITIVE := NULL, sqlset_owner IN VARCHAR2 := NULL);-----------------------------*---3.4 查看优化集相信信息SELECT sqlset_name, elapsed_time,cpu_time, buffer_gets, disk_reads, sql_textFROM dba_sqlset_statementsWHERE sqlset_name = 'OCPYANG_STS';************************************************************4.查看内存中资源密集型的sql************************************************************---4.1 语法DBMS_SQLTUNE.SELECT_CURSOR_CACHE ( basic_filter IN VARCHAR2 := NULL, object_filter IN VARCHAR2 := NULL, ranking_measure1 IN VARCHAR2 := NULL, ranking_measure2 IN VARCHAR2 := NULL, ranking_measure3 IN VARCHAR2 := NULL, result_percentage IN NUMBER := 1, result_limit IN NUMBER := NULL, attribute_list IN VARCHAR2 := NULL) RETURN sys.sqlset PIPELINED;---4.2 从内存中选择读取磁盘超过1000000 SELECT sql_id, substr(sql_text,1,20), disk_reads,cpu_time, elapsed_time,buffer_gets, parsing_schema_nameFROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('disk_reads>1000000'))order by sql_id;---4.3 查看内存中非sys账户用户CPU时间最长的10个查询SELECT sql_id, substr(sql_text,1,120), disk_reads,cpu_time, elapsed_time,buffer_gets, parsing_schema_nameFROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE( basic_filter => 'parsing_schema_name <> ''SYS''',ranking_measure1 => 'cpu_time',result_limit => 10));---4.4 查看内存中非sys账户运行返回时间超过1秒的SELECT sql_id, substr(sql_text,1,120),disk_reads, cpu_time, elapsed_timeFROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('parsing_schema_name <> ''SYS'' AND elapsed_time > 1000000'))ORDER BY sql_id;---4.5 查看具体sql_id执行细节SELECT *FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('sql_id = ''byzwu34haqkn4'''));----4.6 各种案例-- Select all statements in the cursor cache.DECLARE cur sys_refcursor;BEGIN OPEN cur FOR SELECT value(P) FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE) P; -- Process each statement (or pass cursor to load_sqlset). CLOSE cur;END;/ -- Look for statements not parsed by SYS.DECLARE cur sys_refcursor;BEGIN OPEN cur for SELECT VALUE(P) FROM table( DBMS_SQLTUNE.SELECT_CURSOR_CACHE('parsing_schema_name <> ''SYS''')) P; -- Process each statement (or pass cursor to load_sqlset). CLOSE cur;end;/ -- All statements from a particular module/action.DECLARE cur sys_refcursor;BEGIN OPEN cur FOR SELECT VALUE(P) FROM table( DBMS_SQLTUNE.SELECT_CURSOR_CACHE( 'module = ''MY_APPLICATION'' and action = ''MY_ACTION''')) P; -- Process each statement (or pass cursor to load_sqlset) CLOSE cur;END;/ -- all statements that ran for at least five secondsDECLARE cur sys_refcursor;BEGIN OPEN cur FOR SELECT VALUE(P) FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('elapsed_time > 5000000')) P; -- Process each statement (or pass cursor to load_sqlset) CLOSE cur;end;/ -- select all statements that pass a simple buffer_gets threshold and -- are coming from an APPS userDECLARE cur sys_refcursor;BEGIN OPEN cur FOR SELECT VALUE(P) FROM table( DBMS_SQLTUNE.SELECT_CURSOR_CACHE( 'buffer_gets > 100 and parsing_schema_name = ''APPS'''))P; -- Process each statement (or pass cursor to load_sqlset) CLOSE cur;end;/ -- select all statements exceeding 5 seconds in elapsed time, but also-- select the plans (by default we only select execution stats and binds-- for performance reasons - in this case the SQL_PLAN attribute of sqlset_row-- is NULL) DECLARE cur sys_refcursor;BEGIN OPEN cur FOR SELECT VALUE(P) FROM table(dbms_sqltune.select_cursor_cache( 'elapsed_time > 5000000', NULL, NULL, NULL, NULL, 1, NULL, 'EXECUTION_STATISTICS, SQL_BINDS, SQL_PLAN')) P; -- Process each statement (or pass cursor to load_sqlset) CLOSE cur;END;/ -- Select the top 100 statements in the cursor cache ordering by elapsed_time.DECLARE cur sys_refcursor;BEGIN OPEN cur FOR SELECT VALUE(P) FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE(NULL, NULL, 'ELAPSED_TIME', NULL, NULL, 1, 100)) P; -- Process each statement (or pass cursor to load_sqlset) CLOSE cur;end;/ -- Select the set of statements which cumulatively account for 90% of the -- buffer gets in the cursor cache. This means that the buffer gets of all-- of these statements added up is approximately 90% of the sum of all -- statements currently in the cache.DECLARE cur sys_refcursor;BEGIN OPEN cur FOR SELECT VALUE(P) FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE(NULL, NULL, 'BUFFER_GETS', NULL, NULL, .9)) P; -- Process each statement (or pass cursor to load_sqlset). CLOSE cur;END;/************************************************************5.用内存中高资源消耗的sql填充调优集************************************************************---5.0 删除存在的STSBEGIN DBMS_SQLTUNE.DROP_SQLSET( sqlset_name => 'OCPYANG_STS' );END;/--5.1新建调优集BEGIN DBMS_SQLTUNE.CREATE_SQLSET( sqlset_name => 'OCPYANG_STS', sqlset_owner => 'SCOTT', description => 'ocpyangtest');END;/---5.2 通过游标缓存从内存中读取sql填充DECLARE cur DBMS_SQLTUNE.SQLSET_CURSOR;BEGIN OPEN cur FOR SELECT VALUE(x) FROM table( DBMS_SQLTUNE.SELECT_CURSOR_CACHE( 'parsing_schema_name <> ''SYS'' AND disk_reads > 1000000', NULL, NULL, NULL, NULL, 1, NULL,'ALL')) x;-- DBMS_SQLTUNE.LOAD_SQLSET( sqlset_owner =>'SCOTT', sqlset_name => 'OCPYANG_STS', populate_cursor => cur);END;//**********常见错误第 1 行出现错误:ORA-13761: 过滤器无效ORA-06512: 在 "SYS.DBMS_SQLTUNE", line 4715ORA-06512: 在 line 11使用SYS账户执行即可.************************************/SELECT sqlset_name, elapsed_time,cpu_time, buffer_gets, disk_reads, sql_textFROM dba_sqlset_statementsWHERE sqlset_name = 'OCPYANG_STS';---5.3 将内存中指定时间内的所有sql加载--语法:DBMS_SQLTUNE.CAPTURE_CURSOR_CACHE_SQLSET ( sqlset_name IN VARCHAR2, time_limit IN POSITIVE := 1800, repeat_interval IN POSITIVE := 300, capture_option IN VARCHAR2 := 'MERGE', capture_mode IN NUMBER := MODE_REPLACE_OLD_STATS, basic_filter IN VARCHAR2 := NULL, sqlset_owner IN VARCHAR2 := NULL);BEGIN DBMS_SQLTUNE.CAPTURE_CURSOR_CACHE_SQLSET( sqlset_owner =>'SCOTT' , sqlset_name => 'PROD_WORKLOAD' ,time_limit => 3600 --3600秒 ,repeat_interval => 20); --每隔20秒END;/************************************************************6.选择性的从sql调优集中删除sql************************************************************select sqlset_name, disk_reads, cpu_time, elapsed_time, buffer_getsfrom dba_sqlset_statements;BEGIN DBMS_SQLTUNE.DELETE_SQLSET( sqlset_owner => 'SCOTT', sqlset_name => 'IO_STS' ,basic_filter => 'disk_reads < 2000000');END;/************************************************************7.传输sql调优集-STS************************************************************1.新建一个STS---删除存在的STSBEGIN DBMS_SQLTUNE.DROP_SQLSET( sqlset_name => 'OCPYANG_STS' );END;/---新建STSBEGIN DBMS_SQLTUNE.CREATE_SQLSET( sqlset_name => 'OCPYANG_STS', sqlset_owner => 'SCOTT', description => 'ocpyangtest');END;/---2.加载STS(可以参考STS收集的方法)declarebaseline_ref_cur DBMS_SQLTUNE.SQLSET_CURSOR;beginopen baseline_ref_cur forselect VALUE(p) from table(DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(&begin_snap_id, &end_snap_id,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'ALL')) p; DBMS_SQLTUNE.LOAD_SQLSET( sqlset_name => 'my_sql_tuning_set', populate_cursor => baseline_cursor);end;/SELECT * FROM TABLE(DBMS_SQLTUNE.SELECT_SQLSET( 'my_sql_tuning_set', '(disk_reads/buffer_gets) >= 0.75'));---3.新建搜集baseline的表BEGIN dbms_spm.create_stgtab_baseline(table_name => 'BASELINE_STG01',table_owner => 'SCOTT',db_version => DBMS_SQLTUNE.STS_STGTAB_11_2_VERSION ); --不能新建在SYS账户下END;/----4.把Baseline数据填到表BEGIN DBMS_SQLTUNE.pack_stgtab_sqlset( sqlset_name => 'OCPYANG_STS', sqlset_owner => 'SCOTT', staging_table_name => 'BASELINE_STG01', staging_schema_owner => 'SCOTT', db_version => DBMS_SQLTUNE.STS_STGTAB_11_2_VERSION );END;/ /**********语法DBMS_SQLTUNE.PACK_STGTAB_SQLSET ( sqlset_name IN VARCHAR2, sqlset_owner IN VARCHAR2 := NULL, staging_table_name IN VARCHAR2, staging_schema_owner IN VARCHAR2 := NULL, db_version IN NUMBER := NULL);***********/---5.传递数据到目标服务器使用Oracle Data Pump or database link or expdp等将表BASELINE_STG01迁移到目标服务器.---6.目标服务器新建STSBEGIN DBMS_SQLTUNE.CREATE_SQLSET( sqlset_name => 'OCPYANG_STS01', sqlset_owner => 'SCOTT', description => 'ocpyangtest');END;/----7.导入数据到目标服务器的STSBEGIN DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET( sqlset_name => 'OCPYANG_STS01', replace => TRUE, staging_table_name => 'BASELINE_STG01');END;/---8.通过SPM BASELINE的包来把SQL调优集里的SQL都批量的生成BASELINEdeclareret number;beginret := dbms_spm.load_plans_from_sqlset(sqlset_name => 'OCPYANG_STS01',sqlset_owner => 'SCOTT');end;/
0 0
- oracle sql调优集
- sql----oracle
- oracle sql
- Oracle SQL
- Oracle SQL
- Oracle SQL
- sql oRACLE
- oracle sql
- Oracle sql
- Oracle sql
- oracle sql
- oracle sql
- ORACLE-SQL
- Oracle SQL
- Oracle SQL
- oracle.sql
- oracle sql
- Oracle Sql
- Android 自定义View并添加属性
- Unity3D技术之参考坐标系详解
- JasperReports学习笔记6-jasperreports和ssh工程整合
- HTTPS那些事
- storm kafka集成
- oracle sql调优集
- 连号区间数
- nrf24l01的调试测试程序
- 芯客-Rayeager PX2应用之USB摄像头---UVC摄像头篇
- 同一台Windows机器中启动多个Memcached服务
- Unity3D技术之构建3D游戏世界
- Unity3D技术之加载游戏场景并显示进度条实现详解
- 10046入门:使用 10046 查看执行计划并读懂 trace文件
- JQuery写的防止退格键返回的方法