Help - Search - Members - Calendar
Full Version: Tuning insert statement
Oracle DBA Forums > Oracle > Oracle Forum
karthickr
Hi,
Is there any way to tune Insert statement .(I dont want to use Parallel insert) . An insert statement
that took 30 min before is taking 3hrs now after index rebuild .Optimizer mode is Rule .Any idea??
dave
yet you post no query, no execution plan - no nothing
karthickr
Hi,
Is there any way to tune Insert statement .(I dont want to use Parallel insert) . An insert statement
that took 30 min before is taking 3hrs now after index rebuild .Optimizer mode is Rule .Any idea??


INSERT INTO ACC_TEMP( SELECT A.BRANCH_CODE, A.ACCOUNT, A.BKG_DATE,
A.ACC_CCY, LCY_CLOSING_BAL, ACY_CLOSING_BAL FROM AC_HISTORY A,
(SELECT BRANCH_CODE, ACCOUNT, ACC_CCY, MAX(BKG_DATE) BKG_DATE FROM
AC_HISTORY WHERE BRANCH_CODE=:B1 GROUP BY BRANCH_CODE, ACCOUNT,
ACC_CCY) B WHERE A.BRANCH_CODE = B.BRANCH_CODE AND A.ACCOUNT = B.ACCOUNT
AND A.ACC_CCY = B.ACC_CCY AND A.BKG_DATE = B.BKG_DATE AND A.BRANCH_CODE =
:B1 )

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 84 0.00 0.00 0 0 0 0
Execute 84 5331.03 5577.89 18049 561127565 1912 13536
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 168 5331.03 5577.89 18049 561127565 1912 13536

Misses in library cache during parse: 1
Optimizer goal: RULE
Parsing user id: 23 (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
175 FILTER
2198 SORT GROUP BY
219256 TABLE ACCESS BY INDEX ROWID AC_HISTORY
221455 NESTED LOOPS
2198 TABLE ACCESS BY INDEX ROWID AC_HISTORY
2198 INDEX RANGE SCAN IND_ACCBAL2 (object id 24224)
219256 INDEX RANGE SCAN IND_ACCBAL2 (object id 24224)


Index details

AC_HISTORY ;IND_ACCBAL1 ;BRANCH_CODE ; 1
AC_HISTORY ;IND_ACCBAL1 ;ACCOUNT ; 2
AC_HISTORY ;IND_ACCBAL1 ;ACC_CCY ; 3
AC_HISTORY ;IND_ACCBAL1 ;BKG_DATE ; 4

AC_HISTORY ;IND_ACCBAL2 ;BRANCH_CODE ; 1
AC_HISTORY ;IND_ACCBAL2 ;ACCOUNT ; 2
AC_HISTORY ;IND_ACCBAL2 ;ACC_CCY ; 3

AC_HISTORY ;PK_ACBALHIST ;BRANCH_CODE ; 1
AC_HISTORY ;PK_ACBALHIST ;ACCOUNT ; 2
AC_HISTORY ;PK_ACBALHIST ;BKG_DATE ; 3
AC_HISTORY ;PK_ACBALHIST ;ACC_CCY ; 4
burleson
>> Is there any way to tune Insert statement

Sure! Here are my notes on tuning inserts:

http://www.dba-oracle.com/t_insert_tuning.htm

http://www.dba-oracle.com/t_optimize_inser...performance.htm
burleson
>> An insert statement that took 30 min before is taking 3hrs now after index rebuild

Go back and grab the earlier execution plan from STATSPACK or AWR (dba_hist_sqlstat & dba_hist_sqlplan), and then duplicate it with the RBO.

http://www.dba-oracle.com/oracle10g_tuning...ist_sqlstat.htm
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.
Invision Power Board © 2001-2014 Invision Power Services, Inc.