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无异于自杀...慎用!
分享到:
相关推荐
ALTER TABLE `ecs_brand` ADD COLUMN `brand_banner` VARCHAR(80) NOT NULL COMMENT '商品品牌banner'; ALTER TABLE `ecs_goods` ADD COLUMN `sales_count` int(10) UNSIGNED NOT NULL DEFAULT 0 AFTER `click_...
– 解决性能问题的步骤 分析症状 确定问题范围 参数调整or 结构调整or <br> 应用调整 性能监控 <br>Shared Pool – SHARED_POOL_SIZE 控制共享SQL缓冲存储区和数据字 ...
数据库触发器样例程序ALTER TRIGGER [dbo].[djh_wt1] ON [dbo].[WT03] FOR INSERTASinsert into djh_wt select yllhdbo.ylmc(yllh) as ylmcjscbzxbmcljzjscwdmczsjchcllb from inserted
SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION; System altered. SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0; System altered. SQL> ALTER SYSTEM SET AQ_TM_PROCESSES=0; System altered. 启动数据库...
ALTER TABLE `ecs_cart` ADD `package_attr_id` VARCHAR( 100 ) NOT NULL; ALTER TABLE `ecs_order_goods` ADD `package_attr_id` VARCHAR( 100 ) NOT NULL; 需要修改的文件 /goods.php /flow.php /includes/lib_...
alter table emlog_user add sex varchar(2) NOT NULL DEFAULT ‘1’; alter table emlog_user add qq varchar(20) NOT NULL; alter table emlog_user add zhuye varchar(50) NOT NULL; alter table emlog_user add ...
alter table in Sql server
Quartus ip核中alter_avalon_sc_fifo.v文件
alter system set sec_case_sensitive_logon=false ; --Oracle11g中Exp空表的问题:禁用插入数据时才分配空间功能 show parameter deferred_segment_creation alter system set deferred_segment_creation=false; ...
文章观看后评论插件V4.0版(紧急修正) 1. 进入后台-----数据库管理---将下面代码复制到框中--运行 2.V4.0版本请在整站目录下面imagess/风格...ALTER TABLE `p8_article` ADD `ddf_time` int(10) DEFAULT '0' NULL
Game alter ego for nintendo with source code
alter system set db_recovery_file_size=20G; SQL> shutown immediate SQL> conn / as sysdba SQL> startup mount SQL> alter database archivelog; 启动归档 SQL> alter database open; SQL> archive...
一个字段: ALTER TABLE table_name MODIFY(字段类型 数据类型)); 添加一个字段: ALTER TABLE table_name ADD(字段类型 数据类型);
[扩大序列缓存]alter_seq_cache.sql
1、 启用快速恢复区域 将 oracle 数据库置于“安装模式”...SQL> alter system set db_recovery_file_dest_size=8G; SQL> alter system set db_recovery_file_dest=/opt/oracle/fast_recovery_area; SQL> startup force
alter system set open_cursors = 10000; 3.获取打开的游标数 select o.sid, osuser, machine, count(*) num_curs from v$open_cursor o, v$session s where user_name = 'AF651_U8' and o.sid = s.sid group...
有时不清楚一些参数的... alter system set “_db_block_cache_protect”=what scope=spfile * ERROR at line 1: ORA-00096: invalid value WHAT for parameter _db_block_cache_protect, must be from among TRUE, M
alter session set nls_date_language='AMERICAN' 1、显示雇员EMP表的所有数据 select * from emp; 2、显示部门DEPT表的所有数据 select * from dept; 3、只显示部门表的deptno和loc列 select deptno,loc from ...
SQL>alter system set control_files=’/u01/app/oracle/oradata/prod/ora_control1.ctl’,’/u01/app/oracle/oradata/prod/ora_control2.ctl’ scope=spfile; 解决方法: 1.show parameter spfile; 这时候可以...
alter user system default tablespace users01; 8、 创建数据字典 start E:\app\oracle\product\11.1.0\db_1\RDBMS\ADMIN\catalog.sql 9、创建package 包 start E:\app\oracle\product\11.1.0\db_1\RDBMS\...