数据库压力问题求助

Question

现像：
前台应用进程使用完，出现队列排队等待，处理没停止，但是请求数大于响应处理数。
从后台数据库日志看是以下情况，希望有DBA能协助分析一下造成的原因：
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
            Buffer Nowait %:   99.78       Redo NoWait %:    100.00
            Buffer  Hit   %:   77.98    In-memory Sort %:     99.94
            Library Hit   %:   97.23        Soft Parse %:     90.78
         Execute to Parse %:   75.74         Latch Hit %:     99.96
Parse CPU to Parse Elapsd %:   91.18     % Non-Parse CPU:     97.88 Shared Pool Statistics        Begin   End
                               ------  ------
             Memory Usage %:   92.63   92.58
    % SQL with executions>1:   77.53   45.17
  % Memory for SQL w/exec>1:   72.68   39.49Top 5 Timed Events
~~~~~~~~~~~~~~~~~~                                                     % Total
Event                                               Waits    Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
db file scattered read                         11,103,913      20,416    31.67
CPU time                                                       18,119    28.10
db file sequential read                        11,754,482      12,434    19.29
direct path read                                5,690,556       8,611    13.36
buffer busy waits                               1,061,541       3,856     5.98Wait Events for DB: CFSPDDB  Instance: cfspddb  Snaps: 17096 -17103
-> s  - second
-> cs - centisecond -     100th of a second
-> ms - millisecond -    1000th of a second
-> us - microsecond - 1000000th of a second
-> ordered by wait time desc, waits desc (idle events last)                                                                   Avg
                                                     Total Wait   wait    Waits
Event                               Waits   Timeouts   Time (s)   (ms)     /txn
---------------------------- ------------ ---------- ---------- ------ --------
db file scattered read         11,103,913          0     20,416      2    123.7
db file sequential read        11,754,482          0     12,434      1    131.0
direct path read                5,690,556          0      8,611      2     63.4
buffer busy waits               1,061,541          0      3,856      4     11.8
direct path write                 688,609          0        527      1      7.7
log file sync                      89,944          0        146      2      1.0
log file parallel write           185,131          0        138      1      2.1
sbtbackup                               2          0        117  58485      0.0
SQL*Net more data to client     1,735,038          0         33      0     19.3
db file parallel read               5,821          0         15      3      0.1Background Wait Events for DB: CFSPDDB  Instance: cfspddb  Snaps: 17096 -17103
-> ordered by wait time desc, waits desc (idle events last)                                                                   Avg
                                                     Total Wait   wait    Waits
Event                               Waits   Timeouts   Time (s)   (ms)     /txn
---------------------------- ------------ ---------- ---------- ------ --------
log file parallel write           185,131          0        138      1      2.1
control file parallel write         4,220          0          7      2      0.0
db file parallel write              1,365          0          6      4      0.0
control file sequential read        3,762          0          2      1      0.0
log file sequential read              268          0          2      9      0.0
db file sequential read               457          0          2      5      0.0
db file scattered read                153          0          2     11      0.0

Answer 1

Instance Activity Stats for DB: CFSPDDB  Instance: cfspddb  Snaps: 17096 -17103Statistic                                      Total     per Second    per Trans
--------------------------------- ------------------ -------------- ------------
failed probes on index block recl                  0            0.0          0.0
free buffer inspected                        381,946           30.3          4.3
free buffer requested                    105,156,627        8,346.4      1,171.8
hot buffers moved to head of LRU           3,410,476          270.7         38.0
immediate (CR) block cleanout app                434            0.0          0.0
immediate (CURRENT) block cleanou             17,768            1.4          0.2
index crx upgrade (positioned)               127,110           10.1          1.4
index fast full scans (full)                     159            0.0          0.0
index fetch by key                        98,449,124        7,814.0      1,097.0
index scans kdiixs1                       76,172,906        6,046.0        848.8
leaf node 90-10 splits                            20            0.0          0.0
leaf node splits                                 500            0.0          0.0
logons cumulative                                427            0.0          0.0
messages received                             96,774            7.7          1.1
messages sent                                 96,774            7.7          1.1
no buffer to keep pinned count                     0            0.0          0.0
no work - consistent read gets           305,784,091       24,270.5      3,407.3
opened cursors cumulative                    623,965           49.5          7.0
parse count (failures)                             5            0.0          0.0
parse count (hard)                            57,552            4.6          0.6
parse count (total)                          624,316           49.6          7.0
parse time cpu                                38,420            3.1          0.4
parse time elapsed                            42,136            3.3          0.5
physical reads                           139,258,432       11,053.1      1,551.8
physical reads direct                     34,138,095        2,709.6        380.4
physical writes                           14,824,943        1,176.7        165.2
physical writes direct                    14,783,924        1,173.4        164.7
physical writes non checkpoint            14,824,256        1,176.6        165.2
pinned buffers inspected                     366,733           29.1          4.1
prefetch clients - default                         1            0.0          0.0
prefetched blocks                         82,262,934        6,529.3        916.7
prefetched blocks aged out before                186            0.0          0.0
process last non-idle time                    12,598            1.0          0.1
recursive calls                              401,977           31.9          4.5
recursive cpu usage                            6,083            0.5          0.1
redo blocks written                          385,260           30.6          4.3
redo buffer allocation retries                     2            0.0          0.0
redo entries                                 374,218           29.7          4.2
redo log space requests                            2            0.0          0.0
redo log space wait time                           7            0.0          0.0
redo ordering s                                0            0.0          0.0
redo size                                337,424,336       26,781.8      3,759.9
redo synch time                               14,973            1.2          0.2
redo synch writes                             89,997            7.1          1.0
redo wastage                              50,841,584        4,035.4        566.5
redo write time                               14,075            1.1          0.2
redo writer latching time                          0            0.0          0.0
redo writes                                   92,650            7.4          1.0
rollback changes - undo records a                  6            0.0          0.0
rollbacks only - consistent read              12,393            1.0          0.1
rows fetched via callback                 71,255,506        5,655.7        794.0
session connect time                               0            0.0          0.0
session logical reads                    477,386,065       37,890.8      5,319.5
session pga memory                        15,687,848        1,245.2        174.8
session uga memory                    30,073,379,952    2,386,965.6    335,105.6
session uga memory max                   849,862,168       67,454.7      9,470.0

