Home » RDBMS Server » Performance Tuning » A question about execution plan (Oracle 11.2.0.4. on Linux)
A question about execution plan [message #628480] Tue, 25 November 2014 10:29 Go to next message
orausern
Messages: 819
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 messageGo to next message
John Watson
Messages: 7620
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.
Re: A question about execution plan [message #628502 is a reply to message #628482] Tue, 25 November 2014 12:52 Go to previous message
orausern
Messages: 819
Registered: December 2005
Senior Member
Thanks a lot John!!!
Previous Topic: Index vs Full table scan on sysdate
Next Topic: Please help me to understand why the query works longer of server_A than on server_B
Goto Forum:
  


Current Time: Sun Oct 21 02:07:44 CDT 2018