分析数据库性能的SQL

用于查看哪些实例的哪些 *** 作使用了大量的临时段SELECT to_number(decode(SID NULL SID)) sid operation_type OPERATION trunc(EXPECTED_SIZE/ ) ESIZE trunc(ACTUAL_MEM_USED/ ) MEM trunc(MAX_MEM_USED/ ) MAX MEM NUMBER_PASSES PASS trunc(TEMPSEG_SIZE/ ) TSIZEFROM V$SQL_WORKAREA_ACTIVEORDER BY ; 查询有热块查询的SQL语句select hash_valuefrom v$sqltext a (select distinct a owner a segment_name a segment_type fromdba_extents a (select dbarfil dbablkfrom (select dbarfil dbablkfrom x$bh order by tch desc) where rownum < ) bwhere a RELATIVE_FNO = b dbarfiland a BLOCK_ID <= b dbablk and a block_id + a blocks > b dbablk) bwhere a sql_text like % ||b segment_name|| % and b segment_type = TABLE order by　a hash_value a address a piece; 全表扫描select opname target b num_rows b tablespace_name count(target) from v$session_longops a all_all_tables bwhere a TARGET=b owner|| ||b table_namehaving count(target)> group by　 opname target b num_rows b tablespace_name 查看磁盘排序和缓存排序次数select　to_char(sn snap_time yyyy mm dd hh ) time_ avg(newmen value oldmen value) sorts_memeory avg(newdsk value olddsk value) disk_sortfromstats$sysstat oldmen stats$sysstat newmen stats$sysstat newdsk stats$sysstat olddsk stats$snapshot snwhere　 newdsk snap_id=sn snap_idand olddsk snap_id=sn snap_id and newmen snap_id=sn snap_idand newdsk snap_id=sn snap_id and oldmen name= sorts (memory) and newmen name= sorts (memory) and olddsk name= sorts (disk) and newdsk name= sorts (disk) group by　to_char(sn snap_time yyyy mm dd hh ) 执行最慢的前 个SQL？？？select from (selectto_char(snap_time dd Mon HH :mi:ss ) mydate executions exec loads　loads parse_callsparse disk_reads reads buffer_getsgets rows_processed rows_proc sorts　sorts sql_text hash_valuefromperfstat stats$sql_summary sql perfstat stats$snapshot snwheresql snap_id >(select min(snap_id) min_snapfrom stats$snapshot where snap_time > sysdate $days_back)andsql snap_id = sn snap_idorder by $sortskey desc)　tt where rownum< ; SQL缓存池的命中率查询（pinhitratio gethitratio应该大于 %以上）select namespace gethitratio pinhitratio reloads invalidationsfrom v$librarycachewhere namespace in ( SQL AREA TABLE/PROCEDURE BODY TRIGGER ) 数据库的常规参数我就不说了 除了V$parameter中的常规参数外 ORACLE还有大量的隐含参数 下面的语句就可以查询到数据库的所有隐含参数以及其值与参数的描述 SELECT NAME VALUE decode(isdefault TRUE Y N ) as Default decode(ISEM TRUE Y N ) as SesMod decode(ISYM IMMEDIATE I DEFERRED D FALSE N ) as SysMod decode(IMOD MODIFIED U SYS_MODIFIED S N ) as Modified decode(IADJ TRUE Y N ) as Adjusted descriptionFROM ( GV$SYSTEM_PARAMETERSELECT x inst_id as instance x indx+ ksppinm as NAME ksppity ksppstvl as VALUE ksppstdf as isdefault decode(bitand(ksppiflg/ ) TRUE FALSE ) as ISEM decode(bitand(ksppiflg/ ) IMMEDIATE DEFERRED FALSE ) as ISYM decode(bitand(ksppstvf ) MODIFIED FALSE ) as IMOD decode(bitand(ksppstvf ) TRUE FALSE ) as IADJ ksppdesc as DESCRIPTIONFROM x$ksppi x x$ksppsv yWHERE x indx = y indxAND substr(ksppinm ) = _ AND x inst_id = USERENV( Instance ))ORDER BY NAME 想知道现在哪个用户正在利用临时段吗？这个语句将告诉你哪个用户正在利用临时段 SELECT b tablespace b segfile# b segblk# b blocks a sid a serial# a username a osuser a status c sql_textFROM v$session a v$sort_usage b v$sql cWHERE a saddr = b session_addrAND a sql_address = c address(+)ORDER BY b tablespace b segfile# b segblk# b blocks; 查看磁盘碎片select tablespace_name sqrt(max(blocks)/sum(blocks))( /sqrt(sqrt(count(blocks)))) FSFIfrom dba_free_spacegroup by tablespace_name order by 查看表空间的名称及大小select t tablespace_name round(sum(bytes/( )) ) ts_sizefrom dba_tablespaces t dba_data_files dwhere t tablespace_name = d tablespace_namegroup by t tablespace_name; 查看表空间物理文件的名称及大小select tablespace_name file_id file_name round(bytes/( ) ) total_spacefrom dba_data_filesorder by tablespace_name; 查看回滚段名称及大小select segment_name tablespace_name r status (initial_extent/ ) InitialExtent (next_extent/ ) NextExtent max_extents v curext CurExtentFrom dba_rollback_segs r v$rollstat vWhere r segment_id = v usn(+)order by segment_name 耗资源的进程（top session)select s schemaname schema_name decode(sign( mand) to_char(mand) Action Code # || to_char(mand) ) action statussession_status s osuser os_user_name s sid p spid s serial# serial_num nvl(s username [Oracle process] ) user_name s terminal terminal s program program st value criteria_value　from v$sesstat st v$session s　 v$process pwhere st sid = s sid and　 st statistic# = to_number( ) and　 ( ALL = ALL or s status = ALL ) and p addr = s paddr order by st value descp spid asc s username asc s osuser asc 查看锁（lock）情况select /+ RULE / ls osuser os_user_name ls username user_name decode(ls type RW Row wait enqueue lock TM DML enqueue lock TX Transaction enqueue lock UL User supplied lock ) lock_type o object_name object decode(ls lmode null Row Share Row Exclusive Share Share Row Exclusive Exclusive null)lock_mode o owner ls sid ls serial# serial_num ls id ls id from sys dba_objects o (　 select s osuser s username l type l lmode s sid s serial# l id l id from v$session s v$lock l　 where s sid = l sid ) ls　where o object_id = ls id ando owner<> SYS order by o owner o object_name 查看低效率的SQL语句SELECT EXECUTIONS DISK_READS BUFFER_GETS ROUND((BUFFER_GETS DISK_READS)/BUFFER_GETS ) Hit_radio ROUND(DISK_READS/EXECUTIONS ) Reads_per_run SQL_TEXTFROM　 V$SQLAREAWHERE　EXECUTIONS> AND BUFFER_GETS > AND (BUFFER_GETS DISK_READS)/BUFFER_GETS < ORDER BY DESC lishixinzhi/Article/program/Oracle/201311/17408

