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