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緩沖池,並改變其大小。