在数据库优化上有两个主要方面：

安全：数据可持续性。

性能：数据的高性能访问。

优化的范围有哪些

存储、主机和 *** 作系统方面：

主机架构稳定性

I/O 规划及配置

Swap 交换分区

OS 内核参数和网络问题

应用程序方面：

应用程序稳定性

SQL 语句性能

串行访问资源

性能欠佳会话管理

这个应用适不适合用 MySQL

数据库优化方面：

内存

数据库结构(物理&逻辑)

实例配置

说明：不管是设计系统、定位问题还是优化，都可以按照这个顺序执行。

数据库优化维度有如下四个：

硬件

系统配置

数据库表结构

SQL 及索引

优化选择：

优化成本：硬件>系统配置>数据库表结构>SQL 及索引。

优化效果：硬件<系统配置<数据库表结构

Interpretation

在处理性能问题时，我们最关注的是数据库正在等待什么。

当进程因为某些原因不能进行 *** 作时，它需要等待。花费时间最多的等待事件是我们最需要关注的，因为降低它，我们能够获得最大的好处。

AWR报告中的"Top 5 Timed Events"部分就提供了这样的信息，可以让我们只关注主要的问题。

•

Top 5 Timed Events

正如前面提到的，"Top 5 Timed Events"是AWR报告中最重要的部分。它指出了数据库的sessions花费时间最多的等待事件，如下：

Top 5 Timed Events Avg %Total

~~~~~~~~~~~~~~~~~~ wait Call

Event Waits Time (s) (ms) Time Wait Class

------------------------------ ------------ ----------- ------ ------ ----------

db file scattered read 10,152,564 81,327 8 296 User I/O

db file sequential read 10,327,231 75,878 7 276 User I/O

CPU time 56,207 205

read by other session 4,397,330 33,455 8 122 User I/O

PX Deq Credit: send blkd 31,398 26,576 846 97 Other

-------------------------------------------------------------

Top 5 Events部分包含了一些跟Events（事件）相关的信息。它记录了这期间遇到的等待的总次数，等待所花费的总时间，每次等待的平均时间；这一部分是按照每个Event占总体call time的百分比来进行排序的。

根 据Top 5

Events部分的信息的不同，接下来我们需要检查AWR报告的其他部分，来验证发现的问题或者做定量分析。等待事件需要根据报告期的持续时间和当时数据

库中的并发用户数进行评估。如：10分钟内1000万次的等待事件比10个小时内的1000万等待更有问题；10个用户引起的1000万次的等待事件比

10,000个用户引起的相同的等待要更有问题。

就像上面的例子，将近60%的时间是在等待IO相关的事件。

