DBA 日常管理过程中会遇到需要把特定的sql语句的执行计划从shared pool
中清除或者需要重新生产新的执行计划的要求。通常的alter system flush
shared_pool;或者重新执行信息统计又会造成较大的影响，所以可以使用oracle提供的
dbms_shared_pool.purge 来清除某个特定的sql的执行计划。
下面是关于dbms_shared_pool.purge 的官方介绍：
The syntax for the DBMS_SHARED_POOL.PURGE package is:
procere purge (name varchar2, flag char DEFAULT 'P', heaps number DEFAULT 1);
name: 指定要处理的对象的名称，有两种情况：
1 PL/SQL 对象 就是对象的名称
2 SQL对象，指定 'address','hash_value' 的 SQL 游标。
标记位 flag是可选的。如果忽略了该参数，则该包默认是包，过程，函数的名称并且忽视名称。否则，该参数就指定一个对象类型。标记位是大小写敏感的。
标记位代表的对象类型：
Value Kind of Object to keep
----- ----------------------
P package/procere/function
Q sequence
R trigger
T type
JS java source
JC java class
JR java resource
JD java shared data
C cursor --游标
heaps: heaps to purge. e.g if heap 0 and heap 6 are to be purged.
1<<0 | 1<<6 => hex 0x41 => decimal 65. so specify heaps=>65.
Default is 1 i.e heap 0 which means the whole object will be purged.
实验环境 版本：11.2.0.1.0 dbms_shared_pool.purge 只能通过dba才能使用
oracle@rac1:rac1 /home/oracle>sqlplus yang/yang
SQL*Plus: Release 11.2.0.1.0 Proction on Fri Jul 8 15:32:43 2011
yang@rac1>create table yangtab (id int) ;
Table created.
yang@rac1>select * from yangtab;
no rows selected
yang@rac1>select address,hash_value,executions,parse_calls
2 from v$sql where sql_text like 'select * from yangtab%';
ADDRESS HASH_VALUE EXECUTIONS PARSE_CALLS
---------------- ---------- ---------- -----------
00000001736EFBB8 3337951036 1 1
yang@rac1>exec dbms_shared_pool.purge('00000001736EFBB8,3337951036','C');
BEGIN dbms_shared_pool.purge('00000001736EFBB8,3337951036','C'); END;
*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'DBMS_SHARED_POOL.PURGE' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
yang@rac1>conn /as sysdba
Connected.
sys@rac1>create table yangt (id int);
Table created.
sys@rac1>select * from yangt;
no rows selected
sys@rac1>select address,hash_value,executions,parse_calls
2 from v$sql where sql_text like 'select * from yangt%';
ADDRESS HASH_VALUE EXECUTIONS PARSE_CALLS
---------------- ---------- ---------- -----------
00000001736EFBB8 3337951036 1 1 -- 使用普通用户时生成的。
0000000170D9BA50 2693392179 1 1
使用dbms_shared_pool.purge 删除第一个sql的游标。
sys@rac1> exec dbms_shared_pool.purge('00000001736EFBB8,3337951036','C');
PL/SQL procere successfully completed.
sys@rac1>select address,hash_value,executions,parse_calls
2 from v$sql where sql_text like 'select * from yangt%';
ADDRESS HASH_VALUE EXECUTIONS PARSE_CALLS
---------------- ---------- ---------- -----------
0000000170D9BA50 2693392179 1 1
sys@rac1>exec dbms_shared_pool.purge('0000000170D9BA50,2693392179','C');
PL/SQL procere successfully completed.
sys@rac1>select address,hash_value,executions,parse_calls
2 from v$sql where sql_text like 'select * from yangt%'
no rows selected
❷ 怎样能清除SQL缓存数据
先设置db_recycle_cache_size的大小
然后把你希望不缓存的segment alter table / index ... storage (buffer_pool recycle)
这个只能在segment级别上定义，没法在sql上定义
❸ 如何清除SQL缓存数据
先设置db_recycle_cache_size的大小
然后把你希望不缓存的segment alter table / index ... storage (buffer_pool recycle)
这个只能在segment级别上定义，没法在sql上定义
❹ 如何清空sql2008 快速查询框中的缓存，经常使用ctrl+F查找代码，导致查询的记录都在里面
直接建个查询：
dbcc freeproccache
❺ SQL清除语句
CREATE SNAPSHOT [schema.]snapshot
[ [PCTFREE integer] [PCTUSED integer]
[INITRANS integer] [MAXTRANS integer]
[TABLESPACE tablespace]
[STORAGE storage_clause]
[ USING INDEX [ PCTFREE integer | TABLESPACE tablespace
| INITTRANS integer | MAXTRANS integer
| STORAGE storage_clause ] ...
| [CLUSTER cluster (column [, column]...)] ]
[ REFRESH [FAST | COMPLETE | FORCE] [START WITH date] [NEXT date]]
AS subquery
schema
is the schema to contain the snapshot. If you omit schema, Oracle
creates the snapshot in your schema.
snapshot
is the name of the snapshot to be created.
Oracle chooses names for the table, views, and index used to
maintain the snapshot by prefixing the snapshot name. To limit
these names to 30 bytes and allow them to contain the entire
snapshot name, Oracle Corporation recommends that you limit your
snapshot names to 23 bytes.
PCTFREE
PCTUSED
INITRANS
MAXTRANS
establishes values for these parameters for the internal table
Oracle uses to maintain the snapshot's data.
TABLESPACE
specifies the tablespace in which the snapshot is to be created. If
you omit this option, Oracle creates the snapshot in the default
tablespace of the owner of the snapshot's schema.
STORAGE
establishes storage characteristics for the table Oracle uses to
maintain the snapshot's data.
USING INDEX
specifies the storage characteristics for the index on a simple
snapshot. If the USING INDEX clause not specified, the index is
create with the same tablespace and storage parameters as the
snapshot.
CLUSTER
creates the snapshot as part of the specified cluster. Since a
clustered snapshot uses the cluster's space allocation, do not use
the PCTFREE, PCTUSED, INITRANS, or MAXTRANS parameters, the
TABLESPACE option, or the STORAGE clause in conjunction with the
CLUSTER option.
REFRESH
specifies how and when Oracle automatically refreshes the snapshot:
FAST
specifies a fast refresh, or a refresh using only the
updated data stored in the snapshot log associated
with the master table.
COMPLETE
specifies a complete refresh, or a refresh that re-
executes the snapshot's query.
FORCE
specifies a fast refresh if one is possible or
complete refresh if a fast refresh is not possible.
Oracle decides whether a fast refresh is possible at
refresh time.
If you omit the FAST, COMPLETE, and FORCE options,
Oracle uses FORCE by default.
START WITH
specifies a date expression for the first automatic
refresh time.
NEXT
specifies a date expression for calculating the
interval between automatic refreshes.
Both the START WITH and NEXT values must evaluate to a time in the
future. If you omit the START WITH value, Oracle determines the
first automatic refresh time by evaluating the NEXT expression when
you create the snapshot. If you specify a START WITH value but omit
the NEXT value, Oracle refreshes the snapshot only once. If you
omit both the START WITH and NEXT values or if you omit the REFRESH
clause entirely, Oracle does not automatically refresh the snapshot.
AS subquery
specifies the snapshot query. When you create the snapshot, Oracle
executes this query and places the results in the snapshot. The
select list can contain up to 253 expressions. A snapshot query is
subject to the same restrictions as a view query.
PREREQUISITES:
To create a snapshot in your own schema, you must have CREATE
SNAPSHOT system privilege. To create a snapshot in another user's
schema, you must have CREATE ANY SNAPSHOT system privilege.
Before a snapshot can be created, the user SYS must run the SQL
script DBMSSNAP.SQL on both the database to contain the snapshot and
the database(s) containing the tables and views of the snapshot's
query. This script creates the package SNAPSHOT which contains both
public and private stored proceres used for refreshing the
snapshot and purging the snapshot log. The exact name and location
of this script may vary depending on your operating system.
When you create a snapshot, Oracle creates a table, two views, and
an index in the schema of the snapshot. Oracle uses these objects
to maintain the snapshot's data. You must have the privileges
necessary to create these objects. For information on these
privileges, see the CREATE TABLE, CREATE VIEW, and CREATE INDEX
commands.
The owner of the schema containing the snapshot must have either
space quota on the tablespace to contain the snapshot or UNLIMITED
TABLESPACE system privilege. Also, both you (the creator) and the
owner must also have the privileges necessary to issue the
snapshot's query.
To create a snapshot, you must be using Oracle with the proceral
option. To create a snapshot on a remote table or view, you must
also be using the distributed option.
❻ sqlserver 中一些常看的指标和清除缓存的方法
如何查看磁盘I/O操作信息
SET
STATISTICS
IO
ON
命令是一个
使
SQL
Server
显示有关由
Transact-SQL
语句生成的磁盘活动量的信息。
我们在分析索引性能的时候，会非常有用。
启用了这个属性后，我们在执行
SQL
语句后，会收到类似如下的信息，这有利于我们分析SQL的性能：
(3999
row(s)
affected)
表
'ChargeCL'。扫描计数
1，逻辑读取
9547
次，物理读取
0
次，预读
0
次，lob
逻辑读取
0
次，lob
物理读取
0
次，lob
预读
0
次。
其中的
lob
逻辑读取、lob
物理读取、lob
预读
这三个指标是
读取
text、ntext、image
或大值类型
(varchar(max)、nvarchar(max)、varbinary(max))
时的指标。
而
逻辑读取、物理读取、预读
是对普通数据页的读取。
使用
SQL
Server
Management
Studio
Standard
Reports
我们在
SQL
Server
Management
Studio
中，选择数据库服务器，或者具体数据库，或者Security
--
Logins
时，或者Management
时，Notification
Services
或者
SQL
Server
Agent
对象时候，都会看到SQL
Server
替我们提供的一些现成报表，这些报表的数据，有利于我们分析数据库的状态。
比如在
SQL
Server
索引基础知识(1)---
记录数据的基本格式
http://blog.joycode.com/ghj/archive/2008/01/02/113290.aspx
中，我们就使用数据表占用空间的报表
具体报表可以参考以下链接：
SQL
Server
Management
Studio
Standard
Reports
-
Overview
http://blogs.msdn.com/buckwoody/archive/2007/10/09/sql-server-management-studio-standard-reports-overview.aspx
测试中，释放缓存的一些方法
尤其查询语句性能测试时，数据是否被缓存，这是测试中一个重要点。下面几个命令帮助我们清除缓存。方便测试。
清除缓存有关的命令：
SQL
2000里面除了dbcc
unpintable好像就没有了
而且这个操作也不会立即释放表内存Buffer
(DBCC
UNPINTABLE
does
not
cause
the
table
to
be
immediately
flushed
from
the
data
cache.
It
specifies
that
all
of
the
pages
for
the
table
in
the
buffer
cache
can
be
flushed
if
space
is
needed
to
read
in
a
new
page
from
disk.)
SQL
2005/2008让DBA能够更自由的对SQL所占用的内存空间做处理
如：
CHECKPOINT
将当前数据库的全部脏页写入磁盘。“脏页”是已输入缓存区高速缓存且已修改但尚未写入磁盘的数据页。CHECKPOINT
可创建一个检查点，在该点保证全部脏页都已写入磁盘，从而在以后的恢复过程中节省时间。
DBCC
DROPCLEANBUFFERS
从缓冲池中删除所有清除缓冲区。
DBCC
FREEPROCCACHE
从过程缓存中删除所有元素。
DBCC
FREESYSTEMCACHE
从所有缓存中释放所有未使用的缓存条目。SQL
Server
2005
数据库引擎会事先在后台清理未使用的缓存条目，以使内存可用于当前条目。但是，可以使用此命令从所有缓存中手动删除未使用的条目。
另外还可以
sp_cursor_list
查看全部游标
DBCC
OPENTRAN查看数据库打开事务状态等
❼ 如何清除Hibernate JPA中缓存的sql语句
fulsh强制session东西刷新能提交数据删除或具体看程序所说清空错提交掉或刷新掉说能准确些,比添加量数据,性session面放入量象,严重影响性能,执行部执行fulsh(),强制前session面象进行持久化! 清空要使用clear(),evict都效
❽ 如何清除SQL Server 缓存
1.打开Sql Server Management(企业管理器);
2.打开Sql Server实例的属性面板;3.找到内存设置，改变其中的最大服务器内存使用即可 。
❾ sql删除缓冲区的语句的语句
sp_cacheconfig 创建或删除命名缓冲区,改变缓冲区的大小或类型。
sp_poolconfig 创建或删除I/O缓冲池,并改变其大小。