自用杂货铺

1:将表放入缓存
ALTER TABLE SUPMAN."Base_StaffInfo" CACHE;
ALTER TABLE SUPMAN."Base_StaffInfo"  STORAGE ( BUFFER_POOL KEEP );
2:查看某个语句的历史执行计划 
1）：语句在快照之间
exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>'SYS',tabname=>'TEST_PLAN',method_opt=>'FOR ALL COLUMNS SIZE AUTO',CASCADE=>TRUE);
exec dbms_workload_repository.create_snapshot();
select sql_id,HASH_VALUE,  ADDRESS from v$sql where sql_text like 'SELECT %';
2）：根据sid找出对应的sql_id
select sid,SQL_HASH_VALUE,SQL_ID,PREV_HASH_VALUE,PREV_SQL_ID from v$session where sid=542;
3)  :根据sql_id找到对应的plan_hash_value
select sql_id,                   
        plan_hash_value,         
        id,                      
        operation,               
        options,                 
        object_owner,            
        object_name,             
        depth,                   
        cost,                    
        timestamp                
   from DBA_HIST_SQL_PLAN        
  where sql_id = '36qmvdf7f2xz0' 
  order by 2, 3;                 
4）：根据sql_id 找出执行计划变化的时间点
select a.INSTANCE_NUMBER,                         
         a.snap_id,                               
         a.sql_id,                                
         a.plan_hash_value,                       
         b.begin_interval_time                    
    from dba_hist_sqlstat a, dba_hist_snapshot b  
   where sql_id = '36qmvdf7f2xz0'                 
     and a.snap_id = b.snap_id                    
   order by instance_number, snap_id;             
5）：想办法固定最优的执行计划，结合等待事件来优化。

select sql_text from V$sqlarea  where (address,hash_value) in (select sql_address,sql_hash_value from v$session  where event like 'library%');

此语句只能运行于10g版本以上，因为10g中v$session视图包含了等待事件的信息了，9i中没有


根据sql找出对应的信息sql_id
select sql_id,HASH_VALUE,  ADDRESS from v$sql where sql_text like 'SELECT %';


2：根据v$sql_plan查看sql的执行计划
select ADDRESS ,HASH_VALUE,SQL_ID,PLAN_HASH_VALUE,COST,CPU_COST, ACCESS_PREDICATES  from v$sql_plan where SQL_ID='46n9n9d019tp2';


3:
一：
根据pid找出负载过高的sql及执行对应sql的主机和Ip
（1）：
根据pid找到对应的sql


select sql_text,spid,v$session.program,process  from 
v$sqlarea,v$session,v$process
where v$sqlarea.address=v$session.sql_address
and v$sqlarea.hash_value=v$session.sql_hash_value
and v$session.paddr=v$process.addr
and v$process.spid in (11350);


（2）：


根据pid找到sid
select sid from v$session where paddr in (select addr from v$process where spid=11859);

根据sql_id 找到对应的sql
select SQL_ID ,SQL_TEXT ,LAST_ACTIVE_TIME from v$sql where sql_id = '4gd6b1r53yt88';

select SQL_ID ,SQL_TEXT ,LAST_ACTIVE_TIME from v$sql where sql_text  like %

（3）：
根据sid查是哪台电脑的链接
column osuser format a15
column username format a10
column machine format a30
select LOGON_TIME,osuser,machine,username,sid,serial#,CLIENT_INFO  from v$session where sid='1626';




（二）：查看sql执行计划


(1):
根据查到的sql，找到SQL_ID


select SQL_ID from v$sql where sql_text='select acc_nbr from serv where serv_id =:"SYS_B_0"';


（2）：根据SQL_ID查看执行计划
查看执行计划：
select * from table(dbms_xplan.display_awr('b508jrv503nku')) 
 
(3):使用v$sql_plan查看执行计划
   
   根据sql_id找到相应的hash_value
   select hash_value from v$sqltext_with_newlines where sql_id='8zfr3acxhuj7c';
   根据hash_value找到相应的执行计划


