Statspack的使用.doc

Statspack的使用 博客分类： Oracle开发与管理 OracleSQL脚本数据结构CacheStatspack是Oracle自带的强大的性能分析工具。他可以对当前数据库的运行状况作出全面的分析（包括实例信息、PGA顾问信息、命中率、top sql、top 5 wait event等等），是个好东西吧！下面切入正题：1.以sysdba身份登陆，查看数据文件路径 Sql代码 1. C:DocumentsandSettingsAdministratorsqlplus/assysdba 2. 3. SQL*Plus:Release.0-Productionon星期三4月1410:51:412010 4. 5. Copyright(c)1982,2005,Oracle.Allrightsreserved. 6. 7. 8. 连接到: 9. OracleDatabase10gEnterpriseEditionRelease.0-Production 10. WiththePartitioning,OLAPandDataMiningoptions 11. 12. sysAAAshowuser; 13. USER为SYS14. sysAAAselectfile_namefromdba_data_files; 15. 16. FILE_NAME 17. - 18. D:ORACLEPRODUCT10.2.0ORADATAAAAUSERS01.DBF 19. D:ORACLEPRODUCT10.2.0ORADATAAAASYSAUX01.DBF 20. D:ORACLEPRODUCT10.2.0ORADATAAAAUNDOTBS01.DBF 21. D:ORACLEPRODUCT10.2.0ORADATAAAASYSTEM01.DBF 22. D:ORACLEPRODUCT10.2.0ORADATAAAABBB.DBF 23. D:ORACLEPRODUCT10.2.0ORADATAAAAPERFSTAT.DBF 24. 25. 已选择6行。C:Documents and SettingsAdministratorsqlplus / as sysdbaSQL*Plus: Release .0 - Production on 星期三 4月 14 10:51:41 2010Copyright (c) 1982, 2005, Oracle. All rights reserved.连接到:Oracle Database 10g Enterprise Edition Release .0 - ProductionWith the Partitioning, OLAP and Data Mining optionssysAAAshow user;USER 为 SYSsysAAAselect file_name from dba_data_files;FILE_NAME-D:ORACLEPRODUCT10.2.0ORADATAAAAUSERS01.DBFD:ORACLEPRODUCT10.2.0ORADATAAAASYSAUX01.DBFD:ORACLEPRODUCT10.2.0ORADATAAAAUNDOTBS01.DBFD:ORACLEPRODUCT10.2.0ORADATAAAASYSTEM01.DBFD:ORACLEPRODUCT10.2.0ORADATAAAABBB.DBFD:ORACLEPRODUCT10.2.0ORADATAAAAPERFSTAT.DBF已选择6行。2.创建statspack存储数据的表空间，（注：statspack往往会产生大量的分析数据，所以表空间还是大点为好）。 Sql代码 1. createtablespaceperfstatdatafileD:ORACLEPRODUCT10.2.0ORADATAAAAPERFSTAT.DBFsize2G;create tablespace perfstat datafile D:ORACLEPRODUCT10.2.0ORADATAAAAPERFSTAT.DBF size 2G;3.运行statspack安装脚本。默认位置在$oracle_homerdbmsadminspcreate.sql Sql代码 1. sysAAAD:oracleproduct10.2.0db_1RDBMSADMINspcreate.sql 2. 3. . 4. 5. 输入perfstat_password的值:perfstat 6. 7. . 8. 9. 输入default_tablespace的值:perfstat 10. . 11. . 12. 输入temporary_tablespace的值:temp13. . 14. .sysAAA D:oracleproduct10.2.0db_1RDBMSADMINspcreate.sql.输入 perfstat_password 的值: perfstat.输入 default_tablespace 的值: perfstat.输入 temporary_tablespace 的值: temp.安装完之后 会自动切换用户到perfstat下： Sql代码 1. PERFSTATAAAshowuser; 2. 3. USERisPERFSTATPERFSTATAAA show user;USER is PERFSTAT安装完毕！4.接下来采样分析，设定一个job，每小时执行一次采样。 首先查看当前DB中有没有正在运行的JOB： Sql代码 1. perfstatAAAselectjob,schema_user,next_date,interval,whatfromuser_jobs; 2. 3. 未选定行perfstatAAAselect job,schema_user,next_date,interval,what from user_jobs;未选定行创建statspack采样的job，没每个小时采样一次。 Sql代码 1. perfstatAAAvariablejobnumber; 2. perfstatAAAbegin3. 2dbms_job.submit(:job,statspack.snap;,trunc(sysdate+1/24,hh24),trunc(sysdate+1/24,hh24); 4. 3commit; 5. 4end; 6. 5/ 7. 8. PL/SQL过程已成功完成。perfstatAAAvariable job number;perfstatAAAbegin 2 dbms_job.submit(:job,statspack.snap;,trunc(sysdate+1/24,hh24),trunc(sysdate+1/24,hh24); 3 commit; 4 end; 5 /PL/SQL 过程已成功完成。查看当前正在运行的job有哪些？ Sql代码 1. perfstatAAAselectjobasj,schema_user,next_date,interval,whatfromuser_jobs; 2. JSCHEMA_USERNEXT_DATEINTERVALWHAT 3. - 4. 1PERFSTAT14-4月-10trunc(sysdstatspack. 5. ate+1/24,snap; 6. hh24)perfstatAAAselect job as j,schema_user,next_date,interval,what from user_jobs; J SCHEMA_USER NEXT_DATE INTERVAL WHAT- - - - - 1 PERFSTAT 14-4月 -10 trunc(sysd statspack. ate+1/24, snap; hh24)5.由于statspack的采集和分析会做很多DB的分析，产生大量的分析数据，所以频繁的采样肯定会消耗系统性能，特别是在生产库中，所以当你建立了上面每小时执行一次的那个job，请务必在不需要的时候停止它。不然的话，这个失误可能会是致命的（ statspack job每小时都会跑，永不停的跑下去，呵呵。），尤其在生产库中。明天凌晨，系统比较清闲，采样已经没多大意义（采样分析的最终目的是分析高峰时段的系统瓶颈），所以停止这个job. Sql代码 1. perfstatAAAvariablejobnumber; 2. perfstatAAAbegin3. 2dbms_job.submit(:job,dbms_job.broken(1,true);,trunc(sysdate+1),null); 4. 3commit; 5. 4end; 6. 5/ 7. 8. PL/SQL过程已成功完成。perfstatAAAvariable job number;perfstatAAAbegin 2 dbms_job.submit(:job,dbms_job.broken(1,true);,trunc(sysdate+1),null); 3 commit; 4 end; 5 /PL/SQL 过程已成功完成。6.几个小时候后，看看生成的哪些快照。Sql代码 1. perfstatAAAselectsnap_id,snap_time,startup_timefromstats$snapshot; 2. 3. SNAP_IDSNAP_TIMESTARTUP_TIME 4. - 5. 114-4月-1014-4月-10 6. 214-4月-1014-4月-10perfstatAAAselect snap_id,snap_time,startup_time from stats$snapshot; SNAP_ID SNAP_TIME STARTUP_TIME- - - 1 14-4月 -10 14-4月 -10 2 14-4月 -10 14-4月 -107.设定任意两个快照，产生这段时间内的性能分析报告（此时需要跑spreport脚本，路径和刚才那个脚本一致）。Sql代码 1. perfstatAAAD:oracleproduct10.2.0db_1RDBMSADMINspreport.sql 2. 3. CurrentInstance 4. 5. DBIdDBNameInstNumInstance 6. - 7. 1858440386AAA1aaa 8. 9. 10. 11. InstancesinthisStatspackschema12. 13. 14. DBIdInstNumDBNameInstanceHost 15. - 16.AAaaa6979580041BD 17. 490 18. 19. Using1858440386fordatabaseId 20. Using1forinstancenumber 21. 22. 23. Specifythenumberofdaysofsnapshotstochoosefrom24. 25. Enteringthenumberofdays(n)willresultinthemostrecent 26. (n)daysofsnapshotsbeinglisted.Pressingwithout 27. specifyinganumberlistsallcompletedsnapshots. 28. 29. 30. 31. ListingallCompletedSnapshots 32. 33. Snap 34. InstanceDBNameSnapIdSnapStartedLevelComment 35. - 36. aaaAAA1144月201009:55 37. 2 38. 2144月201009:55 39. 3 40. 3144月201011:05 41. 0 42. 43. 44. 45. SpecifytheBeginandEndSnapshotIds 46. 47. 输入begin_snap的值:1 48. BeginSnapshotIdspecified:1 49. 50. 输入end_snap的值:2 51. EndSnapshotIdspecified:2 52. 53. 54. 55. SpecifytheReportName56. 57. Thedefaultreportfilenameissp_1_2.Tousethisname, 58. presstocontinue,otherwiseenteranalternative. 59. 60. 输入report_name的值:d:myreport.txtperfstatAAAD:oracleproduct10.2.0db_1RDBMSADMINspreport.sqlCurrent Instance DB Id DB Name Inst Num Instance- - - - 1858440386 AAA 1 aaaInstances in this Statspack schema DB Id Inst Num DB Name Instance Host- - - - - 1858440386 1 AAA aaa 6979580041BD 490Using 1858440386 for database IdUsing 1 for instance numberSpecify the number of days of snapshots to choose fromEntering the number of days (n) will result in the most recent(n) days of snapshots being listed. Pressing withoutspecifying a number lists all completed snapshots.Listing all Completed Snapshots SnapInstance DB Name Snap Id Snap Started Level Comment- - - - - -aaa AAA 1 14 4月 2010 09:5 5 2 2 14 4月 2010 09:5 5 3 3 14 4月 2010 11:0 5 0Specify the Begin and End Snapshot Ids输入 begin_snap 的值: 1Begin Snapshot Id specified: 1输入 end_snap 的值: 2End Snapshot Id specified: 2Specify the Report NameThe default report file name is sp_1_2. To use this name,press to continue, otherwise enter an alternative.输入 report_name 的值: d:myreport.txtSql代码 1. .回车.回车8.完成后，会产生一个分析报告（d:myreport.txt）。附件：报告的截取片段：Sql代码 1. STATSPACKreportfor2. 3. DatabaseDBIdInstanceInstNumStartupTimeReleaseRAC 4. - 5. 1858440386aaa114-4月-1009:.0NO6. 2 7. 8. HostName:6979580041BD490NumCPUs:2PhysMemory(MB):3,326 9. 10. 11. SnapshotSnapIdSnapTimeSessionsCurs/SessComment 12. - 13. BeginSnap:114-4月-1009:52:22154.3 14. EndSnap:214-4月-1009:53:20155.8 15. Elapsed:0.97(mins) 16. 17. CacheSizesBeginEnd18. - 19. BufferCache:184MStdBlockSize:8K 20. SharedPoolSize:380MLogBuffer:6,860K 21. 22. LoadProfilePerSecondPerTransaction23. - 24. Redosize:10,075.66584,388.00 25. Logicalreads:58.413,388.00 26. Blockchanges:18.811,091.00 27. Physicalreads:0.2213.00 28. Physicalwrites:0.000.00 29. Usercalls:0.5230.00 30. Parses:2.83164.00 31. Hardparses:0.7242.00 32. Sorts:1.76102.00 33. Logons:0.021.00 34. Executes:10.88631.00 35. Transactions:0.02 36. 37. %BlockschangedperRead:32.20RecursiveCall%:99.69 38. Rollbackpertransaction%:0.00RowsperSort:70.69 39. 40. InstanceEfficiencyPercentages 41. 42. BufferNowait%:100.00RedoNoWait%:100.00 43. BufferHit%:99.62In-memorySort%:100.00 44. LibraryHit%:90.06SoftParse%:74.39 45. ExecutetoParse%:74.01LatchHit%:100.00 46. ParseCPUtoParseElapsd%:14.29%Non-ParseCPU:98.41 47. 48. SharedPoolStatisticsBeginEnd49. - 50. MemoryUsage%:21.0520.98 51. %SQLwithexecutions1:54.0560.06 52. %MemoryforSQLw/exec1:80.5183.00 53. 54. Top5TimedEventsAvg%Total 55. waitCall 56. EventWaitsTime(s)(ms)Time57. - 58. CPUtime170.7 59. controlfilesequentialread1890123.6 60. dbfilesequentialread8032.5 61. controlfileparallelwrite27011.9 62. logfilesync105.6 63. 64. . 65. . 66. .STATSPACK report forDatabase DB Id Instance Inst Num Startup Time Release RAC - - - - - - 1858440386 aaa 1 14-4月 -10 09:2 .0 NO 2Host Name: 6979580041BD490 Num CPUs: 2 Phys Memory (MB): 3,326Snapshot Snap Id Snap Time Sessions Curs/Sess Comment - - - - -Begin Snap: 1 14-4月 -10 09:52:22 15 4.3 End Snap: 2 14-4月 -10 09:53:20 15 5.8 Elapsed: 0.97 (mins)Cache Sizes Begin End - - Buffer Cache: 184M Std Block Size: 8K Shared Pool Size: 380M Log Buffer: 6,860KLoad Profile Per Second Per Transaction - - Redo size: 10,075.66 584,388.00 Logical reads: 58.41 3,388.00 Block changes: 18.81 1,091.00 Physical reads: 0.22 13.00 Physical writes: 0.00 0.00 User calls: 0.52 30.00 Parses: 2.83 164.00 Hard parses: 0.72 42.00 Sorts: 1.76 102.00 Logons: 0.02 1.00 Executes: 10.88 631.00 Transactions: 0.02