|
|
最初由 baikejia 发布
[B]修改了db_file_multi_block_count=16
新的report [/B]
Snap Id Snap Time Sessions
------- ------------------ --------
Begin Snap: 52 11-Jan-05 11:12:29 991
End Snap: 53 11-Jan-05 12:20:10 991
Elapsed: 67.68 (mins)
Cache Sizes
~~~~~~~~~~~
db_block_buffers: 107668 log_buffer: 163840
db_block_size: 8192 shared_pool_size: 439765555
Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 16,767.11 4,202.38
Logical reads: 27,353.64 6,855.71
Block changes: 102.38 25.66
Physical reads: 3,666.67 918.99
Physical writes: 11.27 2.82
User calls: 264.06 66.18
Parses: 48.22 12.09
Hard parses: 7.17 1.80
Sorts: 3.63 0.91
Logons: 0.39 0.10
Executes: 159.90 40.08
Transactions: 3.99
% Blocks changed per Read: 0.37 Recursive Call %: 63.12
Rollback per transaction %: 28.88 Rows per Sort: #######
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 99.66 Redo NoWait %: 99.99
Buffer Hit %: 86.60 In-memory Sort %: 99.94
Library Hit %: 95.26 Soft Parse %: 85.14
Execute to Parse %: 69.85 Latch Hit %: 99.86
Parse CPU to Parse Elapsd %: 8.43 % Non-Parse CPU: 98.53
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 73.90 73.05
% SQL with executions>1: 61.86 64.08
% Memory for SQL w/exec>1: 59.56 60.39
Top 5 Wait Events
~~~~~~~~~~~~~~~~~ Wait % Total
Event Waits Time (cs) Wt Time
-------------------------------------------- ------------ ------------ -------
db file sequential read 1,598,924 2,339,669 33.15
enqueue 5,168 1,557,897 22.07
db file scattered read 1,172,484 1,361,297 19.29
buffer busy waits 374,124 580,520 8.22
log file sync 17,811 471,563 6.68
改了之后,还是偏大,就是说与这个参数影响不太.
你有6G的内存,应该是32bit的oracle,结果SGA只能
限制在1.7G以内.
现在要做的就是降低shared_pool_size到144m,然后增加
db_block_buffers=200000blocks,然后就是将共享游标参
数修改cursor_sharing=foce.来降低CPU的使用.
其它的只能是优化SQL.
Physical Reads Executions Reads per Exec % Total Hash Value
--------------- ------------ -------------- ------- ------------
963,318 126 7,645.4 6.5 1820587167
SELECT "SITE_ID" FROM "D_ASK_LIST" WHERE ("ASK_ID" IS NULL)
799,031 110 7,263.9 5.4 3920706031
SELECT "STAFF_ID" FROM "D_ASK_LIST" WHERE ("ASK_ID" IS NULL)
714,579 5 142,915.8 4.8 3207605478
SELECT "ASK_ID","OP_TYPE","BRANCH_NO" FROM "D_ADSL_ASK" "E" WHER
E "OP_TYPE"='1'
209,355 3 69,785.0 1.4 1342134103
SELECT "NAME" FROM "D_NUM_PUBLIC_ASK" WHERE ("DISTRICT_NO" = :"W
PH1" AND "ASK_ID" IS NULL)
181,958 12 15,163.2 1.2 2500482218
SELECT /*+ FIRST_ROWS */ "ASK_ID", "BUSI_DETAIL_ID", "ASK_DATE"
, "DISTRICT_NO", "NUM_ID", "SITE_ID", "STAFF_ID", "COMPLETION",
"ARCHIVE_DATE", "BEFOR_ID", "AFTER_ID", "ASK_GRADE", "ALL_ID", "
NOTES", "PREASK_FLAG", "CHECK_FLAG", "COMPLETE_DATE", "SELF_COMP
_DATE", "INSTALL_BRANCH" FROM "D_ASK_LIST" WHERE (("ASK_DATE" >=
116,843 11 10,622.1 0.8 2112985494
SELECT /*+ FIRST_ROWS */ "ASK_ID", "BUSI_DETAIL_ID", "ASK_DATE"
, "DISTRICT_NO", "NUM_ID", "SITE_ID", "STAFF_ID", "COMPLETION",
"ARCHIVE_DATE", "BEFOR_ID", "AFTER_ID", "ASK_GRADE", "ALL_ID", "
NOTES", "PREASK_FLAG", "CHECK_FLAG", "COMPLETE_DATE", "SELF_COMP
_DATE", "INSTALL_BRANCH" FROM "D_ASK_LIST" WHERE (("ARCHIVE_DATE
91,417 1 91,417.0 0.6 1354302601
select count(*) from d_sheet_his where dept_type like 'K7' and s
heet_statu in ('1','2','4','5','8','9','a') and branch_no like '
130000' and build_no like '%' and line_test_no like '%' and sen
d_date>=to_date('2005.01.11 00:00:00','yyyy.mm.dd hh24:mi:ss') a
nd send_date<=to_date('2005.01.11 23:59:59','yyyy.mm.dd hh24:mi:
91,163 1 91,163.0 0.6 91298319
select count(*) from d_sheet_his where dept_type like 'K7' and s
heet_statu in ('1','2','4','5','8','9','a') and branch_no like '
H08000' and build_no like 'H08100' and line_test_no like '%' and
send_date>=to_date('2004.11.21 00:00:00','yyyy.mm.dd hh24:mi:s
s') and send_date<=to_date('2004.12.20 23:59:59','yyyy.mm.dd hh2
90,810 7 12,972.9 0.6 3249724588
SELECT /*+ FIRST_ROWS */ "ASK_ID", "BUSI_DETAIL_ID", "ASK_DATE"
, "DISTRICT_NO", "NUM_ID", "SITE_ID", "STAFF_ID", "COMPLETION",
"ARCHIVE_DATE", "BEFOR_ID", "AFTER_ID", "ASK_GRADE", "ALL_ID", "
NOTES", "PREASK_FLAG", "CHECK_FLAG", "COMPLETE_DATE", "SELF_COMP
_DATE", "INSTALL_BRANCH" FROM "D_ASK_LIST" WHERE ("ASK_ID" NOT L
90,439 1 90,439.0 0.6 3374848123
SELECT /*+ FIRST_ROWS */ "SHEET_NO", "CIRCUIT_NO", "DEPT_TYPE",
"ASK_ID", "PROJECT_NO", "SHEET_TYPE", "SHEET_STATU", "SEND_TYPE
", "INSTALL_FLAG", "DISTRICT_NO", "BRANCH_NO", "BUILD_NO", "LINE
_TEST_NO", "BUSI_DETAIL_ID", "USER_NUM", "NUM_TYPE_ID", "CREAT_S
HEET_DATE", "SHEET_SITE_ID", "SHEET_STAFF_ID", "SEND_STAFF_ID",
SQL ordered by Reads for DB: SJ97 Instance: sj97 Snaps: 52 -53
-> End Disk Reads Threshold: 1000
Physical Reads Executions Reads per Exec % Total Hash Value
--------------- ------------ -------------- ------- ------------
87,827 1 87,827.0 0.6 404771571
select count(*) from d_sheet_his where dept_type like 'K7' and s
heet_statu in ('6','7','b') and branch_no like 'H08000' and bui
ld_no like 'H08100' and line_test_no like '%' and send_date>=to_
date('2004.11.21 00:00:00','yyyy.mm.dd hh24:mi:ss') and send_dat
e<=to_date('2004.12.20 23:59:59','yyyy.mm.dd hh24:mi:ss')
87,058 1 87,058.0 0.6 3562340104
select count(*) from d_sheet_his where dept_type like 'K7' and s
heet_statu in ('6','7','b') and branch_no like '130000' and bui
ld_no like '%' and line_test_no like '%' and send_date>=to_date(
'2005.01.11 00:00:00','yyyy.mm.dd hh24:mi:ss') and send_date<=to
_date('2005.01.11 23:59:59','yyyy.mm.dd hh24:mi:ss')
你的这些SQL都不是好对付的,每个物理读都特大。
963,318 126 7,645.4 6.5 1820587167
SELECT "SITE_ID" FROM "D_ASK_LIST" WHERE ("ASK_ID" IS NULL)
799,031 110 7,263.9 5.4 3920706031
SELECT "STAFF_ID" FROM "D_ASK_LIST" WHERE ("ASK_ID" IS NULL)
这个地方明显很有问题.is null是不走索引的.
这个地方只要稍稍处理一下就可以达到相当好的效果. |
|
楼主: baikejia
IP卡
狗仔卡
楼主
显身卡
发表于 2005-1-10 19:30
发表于 2005-1-10 21:14
