ORACLE_DIARY

SQL执行过程

1. 客户端输入sql语句，如： select * from dba_data_files
2. sql语句通过网络到达数据库实例
3. server process接收sql语句
   1. sql–解析成执行计划(缓存到shared pool)，然后才能执行
   2. 检查语法
   3. 权限

shared pool

shared pool : （chain,chunk）
	free, 
	libarary cache（缓存sql和执行计划）,
	row cache(字典缓存) 

清空共享池（注意：清除之后，会产生大量的硬解析）：

alter system flush shared_pool

sql的硬解析和软解析

硬解析：判断和在寻找最优执行计划等
软解析：只做判断
sql --> ascii --> 运算 --> 值 记录下来作为缓存 ，chain和chunk(相当于地址)

查看软硬解析的具体情况：

select name,value from v$sysstat where name like 'parse%'

共享sql,绑定变量

SQL语句组成 动态部分 静态部分
查看sql id

select SQL_ID,sql_text,EXXECUTIONS 
	from v$sql where SQL_TEXT like 

找出没有共享的SQL语句
如何找出没有共享的sql语句
在v$sql中查找执行次数较少的sql语句很有可能没有共享，观察这些sql语句是否是经常执行的

select SQL_FULLTEXT 
	from v$sql 
	where EXECUTIONS=1 
	and sql_text like '%from t%';
select SQL_FULLTEXT 
	from v$sql 
	where EXECUTIONS=1 order by sql_text;

命中率

命中率：逻辑读比上逻辑读加物理读 L/L+P

select sum(pinhits)/sum(pins)*100 
	from v$librarycache;
select sum(gets),sum(getmisses),
	100*sum(gets-getmisses)/sum(gets) 
	from v$rowcache where gets>0;

解决4031错误

临时解决：

alter system flush shared_pool;

用共享SQL解决

alter system set cursor_sharing='force';

keep 掉大的chunk
 {	 select * 
	from v$db_object_cache 
	where sharable_mem >1000 
	and (
		type = 'PACKAGE' or type = 'PACKAGE BODY' or type = 'FUNCTION' or type = 'PROCEDURE'
			) 
		and kept = 'NO';

执行	

dbms_shared_pool.keep('对象名');

DBMS_SHARED_POOL
@?/rdbms/admin/dbmspool.sql
}

保留区

select REQUEST_MISSES from v$shared_pool_reserved;

增加shared_pool空间

select COMPONENT,CURRENT_SIZE from v$SGA_DYNAMIC_COMPONENTS;
show parameter sga_target
show parameter sga_max_size
alter system set shared_pool size = 150M scope = both;

buffer cache

表 = 段 分配区 区是连续的块（block）块放数据行（多个）

块是oracleIO的最小单位，
块在buffer cache中是buffer

buffer cache 的意义

减少IO

undo 构造cr块，回滚未提交数据

buffer cache 内存结构

CBC : cache buffer chain 以地址的形式把buffer cache 中所有的buffer链起来
LRU : 最近最少使用
LRUW : 脏  按照被脏频率连接起来
	checkpoint

buffer cache 大小设置及依据

buffer cache 的重要参数配置

select component,current_size from v$sga_dynamic_components;

Buffer Cache的大小配置

`alter system set db_cache_size=20M scope=both;`

在OLTP系统中，对于DB_CACHE_SIZE的设置，推荐配置是：
DB_CACHE_SIZE = SGA_MAS_SIZE/2~SGA_MAX_SIZE*2/3
使用advice来确认buffer cache的大小

select size_for_estimate "cache size (MB)",
	size_factor,buffers_for_estimate "buffers",
	estd_physical_read_factor est_read_factor,
	estd_physical_reads estd_phy_red,estd_physical_read_time est_phy_red_t
	from v$db_cache_advice where name='DEFAULT' 
	and block_size = (
	select value from v$parameter where name = 'db_block_size'
						);

buffer 状态

	x$bh

create table t1 (id number,name varchar2(10));
insert into t1 (1,'probe')