Answer 2

                     Undo      Num Max Qry   Max Tx  Snap   Out of uS/uR/uU/
End Time           Blocks    Trans Len (s)    Concy Too Old  Space eS/eR/eU
------------ ------------ -------- ------- -------- ------- ------ -------------
21-May 17:31          376 ########     311        1       0      0 0/0/0/0/0/0
21-May 17:21          725 ########     201        1       0      0 0/0/0/0/0/0
21-May 17:11          916 ########     244        1       0      0 0/0/0/0/0/0
21-May 17:01          789 ########     286        1       0      0 0/0/0/0/0/0
21-May 16:51          845 ########     367        1       0      0 0/0/0/0/0/0
21-May 16:41          888 ########     524        1       0      0 0/0/0/0/0/0
21-May 16:31          634 ########     487        1       0      0 0/0/0/0/0/0
21-May 16:21          763 ########     518        1       0      0 0/0/0/0/0/0
21-May 16:11          780 ########     289        2       0      0 0/0/0/0/0/0
21-May 16:01        1,364 ########     771        1       0      0 0/0/0/0/0/0
21-May 15:51          987 ########     623        1       0      0 0/0/0/0/0/0
21-May 15:41          922 ########   1,708        1       0      0 0/0/0/0/0/0
21-May 15:31          878 ########   1,930        1       0      0 0/0/0/0/0/0
21-May 15:21          905 ########     647        1       0      0 0/0/0/0/0/0
21-May 15:11          990 ########     576        2       0      0 0/0/0/0/0/0
21-May 15:01          996 ########     431        1       0      0 0/0/0/0/0/0
21-May 14:51          937 ########     295        1       0      0 0/0/0/0/0/0
21-May 14:41        1,135 ########     257        1       0      0 0/0/0/0/0/0
21-May 14:31          922 ########     346        1       0      0 0/0/0/0/0/0
21-May 14:21        1,011 ########     132        1       0      0 0/0/0/0/0/0
21-May 14:11        1,147 ########     151        1       0      0 0/0/0/0/0/0

Answer 3

1. Buffer Hit %: 77.98 In-memory Sort %: 99.94
-- buffer 命中率偏低， 最好在95%左右， 建议调整SGA 大小. 2. 前台应用进程使用完，出现队列排队等待，处理没停止，但是请求数大于响应处理数。
-- 没怎么看明白， 你的sessions 设的是多少？是不是sessions 设小了？

Answer 4

WEBLOGIC设置的进程数是60，全部用满，等待的队列最高达到700，一直在这个数字上前后变化Parameter Name                Begin value                       (if different)
----------------------------- --------------------------------- --------------
aq_tm_processes               1
background_dump_dest          /home/db/oracle/admin/cfspddb/bdu
compatible                    9.2.0.0.0
control_files                 /home/db/oracle/oradata/cfspddb/r
core_dump_dest                /home/db/oracle/admin/cfspddb/cdu
db_block_size                 8192
db_cache_size                 1073741824
db_domain
db_file_multiblock_read_count 16
db_name                       cfspddb
fast_start_mttr_target        300
hash_join_enabled             TRUE
instance_name                 cfspddb
java_pool_size                0
job_queue_processes           10
large_pool_size               16777216
log_archive_dest              /home/db/orarch
log_archive_start             TRUE
open_cursors                  300
pga_aggregate_target          67108864
processes                     500
query_rewrite_enabled         FALSE
remote_login_passwordfile     EXCLUSIVE
sga_max_size                  2685891664
shared_pool_size              536870912
sort_area_size                524288
spfile                        /home/db/oracle/oradata/cfspddb/r
star_transformation_enabled   FALSE
timed_statistics              TRUE
undo_management               AUTO
undo_retention                10800
undo_tablespace               UNDOTBS1
user_dump_dest                /home/db/oracle/admin/cfspddb/udu

Answer 5

同意。同时，楼主处理下TOP 5 SQL, 猜测走全表扫描的SQL严重影响了性能

Answer 6

请教楼主，这些参数是怎么统计出来的呀，我也在做并发的优化。不过我这里的Execute to Parse是96%。