CPU居高不下

cc59

Begin Snap:       655 19-4月 -08 08:08:03      67     387.5
  End Snap:       671 19-4月 -08 10:13:26      45     546.4
   Elapsed:              125.38 (mins)

这个时间点都很慢吗?
125分钟太长了点.

logical reads

比较高...先tuning top sql吧. 特别是top buffer gets sql

remen

原帖由 flying.hg 于 2008-4-19 13:52 发表
楼主做statspack时,系统不是很繁忙吧!在出问题时做,建议做半小时的,贴上来看一下!

内存还足够的话,可以适当增加buffer cache size.当然,这不是关键的.

     33,448,311            1   33,448,311.0   11.4   119.06    154.96 3783859235
Module: PL/SQL Developer
select t.流程,        t.责任人,        t.开工时间 签单时间,
   t.完成时间,        t.完成时间,        t.实际周期,        t.承
诺完成时间,        t.承诺周期,        t.备注   from erp.dev_repo
rt_new_all t  where t.开发单号 = '2008-02-004-01_EU105'

      5,787,370            1    5,787,370.0    2.0    25.66     96.70 1159692996
Module: PL/SQL Developer
select t.流程,t.责任人,t.开工时间 签单时间,t.完成时间,t.完成时间
,t.实际周期,t.承诺完成时间,t.承诺周期,t.备注   from dev_report_n
ew_all t  where t.开发单号 = 'BSJ_XP_2008-01-S03_F1124'

        974,058            1      974,058.0    0.3     6.09      7.49  620523615
Module: oracle@ebs (TNS V1-V3)
SELECT "DOC_NUMBER","CPBM" FROM "ERP"."DEV_NEW_PRODUCT_DOC" "EDP

         12,023            1       12,023.0    0.3    25.66     96.70 1159692996
Module: PL/SQL Developer
select t.流程,t.责任人,t.开工时间 签单时间,t.完成时间,t.完成时间
,t.实际周期,t.承诺完成时间,t.承诺周期,t.备注   from dev_report_n
ew_all t  where t.开发单号 = 'BSJ_XP_2008-01-S03_F1124'

上面这些都不是生产环境正式的应用吧,要控管哦,生产环境不能这样随便连入拉数据的,这些只要一连接,肯定top 1 开销就是它了!
特别是在系统io本事瓶颈情况下,这样连接,肯定会影响正常应用的.一定要控管!

另将单个sql buffer get,physical read比较大的,拿出来看一下执行计划,看能否调整了!

如果不调应用,进行控管的话,感觉没有大的调优空间了.
好的方式,升级硬件,如cpu是瓶颈就加cpu了.
当然,最好是把生产环境迁移到linux下,可能不升级的话,还能抗一段时间了~

这个是临时的，这个session是我们信息部门其他同事做的查询，这些都好说，我可以控制它，关键是应用本身的问题足以导致cpu居高不下了

flying.hg

呵呵，如果系统负载很大的话，千万不要低估这类连接，很可拍的哦，之前碰到过这样的问题！

不能调整应用的话，将单个sql buffer get,physical read比较大的,拿出来看一下执行计划,看能否调整了!

不是什么时候都是cpu居高不下吧，在出问题时，做一个statspack看一下，这样更有针对性，十分钟左右就行了！

baobao219

3 Recommendations:
Your database has relatively high logical I/O at 38,869 reads per second. Logical Reads includes data block reads from both memory and disk. High LIO is sometimes associated with high CPU activity. CPU bottlenecks occur when the CPU run queue exceeds the number of CPUs on the database server, and this can be seen by looking at the "r" column in the vmstat UNIX/Linux utility or within the Windows performance manager. Consider tuning your application to reduce unnecessary data buffer touches (SQL Tuning or PL/SQL bulking), using faster CPUs or adding more CPUs to your system.
You are performing more than 518 disk reads per second. High disk latency can be caused by too-few physical disk spindles. Compare your read times across multiple datafiles to see which datafiles are slower than others. Disk read times may be improved if contention is reduced on the datafile, even though read times may be high due to the file residing on a slow disk. You should identify whether the SQL accessing the file can be tuned, as well as the underlying characteristics of the hardware devices.
Check your average disk read speed later in this report and ensure that it is under 7ms. Assuming that the SQL is optimized, the only remaining solutions are the addition of RAM for the data buffers or a switch to solid state disks. Give careful consideration these tablespaces with high read I/O: TIPLM_CUS , TIPLM , TIPLM_BPM_H , TIPLM_IDX , TIPLM_ITEM .

