I'm using Oracle 11.2 SE on a 16GB Xeon E3 Linux machine as well as 11.2 XE on a 8GB i7 Win8 machine:
SELECT banner FROM v$version: Oracle Database 11g Release 18.104.22.168.0 - 64bit Production PL/SQL Release 22.214.171.124.0 - Production "CORE 126.96.36.199.0 Production" TNS for Linux: Version 188.8.131.52.0 - Production NLSRTL Version 184.108.40.206.0 - Production
Oracle Database 11g Express Edition Release 220.127.116.11.0 - Production PL/SQL Release 18.104.22.168.0 - Production "CORE 22.214.171.124.0 Production" TNS for 32-bit Windows: Version 126.96.36.199.0 - Production NLSRTL Version 188.8.131.52.0 - Production
Both systems suffer from the same problem: I have a complicated SELECT (120 steps in EXPLAIN PLAN) for a right now pretty empty schema (20 MB in total):
SELECT segment_type, COUNT(*) AS objectcount, SUM(bytes / 1024 / 1024) AS MB FROM user_segments GROUP BY segment_type; LOBINDEX 1 0,0625 LOBSEGMENT 1 2 TABLE 55 6,4375 INDEX 204 12,75
Table stats are gathered with:
DBMS_STATS.GATHER_TABLE_STATS(ownname=>USER, tabname => loop_tablesnames, estimate_percent=>100, method_opt=>'FOR ALL COLUMNS', cascade=>true, force=>true);
The EXPLAIN PLAN for the statement leading to 120 steps (these are expected) takes approx 7sec on the linux machine and 22sec on the win8 machine.
The installs are basic, no changes to the Oracle configuration have been done.
As my SQLs are generated and do not use parameter binding, almost every statement is hard-parsed leading to the same delay for statement execution. Once no hard parse is needed, the statement executes within milliseconds.
For less complicated statements, the EXPLAIN PLAN is fast.
Is there anything I can do to speed up the EXPLAIN PLAN and the hard parse step (I'd think that the solution for EXPLAIN PLAN will also solve the other)?
Thank you & best regards, Blama