| how to get explain plan [message #470741] |
Tue, 10 August 2010 10:51  |
prashant_ora
Messages: 196
Registered: July 2010
|
Senior Member |
|
|
This query took alots of buffer gets
UPDATE AUTOPLANALLOCATION AA SET ( REM_EFFORT, PCOMP, ACTL_START, MODIFIED_DATE ) =
(SELECT APA_TEMP.REM_EFFORT, APA_TEMP.PCOMP, APA_TEMP.ACTL_START, SYSDATE FROM AUTOPLANALLOCATION_TEMP APA_TEMP
WHERE AA.TASK_ID = APA_TEMP.TASK_ID AND AA.RESOURCE_ID = APA_TEMP.RESOURCE_ID) WHERE (RESOURCE_ID ,TASK_ID) IN
(SELECT APA_TEMP.TASK_ID,APA_TEMP.RESOURCE_ID FROM AUTOPLANALLOCATION AA,AUTOPLANALLOCATION_TEMP APA_TEMP
WHERE AA.TASK_ID = APA_TEMP.TASK_ID AND AA.RESOURCE_ID = APA_TEMP.RESOURCE_ID AND (NVL(APA_TEMP.REM_EFFORT, 0) != NVL(AA.REM_EFFORT, 0)
OR NVL(APA_TEMP.PCOMP, 0) != NVL(AA.PCOMP, 0) OR NVL(AA.ACTL_START, SYSDATE) != NVL(APA_TEMP.ACTL_START, SYSDATE))
Please advice how can i get its explain plan , becuase what would be the sql statement which i will use inside the explain plan for
Total no of records AUTOPLANALLOCATION =1163613
This AUTOPLANALLOCATION_temp is a global temporary table
Total no of records AUTOPLANALLOCATION_temp will be not more than 2000
Index detail of AUTOPLANALLOCATION table
Sr. INDEX_NAME COLUMN_NAME COLUMN_POSITION
1 ALLOC_UID_IDX ALLOC_UID 1
2 ALLOC_UID_IDX ENTERPRISEID 2
3 ALLOC_UID_IDX PROJECT_ID 3
4 AUTOPLANALLOCATION_PROJID_IDX1 PROJECT_ID 1
5 AUTOPLANAL_IDX2 TASK_ID 1
6 IDX_AP_ALLOC_001 PROJECT_ID 1
7 IDX_AP_ALLOC_001 RESOURCE_ID 2
8 PK_AUTOPLANALLOCATION ENTERPRISEID 1
9 PK_AUTOPLANALLOCATION TASK_ID 2
10 PK_AUTOPLANALLOCATION PROJECT_ID 3
11 PK_AUTOPLANALLOCATION RESOURCE_ID 4
12 UK_AUTOPLANALLOCATION_TSK_RES TASK_ID 1
13 UK_AUTOPLANALLOCATION_TSK_RES RESOURCE_ID 2
14 XIE_AP_ALLOC_RESOID RESOURCE_ID 1
Index detail of AUTOPLANALLOCATION_TEMP table
Sr. INDEX_NAME COLUMN_NAME COLUMN_POSITION
1 HCLT_APT_TEMP TASK_ID 1
2 HCLT_APT_TEMP RESOURCE_ID 2
|
|
|
|
|
|
|
|
| Re: how to get explain plan [message #470752 is a reply to message #470746] |
Tue, 10 August 2010 11:13  |
prashant_ora
Messages: 196
Registered: July 2010
|
Senior Member |
|
|
|
actually this select has multiple join of oouter table, so how can i get the select, because select is very simple getting the data from temp table. so can any one pl help to get the select statement which i can use in explain plan.
|
|
|
|
| Re: how to get explain plan [message #470756 is a reply to message #470752] |
Tue, 10 August 2010 11:20 |
cookiemonster
Messages: 13921
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Explain will quite happily do updates:
EXPLAIN PLAN FOR UPDATE AUTOPLANALLOCATION AA SET ( REM_EFFORT, PCOMP, ACTL_START, MODIFIED_DATE ) =
(SELECT APA_TEMP.REM_EFFORT, APA_TEMP.PCOMP, APA_TEMP.ACTL_START, SYSDATE FROM AUTOPLANALLOCATION_TEMP APA_TEMP
WHERE AA.TASK_ID = APA_TEMP.TASK_ID AND AA.RESOURCE_ID = APA_TEMP.RESOURCE_ID) WHERE (RESOURCE_ID ,TASK_ID) IN
(SELECT APA_TEMP.TASK_ID,APA_TEMP.RESOURCE_ID FROM AUTOPLANALLOCATION AA,AUTOPLANALLOCATION_TEMP APA_TEMP
WHERE AA.TASK_ID = APA_TEMP.TASK_ID AND AA.RESOURCE_ID = APA_TEMP.RESOURCE_ID AND (NVL(APA_TEMP.REM_EFFORT, 0) != NVL(AA.REM_EFFORT, 0)
OR NVL(APA_TEMP.PCOMP, 0) != NVL(AA.PCOMP, 0) OR NVL(AA.ACTL_START, SYSDATE) != NVL(APA_TEMP.ACTL_START, SYSDATE));
SELECT * FROM table(dbms_xplan.display);
|
|
|
|
|
|
|
|
| Re: how to get explain plan [message #470765 is a reply to message #470756] |
Tue, 10 August 2010 11:40 |
prashant_ora
Messages: 196
Registered: July 2010
|
Senior Member |
|
|
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 1 | 272 | 7 (15)|
| 1 | UPDATE | AUTOPLANALLOCATION | | | |
|* 2 | TABLE ACCESS BY INDEX ROWID | AUTOPLANALLOCATION | 1 | 246 | 2 (0)|
| 3 | NESTED LOOPS | | 1 | 272 | 7 (15)|
| 4 | VIEW | VW_NSO_1 | 1 | 26 | 4 (0)|
| 5 | SORT UNIQUE | | 1 | 84 | |
|* 6 | TABLE ACCESS BY INDEX ROWID| AUTOPLANALLOCATION | 1 | 23 | 2 (0)|
| 7 | NESTED LOOPS | | 1 | 84 | 4 (0)|
| 8 | TABLE ACCESS FULL | AUTOPLANALLOCATION_TEMP | 1 | 61 | 2 (0)|
|* 9 | INDEX RANGE SCAN | XIE_AP_ALLOC_RESOID | 193 | | 2 (0)|
|* 10 | INDEX RANGE SCAN | XIE_AP_ALLOC_RESOID | 193 | | 2 (0)|
| 11 | TABLE ACCESS BY INDEX ROWID | AUTOPLANALLOCATION_TEMP | 1 | 61 | 1 (0)|
|* 12 | INDEX RANGE SCAN | HCLT_APT_TEMP | 1 | | 1 (0)|
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("TASK_ID"="$nso_col_2")
6 - filter("AA"."TASK_ID"="APA_TEMP"."TASK_ID" AND
(NVL("APA_TEMP"."REM_EFFORT",0)<>NVL("AA"."REM_EFFORT",0) OR
NVL("APA_TEMP"."PCOMP",0)<>NVL("AA"."PCOMP",0) OR
NVL("AA"."ACTL_START",SYSDATE@!)<>NVL("APA_TEMP"."ACTL_START",SYSDATE@!)))
9 - access("AA"."RESOURCE_ID"="APA_TEMP"."RESOURCE_ID")
10 - access("RESOURCE_ID"="$nso_col_1")
12 - access("APA_TEMP"."TASK_ID"=:B1 AND "APA_TEMP"."RESOURCE_ID"=:B2)
This is the plan, now how can i reduce the cpu utilisation.
|
|
|
|
| Re: how to get explain plan [message #470766 is a reply to message #470764] |
Tue, 10 August 2010 11:41 |
 |
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
|
Senior Member |
|
|
1 UPDATE AUTOPLANALLOCATION AA SET ( REM_EFFORT, PCOMP, ACTL_START, MODIFIED_DATE ) =
2 (SELECT APA_TEMP.REM_EFFORT, APA_TEMP.PCOMP, APA_TEMP.ACTL_START, SYSDATE FROM AUTOPLANALLOCATION_TEMP APA_TEMP
3 WHERE AA.TASK_ID = APA_TEMP.TASK_ID AND AA.RESOURCE_ID = APA_TEMP.RESOURCE_ID) WHERE (RESOURCE_ID ,TASK_ID) IN
4 (SELECT APA_TEMP.TASK_ID,APA_TEMP.RESOURCE_ID FROM AUTOPLANALLOCATION AA,AUTOPLANALLOCATION_TEMP APA_TEMP
5 WHERE AA.TASK_ID = APA_TEMP.TASK_ID AND AA.RESOURCE_ID = APA_TEMP.RESOURCE_ID AND (NVL(APA_TEMP.REM_EFFORT, 0) != NVL(AA.REM_EFFORT, 0)
6* OR NVL(APA_TEMP.PCOMP, 0) != NVL(AA.PCOMP, 0) OR NVL(AA.ACTL_START, SYSDATE) != NVL(APA_TEMP.ACTL_START, SYSDATE))
09:40:05 SQL> /
OR NVL(APA_TEMP.PCOMP, 0) != NVL(AA.PCOMP, 0) OR NVL(AA.ACTL_START, SYSDATE) != NVL(APA_TEMP.ACTL_START, SYSDATE))
*
ERROR at line 6:
ORA-00920: invalid relational operator
missing closing parenthesis, perhaps
|
|
|
|
| Re: how to get explain plan [message #470767 is a reply to message #470765] |
Tue, 10 August 2010 11:42 |
cookiemonster
Messages: 13921
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Looks like you ran that on a DB with very little data.
Assuming that normally you will have lots of data it proves nothing.
That plan shows that the update will be very fast.
|
|
|
|
|
|
| Re: how to get explain plan [message #470770 is a reply to message #470767] |
Tue, 10 August 2010 11:51 |
prashant_ora
Messages: 196
Registered: July 2010
|
Senior Member |
|
|
|
This update is a part of procedure, actually first we populate the temp table and then on the basis of temp table we do this update statement, so how can i give u the exact plan. One thing i can do, i can get the data from gv$sql_plan of that particular sql, but this table has no of columns, how all the info will be fit-in here, pl advice.
|
|
|
|
|
|
| Re: how to get explain plan [message #470772 is a reply to message #470771] |
Tue, 10 August 2010 11:58 |
prashant_ora
Messages: 196
Registered: July 2010
|
Senior Member |
|
|
|
This procedure call from inside the application that is in java and from there some parameter pass inside the proc, so tough to trace the session, i have only option to get the data from gv$sql_plan, but how to fit in do not know.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: how to get explain plan [message #470783 is a reply to message #470779] |
Tue, 10 August 2010 13:53 |
cookiemonster
Messages: 13921
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Do you realise how meaningless that is?
Without the plan (against tables with a representative amount of data)
or the trace we have no way of knowing if that figure is good or bad.
|
|
|
|
|
|
| Re: how to get explain plan [message #470829 is a reply to message #470756] |
Wed, 11 August 2010 01:21 |
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
|
Senior Member |
|
|
@prashant_ora/Senior members,
Giving a try ....
Does rewrite of query gives better performance and also give you expected result ?
I have no data with me to verify. Can WE do something like...
SELECT apa_temp.rem_effort,
apa_temp.pcomp,
apa_temp.actl_start,
sysdate
FROM autoplanallocation_temp apa_temp,
autoplanallocation aa
WHERE aa.task_id = apa_temp.task_id
AND aa.resource_id = apa_temp.resource_id
AND ( Nvl(apa_temp.rem_effort, 0) != Nvl(aa.rem_effort, 0)
OR Nvl(apa_temp.pcomp, 0) != Nvl(aa.pcomp, 0)
OR Nvl(aa.actl_start, sysdate) != Nvl(apa_temp.actl_start, sysdate
) );
UPDATE AUTOPLANALLOCATION AA SET ( aa.REM_EFFORT, aa.PCOMP, aa.ACTL_START, aa.MODIFIED_DATE ) =
(SELECT apa_temp.rem_effort,
apa_temp.pcomp,
apa_temp.actl_start,
sysdate
FROM autoplanallocation_temp apa_temp
--,autoplanallocation aa
WHERE aa.task_id = apa_temp.task_id
AND aa.resource_id = apa_temp.resource_id
AND ( Nvl(apa_temp.rem_effort, 0) != Nvl(aa.rem_effort, 0)
OR Nvl(apa_temp.pcomp, 0) != Nvl(aa.pcomp, 0)
OR Nvl(aa.actl_start, sysdate) != Nvl(apa_temp.actl_start, sysdate
) ) ) ;
Regards
Ved
[Updated on: Wed, 11 August 2010 01:24] Report message to a moderator |
|
|
|
| Re: how to get explain plan [message #470833 is a reply to message #470815] |
Wed, 11 August 2010 01:28 |
trantuananh24hg
Messages: 744
Registered: January 2007
Location: Ha Noi, Viet Nam
|
Senior Member |
|
|
prashant_ora wrote on Wed, 11 August 2010 12:28Ok..
This update is a part of procedure, so pl advice me how can i get the trace information of the procedure, if i call it from sql prompt.
Don't you know about what proc are being used?
|
|
|
|
| Re: how to get explain plan [message #470848 is a reply to message #470815] |
Wed, 11 August 2010 02:22 |
cookiemonster
Messages: 13921
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
prashant_ora wrote on Wed, 11 August 2010 06:28Ok..
This update is a part of procedure, so pl advice me how can i get the trace information of the procedure, if i call it from sql prompt.
Trace session. Run procedure.
What's the problem?
|
|
|
|
| Re: how to get explain plan [message #470849 is a reply to message #470833] |
Wed, 11 August 2010 02:24 |
prashant_ora
Messages: 196
Registered: July 2010
|
Senior Member |
|
|
This is the trace info
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.01 0 8 0 0
Execute 1 0.00 0.04 9 578 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.01 0.05 9 586 0 0
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 72 (recursive depth: 1)
This info is from dev box as i can not run it on prod , but whenever i get the information from gv$sql of this update statement i get buffer_gets is vry high.
Buffer_gets executions
65733183 607
|
|
|
|
| Re: how to get explain plan [message #470851 is a reply to message #470849] |
Wed, 11 August 2010 02:31 |
cookiemonster
Messages: 13921
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
You need a test box that has similar data voulmes to production.
Either that or you need to trace it on production.
The information you are giving us here is completely useless.
It's either stuff from dev which shows it runs fast on dev.
Or fragments of info from prod which aren't enough to tell anything.
So what if buffer gets is high? Might be there is nothing that can be done about it, without seeing a proper explain plan, or better execution plan we've got no way of knowing.
You should also realise that it is highly likely that the best way to improve the performance of this procedure is to rewrite so that it doesn't use the temp table
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
