[日记]alter system flush shared_pool的作用
来源:互联网 发布:初中单词背诵软件 编辑:程序博客网 时间:2024/04/29 21:23
2008-03-29 14:28
FLUSH SHARED_POOL Clause
The FLUSH SHARED POOL clause lets you clear all data from the shared pool in the system global area (SGA). The shared pool stores Cached data dictionary information and Shared SQL and PL/SQL areas for SQL statements, stored procedures, function, packages, and triggers.
This statement does not clear shared SQL and PL/SQL areas for items that are currently being executed. You can use this clause regardless of whether your instance has the database dismounted or mounted, open or closed.
实验步骤如下:
----------------------------------------------------------------------------------------
1.查看shared_pool中碎片
SQL> select count(*) from x$ksmsp;
COUNT(*)
----------
7260
2.使用一个以前未曾使用过的查询,来让share pool分配内存,增加share pool中的chunk碎片
SQL> select count(*) from user_tables;
COUNT(*)
----------
667
3.再次查询shared_pool中的碎片
SQL> select count(*) from x$ksmsp;
COUNT(*)
----------
7515
PS:我靠...这增加的也太快了吧...每个buckets的碎片数量>2000就认为是不太好的一个情况,可能会引起share pool latch争用!
4.使用alter system flush shared_pool命令,并再次查询shared_pool中的碎片
SQL> alter system flush shared_pool;
系统已更改。
SQL> select count(*) from x$ksmsp;
COUNT(*)
----------
7194
-----------------------------------------------------------------------------------------------------------------------
总结:执行这个语句的结果是将缓存在library cache和data dictionary cache 中的sql,pl/sql和数据字典定义都从共享池中清除了
PS:在负载很重的生产库里执行flush shared_pool无异于自杀...慎用!
The FLUSH SHARED POOL clause lets you clear all data from the shared pool in the system global area (SGA). The shared pool stores Cached data dictionary information and Shared SQL and PL/SQL areas for SQL statements, stored procedures, function, packages, and triggers.
This statement does not clear shared SQL and PL/SQL areas for items that are currently being executed. You can use this clause regardless of whether your instance has the database dismounted or mounted, open or closed.
实验步骤如下:
----------------------------------------------------------------------------------------
1.查看shared_pool中碎片
SQL> select count(*) from x$ksmsp;
COUNT(*)
----------
7260
2.使用一个以前未曾使用过的查询,来让share pool分配内存,增加share pool中的chunk碎片
SQL> select count(*) from user_tables;
COUNT(*)
----------
667
3.再次查询shared_pool中的碎片
SQL> select count(*) from x$ksmsp;
COUNT(*)
----------
7515
PS:我靠...这增加的也太快了吧...每个buckets的碎片数量>2000就认为是不太好的一个情况,可能会引起share pool latch争用!
4.使用alter system flush shared_pool命令,并再次查询shared_pool中的碎片
SQL> alter system flush shared_pool;
系统已更改。
SQL> select count(*) from x$ksmsp;
COUNT(*)
----------
7194
-----------------------------------------------------------------------------------------------------------------------
总结:执行这个语句的结果是将缓存在library cache和data dictionary cache 中的sql,pl/sql和数据字典定义都从共享池中清除了
PS:在负载很重的生产库里执行flush shared_pool无异于自杀...慎用!
- [日记]alter system flush shared_pool的作用
- alter system flush shared_pool的作用
- alter system flush shared_pool的作用 .
- alter system flush shared_pool;
- alter system flush shared_pool
- alter system flush shared_pool;
- alter system flush "oracle的缓存"
- alter system flush“oracle的缓存”
- alter system flush "oracle的缓存"
- alter system flush buffer_cache;
- alter system flush buffer_cache
- alter system checkpoint的作用
- "flush shared_pool" "version_count过高引起的bug"
- alter system switch logfile作用
- alter system的常用命令
- Response.Flush()的作用
- session.flush()的作用
- alter database 与 alter system的区别
- oracle之位图索引
- BOJ 288
- 【LCA+树形DP】POJ 3417
- 一个逗号都改了N多遍了
- 行业用户身份信息认证服务
- [日记]alter system flush shared_pool的作用
- PHP读写XML文件
- MA和EMA的数学表达式
- Android jni
- CB程序练手总结——家用记账小程序(附链接)
- 述CRichEditCtrl的先天不足及进化方法
- 自定義ProgressBar大小
- JDK动态代理和CGLIB代理
- python之dict