Long parsing on Oracle 12.2 and the discovery of SQLT Xplore
Today I discovered a fantastic free tool from Oracle: SQLT Xplore!
At a client I had one query that took long time parsing. The tkprof result of the 10046 trace showed it took 5 seconds to parse:
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 5.17 5.30 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 665 0 26
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 5.17 5.30 0 665 0 26
Looking around I’ve noticed that disabling the cost based transformation:
ALTER SESSIONS SET "_optimizer_cost_based_transformation"=off;
…the query was much faster to parse: only 0,5 seconds.
(more…)