Oracle数据库数据恢复、性能优化»论坛 › Oracle › Oracle数据库管理 › 求助 insert 慢

26 积分	0 好友	1 主题

发消息

求助 insert 慢

1^#

发表于 2011-10-21 20:51:26 | 查看: 7629| 回复: 9

10.2.0.3

已作100046

8924-2.rar (2.1 KB, 下载次数: 1183)

执行计划如下

未命名.jpg

SELECT fissue,
FM_ID,
SSQ.P_CAL_issue (lot_class, fissue, FM_ID) AS ori_val,
-- "公式值",
f_mod,
f_zero,
f_plus,
FML_TYPE,
LOT_CLASS
FROM SSQ.T_BASIC_n, SSQ.T_F_M
WHERE lot_class = fml_class AND fml_class = 'SSQ'

复制代码

[ 本帖最后由 flyfish576 于 2011-10-21 21:36 编辑 ]

分享0

收藏0 回复只看该作者道具举报

162743

2^#

发表于 2011-10-21 20:59:21

请群主解决！！！！！！！！

回复只看该作者道具举报

Maclean Liu(刘相兵

3^#

发表于 2011-10-21 21:05:22

lz,

请把SQL语句列出来，不要直接放附件里，执行计划请列出用code代码形式。

insert 和select的分别做一个10046

回复只看该作者道具举报

Maclean Liu(刘相兵

4^#

发表于 2011-10-21 21:30:42

代码

SELECT fissue,
FM_ID,
SSQ.P_CAL_issue(lot_class, fissue, FM_ID) AS ori_val,
-- "公式值",
f_mod,
f_zero,
f_plus,
FML_TYPE,
LOT_CLASS
FROM SSQ.T_BASIC_n, SSQ.T_F_M
WHERE lot_class = fml_class
and fml_class = 'SSQ'

复制代码

回复只看该作者道具举报

Maclean Liu(刘相兵

5^#

发表于 2011-10-21 21:34:20

action plan:

alter system flush shared_pool;
alter system flush buffer_cache;
alter session set events '10046 trace name context forever,level 8';
RUN SELECT statement
RUN INSERT statement
alter session set events '10046 trace name context off';
SELECT d.VALUE
|| '/'
|| LOWER (RTRIM (i.INSTANCE, CHR (0)))
|| '_ora_'
|| p.spid
|| '.trc' trace_file_name
FROM (SELECT p.spid
FROM v$mystat m, v$session s, v$process p
WHERE m.statistic# = 1 AND s.SID = m.SID AND p.addr = s.paddr) p,
(SELECT t.INSTANCE
FROM v$thread t, v$parameter v
WHERE v.NAME = 'thread'
AND (v.VALUE = 0 OR t.thread# = TO_NUMBER (v.VALUE))) i,
(SELECT VALUE
FROM v$parameter
WHERE NAME = 'user_dump_dest') d;
Upload the raw trace file ,remember the raw trace file!!!

复制代码

回复只看该作者道具举报

flyfish576

6^#

发表于 2011-10-21 21:38:04

回复 5# 的帖子

支持 maclean

看了看没的优化

主要时间耗在正则替换部分了

SELECT FISSUE
FROM
( SELECT ROWNUM RN,FISSUE FROM T_BASIC WHERE LOT_CLASS =:B2 AND FISSUE<:B1
ORDER BY FISSUE DESC) WHERE RN=:B3
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 6160 2.12 2.30 0 0 0 0
Fetch 6160 1.82 2.02 0 24640 0 6160
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 12321 3.95 4.33 0 24640 0 6160
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 77 (recursive depth: 1)
********************************************************************************
SELECT REGEXP_SUBSTR(:B2 ,'[[:punct:]]?'||UPPER (:B1 )||'[[:punct:]]?')
FROM
DUAL
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 6160 1.46 1.64 0 0 0 0
Fetch 6160 0.59 0.40 0 0 0 6160
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 12321 2.06 2.04 0 0 0 6160
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 77 (recursive depth: 1)
********************************************************************************
SELECT REPLACE ( :B2 , :B1 , ( SELECT REPLACE( :B1 , :B3 , FVALUE )
FROM
T_BASIC_S WHERE LOT_CLASS=:B6 AND FISSUE=:B5 AND FID=:B4 ) ) FROM DUAL
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 6160 4.73 4.64 0 0 0 0
Fetch 6160 0.39 0.49 0 18480 0 6160
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 12321 5.14 5.13 0 18480 0 6160
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 77 (recursive depth: 1)
********************************************************************************