公司将原服务器上程序及数据库移植至小型机后,发现原来运转良好的项目现在奇慢。小型机的操作系统是AIX6104,各方面配置比以前的DELL服务器高了很多。对数据库表分析、索引分析后无改观。下面将数据库STATSPACK报告发送,请大家帮忙分析下原因,谢谢!!!STATSPACK report forDB Name DB Id Instance Inst Num Release Cluster Host
------------ ----------- ------------ -------- ----------- ------- ------------
ORA9I 2529836250 ora9i 1 9.2.0.1.0 NO ykgjj Snap Id Snap Time Sessions Curs/Sess Comment
------- ------------------ -------- --------- -------------------
Begin Snap: 1 14-Oct-10 15:01:37 18 8.3
End Snap: 2 14-Oct-10 16:45:54 11 4.3
Elapsed: 104.28 (mins)Cache Sizes (end)
~~~~~~~~~~~~~~~~~
Buffer Cache: 1,520M Std Block Size: 8K
Shared Pool Size: 1,008M Log Buffer: 2,048KLoad Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 11,433.19 92,187.50
Logical reads: 4,834.45 38,980.87
Block changes: 79.26 639.10
Physical reads: 2.08 16.78
Physical writes: 18.46 148.88
User calls: 363.15 2,928.12
Parses: 15.66 126.28
Hard parses: 0.13 1.08
Sorts: 14.05 113.29
Logons: 0.78 6.27
Executes: 49.32 397.70
Transactions: 0.12 % Blocks changed per Read: 1.64 Recursive Call %: 20.25
Rollback per transaction %: 0.64 Rows per Sort: 12.23Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 100.00 In-memory Sort %: 100.00
Library Hit %: 99.68 Soft Parse %: 99.14
Execute to Parse %: 68.25 Latch Hit %: 100.00
Parse CPU to Parse Elapsd %: 8.90 % Non-Parse CPU: 99.91 Shared Pool Statistics Begin End
------ ------
Memory Usage %: 37.44 39.23
% SQL with executions>1: 61.96 62.23
% Memory for SQL w/exec>1: 67.03 67.65Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total
Event Waits Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
CPU time 319 47.49
direct path write 14,523 185 27.61
db file parallel write 1,374 79 11.74
control file parallel write 2,028 32 4.80
log file parallel write 2,043 20 2.97
-------------------------------------------------------------
Wait Events for DB: ORA9I Instance: ora9i Snaps: 1 -2
-> 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
---------------------------- ------------ ---------- ---------- ------ --------
direct path write 14,523 0 185 13 18.7
db file parallel write 1,374 687 79 57 1.8
control file parallel write 2,028 0 32 16 2.6
log file parallel write 2,043 1,803 20 10 2.6
log file sync 1,134 2 14 12 1.5
direct path read 1,378 0 10 7 1.8
SQL*Net break/reset to clien 1,296 0 6 4 1.7
control file sequential read 859 0 3 4 1.1
db file sequential read 603 0 2 2 0.8
SQL*Net more data to client 50,812 0 1 0 65.5
log buffer space 5 0 0 27 0.0
enqueue 2 0 0 50 0.0
latch free 18 4 0 4 0.0
buffer busy waits 12 0 0 1 0.0
LGWR wait for redo copy 7 0 0 0 0.0
SQL*Net message from client 2,384,174 0 39,938 17 3,072.4
virtual circuit status 209 209 6,100 29186 0.3
wakeup time manager 197 197 5,895 29922 0.3
SQL*Net more data from clien 1,194 0 168 140 1.5
SQL*Net message to client 2,384,167 0 1 0 3,072.4
-------------------------------------------------------------
Background Wait Events for DB: ORA9I Instance: ora9i Snaps: 1 -2
-> ordered by wait time desc, waits desc (idle events last) Avg
Total Wait wait Waits
Event Waits Timeouts Time (s) (ms) /txn
---------------------------- ------------ ---------- ---------- ------ --------
db file parallel write 1,374 687 79 57 1.8
control file parallel write 2,028 0 32 16 2.6
log file parallel write 2,043 1,803 20 10 2.6
control file sequential read 815 0 3 4 1.1
direct path read 11 0 1 122 0.0
direct path write 11 0 0 4 0.0
buffer busy waits 5 0 0 2 0.0
rdbms ipc reply 5 0 0 0 0.0
LGWR wait for redo copy 7 0 0 0 0.0
rdbms ipc message 13,124 11,396 35,897 2735 16.9
smon timer 22 20 6,074 ###### 0.0init.ora Parameters for DB: ORA9I Instance: ora9i Snaps: 1 -2 End value
Parameter Name Begin value (if different)
----------------------------- --------------------------------- --------------
aq_tm_processes 1
background_dump_dest /home/oracle/OraHome1/admin/ora9i
compatible 9.2.0.0.0
control_files /oradata/ora9i/control01.ctl, /or
core_dump_dest /home/oracle/OraHome1/admin/ora9i
db_block_size 8192
db_cache_size 1593835520
db_domain
db_file_multiblock_read_count 16
db_name ora9i
dispatchers (PROTOCOL=TCP) (SERVICE=ora9iXDB)
fast_start_mttr_target 300
hash_join_enabled TRUE
instance_name ora9i
java_pool_size 117440512
job_queue_processes 10
large_pool_size 218103808
open_cursors 300
pga_aggregate_target 50331648
processes 150
query_rewrite_enabled FALSE
remote_login_passwordfile EXCLUSIVE
sga_max_size 5254388712
shared_pool_size 1056964608
sort_area_size 524288
star_transformation_enabled FALSE
timed_statistics TRUE
undo_management AUTO
undo_retention 10800
undo_tablespace UNDOTBS1
user_dump_dest /home/oracle/OraHome1/admin/ora9i
-------------------------------------------------------------End of Report
------------ ----------- ------------ -------- ----------- ------- ------------
ORA9I 2529836250 ora9i 1 9.2.0.1.0 NO ykgjj Snap Id Snap Time Sessions Curs/Sess Comment
------- ------------------ -------- --------- -------------------
Begin Snap: 1 14-Oct-10 15:01:37 18 8.3
End Snap: 2 14-Oct-10 16:45:54 11 4.3
Elapsed: 104.28 (mins)Cache Sizes (end)
~~~~~~~~~~~~~~~~~
Buffer Cache: 1,520M Std Block Size: 8K
Shared Pool Size: 1,008M Log Buffer: 2,048KLoad Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 11,433.19 92,187.50
Logical reads: 4,834.45 38,980.87
Block changes: 79.26 639.10
Physical reads: 2.08 16.78
Physical writes: 18.46 148.88
User calls: 363.15 2,928.12
Parses: 15.66 126.28
Hard parses: 0.13 1.08
Sorts: 14.05 113.29
Logons: 0.78 6.27
Executes: 49.32 397.70
Transactions: 0.12 % Blocks changed per Read: 1.64 Recursive Call %: 20.25
Rollback per transaction %: 0.64 Rows per Sort: 12.23Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 100.00 In-memory Sort %: 100.00
Library Hit %: 99.68 Soft Parse %: 99.14
Execute to Parse %: 68.25 Latch Hit %: 100.00
Parse CPU to Parse Elapsd %: 8.90 % Non-Parse CPU: 99.91 Shared Pool Statistics Begin End
------ ------
Memory Usage %: 37.44 39.23
% SQL with executions>1: 61.96 62.23
% Memory for SQL w/exec>1: 67.03 67.65Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total
Event Waits Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
CPU time 319 47.49
direct path write 14,523 185 27.61
db file parallel write 1,374 79 11.74
control file parallel write 2,028 32 4.80
log file parallel write 2,043 20 2.97
-------------------------------------------------------------
Wait Events for DB: ORA9I Instance: ora9i Snaps: 1 -2
-> 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
---------------------------- ------------ ---------- ---------- ------ --------
direct path write 14,523 0 185 13 18.7
db file parallel write 1,374 687 79 57 1.8
control file parallel write 2,028 0 32 16 2.6
log file parallel write 2,043 1,803 20 10 2.6
log file sync 1,134 2 14 12 1.5
direct path read 1,378 0 10 7 1.8
SQL*Net break/reset to clien 1,296 0 6 4 1.7
control file sequential read 859 0 3 4 1.1
db file sequential read 603 0 2 2 0.8
SQL*Net more data to client 50,812 0 1 0 65.5
log buffer space 5 0 0 27 0.0
enqueue 2 0 0 50 0.0
latch free 18 4 0 4 0.0
buffer busy waits 12 0 0 1 0.0
LGWR wait for redo copy 7 0 0 0 0.0
SQL*Net message from client 2,384,174 0 39,938 17 3,072.4
virtual circuit status 209 209 6,100 29186 0.3
wakeup time manager 197 197 5,895 29922 0.3
SQL*Net more data from clien 1,194 0 168 140 1.5
SQL*Net message to client 2,384,167 0 1 0 3,072.4
-------------------------------------------------------------
Background Wait Events for DB: ORA9I Instance: ora9i Snaps: 1 -2
-> ordered by wait time desc, waits desc (idle events last) Avg
Total Wait wait Waits
Event Waits Timeouts Time (s) (ms) /txn
---------------------------- ------------ ---------- ---------- ------ --------
db file parallel write 1,374 687 79 57 1.8
control file parallel write 2,028 0 32 16 2.6
log file parallel write 2,043 1,803 20 10 2.6
control file sequential read 815 0 3 4 1.1
direct path read 11 0 1 122 0.0
direct path write 11 0 0 4 0.0
buffer busy waits 5 0 0 2 0.0
rdbms ipc reply 5 0 0 0 0.0
LGWR wait for redo copy 7 0 0 0 0.0
rdbms ipc message 13,124 11,396 35,897 2735 16.9
smon timer 22 20 6,074 ###### 0.0init.ora Parameters for DB: ORA9I Instance: ora9i Snaps: 1 -2 End value
Parameter Name Begin value (if different)
----------------------------- --------------------------------- --------------
aq_tm_processes 1
background_dump_dest /home/oracle/OraHome1/admin/ora9i
compatible 9.2.0.0.0
control_files /oradata/ora9i/control01.ctl, /or
core_dump_dest /home/oracle/OraHome1/admin/ora9i
db_block_size 8192
db_cache_size 1593835520
db_domain
db_file_multiblock_read_count 16
db_name ora9i
dispatchers (PROTOCOL=TCP) (SERVICE=ora9iXDB)
fast_start_mttr_target 300
hash_join_enabled TRUE
instance_name ora9i
java_pool_size 117440512
job_queue_processes 10
large_pool_size 218103808
open_cursors 300
pga_aggregate_target 50331648
processes 150
query_rewrite_enabled FALSE
remote_login_passwordfile EXCLUSIVE
sga_max_size 5254388712
shared_pool_size 1056964608
sort_area_size 524288
star_transformation_enabled FALSE
timed_statistics TRUE
undo_management AUTO
undo_retention 10800
undo_tablespace UNDOTBS1
user_dump_dest /home/oracle/OraHome1/admin/ora9i
-------------------------------------------------------------End of Report
解决方案 »
- 触发器中取sequence的值
- sql中,怎样去掉记录中的回车键
- **********一个有关调整的问题************
- OCP考试通过散分?
- 学学oracle ,java。 准备配台电脑,主机预算4300以下,麻烦各位推荐一下配置,谢谢
- 请教最为优化的查询语句。
- set echo on feedback on pagesize 999是什么意思呀?
- 将数据库中一个用户下的所有东东复制到新用户
- 在服务器端能以sys/change_on_install 以sysdba 身份联接,但在客户端却不能这样连接,而只能以normal 身份连接,为何?如何改
- max(to_number())无效数字
- 如何讲字段名作为参数穿给包?
- SQL数据库 恢复数据?
~~~~~~~~~~~~~~~~~~ % Total
Event Waits Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
CPU time 319 47.49
direct path write 14,523 185 27.61
db file parallel write 1,374 79 11.74
control file parallel write 2,028 32 4.80
log file parallel write 2,043 20 2.97
1.抓一下高cpu的语句有哪些。看看都跑的是些什么东西。
2.检查辖是不是有exp之类的挂起。
3。对比下原服务器cpu个数与现在的。
4。最好说明下使用何种方式迁移的数据库。
现在使用小型机配置如下:P6-550 4路4.2 GHz*8 /16GB内存/ 2块300 GB 1.5K SAS磁盘。小型机的系统、数据库都是由硬件提供商安装的,我们从老服务器中exp的数据库,然后在小型机数据库imp的
log buffer大一些。
有没有top sql,拿出来看看。顺便问一下,此时数据库在做什么。
show parameter sga;
End Snap: 2 14-Oct-10 16:45:54 11 4.3
Elapsed: 104.28 (mins)
statcpack, 搞这么久? 20分钟就好了。
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 50331648SQL> show parameter sga;NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 5254388712*******************************************************************************
# lsdev -Cc disk
hdisk0 Available 00-08-00 SAS Disk Drive
hdisk1 Available 00-08-00 SAS Disk Drive
*******************************************************************************
lsdev -Cc aio*******************************************************************************
# ssa_fw_status -a ssa0
/usr/bin/ksh: ssa_fw_status: not found.
*******************************************************************************
# sar -u 30 5 (cpu利用率)AIX ykgjj 1 6 00CC35D54C00 11/01/10System configuration: lcpu=16 mode=Capped 14:58:47 %usr %sys %wio %idle physc
14:59:17 4 1 1 94 8.00
14:59:47 5 2 1 93 7.99
15:00:17 5 2 1 92 8.00
15:00:47 4 2 0 93 8.00
15:01:17 7 3 0 90 8.00Average 5 2 1 92 8.00
*******************************************************************************
# sar -v 30 5 (进程、I节点、文件和锁表状态)AIX ykgjj 1 6 00CC35D54C00 11/01/10System configuration: mode=Capped 15:04:08 proc-sz inod-sz file-sz thrd-sz
15:04:38 596/262144 0/212 1824/31828 982/524288
15:05:08 600/262144 0/212 1845/31828 988/524288
15:05:38 620/262144 0/212 1944/31828 999/524288
15:06:08 601/262144 0/212 1821/31828 981/524288
15:06:38 595/262144 0/212 1825/31921 976/524288*******************************************************************************
# sar -d 30 5 (硬盘使用情况)AIX ykgjj 1 6 00CC35D54C00 11/01/10System configuration: lcpu=16 drives=3 mode=Capped 15:08:08 device %busy avque r+w/s Kbs/s avwait avserv15:08:38 hdisk0 1 0.0 3 21 0.0 7.3
hdisk1 1 0.0 3 19 0.0 6.3
cd0 0 0.0 0 0 0.0 0.0
15:09:08 hdisk0 1 0.0 3 20 0.5 12.0
hdisk1 1 0.0 2 18 0.5 11.5
cd0 0 0.0 0 0 0.0 0.0
15:09:38 hdisk0 3 0.0 5 34 0.0 6.2
hdisk1 2 0.0 5 31 0.0 6.0
cd0 0 0.0 0 0 0.0 0.0
15:10:08 hdisk0 1 0.0 4 61 0.0 5.7
hdisk1 1 0.0 2 38 0.0 7.0
cd0 0 0.0 0 0 0.0 0.0
15:10:38 hdisk0 0 0.0 1 13 0.0 6.2
hdisk1 0 0.0 1 11 0.0 5.3
cd0 0 0.0 0 0 0.0 0.0
Average hdisk0 1 0.0 3 29 0.1 7.5
hdisk1 1 0.0 2 23 0.1 7.2
cd0 0 0.0 0 0 0.0 0.0# vmstat 5 5 System configuration: lcpu=16 mem=15680MBkthr memory page faults cpu
----- ----------- ------------------------ ------------ -----------
r b avm fre re pi po fr sr cy in sy cs us sy id wa
1 0 1258029 2530174 0 0 0 0 0 0 1012 9766 2733 1 1 98 0
1 0 1258030 2530173 0 0 0 0 0 0 1058 9287 2779 1 1 98 0
1 0 1258018 2530185 0 0 0 0 0 0 1349 11072 3120 1 1 98 0
1 0 1258018 2530185 0 0 0 0 0 0 1274 10099 2893 1 1 98 0
1 0 1258018 2530185 0 0 0 0 0 0 1141 9113 2736 1 1 98 0
*******************************************************************************
# iostat 5 5System configuration: lcpu=16 drives=3 paths=2 vdisks=0tty: tin tout avg-cpu: % user % sys % idle % iowait
0.0 11.7 5.4 0.4 94.1 0.1Disks: % tm_act Kbps tps Kb_read Kb_wrtn
hdisk0 4.4 27.8 5.2 32 108
hdisk1 4.0 21.4 4.4 0 108
cd0 0.0 0.0 0.0 0 0tty: tin tout avg-cpu: % user % sys % idle % iowait
0.0 78.6 0.2 0.6 99.1 0.0Disks: % tm_act Kbps tps Kb_read Kb_wrtn
hdisk0 0.2 4.8 0.6 0 24
hdisk1 0.2 4.8 0.6 0 24
cd0 0.0 0.0 0.0 0 0tty: tin tout avg-cpu: % user % sys % idle % iowait
0.0 79.4 0.3 0.6 98.9 0.1Disks: % tm_act Kbps tps Kb_read Kb_wrtn
hdisk0 0.2 24.2 3.0 16 104
hdisk1 0.2 21.0 2.2 0 104
cd0 0.0 0.0 0.0 0 0tty: tin tout avg-cpu: % user % sys % idle % iowait
0.0 78.6 0.2 0.4 99.2 0.1Disks: % tm_act Kbps tps Kb_read Kb_wrtn
hdisk0 2.0 12.8 2.0 4 60
hdisk1 2.0 12.0 1.8 0 60
cd0 0.0 0.0 0.0 0 0tty: tin tout avg-cpu: % user % sys % idle % iowait
0.0 78.6 0.7 0.7 98.6 0.0Disks: % tm_act Kbps tps Kb_read Kb_wrtn
hdisk0 0.0 4.8 0.6 0 24
hdisk1 0.0 4.8 0.6 0 24
cd0 0.0 0.0 0.0 0 0
*******************************************************************************
1、查下应用,在做什么
2、看看磁盘做的是raid几?磁盘性能如何?