crm系统快慢死了

显示全部楼层 · 2014-5-9 08:24:37

crm系统由于sql等问题，快慢死了。要是找个临时dba给有优化一下靠谱吗？
oracle9ir2
windows2000server

千问 · 2014-5-9 08:24:37

自己动手,查询buffer/exec高的sql,也许很简单就是建立索引.
SELECT DISTINCT
vs.sql_text, vs.sharable_mem, vs.persistent_mem, vs.runtime_mem, vs.sorts, vs.executions,
vs.parse_calls, vs.module, vs.action, vs.buffer_gets, vs.disk_reads, vs.version_count,
vs.loaded_versions, vs.open_versions, vs.users_opening, vs.loads, vs.users_executing,
vs.invalidations, vs.serializable_aborts, vs.command_type,
to_char(to_date(vs.first_load_time,'YYYY-MM-DD/HH24:MI:SS'),'MM/DDHH24:MI:SS') first_load_time,
rawtohex(vs.address) address, vs.hash_value hash_value, vs.parsing_user_id
,rows_processed
,optimizer_mode
,vs.is_obsolete, vs.elapsed_time, vs.cpu_time
,vs.Child_latch, vs.fetches
FROM v$sqlarea vs

修改一下,查询buffer get/exec很高的sql语句.

千问 · 2014-5-9 08:24:37

good

千问 · 2014-5-9 08:24:37

看等待。

千问 · 2014-5-9 08:24:37

感谢...解决中.

千问 · 2014-5-9 08:24:37

学习了

千问 · 2014-5-9 08:24:37

又學一招

千问 · 2014-5-9 08:24:37

我用这个查完后，有1000多条数据....我是要优化每一条吗？
SQL_TEXT
SHARABLE_MEM
PERSISTENT_MEM
RUNTIME_MEM
SORTS
EXECUTIONS
PARSE_CALLS
MODULE
ACTION
BUFFER_GETS
DISK_READS
VERSION_COUNT
LOADED_VERSIONS
OPEN_VERSIONS
USERS_OPENING
LOADS
USERS_EXECUTING
INVALIDATIONS
SERIALIZABLE_ABORTS
COMMAND_TYPE
FIRST_LOAD_TIME
ADDRESS
HASH_VALUE
PARSING_USER_ID
ROWS_PROCESSED
OPTIMIZER_MODE
IS_OBSOLETE
ELAPSED_TIME
CPU_TIME
CHILD_LATCH
FETCHES
以上各列都是什么意思呀？
最初由 lfree 发布
[B]自己动手,查询buffer/exec高的sql,也许很简单就是建立索引.
SELECT DISTINCT
vs.sql_text, vs.sharable_mem, vs.persistent_mem, vs.runtime_mem, vs.sorts, vs.executions,
vs.parse_calls, vs.module, vs.action, vs.buffer_gets, vs.disk_reads, vs.version_count,
vs.loaded_versions, vs.open_versions, vs.users_opening, vs.loads, vs.users_executing,
vs.invalidations, vs.serializable_aborts, vs.command_type,
to_char(to_date(vs.first_load_time,'YYYY-MM-DD/HH24:MI:SS'),'MM/DDHH24:MI:SS') first_load_time,
rawtohex(vs.address) address, vs.hash_value hash_value, vs.parsing_user_id
,rows_processed
,optimizer_mode
,vs.is_obsolete, vs.elapsed_time, vs.cpu_time
,vs.Child_latch, vs.fetches
FROM v$sqlarea vs

修改一下,查询buffer get/exec很高的sql语句. [/B]

千问 · 2014-5-9 08:24:37

ps:我每个表都有index的! 有个sale销售表和customer顾客信息表，每次insert都很慢，也在上面的v$sqlarea中出现：

INSERT INTOSALE(BRAND_TYPE_CD,SALE_BILL_NO, SALE_DATE,CORNER_NO,CUST_NO,SALE_TYPE_CD, RETURN_INVOICE_NO,ORMAL_SALE_AMT,DC_YN,DC_CHARGER_CD,ACTUAL_SALE_AMT,LOGIN_ID,CREATE_DATE,CREATED_BY,ACTIVE)
V
19600
812
2584

0
4
4
269610
8497
1
1
0
0
3
0
0
0
2
10/10 08:22:32
190DC7FC

1864526511
62
4
CHOOSE
N
24589451
3125000
11
0

INSERT INTO SALE_PRODUCT(BRAND_TYPE_CD,SALE_BILL_NO,PRODUCT_ID,CUST_UNIT_PRICE,NORMAL_SALE_AMT,ACTUAL_SALE_AMT,SALE_QTY,SALE_AVAILABLE_QTY,SALE_UNAVAILABLE_QTY,CREATE_DATE,CREATED_BY,ACTIVE)
VALUES (:1,:2,:3,:4,:5,:6,:7,:8,:9,SYSDATE,:10,:11)
14430
748
2244
0
6
6
56
31
1
1
0

0
3
0
0
0
2
10/10 08:22:45
190DA110
2784821585
62
6
CHOOSE
N
139797
0
1
0

INSERT INTO CUSTOMER(CUST_NO,CUST_NAME,CUST_NAME_PN,CUST_ID_CARD_NO,CUST_BRAND_CD,CUST_GRADE_CD,CUST_TYPE_CD,GENDER,DOB,WEDDING_DATE,MOBILE_PHONE_NO,HOME_PHONE_NO,HOME_POSTCODE,HOME_ADDRESS,HOME_PROVINCE_CD,
40864
1428
4360
0
1
1
21

18
1
1
0
0
3
0
0
0
2
10/10 08:20:31
1811A334
34736196
62
1
CHOOSE
N

89766
0
6
0
能帮忙解释一下吗？

千问 · 2014-5-9 08:24:37

最初由 firstpenny 发布
[B]我用这个查完后，有1000多条数据....我是要优化每一条吗？
SQL_TEXT
SHARABLE_MEM
PERSISTENT_MEM
RUNTIME_MEM
SORTS
EXECUTIONS
PARSE_CALLS
MODULE
ACTION
BUFFER_GETS
DISK_READS
VERSION_COUNT
LOADED_VERSIONS
OPEN_VERSIONS
USERS_OPENING
LOADS
USERS_EXECUTING
INVALIDATIONS
SERIALIZABLE_ABORTS
COMMAND_TYPE
FIRST_LOAD_TIME
ADDRESS
HASH_VALUE
PARSING_USER_ID
ROWS_PROCESSED
OPTIMIZER_MODE
IS_OBSOLETE
ELAPSED_TIME
CPU_TIME
CHILD_LATCH
FETCHES
以上各列都是什么意思呀？
[/B]
一般靠前的需要优化.