我的小金库(跬步千里)

------------------------------------------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_COUNT  FROM V$PGA_TARGET_ADVICE;

查看_pga_max_size隐含参数:

select ksppinm "NAME" , ksppstvl "Value", ksppdesc "Desc"from x$ksppi x, x$ksppcv ywhere 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,CASEWHEN current_parse_time_elapsed_s + adjustment_s < 0THEN 0ELSEcurrent_parse_time_elapsed_s + adjustment_sEND response_timeFROM(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                                                          121parse time elapsed                                                      791parse count (total)                                                    4914parse count (hard)                                                     1140parse 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_reads   FROM V$DB_CACHE_ADVICE   WHERE 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(*) blocksfrom x$bh b, dba_objects owhere b.obj = o.data_object_id and o.object_name='T1'group by o.object_name, stateorder 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(*) blocksfrom x$bh b,dba_objects owhere b.obj=o.data_object_id and state <>0group by o.object_name,stateorder 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$                                                                                    287 SQL> 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$bhgroup by decode(state,0,'FREE',1,decode(lrba_seq,0,'AVAILABLE','BEING USED'),3,'BEING USED',state);BLOCK STATUS                               COUNT(*)---------------------------------------- ----------BEING USED                                      171AVAILABLE                                     16188FREE                                              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 varchar2isls_my_rowid varchar2(200);rowid_type number;object_number number;relative_file_no number;block_number number;row_number number;begin  dbms_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     :73473Relative_file_no is      :4Block number is          :164Row 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));

SPID
------------------------
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                                  2592ORDADM                         ORDERS                         buffer busy waits                                 0ORDADM                         ORDERS                         gc buffer busy                                    0ORDADM                         ORDERS                         db block changes                                  0ORDADM                         ORDERS                         physical reads                                 1651ORDADM                         ORDERS                         physical writes                                   0ORDADM                         ORDERS                         physical reads direct                             0ORDADM                         ORDERS                         physical writes direct                            0ORDADM                         ORDERS                         gc cr blocks received                             0ORDADM                         ORDERS                         gc current blocks received                        0ORDADM                         ORDERS                         ITL waits                                         0OWNER                          OBJECT_NAME                    STATISTIC_NAME                                VALUE------------------------------ ------------------------------ ---------------------------------------- ----------ORDADM                         ORDERS                         row lock waits                                    0ORDADM                         ORDERS                         space used                                        0ORDADM                         ORDERS                         space allocated                                   0ORDADM                         ORDERS                         segment scans                                     0

这个命令会找到当前表中所有block的最大行数，并记录到数据字典，以后新插入的数据的block不会大于这个行数。

alter table minimize records_per_block;       ----这个命令可以分散热块，将表数据分散到更多的数据库上，缺点是会导致更多的io成本