ORACLE SQL rewritten query

Question

In an Oracle database, how can I see the SQL that is really executed?

Let's say I have a query that looks like this:

WITH numsA
     AS (SELECT 1 num FROM DUAL
         UNION
         SELECT 2 FROM DUAL
         UNION
         SELECT 3 FROM DUAL)
SELECT *
  FROM numsA
       FULL OUTER JOIN (SELECT 3 num FROM DUAL
                        UNION
                        SELECT 4 FROM DUAL
                        UNION
                        SELECT 5 FROM DUAL) numsB
          ON numsA.num = numsB.num

I suppose that the SQL engine will rewrite this SQL into something different before executing it. Can some tell me how can I see that rewritten query (with tkprof maybe)?

"SQL" doesn't get executed. The SQL is transformed and optimized into an execution plan. You need to learn about explain. — Gordon Linoff
– Gordon Linoff, Commented Nov 20, 2019 at 20:20

Popeye · Accepted Answer · 2019-11-21 06:24:07Z

As @Gordon already commented that query does not execute in the oracle. Oracle creates the execution plan and further processing is done using the best plan chosen by the optimizer.

If you are keen to see how the query is executed then you must go for the execution plan.

Many tools provide the feature to directly see the execution plan and if you want to see the execution plan by yourself then you can achieve it using the following technique(taking the simplest example with query SELECT 1 FROM DUAL):

SQL> explain plan for
  2  select 1 from dual;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1388734953

-----------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |     2   (0)| 00:00:01 |
|   1 |  FAST DUAL       |      |     1 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------

8 rows selected.

SQL>

To understand the explain plan you must have to go through all the details related to it.

I advise you to refer to Oracle documentation for Reading Execution Plans

Cheers!!

Collectives™ on Stack Overflow

ORACLE SQL rewritten query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related