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;
阅读(1514) | 评论(0) | 转发(1) |