A question about execution plan [message #628480] |
Tue, 25 November 2014 10:29 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
orausern
Messages: 826 Registered: December 2005
|
Senior Member |
|
|
Hi experts,
I am on Oracle 11.2.0.4 on Linux. I have following query plan (a part of the big plan I have copied here) for one of the sql and have a question about it. It shows some big numbers under OMEM/1MEM/USED-MEM sections of the runtime execution plan. What does it imply? Does it mean that the query has an issue? The query completes in less than 10 seconds which is acceptable but i am not sure of the memory related issues and need help on it: The output is from this command and I didn't want to reveal the actual table names etc. so have put here a part of the plan.
select * from table(dbms_xplan.display_cursor(null,null,'ALL allstats last +outline' ));
I will be thankful for the help on it.
OrauserN
Part of the execution plan:
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 2730 (100)| | 25 |00:00:02.20 | 2587 | | | |
|* 1 | VIEW | | 1 | 1 | 237 | 2730 (1)| 00:00:33 | 25 |00:00:02.20 | 2587 | | | |
| 2 | COUNT | | 1 | | | | | 127K|00:00:02.17 | 2587 | | | |
| 3 | VIEW | | 1 | 1 | 224 | 2730 (1)| 00:00:33 | 127K|00:00:02.15 | 2587 | | | |
| 4 | SORT UNIQUE | | 1 | 1 | 190 | 2729 (1)| 00:00:33 | 127K|00:00:02.13 | 2587 | 16M| 1658K| 14M (0)|
|* 5 | FILTER | | 1 | | | | | 152K|00:00:01.77 | 2587 | | | |
|* 6 | HASH JOIN OUTER | | 1 | 1 | 190 | 2728 (1)| 00:00:33 | 365K|00:00:01.72 | 2587 | 71M| 8481K| 72M (0)|
| 7 | VIEW | | 1 | 1 | 131 | 895 (1)| 00:00:11 | 365K|00:00:01.01 | 893 | | | |
|* 8 | HASH JOIN OUTER | | 1 | 1 | 263 | 895 (1)| 00:00:11 | 365K|00:00:00.95 | 893 | 70M| 8506K| 75M (0)|
|* 9 | HASH JOIN | | 1 | 1 | 227 | 802 (1)| 00:00:10 | 365K|00:00:00.52 | 754 | 49M| 4589K| 56M (0)|
|* 10 | HASH JOIN | | 1 | 1 | 136 | 661 (1)| 00:00:08 | 365K|00:00:00.14 | 503 | 3365K| 1171K| 3872K (0)|
|* 11 | HASH JOIN | | 1 | 1 | 105 | 659 (1)| 00:00:08 | 21490 |00:00:00.05 | 500 | 1148K| 1148K| 750K (0)|
|
|
|
Re: A question about execution plan [message #628482 is a reply to message #628480] |
Tue, 25 November 2014 10:58 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
John Watson
Messages: 8944 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Omem is the estimated PGA needed to run the the query optimally (ie, no use of temp
space on disc), 1mem is the estimated amount needed for a one extra pass execution, which
would need space in temp. Usedmem is what it actually used. So it looks as though your
last execution was optimal. But the next one might not be: that would depend on
what else is happening in the instance.
|
|
|
|