Oracle数据库优化时常用的sql

ALTER TABLE SUPMAN."Base_StaffInfo"  CACHE;
ALTER TABLE SUPMAN."Base_StaffInfo"  STORAGE (BUFFER_POOL KEEP);




oracle 回滚段查看：


select s.username, u.name from v$transaction t,v$rollstat r,
v$rollname u,v$session s where s.taddr=t.addr and
t.xidusn=r.usn and r.usn=u.usn order by s.username;


Col username format a10
Col name format a10
SELECT a.username, b.name, c.used_ublk
FROM v$session a, v$rollname b, v$transaction c
WHERE a.saddr=c.ses_addr AND b.usn=c.xidusn
AND a.username='XJODS';


select segment_name, owner, tablespace_name, status from dba_rollback_segs; 




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中没有


查看执行计划的地址：
http://space.itpub.net/519536/viewspace-563050

http://space.itpub.net/354732/viewspace-606385
http://dev.firnow.com/course/7_databases/oracle/oraclejs/20100628/214549.html
http://dbsnake.com/2009/05/execution-plan-order.html
http://space.itpub.net/16857/viewspace-487446


http://blog.sina.com.cn/s/blog_649d1c5d0100hxvi.html?retcode=0



根据sql找出对应的信息


select sql_id,HASH_VALUE,  ADDRESS from v$sql where sql_text like 'SELECT %';
1：根据pid找到对应的sql
根据sql找到对应的sql_id:
select SQL_ID from v$sql where sql_text='select acc_nbr from serv where serv_id =:"SYS_B_0"';


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';


一：
根据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 (55509026);


（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';


实际上简单的方法是看v$sql_plan 知道hash_value 就可以了。这样比较准确一些




看执行计划，可以用dbms_xplan程序包中的多个函数……
可结合AWR的数据字典，如dba_hist_sqlstat之类，或者查询ash信息，应可以确定具体时间段，使用的执行计划












(三）：查看热块


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';


表空间是否自动扩展:
select file_name,autoextensible,increment_by from dba_data_files 


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


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;


1：
Oracle 10g提供了一个新的初始化参数：sga_target来启动ASMM，该参数定义了整个SGA的总容量。同时，初始化参数statistics_level必须设置为typical或all才能启动ASMM，否则如果设置


为basic，则关闭ASMM。
ASMM只能自动调整5个内存池的大小，它们是：shared pool、buffer cache、large pool、java pool和stream pool。我们不再需要设置shared_pool_size、db_cache_size、


large_pool_size、java_pool_size、streams_pool_size这五个初始化参数。而其他的内存池，比如log buffer、keep buffer cache等仍然需要DBA手工进行调整。


查看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';


共享池等的使用情况：
select * from sys.v_$sgastat;


sga:系统全局区大小
cache_size:数据缓冲区大小
shared_size:共享缓冲区大小
cache_used:数据缓冲区使用率
free_shared:共享缓冲区空闲率
上面的均以MB为单位
logk:日志缓冲区大小(以KB为单位)


共享池等的使用情况：
select round(sga,0) sga,
round(cache_size,0) cache_size,
round(coun/cache_size*100,0)||'%' cache_used,
round(shared_size,0) shared_size,
round(freemb/shared_size*100,0)||'%' free_shared,
round(java_size,0) java,
round(large_pool_size,0) large,
round(log,0) logk
from 
(select value/1024/1024 sga from v$parameter where name='sga_max_size'),
(select value/1024/1024 shared_size from v$parameter where name='shared_pool_size'),
(select value/1024/1024 cache_size from v$parameter where name='db_cache_size'),
(select value/1024/1024 java_size from v$parameter where name='java_pool_size'),
(select value/1024/1024 large_pool_size from v$parameter where name='large_pool_size'),
(select value/1024 log from v$parameter where name='log_buffer'),
(select bytes/1024/1024 freemb from v$sgastat s where s.pool='shared pool' and s.name='free memory'),
(select sum(count(*))*8/1024 coun from v$bh where status<>'free' group by status );


2:
将表放入keep
alter table t storage (buffer_pool keep);




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


4:
不够优化的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;


5:热点块：
  还有一个原因可能会引起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; 


这样是不是就算对索引碎片进行了分析和整理了呢






-----------------------------------------------------------------------------------------------------------------------------
SQL 优化语句:
1. 当前连接的Session 有多少


SELECT login_name
    ,[program_name]
    ,COUNT(session_id) AS [session_count]
FROM sys.dm_exec_sessions WITH (NOLOCK)
GROUP BY login_name,[program_name]
ORDER BY COUNT(session_id) desc;
2. 每个数据库上的Session 数量是多少
SELECT DB_NAME(dbid) AS DBName


    ,COUNT(dbid) AS NumberOfConnections


    ,loginame AS LoginName


FROM sys.sysprocesses


WHERE dbid > 0 


GROUP BY dbid,loginame


3. 查看阻塞
SELECT


    SPID                = er.session_id


    ,STATUS             = ses.STATUS


    ,[LOGIN]            = ses.login_name


    ,HOST               = ses.host_name


    ,BlkBy              = er.blocking_session_id


    ,DBName             = DB_NAME(er.database_id)


    ,CommandType        = er.command


    ,SQLStatement       = st.text


    ,BlockingText     = bst.text


    ,ObjectName         = OBJECT_NAME(st.objectid)


    ,ElapsedMS          = er.total_elapsed_time


    ,CPUTime            = er.cpu_time


    ,IOReads            = er.logical_reads + er.reads


    ,IOWrites           = er.writes


    ,LastWaitType       = er.last_wait_type


    ,StartTime          = er.start_time


    ,Protocol           = con.net_transport


    ,ConnectionWrites   = con.num_writes


    ,ConnectionReads    = con.num_reads


    ,ClientAddress      = con.client_net_address


    ,Authentication     = con.auth_scheme


FROM sys.dm_exec_requests er


OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) st


