数据库性能分析-oracle

数据库性能分析-oracle第一页，共29页。目录Oracle常用的操作基本操作导入、导出Process、表空间数据库性能分析数据库服务器分析硬件资源(及工具)资源设计SGA\PGA锁资源SQL分析慢速sql、TOPsql抓取重复SQLExplainSQL第二页，共29页。Oracle常用的基本操作登录数据库：(1)Sqlplus/nologsql>conn

sys/admin

as

sysdba(2)Sqlplus

sys/admin

as

sysdba以系统管理员身份登录：conn/assysdba登录远程数据库：sqlplusv350sp1//ORCL

第三页，共29页。Oracle常用的基本操作查看数据库用户、授权、修改密码、创建用户：(1)select*fromv$pw;(2)grantsysdbatosystem;（3）CREATEUSER"V3XUSER"PROFILE"DEFAULT"IDENTIFIEDBY"123456"DEFAULTTABLESPACE"V3XSPACE"TEMPORARYTABLESPACE"TEMP"ACCOUNTUNLOCK;(4)alter

user

v3xuser

identified

by

111111;

第四页，共29页。Oracle常用的基本操作创建表空间、增加数据文件CREATESMALL"V3XSPACE“DATAFILE'D:\V3XSPACE.DBF'SIZE30720MAUTOEXTENDONNEXT200MMAXSIZEUNLIMITEDLOGGINGEXTENTMANAGEMENTLOCALSEGMENTSPACEMANAGEMENTAUTO;altertablespaceV3XSPACEadddatafile'D:\V3XSPACE01.DBF'size100MAUTOEXTENDONNEXT2000KMAXSIZEUNLIMITED;查询表空间文件位置select,fromv$tablespacet1,v$datawheret1.ts#=t2.ts#;按用户查询表空间selectUSERNAME,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACEfromdba_userswhereusername='V3XUSER查询磁盘剩余（使用ASM）selectname,total_mb,free_mb,(total_mb-free_mb)usedfromv$asm_diskgroup;selectgroup_number,fromv$asm_file；删除表空间及数据文件droptablespacev3xspaceincludingcontentsanddata删除数据文件droptablespacev3xspaceinculdingcontents;//删除表空间第五页，共29页。Oracle常用的基本操作数据导入、导出impv3xuser/123456@orclfull=yignore=yimpctp/12345678@orclfull=ylog="import.log">impres.txtimpv3xuser/v3xuser@；40/buffer=20000000file=f:\231.dmpfull=yignore=y;数据库全库导出Expdp[用户名]/[密码]@[主机字符窜]full=ydirectory=TESTdumplog单个用户方案导入impdp[用户名]/[密码]@[主机字符窜]schemas=[用户名]directory=TESTdumplogignore=y数据库全库导入impdp[用户名]/[密码]@[主机字符窜]full=ydirectory=TESTdumplogfile=X.log第六页，共29页。Oracle和性能相关的参数processconnectsProcess和sessionConnects：数据库连接池(应用程序初始化设置)Session：一个连接对应1个或多个会话SQL>select*fromv$session；这个就可以查看所有的sessionSQL>selectcount(*)fromv$session

#连接数

SQL>Selectcount(*)fromv$sessionwherestatus='ACTIVE'

#并发连接数

SQL>showparameterprocesses

#最大连接

SQL>altersystemsetprocesses=valuescope=spfile;重启数据库

