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 - 64bit Production
PL/SQL Release - Production
"CORE  Production"
TNS for Linux: Version - Production
NLSRTL Version - Production


Oracle Database 11g Express Edition Release - Production
PL/SQL Release - Production
"CORE  Production"
TNS for 32-bit Windows: Version - Production
NLSRTL Version - 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
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

  • Is changing the value of the cursor_sharing parameter an option? – Colin 't Hart Aug 13 '14 at 12:30
  • I'm not sure whether is this your case but maybe Oracle has problems to "age out" some other execution plan from library cache. It is a shared area where are all the execution plans stored. Look into AWR report whether you see something related to wait event "library cache mutex". What you are doing is against vendor(Oracle's) recommendations - you application can not scale. Colin is right you should at least use cursor sharing. – ibre5041 Aug 13 '14 at 13:03
  • How are HW metrics? How about to trace that sql execution? That will give you more information about what is actually doing. Maybe you hit a computational limitation. If everything is hard parsed, and there's no way to change the SQL nor the DB design, changing cursor_sharing as Colin suggested may give you more library cache hits. Although SE is up to 4 sockets per server, note that both XE and SE editions doesn't allow parallel executions. – vegatripy Aug 13 '14 at 13:30
  • The first step would be to use binds, so you get only one parse. Then you first won't really have a problem anymore, and will be able to use the plan management stuff. – Mat Aug 13 '14 at 14:21
  • Hello everyone, I'll try cursor_sharing=force. Adaptive Cursor Sharing ACS is enabled by default. I don't think it is an age-out problem or anything like it, because there is no work on the systems, just me. Regarding AWR and Plan Management: I'm on SE, not EE, so I can't use these. Regarding binds: My SQL is generated by a 3rd party framework and I can't change it to use bind variables. Thanks for the answers so far, I'll let you know how it worked out. – Blama Aug 13 '14 at 14:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.