Scripts:从shared_pool中创建并删除profile的脚本(注意,此脚本危险比较高,生产环境不要使用)flush_sql10p.sql
来源:互联网 发布:青岛惠普大数据图片 编辑:程序博客网 时间:2024/05/16 18:29
----------------------------------------------------------------------------------------
--
-- File name: flush_sql10p.sql
--
-- Purpose: Flush a single SQL statement.
-
-- Author: Kerry Osborne
--
-- Usage: This scripts prompts for a sql_id.
--
-- sql_id: the sql_id of a statement that is in the shared pool (v$sql)
--
--
-- Description: This scripts creates a SQL Profile on the specified statement and then
-- drops it. This has the side affect of flushing the statement from the
-- shared pool. Well, not always flushing, but generally marking any existing
-- children unusable resulting in a parse the next time the statement is
-- executed. Note, this is the second attempt at this. The first was based
-- on Outlines which was a bit flakey.
--
-- See kerryosborne.oracle-guy.com for additional information.
---------------------------------------------------------------------------------------
accept sql_id -
prompt 'Enter value for sql_id: ' -
default 'X0X0X0X0'
set feedback off
set sqlblanklines on
set serveroutput on for wrap
declare
cl_sql_text clob;
begin
select sql_fulltext into cl_sql_text
from v$sql
where sql_id = '&&sql_id'
and rownum < 2;
dbms_sqltune.import_sql_profile(
sql_text => cl_sql_text,
profile => sqlprof_attr('dummy hint'),
category => 'FLUSH',
name => 'FLUSH_'||'&&sql_id'
);
dbms_sqltune.drop_sql_profile (name => 'FLUSH_'||'&&sql_id');
dbms_output.put_line(' ');
dbms_output.put_line('sql_id: '||'&&sql_id'||' flushed.');
dbms_output.put_line(' ');
exception
when NO_DATA_FOUND then
dbms_output.put_line(' ');
dbms_output.put_line('ERROR: sql_id: '||'&&sql_id'||' not found in v$sqlarea.');
dbms_output.put_line(' ');
end;
/
undef sql_id
set sqlblanklines off
set feedback on
--
-- File name: flush_sql10p.sql
--
-- Purpose: Flush a single SQL statement.
-
-- Author: Kerry Osborne
--
-- Usage: This scripts prompts for a sql_id.
--
-- sql_id: the sql_id of a statement that is in the shared pool (v$sql)
--
--
-- Description: This scripts creates a SQL Profile on the specified statement and then
-- drops it. This has the side affect of flushing the statement from the
-- shared pool. Well, not always flushing, but generally marking any existing
-- children unusable resulting in a parse the next time the statement is
-- executed. Note, this is the second attempt at this. The first was based
-- on Outlines which was a bit flakey.
--
-- See kerryosborne.oracle-guy.com for additional information.
---------------------------------------------------------------------------------------
accept sql_id -
prompt 'Enter value for sql_id: ' -
default 'X0X0X0X0'
set feedback off
set sqlblanklines on
set serveroutput on for wrap
declare
cl_sql_text clob;
begin
select sql_fulltext into cl_sql_text
from v$sql
where sql_id = '&&sql_id'
and rownum < 2;
dbms_sqltune.import_sql_profile(
sql_text => cl_sql_text,
profile => sqlprof_attr('dummy hint'),
category => 'FLUSH',
name => 'FLUSH_'||'&&sql_id'
);
dbms_sqltune.drop_sql_profile (name => 'FLUSH_'||'&&sql_id');
dbms_output.put_line(' ');
dbms_output.put_line('sql_id: '||'&&sql_id'||' flushed.');
dbms_output.put_line(' ');
exception
when NO_DATA_FOUND then
dbms_output.put_line(' ');
dbms_output.put_line('ERROR: sql_id: '||'&&sql_id'||' not found in v$sqlarea.');
dbms_output.put_line(' ');
end;
/
undef sql_id
set sqlblanklines off
set feedback on
0 0
- Scripts:从shared_pool中创建并删除profile的脚本(注意,此脚本危险比较高,生产环境不要使用)flush_sql10p.sql
- Scripts:根据sql_id创建sql_profile的脚本(此脚本要慎用)gps.sql
- Scripts:创建手工的SQL PROFILE的脚本,老外写的很好用coe_xfr_sql_profile.sql
- Scripts:重新编译失效对象的脚本(生产环境慎用)dba_recompile_invalid_objects.sql
- Scripts:此脚本慎用lob_fragmentation_user.sql
- Scripts:从AWR中找到SQL执行相关的东西(弄懂脚本再用哦)find_sql_awr.sql
- Scripts:显示AWR中执行计划的脚本dplan_allstats.sql
- Scripts:显示数据库中DML锁的脚本 locks_dml_lock_time.sql
- Scripts:查询每个数据文件使用效率的脚本perf_file_io_efficiency.sql
- SQL屏蔽危险脚本
- Scripts:列出用户信息的脚本sec_users.sql
- Scripts:重建索引的脚本(看懂脚本再用)rebuild_indx.sql
- coe_xfr_sql_profile.sql 从shared pool、awr中提取指定的SQL ID的Outline Data并创建SQL Profile。
- Scripts:创建SQL PROFILE create_sql_profile.sql
- Scripts:查看使用PGA最多的10个SQL的脚本hpga10.sql
- Scripts:找出使用最多buffer get的SQL脚本perf_top_sql_by_buffer_gets.sql
- CentOS 中/etc/profile.d/path.sh 脚本的使用
- Scripts:分析RAC hang的脚本(此脚本要慎用,在某些版本下可能会导致系统重启)RACDIAG.SQL
- 2014搞笑段子
- Linux用户空间与内核空间(转)
- java字符串反转切割
- 省赛总结
- 整理下网络知识
- Scripts:从shared_pool中创建并删除profile的脚本(注意,此脚本危险比较高,生产环境不要使用)flush_sql10p.sql
- dstmnjd m vght,f
- zoj 2676 Network Wars(最小割,01分数规划)
- Maven 核心概念——坐标
- sqlcommand.parameters.add
- C++内联成员函数练习
- 师兄博士答辩有感
- CreateUserWizard输入密码和设置安全问题
- Scripts:显示所有闪回日志的脚本fdb_log_files.sql