#修改连接查询数据库自启动以来最大的并发数量select*fromv$licenseoracle的连接数(sessions)与其参数文件中的进程数(process)相关，它们的关系如下：sessions=(1.1*process+5)若果资源允许，而当前process数过小，那么可以适当增大processs数(session数依赖于process数，一般不去直接修改session数)。第七页，共29页。查看剩余表空间查看表空间使用情况：selecta.tablespace_name,total,free,total-freeusedfrom(selecttablespace_name,sum(bytes)/1024/1024totalfromdba_data_filesgroupbytablespace_name)a,(selecttablespace_name,sum(bytes)/1024/1024freefromdba_free_spacegroupbytablespace_name)bwherea.tablespace_name=b.tablespace_name;查看表空间文件位置select,fromv$tablespacet1,v$datawheret1.ts#=t2.ts#;第八页，共29页。数据库性能分析数据库服务器数据库的性能问题主要体现在：1、硬件资源的不足。如CPU、内存、I/O、网络2、资源设计的不合理。如SGA分配不合理、命中率低3、锁资源不足SQL性能慢速sql、TOPSQL抓取循环SQLExplainSQL第九页，共29页。数据库服务器性能分析硬件资源（以下以linux资源举例）vmstat1010r运行队列Swpd虚拟内存使用大小free

空闲的物理内存的大小si

每秒从磁盘读入虚拟内存的大小，如果这个值大于0，表示物理内存不够用或者内存泄露us用户CPU时间，我曾经在一个做加密解密很频繁的服务器上，可以看到us接近100,r运行队列达到80(机器在做压力测试，性能表现不佳)。sy系统CPU时间，如果太高，表示系统调用时间长，例如是IO操作频繁。id

空闲CPU时间，一般来说，id+us+sy=100,一般我认为id是空闲CPU使用率，us是用户CPU使用率，sy是系统CPU使用率。wt等待IOCPU时间。通过以上，我们可以分析数据库服务器的cpu、系统内存、IO的情况！第十页，共29页。Linux服务器监控工具-nmonNmon工具使用cpu占用率内存使用情况磁盘I/O速度、传输和读写比率文件系统的使用率网络I/O速度、传输和读写比率、错误统计率与传输包的大小消耗资源最多的进程计算机详细信息和资源页面空间和页面I/O速度用户自定义的磁盘组网络文件系统第十一页，共29页。数据库分析SGA\PGA临时表空间使用回滚段争用命中率综合分析：IO、表分析、第十二页，共29页。第十三页，共29页。Oracle动态内存管理按照系统对内存使用方法的不同，Oracle数据库的内存可以分为以下几个部分：

•系统全局区：SGA（SystemGlobalArea

•程序全局区：PGA（ProgrameGlobalArea

•排序池：SortArea

•大池：LargePool

•Java池：JavaPool第十四页，共29页。SGA、PGASGA：是用于存储数据库信息的内存区，该信息为数据库进程所共享。它包含Oracle服务器的数据和控制信息,它是在Oracle服务器所驻留的计算机的实际内存中得以分配，如果实际内存不够再往虚拟内存中写。

PGA：包含单个服务器进程或单个后台进程的数据和控制信息，与几个进程共享的SGA正相反,PGA是只被一个进程使用的区域，PGA在创建进程时分配,在终止进程时回收。

查看SGA:showparametersgaSELECT*fromv$parameteraWHEREIN('shared_pool_size','log_buffer','db_cache_size','java_pool_size','large_pool_size')查看SGA使用情况：selectcomponent,current_size/1048576,min_size/1048576fromv$sga_dynamic_components;查看PGA:SQL>showparameterpga查看PGA使用情况：selectround(sum(pga_alloc_mem)/1048576,1)fromv$process;第十五页，共29页。临时表空间使用情况存储过程报临时表空间不足，是一个常见的问题，需要引起特别重视。所以需要经常监控临时表空间的使用和对不良SQL进行优化。临时表空间大小：SELECTround(sum(bytes)/1048576/1024)fromdba_temp_files;临时表空间使用情况：selecta.USERname,b.MACHINE,b.SID,b.SERIAL#,c.SQL_TEXT,ROUND(SUM(BLOCKS*8192*2)/1048576,2)useredfromv$sort_usagea,v$sessionb,v$sqlareacwherea.SESSION_ADDR=b.SADDR

