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 22.214.171.124.0 - 64bit Production PL/SQL Release 126.96.36.199.0 - Production "CORE 188.8.131.52.0 Production" TNS for Linux: Version 184.108.40.206.0 - Production NLSRTL Version 220.127.116.11.0 - Production
Oracle Database 11g Express Edition Release 18.104.22.168.0 - Production PL/SQL Release 22.214.171.124.0 - Production "CORE 126.96.36.199.0 Production" TNS for 32-bit Windows: Version 188.8.131.52.0 - Production NLSRTL Version 184.108.40.206.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