connect / as sysdba;
set lines 121
set pages 999
col sql_text format a80
select sql_text from
v$sqltext_with_newlines
where hash_value=364285806
order by piece;
set heading off
select '--------------------------------------------------------------------------------' from dual
union all
select '| Operation | PHV/Object Name | Rows | Bytes| Cost |' as "Optimizer Plan:" from dual
union all
select '--------------------------------------------------------------------------------' from dual
union all
select *
from (select
rpad('|'||substr(lpad(' ',1*(depth-1))||operation||
decode(options, null,'',' '||options), 1, 62), 63, ' ')||'|'||
rpad(decode(id, 0, '----- '||to_char(hash_value)||' -----'
, substr(decode(substr(object_name, 1, 7), 'SYS_LE_', null, object_name)
||' ',1, 20)), 21, ' ')||'|'||
lpad(decode(cardinality,null,' ',
decode(sign(cardinality-10000), -1, cardinality||' ',
decode(sign(cardinality-1000000), -1, trunc(cardinality/1000)||'K',
decode(sign(cardinality-1000000000), -1, trunc(cardinality/1000000)||'M',
trunc(cardinality/1000000000)||'G')))), 7, ' ') || '|' ||
lpad(decode(bytes,null,' ',
decode(sign(bytes-1024), -1, bytes||' ',
decode(sign(bytes-1048576), -1, trunc(bytes/1024)||'K',
decode(sign(bytes-1073741824), -1, trunc(bytes/1048576)||'M',
trunc(bytes/1073741824)||'G')))), 6, ' ') || '|' ||
lpad(decode(cost,null,' ',
decode(sign(cost-10000000), -1, cost||' ',
decode(sign(cost-1000000000), -1, trunc(cost/1000000)||'M',
trunc(cost/1000000000)||'G'))), 8, ' ') || '|' as "Explain plan"
from v$sql_plan
where hash_value = 364285806)
union all
select '--------------------------------------------------------------------------------' from dual;


 select ADDRESS  ,HASH_VALUE,SQL_ID,PLAN_HASH_VALUE,COST,CPU_COST, TIMESTAMP , ACCESS_PREDICATES  from v$sql_plan where SQL_ID='46n9n9d019tp2';

