As I know, the query transformer transforms our queries into better ones if possible. So the query I executed and the query database executed at the end can be different.
How can I see the final query that the database executed? I mean the result of the query transformer.
To see the transformed query used by the optimizer you should use a 10053 trace. But an execution plan is more convenient and usually good enough.
For a quick example, this schema contains two simple tables, and each row in the second table must exist in the fist table.
--drop table test2;
--drop table test1;
create table test1(a number primary key);
create table test2(a number primary key references test1(a));
We want to generate a simple query where the transformed query will be different than the original. To do that, the below query has an unnecessary join. Since there's an inner join, and each row in TEST2 must exist once and only once in TEST1, Oracle doesn't need to do the join. Instead, Oracle only needs to read from a single table or index from TEST2.
select count(*) new_name_for_hard_parse_01
from test1
join test2 on test1.a = test2.a;
To find the precise query used by the optimizer, you need to generate an 10053 trace. For example:
alter session set events '10053 trace name context forever, level 1';
select count(*) new_name_for_hard_parse_02
from test1
join test2 on test1.a = test2.a;
alter session set events '10053 trace name context off';
(Notice how I used a different name for the column. You need to change the query and force a hard parse. Otherwise, Oracle may simply re-use the existing execution plan and won't generate a trace.)
Wait a minute, and the file will show up in a trace directory somewhere. Depending on the version and configuration, the file might be in USER_DUMP_DEST or a sub directory under DIAGNOSTIC_DEST. For example, on my PC it was the file D:\app\jon\virtual\diag\rdbms\orcl12\orcl12\trace\orcl12_ora_22576.trc
Open the file and look for a section like this:
...
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT COUNT(*) "NEW_NAME_FOR_HARD_PARSE_02" FROM "JHELLER"."TEST2" "TEST2"
....
The trace file explains the different transformations and shows the final query.
But you almost never want to use Oracle trace files. The trace files are inconvenient, the commands are undocumented and don't work well, and you won't always have access to the server file system. For 99.9% of Oracle performance tuning, tracing is a waste of time.
An execution plan is a faster way to determine how the query runs, which is probably what you're interested in.
explain plan for
select count(*) new_name_for_hard_parse_01
from test1
join test2 on test1.a = test2.a;
select * from table(dbms_xplan.display);
Results:
Plan hash value: 4187894267
------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 0 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FULL SCAN| SYS_C009744 | 1 | 0 (0)| 00:00:01 |
------------------------------------------------------------------------
The execution plan shows how only one object was used. It doesn't explain that join elimination was used, you have to infer it.