• 事件"db file scattered read"一般表明正在做由全表扫描或者index fast full scan引起的多块读。

• 事件"db file sequential read"一般是由不能做多块读的 *** 作引起的单块读（如读索引）

其他20%的时间是花在使用或等待CPU time上。过高的CPU使用经常是性能不佳的SQL引起的（或者这些SQL有可能用更少的资源完成同样的 *** 作）；对于这样的SQL，过多的IO *** 作也是一个症状。关于CPU使用方面，我们会在之后讨论。

在以上基础上，我们将调查是否这个等待事件是有问题的。若有问题，解决它；若是正常的，检查下个等待事件。

过多的IO相关的等待一般会有两个主要的原因：

• 数据库做了太多的读 *** 作

• 每次的IO读 *** 作都很慢

Top 5 Events部分的显示的信息会帮助我们检查：

• 是否数据库做了大量的读 *** 作：

上面的图显示了在这段时间里两类读 *** 作都分别大于1000万，这些 *** 作是否过多取决于报告的时间是1小

时或1分钟。我们可以检查AWR报告的elapsed time如果这些读 *** 作确实是太多了，接下来我们需要检查AWR报告中 SQL

Statistics 部分的信息，因为读 *** 作都是由SQL语句发起的。

• 是否是每次的IO读 *** 作都很慢：

上面的图显示了在这段时间里两类读 *** 作平均的等待时间是小于8ms的

至于8ms是快还是慢取决于底层的硬件设备；一般来讲小于20ms的都可以认为是可以接受的。

我们还可以在AWR报告"Tablespace IO Stats"部分得到更详细的信息

Tablespace IO Stats DB/Inst: VMWREP/VMWREP Snaps: 1-15

-> ordered by IOs (Reads + Writes) desc

Tablespace

------------------------------

Av Av Av Av Buffer Av Buf

Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits Wt(ms)

-------------- ------- ------ ------- ------------ -------- ---------- ------

TS_TX_DATA

14,246,367 283 76 46 145,263,880 2,883 3,844,161 83

USER

204,834 4 107 10 17,849,021 354 15,249 98

UNDOTS1

19,725 0 30 10 10,064,086 200 1,964 49

AE_TS

4,287,567 85 54 67 932 0 465,793 37

TEMP

2,022,883 40 00 58 878,049 17 0 00

UNDOTS3

1,310,493 26 46 10 941,675 19 43 00

TS_TX_IDX

1,884,478 37 73 10 23,695 0 73,703 83

>SYSAUX

346,094 7 56 39 112,744 2 0 00

SYSTEM

101,771 2 79 35 25,098 0 653 27

如上图，我们关心Av Rd(ms)的指标。如果它高于20ms并且同时有很多读 *** 作的，我们可能要开始从OS的角度调查是否有潜在的IO问题。

在需要支持移动/平板电脑应用及普通桌面浏览器访问的时代，网站的普及率和有效性很大程度上取决于其可用性和性能。一个访问缓慢的网站会使得访问者或潜在的客户流失，并导致商业的失败。IT培训认为一个访问速度相当快的网站将会决定访客是否会使用网站提供的产品或服务。

拥有大规模数据库的网站始终需要适当的关注、配置、优化、调整和维护，以确保网站的快速加载。这篇文章将讨论如何优化有海量数据的MySQL数据库。

选择InnoDB作为存储引擎

大型产品的数据库对于可靠性和并发性的要求较高，InnoDB作为默认的MySQL存储引擎，相对于MyISAM来说是个更佳的选择。

优化数据库结构

组织数据库的schema、表和字段以降低I/O的开销，将相关项保存在一起，并提前规划，以便随着数据量的增长，性能可以保持较高的水平。

设计数据表应尽量使其占用的空间最小化，表的主键应尽可能短。

对于InnoDB表，主键所在的列在每个辅助索引条目中都是可复制的，因此如果有很多辅助索引，那么一个短的主键可以节省大量空间。

仅创建你需要改进查询性能的索引。索引有助于检索，但是会增加插入和更新 *** 作的执行时间。

InnoDB的ChangeBuffering特性

InnoDB提供了changebuffering的配置，可减少维护辅助索引所需的磁盘I/O。大规模的数据库可能会遇到大量的表 *** 作和大量的I/O，以保证辅助索引保持最新。当相关页面不在缓冲池里面时，InnoDB的changebuffer将会更改缓存到辅助索引条目，从而避免因不能立即从磁盘读取页面而导致耗时的I/O *** 作。当页面被加载到缓冲池时，缓冲的更改将被合并，更新的页面之后会刷新到磁盘。这样做可提高性能，适用于MySQL55及更高版本。

以上就是关于分析数据库性能的SQL全部的内容，包括:分析数据库性能的SQL、数据库的性能优化有哪些、如何使用AWR报告来诊断数据库性能问题等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！