ANDb.SQL_ID=c.SQL_IDgroupbya.USERname,b.MACHINE,b.SID,b.SERIAL#,c.SQL_TEXT;查看临时表空间大小：selecttablespace_name,fromdba_temp_files;System帐号：selectstatus,enabled,name,bytes/1024/1024fromv_$tempfile;第十六页，共29页。命中率数据缓冲区命中率：select1-round((a.value-b.value-c.value)/d.value,2)ratiofromv$sysstata,v$sysstatb,v$sysstatc,v$sysstatdwhere='physicalreads'and='physicalreadsdirect'and='physicalreadsdirect(lob)'and='sessionlogicalreads';

命中率大于90%是正常的；共享池空闲率计算方法：Selectround(a.bytes/1048576,1)free,round(b.VALUE/1048576,1)all_size,round(a.BYTES/b.VALUE,2)RATIOFromv$sgastata,v$parameterbWhere='freememory'anda.POOL='sharedpool'and='shared_pool_size';SGA中字典缓冲区的命中率：selectparameter,gets,Getmisses,round(getmisses/(gets+getmisses),2)"missratio",ROUND((1-(sum(getmisses)/(sum(gets)+sum(getmisses)))),2)"Hitratio"FROMv$rowcachewheregets+getmisses<>0groupbyparameter,gets,getmisses;第十七页，共29页。v$sysstat和v$sesstat分析IO分析表访问分析时间分析：CPU、IO时间非空闲等待事件SELECT*fromv$system_eventawherea.wait_class<>’Idle’ORDERBYtime_waitedDESC;第十八页，共29页。v$sysstat和v$sesstat分析工具（参考）思路：做一个定时任务每隔一段时间v$sysstat,v$sesstat,v$sql,v$session_event进行拍照。然后对其进行分析。一、创建两个表存储快照信息createtableT_STATS(sidvarchar2(20),namevarchar2(64),classnumber,valuenumber,time_flagnumber,TIMEdate，time_numnumber);createtablet_sql_session(sidnumber(8),serial#number(8),statusvarchar2(20),sql_Idvarchar2(30),sql_textvarchar2(1000),eventvarchar2(64),last_call_etnumber,used_tempnumber,used_ublknumber,schemanamevarchar2(30),time_flagnumber,TIMEdate，time_numnumber);第十九页，共29页。v$sysstat和v$sesstat分析工具（参考）二写一个定时任务DECLAREv_jobnumber;--outnumberv_whatVARCHAR(4000);v_nextdateDATE;--下次（第一次）运行时间,v_intervalVARCHAR2(200);--计算下次运行时间BEGINv_what:='DECLAREv_timenumber;v_dateDATE;v_max_timenumber;BEGINv_time:=dbms_utility.get_time;v_date:=trunc(SYSDATE,''mi'');beginselectnvl(max(time_flag),0)+1intov_max_timefromt_stats;exceptionwhenothersthenv_max_time:=1;end;ifv_max_time>10thenreturn;endif;--插入记录insertintot_stats(sid,name,class,value,time_flag,time,time_num)select''sys'',name,class,value,v_max_time,v_date,v_timefromv$sysstatunionallselectto_char(sid),name,class,value,v_max_time,v_date,v_timefromv$sesstata,v$statnamebwherea.statistic#=b.statistic#UNIONALLselect''latch'',name,null,gets,v_max_time,v_date,v_timefromv$latch;--插入sql和session信息INSERTINTOt_sql_session(sid,serial#,status,sql_Id,sql_text,event,last_call_et,used_temp,used_ublk,schemaname,time_flag,TIME,time_num)SELECTb.sid,b.serial#,b.status,a.sql_Id,a.sql_text,b.event,b.last_call_et,d.used_temp,nvl(c.used_ublk,0),b.schemaname,v_max_time,v_date,v_timeFROMv$sqla,v$sessionb,v$transactionc,(SELECTsession_addr,ROUND(SUM(BLOCKS*8192*2)/1048576,2)used_tempFROMsys.v_$sort_usageGROUPBYsession_addr)dWHEREa.sql_id=b.sql_idANDb.saddr=c.ses_addr(+)ANDb.saddr=d.session_addr(+)ORDERBYlast_call_etDESC;COMMIT;END;';v_nextdate:=to_date('20151207','yyyymmdd');v_interval:='sysdate+1/480';dbms_job.submit(v_job,v_what,v_nextdate,v_interval);commit;end;第二十页，共29页。v$sysstat和v$sesstat分析工具（参考）三分析I/0分析classin(8,16)selecta.sid,,a.class,casewhenlike'%byte%'thenround((b.value-a.value)/1048576/1024,1)else(b.value-a.value)enddif,(b.time_num-a.time_num)/100time,row_number()over(partitionbya.sidorderby(b.value-a.value)desc)rnfromt_statsa,t_statsbwhere=anda.sid=b.sidanda.time_flag=1andb.time_flag=2anda.sid='sys'anda.classin(8,16)--条件判断and(b.value-a.value)>0第二十一页，共29页。v$sysstat和v$sesstat分析工具（参考）时间分析（更多详细内容见附件）I/0分析classin(8,16)selecta.sid,,a.class,casewhenlike'%byte%'thenround((b.value-a.value)/1048576/1024,1)else(b.value-a.value)enddif,(b.time_num-a.time_num)/100time,row_number()over(partitionbya.sidorderby(b.value-a.value)desc)rnfromt_statsa,t_statsbwhere=anda.sid=b.sidanda.time_flag=1andb.time_flag=2anda.sid='sys'LIKE'%time%'OR='CPUusedbythissession'--条件判断and(b.value-a.value)>0第二十二页，共29页。SQL分析慢速sql、TOPsql抓取重复SQLExplainSQL第二十三页，共29页。SQL分析-监控正在运行的sqlSQL监控：监控正在运行的sqlSELECTp.SPID,b.sid,b.serial#,b.status,a.sql_Id,a.sql_text,b.event,round(p.pga_alloc_mem/1048576,2)"PGA(M)",d.used_temp,b.last_call_etrun_time,m.cpu,m.scan_rows,m.physical_reads,m.logical_reads,m.sort_rows,m.sort_memory,m.sort_disk,m.ptx2,nvl(c.used_ublk,0)undo_blocks,b.schemaname,gram,b.module,b.machine,b.OSUSERFROMv$sqla,v$sessionb,v$transactionc,v$processp,(SELECTsession_addr,ROUND(SUM(BLOCKS*8192*2)/1048576,2)used_tempFROMv$sort_usageGROUPBYsession_addr)d,(SELECTA.SID,sum(CASEWHENB.NAME='CPUusedbythissession'THENA.VALUEEND)cpu,sum(CASEWHENB.NAME='tablescanrowsgotten'THENA.VALUEEND)scan_rows,sum(CASEWHENB.NAME='physicalreads'THENA.VALUEEND)physical_reads,sum(CASEWHENB.NAME='sessionlogicalreads'THENA.VALUEEND)logical_reads,sum(CASEWHENB.NAME='sorts(rows)'THENA.VALUEEND)sort_rows,sum(CASEWHENB.NAME='sorts(memory)'THENA.VALUEEND)sort_memory,sum(CASEWHENB.NAME='sorts(disk)'THENA.VALUEEND)sort_disk,sum(CASEWHENB.NAME='queriesparallelized'THENA.VALUEEND)ptx2FROMV$SESSTATA,V$STATNAMEBWHEREA.STATISTIC#=B.STATISTIC#ANDB.NAMEIN('CPUusedbythissession','tablescanrowsgotten','physicalreads','sessionlogicalreads','sorts(rows)','sorts(memory)','sorts(disk)','queriesparallelized')GROUPBYsid)mWHEREa.sql_id=b.sql_idANDb.PADDR=p.addr(+)ANDb.saddr=c.ses_addr(+)ANDb.saddr=d.session_addr(+)ANDb.sid=m.sid(+)ANDb.status='ACTIVE'ORDERBYrun_timeDESC第二十四页，共29页。查询v$sql分析执行时间较长的sqlselectSQL_ID,SQL_TEXT,FIRST_LOAD_TIME,executions,cpu_time/1e6ascpu_sec,el