| Oracle server Tuning [message #465317] |
Tue, 13 July 2010 10:18  |
prax_14
Messages: 64
Registered: July 2008
|
Member |
|
|
Deal All,
I have a procedure which mainly run queries on a Table which has nearly 9.5 million recodes. This procedures takes nearly 15 min to complete execution on our main database. I exported and imported the schema to our backup database and the same procedure just took 3 seconds to complete.
I tried to analyze the table in our main database and tried to execute the procedure again but did not show any improvements. ANALYZE TABLE DN_ACTIONS COMPUTE STATISTICS;
I am not sure computing the statistics for all the tables in the schema will help please advice. I also checked there is enough
disk space where oracle data files are stored.
I am also turning on the sql trace to see what sql statements in the procedure is taking longer time.
Please advice what are the other areas i will need to investigate to resolve this issue.
Thanks in advance.
|
|
|
|
|
|
| Re: Oracle server Tuning [message #465320 is a reply to message #465317] |
Tue, 13 July 2010 10:26  |
cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
1) You shouldn't use analyze table in 11g (or 10g), you should use dbms_stats instead.
2) Trace is the best place to start, work out which sql statements take longest and then look at them in detail. If you post the trace and explain plans for the longest sqls then we can comment.
|
|
|
|
| Re: Oracle server Tuning [message #465363 is a reply to message #465320] |
Tue, 13 July 2010 14:28 |
prax_14
Messages: 64
Registered: July 2008
|
Member |
|
|
Dear Friends I really appreciate your help.
I did use the dbms_stats and now the procedure got execute 3 min 20 sec.
exec DBMS_STATS.GATHER_SCHEMA_STATS('diva');
I also managed to get sqltrace while executing after gathering statistics. I formated with the TkProf. Here is the statement's which took long time to execute. These insert statements are selecting records from table DN_ACTION which has 9.3 million records.
********************************************************************************
INSERT INTO DN_ACTION_DETAILS (DD_ID) SELECT DA_ID FROM ( SELECT DA_ID FROM
DN_ACTIONS, DN_SLOT S WHERE DA_SLOT_ID = S.ID AND DA_ACTION_STATUS = :B4
AND DA_ACTION_TYPE = :B3 AND DA_START_DATE <= :B2 AND DA_EXPIRATION_DATE >=
:B2 AND ( ( CREATEBEGINTIME <= :B1 AND (CREATEENDTIME > :B1 OR
CREATEENDTIME = -1) ) OR ( (CREATEBEGINTIME <= :B1 OR CREATEENDTIME > :B1 )
AND CREATEBEGINTIME > CREATEENDTIME ) ) AND ENABLED = 'Y' ORDER BY
CREATEPRIORITY DESC, DA_SEC_PRIORITY ASC ) WHERE ROWNUM <= :B5
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 3.07 102.29 162204 163197 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 3.07 102.29 162204 163197 0 0
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 23 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
0 COUNT STOPKEY
0 VIEW
0 SORT ORDER BY STOPKEY
0 HASH JOIN
315 TABLE ACCESS FULL DN_SLOT
0 TABLE ACCESS FULL DN_ACTIONS
********************************************************************************
********************************************************************************
INSERT INTO DN_ACTION_DETAILS (DD_ID) SELECT DA_ID FROM ( SELECT DA_ID FROM
DN_ACTIONS, DN_SLOT S WHERE DA_SLOT_ID = S.ID AND DA_ACTION_STATUS = :B4
AND DA_ACTION_TYPE <> :B3 AND DA_START_DATE <= :B2 AND DA_EXPIRATION_DATE >=
:B2 AND ( ( (CREATEBEGINTIME <= :B1 ) AND (CREATEENDTIME > :B1 OR
CREATEENDTIME = -1) ) OR ( (CREATEBEGINTIME <= :B1 OR CREATEENDTIME > :B1 )
AND (CREATEBEGINTIME > CREATEENDTIME) ) ) AND ENABLED = 'Y' ORDER BY
CREATEPRIORITY DESC, DA_SEC_PRIORITY ASC, DA_ACTION_TYPE DESC ) WHERE
ROWNUM <= :B5
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 1 3.17 90.95 162246 163098 6 25
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 3.18 90.95 162246 163098 6 25
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 23 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
25 COUNT STOPKEY
25 VIEW
25 SORT ORDER BY STOPKEY
19574 HASH JOIN
315 TABLE ACCESS FULL DN_SLOT
19574 TABLE ACCESS FULL DN_ACTIONS
********************************************************************************
The same queries on the backup database on the same table DN_ACTIONS having 9.3 million is not doing full table scan and is very quick, Please advice.
********************************************************************************
SQL ID : 3s4g3mahnyht4
INSERT INTO DN_ACTION_DETAILS (DD_ID) SELECT DA_ID FROM ( SELECT DA_ID FROM
DN_ACTIONS, DN_SLOT S WHERE DA_SLOT_ID = S.ID AND DA_ACTION_STATUS = :B4
AND DA_ACTION_TYPE = :B3 AND DA_START_DATE <= :B2 AND DA_EXPIRATION_DATE >=
:B2 AND ( ( CREATEBEGINTIME <= :B1 AND (CREATEENDTIME > :B1 OR
CREATEENDTIME = -1) ) OR ( (CREATEBEGINTIME <= :B1 OR CREATEENDTIME > :B1 )
AND CREATEBEGINTIME > CREATEENDTIME ) ) AND ENABLED = 'Y' ORDER BY
CREATEPRIORITY DESC, DA_SEC_PRIORITY ASC ) WHERE ROWNUM <= :B5
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.01 0.03 3 3 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.01 0.03 3 3 0 0
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 542 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
0 LOAD TABLE CONVENTIONAL (cr=3 pr=3 pw=3 time=0 us)
0 COUNT STOPKEY (cr=3 pr=3 pw=3 time=0 us)
0 VIEW (cr=3 pr=3 pw=3 time=0 us cost=6 size=13 card=1)
0 SORT ORDER BY STOPKEY (cr=3 pr=3 pw=3 time=0 us cost=6 size=59 card=1)
0 NESTED LOOPS (cr=3 pr=3 pw=3 time=0 us)
0 NESTED LOOPS (cr=3 pr=3 pw=3 time=0 us cost=5 size=59 card=1)
0 TABLE ACCESS BY INDEX ROWID DN_ACTIONS (cr=3 pr=3 pw=3 time=0 us cost=4 size=44 card=1)
0 INDEX RANGE SCAN DN_TYP_STA_STP (cr=3 pr=3 pw=3 time=0 us cost=3 size=0 card=1)(object id 394396)
0 INDEX UNIQUE SCAN PK_DN_SLOT (cr=0 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 394435)
0 TABLE ACCESS BY INDEX ROWID DN_SLOT (cr=0 pr=0 pw=0 time=0 us cost=1 size=15 card=1)
********************************************************************************
********************************************************************************
SQL ID : 8vftjhh4a172r
INSERT INTO DN_ACTION_DETAILS (DD_ID) SELECT DA_ID FROM ( SELECT DA_ID FROM
DN_ACTIONS, DN_SLOT S WHERE DA_SLOT_ID = S.ID AND DA_ACTION_STATUS = :B4
AND DA_ACTION_TYPE <> :B3 AND DA_START_DATE <= :B2 AND DA_EXPIRATION_DATE >=
:B2 AND ( ( (CREATEBEGINTIME <= :B1 ) AND (CREATEENDTIME > :B1 OR
CREATEENDTIME = -1) ) OR ( (CREATEBEGINTIME <= :B1 OR CREATEENDTIME > :B1 )
AND (CREATEBEGINTIME > CREATEENDTIME) ) ) AND ENABLED = 'Y' ORDER BY
CREATEPRIORITY DESC, DA_SEC_PRIORITY ASC, DA_ACTION_TYPE DESC ) WHERE
ROWNUM <= :B5
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.17 1.49 2355 2355 5 25
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.17 1.49 2355 2355 5 25
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 542 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
0 LOAD TABLE CONVENTIONAL (cr=2355 pr=2355 pw=2355 time=0 us)
25 COUNT STOPKEY (cr=2354 pr=2352 pw=2352 time=2 us)
25 VIEW (cr=2354 pr=2352 pw=2352 time=1 us cost=776 size=193232 card=14864)
25 SORT ORDER BY STOPKEY (cr=2354 pr=2352 pw=2352 time=0 us cost=776 size=876976 card=14864)
21532 HASH JOIN (cr=2354 pr=2352 pw=2352 time=3523 us cost=562 size=876976 card=14864)
315 TABLE ACCESS FULL DN_SLOT (cr=18 pr=16 pw=16 time=1 us cost=9 size=4725 card=315)
21532 TABLE ACCESS BY INDEX ROWID DN_ACTIONS (cr=2336 pr=2336 pw=2336 time=3274 us cost=552 size=655028 card=14887)
26975 INDEX RANGE SCAN DN_ACTI_STATUS_IDX (cr=55 pr=55 pw=55 time=441 us cost=51 size=0 card=24719)(object id 394392)
********************************************************************************
|
|
|
|
| Re: Oracle server Tuning [message #465367 is a reply to message #465317] |
Tue, 13 July 2010 16:44 |
prax_14
Messages: 64
Registered: July 2008
|
Member |
|
|
Friends,
Can any one please help me understand why the Insert statements is doing a Full table scan in Main database where as it using the indexes in backup database. The Explain plan of the Insert statements on both database is provide in my previous post on this thread.
Thanks In advance.
|
|
|
|
|
|
|
|
| Re: Oracle server Tuning [message #466472 is a reply to message #465317] |
Mon, 19 July 2010 17:21 |
prax_14
Messages: 64
Registered: July 2008
|
Member |
|
|
@Mahesh:
Sorry about the late reply, I lost the window to connect to customer machine, They reopened the VPN connection today and I tried executing DBMS_STATS as you had mentioned but there was no improvement.
Optimizer Parameter from Main database
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean FALSE
optimizer_dynamic_sampling integer 2
optimizer_features_enable string 11.1.0.6
optimizer_index_caching integer 0
optimizer_index_cost_adj integer 100
optimizer_mode string ALL_ROWS
optimizer_secure_view_merging boolean TRUE
optimizer_use_invisible_indexes boolean FALSE
optimizer_use_pending_statistics boolean FALSE
optimizer_use_sql_plan_baselines boolean TRUE
SQL>
Optimizer parameters from backup database
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean FALSE
optimizer_dynamic_sampling integer 2
optimizer_features_enable string 11.1.0.6
optimizer_index_caching integer 0
optimizer_index_cost_adj integer 100
optimizer_mode string ALL_ROWS
optimizer_secure_view_merging boolean TRUE
optimizer_use_invisible_indexes boolean FALSE
optimizer_use_pending_statistics boolean FALSE
optimizer_use_sql_plan_baselines boolean TRUE
@Kevin:
How to check if Hash join is enabled or not?
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