(三）：查看热块


1、检查下v$waitstat,结果贴出来，看看是哪种类型的block为热点块
2、select sid,p1,p2,p3 from v$session_wait where event not like '%SQL%' and event = 'cache buffer chain'
3、select * from v$event_name where name= 'cache buffer chain' ,看看P1，P2，P3的含义
   记得p1\p3\p3中应该有一个是表示latch地址的
latch: cache buffers chains一般来说都是热点块引起的。比较直观简单的现象就是大量会话同时在执行逻辑读比较高的SQL。
做个短时间内的statspack看看逻辑读高的SQL语句。


1：
热点块
select object_name 
from dba_objects 
where data_object_id in
(select obj 
from x$bh 
where hladdr in
    (select addr 
    from (select addr 
        from v$latch_children 
        order by sleeps desc) 
        where rownum < 11)) 
2：


根据占cpu高的进程抓sql


通过sid找出对应系统的pid
SQL> select spid from v$process where addr in (select paddr from v$session where sid=1351);


通过spid 找到sid
select sid from v$session where paddr in (select addr from v$process where spid=29156);

通过sid找出执行的sql语句
select sql_address,sql_hash_value from v$session where sid=2609;


select table_name from dba_tables where buffer_pool='KEEP';


查看一下keep池的大小是不是合适：


show parameter keep

select sum(blocks) from dba_tables where buffer_pool='KEEP';

select 11821*8/1024 from dual;

查看热表的pctfree，发现都是默认的10：


select table_name,pct_free from dba_tables where buffer_pool='KEEP';


查看当前状态下存在的等待事件


topas


select sid,event,p1,p2,seconds_in_wait from v$session_wait where event not like '%SQL%' and event not like 'rdbms%' and event not like '%message%' and event not like 


'%Streams AQ%' AND EVENT NOT LIKE '%slave%' order by event;




检查出现问题的语句


SQL> @sql_sid
Enter value for sid: 1413
old 1: 
select sql_text from v$sqltext where hash_value in(select decode(sql_hash_value,0,prev_hash_value,sql_hash_value) from v$session where sid='&sid') order by piece;


new 1: select sql_text from v$sqltext where hash_value in(select decode(sql_hash_value,0,prev_hash_value,sql_hash_value) from v$session where sid=’1413′) order by 


piece;




oracle10g中才可以使用：那些连接导致了等待


select a.sample_time,b.event_name,a.wait_time,a.time_waited,a.program,a.module
from WRH$_ACTIVE_SESSION_HISTORY a,WRH$_EVENT_NAME b
where a.event_id=b.event_id and a.instance_number=1
and trunc(sample_time,'DD')=to_date('2011-05-4','yyyy-mm-dd')
and b.event_name='PX Deq Credit: need buffer';


1.2.4 检查问题语句的执行计划
set autotrace traceonly
set autotrace on
SQL> explain plan for SELECT COUNT(ID) FROM TABLE_A WHERE COL0107 =” || :B1;
Explained.
SQL> select * from table(DBMS_XPLAN.display); 






1.2.5 检查问题表上是否存在索引
SQL>
select index_name,column_name,column_position from dba_ind_columns where table_name='TEMP_QUOTE' order by 1,3;




select index_name,column_name,column_position from user_ind_columns where table_name='TEMP_QUOTE' order by 1,3;


PK_AICC_CORE                   SESSION_ID                                   1






1.2.7 查看实际数据库容量，表的大小的语句。


记住：dba_segments,dba_data_files，user_tables  
数据量查询：
Select sum(bytes)/1024/1024/1024 G  from  dba_segments;
select   sum(bytes)/1024/1024/1024 G  from   dba_data_files; 
数据库总量：
select   sum(bytes)   from   dba_data_file; 


表的大小：
SQL> select sum(bytes)/1024/1024 from dba_segments where segment_name='TEMP_QUOTE';




cash_bill.ACCT_ITEM_1060_OLD
表的行数：
SQL> select num_rows from user_tables where table_name='STAT.R_M_ACCT_ITEM_BAD_DETAIL';




1：


查看表空间及碎片情况
select a.tablespace_name,
round( sum(a.tots)/1024/1024,0) Tot_Size_M,  
round(sum(a.sumb)/1024/1024,0) Tot_Free_M, 
round(sum(a.sumb)*100/sum(a.tots),0) Pct_Free,  
round(sum(a.largest)/1024/1024,0) Max_Free_M,
sum(a.chunks) Chunks_Free  
from  
( 
select tablespace_name,0 tots,sum(bytes) sumb,  
max(bytes) largest,count(*) chunks 
from dba_free_space a 
group by tablespace_name 
union 
select tablespace_name,sum(bytes) tots,0,0,0 from 
dba_data_files 
group by tablespace_name) a 
group by a.tablespace_name order by 2 desc;




2: 查看表空间大小
col used% for a10
col free% for a10
col tablespace_name for a20
SELECT A.TS1"tablespace_name",
ROUND((1-(B.SUM_B2/A.SUM_B1))*100,2)||'%' "used%",
ROUND((B.SUM_B2/A.SUM_B1)*100,2)||'%'  "free%",
A.SUM_B1/1024/1024"all_tablespace(M)",
(A.SUM_B1 - B.SUM_B2)/1024/1024 "used(M)",
B.SUM_B2/1024/1024 "free(M)"
FROM
(SELECT TABLESPACE_NAME TS1, 
SUM(BYTES) SUM_B1
FROM DBA_DATA_FILES 
GROUP BY TABLESPACE_NAME) A,
(SELECT TABLESPACE_NAME TS2, 
SUM(BYTES) SUM_B2,
MAX(BYTES) MAX_B2,
COUNT(BYTES) CNT_B2
FROM DBA_FREE_SPACE 
GROUP BY TABLESPACE_NAME) B
WHERE A.TS1=B.TS2;


3：查看大小在前十位的对象
Select * from (select substr(segment_name,1,20),segment_type,sum(bytes)/1024/1024 M from dba_segments group by substr(segment_name,1,20),segment_type order by 3 desc) 


where rownum < 10;


4:临时表空间分配
select file#,name,bytes/1024/1024 from v$tempfile;
select (sum (blocks))*8/1000 "MB" from v$sort_usage --当前有多少临时表空间被使用
select * from V$TEMP_SPACE_HEADER;
4:临时表空间分配
select file#,name,bytes/1024/1024 from v$tempfile;
select (sum (blocks))*8/1000 "MB" from v$sort_usage --当前有多少临时表空间被使用
select * from V$TEMP_SPACE_HEADER;


5:
查看SGA使用及剩余内存：
SQL> 
col OBJECT_NAME format a20
col 自由空间百分比(%) format 90.99　　　--格式化，把小数点对齐为两位
select name,sgasize/1024/1024 "Allocated(M)",bytes/1024 "free(K)",round(bytes/sgasize*100, 2) "free(%)"
from   (select sum(bytes) sgasize from sys.v_$sgastat) s, sys.v_$sgastat f
where  f.name = 'free memory';






6:与buffer cache相关的等待事件包括：latch free、buffer busy waits、free buffer waits。
等待事件“latch free”中与buffer cache有关的有两类：cache buffers chains latch和cache buffers lru chain latch。


不够优化的SQL语句是导致cache buffers chains latch的主要原因。如果SQL语句需要访问过多的内存数据块，那么必然会持有latch很长时间。找出逻辑读特别大的sql语句进行调整。
v$sqlarea里那些buffer_gets/executions为较大值的SQL语句就是那些需要调整的SQL语句。这种方式不是很有针对性，比较盲目。网上曾经有人提供了一个比较有针对性的、查找这种引起较


为严重的cache buffers chains latch的SQL语句的方式，其原理是根据latch的地址，到x$bh中找对应的buffer header，x$bh的hladdr表示该buffer header所对应的latch地址。然后根据


buffer header可以找到所对应的表的名称。最后可以到v$sqltext（也可以到stats$sqltext）中找到引用了这些表的SQL语句。我也列在这里。where条件中的rownum<10主要是为了不要返回


太多的行，只要能够处理掉前10个latch等待就能有很大改观。


 select /*+ rule */ s.sql_text
 from x$bh a,dba_extents b,
 (select * from (select addr from v$latch_children
     where name = 'cache buffers chains' order by sleeps desc)
where rownum<11) c,
 v$sqltext s
 where a.hladdr = c.addr
   and a.dbarfil = b.relative_fno
   and a.dbablk between b.block_id and b.block_id + b.blocks
   and s.sql_text like '%'||b.segment_name||'%' and b.segment_type='TABLE'
 order by s.hash_value,s.address,s.piece;
热点块：
  还有一个原因可能会引起cache buffers chains latch，就是热点数据块问题。这是指多个session重复访问一个或多个被同一个child cache buffers chains latch保护的内存数据块。这


主要是应用程序的问题。大多数情况下，单纯增加child cache buffers chains latches的个数对提高性能没有作用。这是因为内存数据块是根据数据块地址以及hash chain的个数来进行


hash运算从而得到具体的hash chain的，而不是根据child cache buffers chains latches的个数。如果数据块的地址以及hash chain的个数保持一致，那么热点块仍然很有可能会被hash到


同一个child cache buffers chains latch上。可以通过v$session_wait的p1raw字段来判断latch free等待事件是否是由于出现了热点块。如果p1raw保持一致，那么说明session在等待同一


个latch地址，系统存在热点块。当然也可以通过x$bh的tch来判断是否出现了热点块，该值越高则数据块越热。


select sid, p1raw, p2, p3, seconds_in_wait, wait_time, state
     from   v$session_wait
    where  event = 'latch free'
     order by p2, p1raw;




接下来，我们就可以根据p1 raw的值去找到所对应的内存数据块以及对应的表的名称了


 select a.hladdr, a.file#, a.dbablk, a.tch, a.obj, b.object_name
 from   x$bh a, dba_objects b
 where  (a.obj = b.object_id  or  a.obj = b.data_object_id)
 and    a.hladdr = '6666535C';


解决方法：
要解决热点块的问题，可以通过将热点块中的行分散到多个数据块中去，这样原来的热点块就变成了多个数据块，这样被hash到同一个latch的几率就降低了。如果热点块属于表，则可以先将


表的数据导出来，然后增加表的pctfree值，最后将数据再导入。如果热点块属于索引，则可以设定较高的 pctfree参数后，重建索引。注意，这会增加索引的高度。


  通过前面我们已经知道，每个working set都会有一个名为cache buffers lru chain的latch（也叫做lru latch）来管理。任何要访问working set的进程都必须先获得cache buffers lru 


chain latch。cache buffers lru chain latch争用也是由于低效的扫描过多的内存数据块的SQL语句引起的。调整这些语句以降低逻辑读和物理读。只要修改一下上面找引起cache buffers 


chains latch的SQL语句即可找到这样的SQL语句。


  select /*+ rule */ s.sql_text
 from x$bh a,dba_extents b,
 (select * from (select addr from v$latch_children
     where name = 'cache buffers lru chain' order by sleeps desc)
 where rownum<11) c,
 v$sqltext s
 where a.hladdr = c.addr
   and a.dbarfil = b.relative_fno
   and a.dbablk between b.block_id and b.block_id + b.blocks
   and s.sql_text like '%'||b.segment_name||'%' and b.segment_type='TABLE'
 order by s.hash_value,s.address,s.piece
 /






select sid,p1,p1raw,p2,p2raw,p3,p3raw,wait_time,seconds_in_wait,state,event from v$session_wait where event not in (select name from v$event_name where 


wait_class='Idle');


select owner,
        substr(object_name,1,30) object_name,
        object_type,
        created
from   dba_objects
where  object_type in ('INDEX','INDEX PARTITION')
order by created;


select sess.SID,  segtype, blocks*8/1000 "MB" ,sql_text from v$sort_usage sort, v$session sess,v$sql sql where sort.SESSION_ADDR = sess.SADDR and sql.ADDRESS = 


sess.SQL_ADDRESS order by blocks desc;


Select se.username,
       se.sid,
       su.extents,
       su.blocks * to_number(rtrim(p.value)) as Space,
       tablespace,
       segtype,
       sql_text
  from v$sort_usage su, v$parameter p, v$session se, v$sql s
 where p.name = 'db_block_size'
   and su.session_addr = se.saddr
   and s.hash_value = su.sqlhash
   and s.address = su.sqladdr
 order by se.username, se.sid；
  
(五）查看死锁的原因


有个table频繁被死锁，请问有没有办法查看是谁死锁了？用户名或者电脑名称什么的？
select 
　　c.owner, 
　　c.object_name, 
　　c.object_type, 
　　b.sid, 
　　b.serial#, 
　　b.status, 
　　b.osuser, 
　　b.machine 
　　from 
　　v$locked_object a , 
　　v$session b, 
　　dba_objects c 
　　where 
　　b.sid = a.session_id 
　　and 
　　a.object_id = c.object_id;




select distinct b.sid,serial#,b.process,osuser,machine,b.osuser,b.status,b.sql_hash_value,
      (select object_name from dba_objects where object_id = a.object_id) object_name,
      c.sql_text--,c.sql_fulltext
from v$locked_object a, v$session b, v$sqlarea c
where a.session_id = b.sid and b.sql_hash_value = c.hash_value(+)
order by sql_text;




建议每周监测一次索引的碎片情况，根据情况制定索引的重建频率以提高索引使用效率。 
SQL> analyze index structure;
SQL> select name,del_lf_rows_len,lf_rows_len,(del_lf_rows_len/lf_rows_len)*100 from index_stats;
索引碎片率(%) = (被删除的索引长度/索引总长)*100 
SQL> alter index <索引名> rebuild; 
SQL> alter index <索引名> coalesce;