本文主要是介绍我的小金库(跬步千里)，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！
------------------------------------------PGA-----------------------------------------
预估pga_target的语句（pga_target的大小）：
SELECT round(PGA_TARGET_FOR_ESTIMATE/1024/1024) target_mb,ESTD_PGA_CACHE_HIT_PERCENTAGE cache_hit_perc,ESTD_EXTRA_BYTES_RW/1024/1024 "Extra read/write_mb",ESTD_OVERALLOC_COUNTFROM V$PGA_TARGET_ADVICE;查看_pga_max_size隐含参数:
select ksppinm "NAME" , ksppstvl "Value", ksppdesc "Desc"
from x$ksppi x, x$ksppcv y
where x.indx=y.indx and ksppinm='_pga_max_size';-----------------------------------------------SGA----------------------------------------------------
shared_pool的组成：
3块区域：free、library cache、row cache
select * from v$sgastat a where a.NAME='library cache';
select * from v$sgastat a where a.POOL='shared pool' and a.NAME='free memory';
select * from v$sgastat a where a.NAME='row cache';
在SGA手动管理模式下，shared pool（shared_pool_size）应该设多大，估算shared_pool_size的语句
SELECT 'Shared Pool' component,shared_pool_size_for_estimate estd_sp_size,estd_lc_time_saved_factor parse_time_factor,
CASE
WHEN current_parse_time_elapsed_s + adjustment_s < 0
THEN 0
ELSE
current_parse_time_elapsed_s + adjustment_s
END response_time
FROM
(select shared_pool_size_for_estimate,shared_pool_size_factor,estd_lc_time_saved_factor,
a.estd_lc_time_saved,e.VALUE / 100 current_parse_time_elapsed_s,c.estd_lc_time_saved - a.estd_lc_time_saved adjustment_s
FROM v$shared_pool_advice a,(SELECT * FROM v$sysstat WHERE NAME ='parse time elapsed') e,
(SELECT estd_lc_time_saved FROM v$shared_pool_advice WHERE shared_pool_size_factor = 1) c);
COMPONENT ESTD_SP_SIZE PARSE_TIME_FACTOR RESPONSE_TIME
----------- ------------ ----------------- -------------
Shared Pool 104 1 6.77
Shared Pool 116 1.0118 5.77
Shared Pool 128 1.0353 3.77
Shared Pool 140 1.0353 3.77
Shared Pool 152 1.0353 3.77
Shared Pool 164 1.0353 3.77
Shared Pool 176 1.0353 3.77
Shared Pool 188 1.0353 3.77
Shared Pool 200 1.0353 3.77
Shared Pool 212 1.0353 3.77
查看解析相关的值：
select name,value from v$sysstat where name like 'parse%';
NAME VALUE
---------------------------------------------------------------- ----------
parse time cpu 121
parse time elapsed 791
parse count (total) 4914
parse count (hard) 1140
parse count (failures) 0查看当前会话的解析情况：
select a.*,b.name
from v$sesstat a , v$statname b
where a.statistic#=b.statistic#
and a.sid=(select distinct sid from v$mystat)
and b.name like '%parse%'; SID STATISTIC# VALUE NAME
---------- ---------- ---------- ----------------------------------------------------------------
140 328 11 parse time cpu
140 329 11 parse time elapsed
140 330 275 parse count (total)
140 331 62 parse count (hard)
140 332 1 parse count (failures)
查看当前SGA中各个池的大小
select component,current_size,min_size from v$sga_dynamic_components; （default 、keep、recycle分开）
select * from v$sgainfo; (default 、keep、recycle不分开)
估算buffer_cache（default池）的语句（db_cache_size的大小）：
**注：db_cache_size=default的大小、db_keep_cache_size=keep的大小、db_recycle_cache_size=recycle的大小
COLUMN size_for_estimate FORMAT 999,999,999,999 heading 'Cache Size (MB)'
COLUMN buffers_for_estimate FORMAT 999,999,999 heading 'Buffers'
COLUMN estd_physical_read_factor FORMAT 999.90 heading 'Estd Phys|Read Factor'
COLUMN estd_physical_reads FORMAT 999,999,999 heading 'Estd Phys| Reads'SELECT size_for_estimate,size_factor, buffers_for_estimate, estd_physical_read_factor, estd_physical_readsFROM V$DB_CACHE_ADVICEWHERE name = 'DEFAULT'AND block_size = (SELECT value FROM V$PARAMETER WHERE name = 'db_block_size')AND advice_status = 'ON';------------------------------------------------buffer cache相关查询语句----------------------------------------------------------------------------------------
一个对象占用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;*X$BH helps you examine the type and the status of the object in database buffer.(并不是default池中的所有块) ----一行就是一个buffer的信息
---buffer所有对象各占用多少块，通过这条语句我可以找到那个对象占用的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 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;SQL> select object_name,object_id from dba_objects where object_id=287;OBJECT_NAME OBJECT_ID
-------------------------------------------------------------------------------- ----------
JOB$ 287SQL> select object_name,DBARFIL,DBABLK from x$bh a,dba_objects b where a.obj=b.object_id and DBARFIL=1 and DBABLK=2008;OBJECT_NAME DBARFIL DBABLK
-------------------------------------------------------------------------------- ---------- ----------
JOB$ 1 2008查看buffer cache中buffer们的状态
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);BLOCK STATUS COUNT(*)
---------------------------------------- ----------
BEING USED 171
AVAILABLE 16188
FREE 4
查看检查点队列（checkpoint queue）上的的脏块树，和控制文件中记录的LRBA、on disk rba
select CPDRT,CPLRBA_SEQ||'.'||CPLRBA_BNO||'.'||CPLRBA_BOF "Low
RBA",CPODR_SEQ||'.'||CPODR_BNO||'.'||CPODR_BOF "On disk RBA",CPODS,CPODT,CPHBT from x$kcccp;
CPDRT列是检查点队列中的脏块数目.
CPODS列是on disk rba的scn
CPODT列是on disk rba的时间戳
CPHBT列是心跳
------------------------------------------------------------------------------------------------------------------------------------
找出消耗物理IO资源最大的sql语句
select disk_reads,substr(sql_text,1,4000) from v$sqlarea order by disk_reads asc;
操作系统看IO的命令：iostat 1 10
vmstat 1 10
top
mpstat 1 10mpstat -P 0 1(0号CPU的使用情况) mpstat -P 1 1
查看链接到库上的会话情况（所用的程序、登陆时间）
select sid,serial#,paddr,program,username,to_char(logon_time,'yyyy-mm-dd hh24:mi:ss') from v$session where username='LOGIN' order by logon_time;
查看当前实例的上的server process信息（包括对应的PGA的使用情况，v$process中的ADDR字段和v$session中的PADDR字段对应）
select spid,program,pga_max_mem,pga_alloc_mem,pga_used_mem,pga_freeable_mem,addr from v$process;
ps -ef|grep LOCAL
查看那些没有运用绑定变量，一直在做硬解析的sql
select sql_fulltext from v$sql where executions=1 order by sql_text;
查看各种latch的争用情况
select addr,latch#,name,gets,spin_gets from v$latch order by spin_gets;
通过rowid获得对象号，文件号，块号，行号的函数
create or replace function get_rowid
(l_rowid in varchar2)
return varchar2
is
ls_my_rowid varchar2(200);
rowid_type number;
object_number number;
relative_file_no number;
block_number number;
row_number number;
begindbms_rowid.rowid_info(l_rowid,rowid_type,object_number,relative_file_no,block_number,row_number);ls_my_rowid :='Object# is :'||to_char(object_number)||chr(10)||'Relative_file_no is :'||to_char(relative_file_no)||chr(10)||'Block number is :'||to_char(block_number)||chr(10)||'Row number is :'||to_char(row_number);return ls_my_rowid;
end;select get_rowid('AAAR8BAAEAAAACkAAA') from dual;GET_ROWID('AAAR8BAAEAAAACKAAA'
--------------------------------------------------------------------------------
Object# is :73473
Relative_file_no is :4
Block number is :164
Row number is :0获取刚刚执行的sql的执行计划
select * from table (dbms_xplan.display_cursor);
select plan_table_output from table(dbms_xplan.display_cursor(null,null,'allstats last'));
----SET AUTOT[RACE] {ON | OFF | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]
详细出处参考：http://www.jb51.net/article/30465.htm
取表空间的元数据
set long 10000
select
'select dbms_metadata.get_ddl('||''''||'TABLESPACE'||''''||','||''''||tablespace_name||''''||') from dual;'
||chr(10)
from dba_tablespaces;set heading off
查看当前会话的serverprocess进程ID(对应的udump下dump文件的后缀)
select spid from v$process where addr in (select paddr from v$session where
sid=(select sid from v$mystat where rownum=1));------------------------
3184
10046事件的使用方法
本会话
alter session set events '10046 trace name context forever ,level 12' ;
alter session set events '10046 trace name context off' ;
其他会话
exec dbms_monitor.session_trace_enable(sid,serial#,waits=>true,binds=>true);
exec dbms_monitor.session_trace_disable(sid,serial#);
EM问题
[oracle@dba ~]$ emctl status dbconsole
TZ set to PRC
OC4J Configuration issue. /u01/app/oracle/product/10.2.0/oc4j/j2ee/OC4J_DBConsole_dba_testyd not found.
解决办法：emca -config dbcontrol db -repos recreate
查看一个对象的统计信息
SQL> select owner,object_name,statistic_name,value from v$segment_statistics where object_name='ORDERS';OWNER OBJECT_NAME STATISTIC_NAME VALUE
------------------------------ ------------------------------ ---------------------------------------- ----------
ORDADM ORDERS logical reads 2592
ORDADM ORDERS buffer busy waits 0
ORDADM ORDERS gc buffer busy 0
ORDADM ORDERS db block changes 0
ORDADM ORDERS physical reads 1651
ORDADM ORDERS physical writes 0
ORDADM ORDERS physical reads direct 0
ORDADM ORDERS physical writes direct 0
ORDADM ORDERS gc cr blocks received 0
ORDADM ORDERS gc current blocks received 0
ORDADM ORDERS ITL waits 0OWNER OBJECT_NAME STATISTIC_NAME VALUE
------------------------------ ------------------------------ ---------------------------------------- ----------
ORDADM ORDERS row lock waits 0
ORDADM ORDERS space used 0
ORDADM ORDERS space allocated 0
ORDADM ORDERS segment scans 0
这个命令会找到当前表中所有block的最大行数，并记录到数据字典，以后新插入的数据的block不会大于这个行数。
alter table minimize records_per_block; ----这个命令可以分散热块，将表数据分散到更多的数据库上，缺点是会导致更多的io成本
这篇关于我的小金库(跬步千里)的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