LEFT JOIN sys.dm_exec_sessions ses


ON ses.session_id = er.session_id


LEFT JOIN sys.dm_exec_connections con


ON con.session_id = ses.session_id


LEFT JOIN sys.dm_exec_requests ber


ON er.blocking_session_id=ber.session_id


OUTER APPLY sys.dm_exec_sql_text(ber.sql_handle) bst


WHERE er.session_id > 50


ORDER BY er.blocking_session_id DESC,er.session_id;
4. 找出哪些表的Index 需要改进
SELECT CONVERT(DECIMAL(18, 2), user_seeks * avg_total_user_cost * (avg_user_impact * 0.01)) AS [index_advantage]


    ,migs.last_user_seek


    ,mid.[statement] AS [Database.Schema.Table]


    ,mid.equality_columns


    ,mid.inequality_columns


    ,mid.included_columns


    ,migs.unique_compiles


    ,migs.user_seeks


    ,migs.avg_total_user_cost


    ,migs.avg_user_impact


FROM sys.dm_db_missing_index_group_stats AS migs WITH (NOLOCK)


INNER JOIN sys.dm_db_missing_index_groups AS mig WITH (NOLOCK) ON migs.group_handle = mig.index_group_handle


INNER JOIN sys.dm_db_missing_index_details AS mid WITH (NOLOCK) ON mig.index_handle = mid.index_handle


ORDER BY index_advantage desc


5. 查看Index 的Statistics 最后更新时间


SELECT SCHEMA_NAME(o.[schema_id]) + N'.' + o.[name] AS [Object Name]


    ,o.type_desc AS [Object Type]


    ,i.[name] AS [Index Name]


    ,STATS_DATE(i.[object_id], i.index_id) AS [Statistics Date]


    ,s.auto_created


    ,s.no_recompute


    ,s.user_created


    ,st.row_count


    ,st.used_page_count


FROM sys.objects AS o WITH (NOLOCK)


INNER JOIN sys.indexes AS i WITH (NOLOCK) ON o.[object_id] = i.[object_id]INNER JOIN sys.stats AS s WITH (NOLOCK) ON i.[object_id] = s.[object_id]


    AND i.index_id = s.stats_id


INNER JOIN sys.dm_db_partition_stats AS st WITH (NOLOCK) ON o.[object_id] = st.[object_id]


    AND i.[index_id] = st.[index_id]WHERE o.[type] IN ('U','V')


    AND st.row_count > 0


ORDER BY STATS_DATE(i.[object_id], i.index_id) desc;


6. 查看Index 碎片化指数


SELECT DB_NAME(ps.database_id) AS [Database Name]


    ,OBJECT_NAME(ps.[object_id]) AS [Object Name]


    ,i.[name] AS [Index Name]


    ,ps.index_id


    ,ps.index_type_desc


    ,ps.avg_fragmentation_in_percent


    ,ps.fragment_count


    ,ps.page_count


    ,i.fill_factor


    ,i.has_filter


    ,i.filter_definition


FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, N'LIMITED') AS ps


INNER JOIN sys.indexes AS i WITH (NOLOCK) ON ps.[object_id] = i.[object_id]


    AND ps.index_id = i.index_id


WHERE ps.database_id = DB_ID()


    AND ps.page_count > 2500


ORDER BY ps.avg_fragmentation_in_percent desc;


7. 查询前 10 个可能是性能最差的 SQL 语句


SELECT TOP 10 TEXT AS 'SQL Statement'
    ,last_execution_time AS 'Last Execution Time'
    ,(total_logical_reads + total_physical_reads + total_logical_writes) / execution_count AS [Average IO]
    ,(total_worker_time / execution_count) / 1000000.0 AS [Average CPU Time (sec)]
    ,(total_elapsed_time / execution_count) / 1000000.0 AS [Average Elapsed Time (sec)]
    ,execution_count AS "Execution Count"
    ,qp.query_plan AS "Query Plan"
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY total_elapsed_time / execution_count DESC;