state:
	0:FREE no valid block image
	1:XCUR a current mode block exclusive to this instance
	2:SCUR a current mode block,shared with other instances
	3:CR a consistent read(stale) block image
	4:READ buffer is reserved for a block being read from disk
	5:MREC a block media recovery mode
	6:IREC a block in instance (crash) recovery mode

一个对象占用一个buffer的具体情况

select o.object_name,
	decode(state,0,'free',1,'xcur',2,'scur',3,'cr',4,'read',5,'mrec',6,'irec',7,'write',8,'pi') state, 
	count(*) blocks from x$bh b,dba_objects o 
	where b.obj = o.data_object_id 
	and o.object_name = 'T1' 
	group by o.object_name,state 
	order by blocks desc;

t1表使用内存的情况：

select distinct object_name,DBARFIL,DBABLK 
	from x$bh a,dba_objects b
	where a.obj = b.object_id and object_name = 'T1';

94577块的内存状态：

  select class,flag,state,lru_flag 
	  from x$bh where dbarfil = 1 and dbablk = 94577;

列出buffer cache 中所有对象占用的buffer的情况(对象使用pool的具体情况-各种池子)：

	select o.object_name,
	decode(state,0,'free',1,'xcur',2,'scur',3,'cr',4,'read',5,'mrec',6,'irec',7,'write',8,'pi') state, 
	count(*) blocks from x$bh b,dba_objects o 
	where b.obj = o.data_object_id 
	and state <>0 
	group by o.object _name,state
	order by blocks asc;

寻找热块：

	select
		obj		object,
		dbarfil file#,
		dbablk	block#,
		tch		touches
		from x$bh where tch > 10 order by tch asc;

找出热块对应的表的名字

		select distinct object_name,DBARFIL,DBABLK 
			from x$bh a,dba_objects b
			where a.obj = b.object_id and dbarfil = 1 
				and dbablk = 2016;

整个数据库所有文件中block的总和

	select sum(blocks)
		from dba_data_files;

空间空闲的比例，最好控制在10%以内

	select decode
		(
			state,0,'FREE',1,
			decode(lrba_seq,0,'AVAILABLE','BEING USED'),
			3,'BEING USED',state
		) "BLOCK STATUS",
		count(*) from x$bh 
		group by decode
		(
			state,0,'FREE',1,
			decode(lrba_seq,0,'AVAILABLE','BEING USED'),
			3,'BEING USED',state
		);

最浪费内存的前十个语句占所有语句的比例，建议控制在5%以内

	select sum(pct_bufgets) "percents"
		from (select rank() over (order by buffer_gets desc) 
		as rank_bufgets,to_char(100*ratio_to_report(buffer_gets)
		over (),'999.99') pct_bufgets from v$sqlarea) 
		where rank_bufgets < 11;

找出消耗物理IO资源最大的sql语句

	select disk_reads,substr(sql_text,1,4000) 
		from v$sqlarea order by disk_reads desc; 

找出消内存资源最大的sql语句

  select BUFFER_GETS,substr(sql_text,1,4000) 
  from v$sqlarea order by BUFFER_GETS desc;

表里面的每一行的rowid的块

create or replace function get_rowid(one_rowid in varchar2)
  return varchar2
  is
  ls_my_rowid varchar2(200);
  rowid_type number;
  object_number number;
  relative_fno number;
  block_number number;
  row_number number;
  begin
    dbms_rowid.rowid_info(one_rowid,rowid_type,object_number,relative_fno,block_number,row_number);
    ls_my_rowid := 'Object# is :'||to_char(object_number)||chr(10)||
       'Relative_fno is :'||to_char(relative_fno)||chr(10)||
       'Block_number is :'||to_char(block_number)||chr(10)||
       'Row_number is   :'||to_char(row_number);
     return ls_my_rowid;
   end;
   /
select dbms_rowid.rowid_block_number(rowid), name from t1;
select rowid,t2.* from t2;	
select get_rowid('AAAVZNAABAAAXFxAAA') row_id from dual;

最后

以上就是迷路小蜜蜂为你收集整理的ORACLE_DIARY的全部内容，希望文章能够帮你解决ORACLE_DIARY所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。