SQL how to find out execution time

Question

I have an sql select statement which takes really long (~3 minutes).

I want to find out what is going on. So I did

explain plan for MY_STATEMENT, but this finishes in < 1 second.

and SELECT * FROM TABLE (dbms_xplan.display); is telling me nothing suspicous

| Id  | Operation              | Name                    | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|                                                                                                                                                                                           
------------------------------------------------------------------------------------------------------------------                                                                                                                                                                                           
|   0 | SELECT STATEMENT       |                         |   943 | 86756 |    15   (0)| 00:00:01 |        |      |                                                                                                                                                                                           
|*  1 |  HASH JOIN RIGHT OUTER |                         |   943 | 86756 |    15   (0)| 00:00:01 |        |      |                                                                                                                                                                                           
|   2 |   TABLE ACCESS FULL    | PTSAREA                 |   442 | 12376 |     5   (0)| 00:00:01 |        |      |                                                                                                                                                                                           
|*  3 |   HASH JOIN RIGHT OUTER|                         |   943 | 60352 |    10   (0)| 00:00:01 |        |      |                                                                                                                                                                                           
|   4 |    TABLE ACCESS FULL   | PTSAREA                 |   442 | 12376 |     5   (0)| 00:00:01 |        |      |                                                                                                                                                                                           
|   5 |    REMOTE              | M_SUPP                  |   943 | 33948 |     5   (0)| 00:00:01 | IXXX_~ | R->S |                                                                                                                                                                                           
------------------------------------------------------------------------------------------------------------------

But when I really execute the statment it takes ~3 minutes. Is there any way to find out what takes so long?

Edit your question and include the query. Actually, I'm rather confused by the question itself: you have the `explain` plan. That gives the information on how the query is running and hence why it takes a certain amount of time. — Gordon Linoff, Apr 29 '16 at 10:49
Where you observing the long running time in production, and, if so, could something else have been eating up your CPU time? — Tim Biegeleisen, Apr 29 '16 at 10:50
where do I see the information ? It is telling me Time: 00:00:01 and the cost is rather low — matthias, Apr 29 '16 at 10:50
Is this the only activity which executes in your production environment, When you experience this latency? — Dinidu Hewage, Apr 29 '16 at 11:12
Post the query and a **complete** execution plan (incl. predicate information). See [here](http://stackoverflow.com/questions/34975406/how-to-describe-performance-issue-in-relational-database?answertab=active#tab-top) what information can be usefull. — Marmite Bomber, Apr 29 '16 at 11:16

score 1 · Answer 1 · answered Apr 29 '16 at 11:14

The first thing you should approach is to check why the optimizer estimates such a low cost and elapsed time.

The most probable cause are wrong or stale object statistics. Verify the number of row in PTSAREA and M_SUPP. Are there realy only 442 resp. 943 rows? I suspect a magnitudes higher volume.

If so gather table statsitics and repeat the explain plan. You should see much realistical cost and time. In some case event a different execution plan.

In other words in most of cases when you see unexpected things in execution plan this is a problem of the input of the optimizer (object statistics, system statistics and/or optimizer parameters).

@matthias: to collect statistics on your tables use something like `BEGIN DBMS_STATS.GATHER_TABLE_STATS(ownname=>'schema', tabname=>'table name'); END;`, putting the schema and name of your table in the obvious places in this code. Run this for each table used by your query, Then get the execution plan again, run the query again, and see where you are. Best of luck. — Bob Jarvis - Слава Україні, Apr 29 '16 at 11:56

SQL how to find out execution time

1 Answers1