5 Recommendations:
You have high network activity with 123.9 SQL*Net roundtrips to/from client per second, which is a high amount of traffic. Review your application to reduce the number of calls to Oracle by encapsulating data requests into larger pieces (i.e. make a single SQL request to populate all online screen items). In addition, check your application to see if it might benefit from bulk collection by using PL/SQL "forall" or "bulk collect" operators.
You have 17,806.8 consistent gets examination per second. "Consistent gets - examination" is different than regular consistent gets. It is used to read undo blocks for consistent read purposes, but also for the first part of an index read and hash cluster I/O. To reduce disk reads, you may consider moving your indexes to a large blocksize tablespace. Because index splitting and spawning are controlled at the block level, a larger blocksize will result in a flatter index tree structure.
You have high update activity with 52.2 db block changes per second. The DB block changes are a rough indication of total database work. This statistic indicates (on a per-transaction level) the rate at which buffers are being dirtied and you may want to optimize your database writer (DBWR) process. You can determine which sessions and SQL statements have the highest db block changes by querying the v$session and v$sessatst views.
You have 1,521 table fetch continued row actions during this period. Migrated/chained rows always cause double the I/O for a row fetch and "table fetch continued row" (chained row fetch) happens when we fetch BLOB/CLOB columns (if the avg_row_len > db_block_size), when we have tables with > 255 columns, and when PCTFREE is too small. You may need to reorganize the affected tables with the dbms_redefintion utility and re-set your PCTFREE parameters to prevent future row chaining.
You have high small table full-table scans, at 764.9 per second. Verify that your KEEP pool is sized properly to cache frequently referenced tables and indexes. Moving frequently-referenced tables and indexes to SSD will significantly increase the speed of small-table full-table scans.

baobao219

2 Recommendations:
You have high cache buffer chain latches with 0.2% get miss. See MetaLink about increasing the hidden parameter _db_block_hash_buckets.
You have high library cache waits with 1.4% get miss. Consider pinning your frequently-used packages in the library cache with dbms_shared_pool.keep.
The Oracle buffer cache advisory utility indicates 101,793,111 disk reads during the sample interval. Oracle estimates that doubling the data buffer size (by increasing db_cache_size) will reduce disk reads to 23,063,432, a 77.34% decrease.

6 Recommendations:
You are not using large blocksizes for your index tablespaces. Oracle research proves that indexes will build flatter tree structures in larger blocksizes.
You have the default value for db_file_multiblock_read_count at 16. The CBO uses this parameter to determine the cost of a full-table scan. The default value is sometimes too large, and you can run scripts to determine the optimal setting. If full-table scans are unavoidable, you may consider placing those tables on SSD .
You are not using your KEEP pool to cache frequently referenced tables and indexes. This may cause unnecessary I/O. When configured properly, the KEEP pool guarantees full caching of popular tables and indexes. Remember, an average buffer get is often 100 times faster than a disk read. Any table or index that consumes > 10% of the data buffer, or tables & indexes that have > 50% of their blocks residing in the data buffer should be cached into the KEEP pool. You can fully automate this process using scripts.
Consider setting your optimizer_index_caching parameter to assist the cost-based optimizer. Set the value of optimizer_index_caching to the average percentage of index segments in the data buffer at any time, which you can estimate from the v$bh view.
You have not enabled Materialized Views and Function-based indexes, which are very powerful features that require you to set query_rewrite_integrity and query_rewrite_enabled.
Your shared pool is set at 304MB, which is an unusually large value. Allocating excessive shared pool resource can adversely impact Oracle performance. For further details, see the shared pool advisory.

ora110

把生产环境迁移到linux吧。