Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Cheuk.

Asked: August 21, 2017 - 12:38 am UTC

Last updated: August 23, 2017 - 2:44 am UTC

Version: 12.2

Viewed 1000+ times

You Asked

Hi All,

I've PL/SQL package body (~50k lines) that used to take a few mins to compile in Oracle 11g & 12.1. The package consists of mostly insert statements to some tables, plus about half a dozen stored procedures. Recently we upgraded our database to 12R2. Now the same package is taking a few times longer to compile. Can someone please explain to me how the compilation process works. What're the deciding factors in the compilation time? I already checked for deadlock from other sessions and ensure there's no other running sessions accessing the database. The package does compile eventually although taking a lot longer than before.

My system has 8GB of memory. DBCA suggested 2GB for SGA_TARGET and 800MB for PGA_AGGREGATE_TARGET. I also tried different combinations of SGA/PGA values. Any suggestions where I should look?

thanks!

and Connor said...

50,000 lines ?!?!??!?!?! Not sure I'd call that a "package"...I'd call that an "entire application" :-)

I'd try a trace first to see if we can narrow down the cause, eg

exec dbms_monitor.session_trace_enable(waits=>true);
create or replace package body ....
exec dbms_monitor.session_trace_disable;

and take a look at the tkprof formatted trace file.

Quick starting point:

grep ^total my_formatted_trace_file.prf

and see where the big numbers are.

There's also the obvious things to check, ie, any plsql related parameters changed between release

plsql_code_type
plsql_debug
plsql_optimize_level
plsql_warnings

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Cheuk Cheng, August 21, 2017 - 1:39 pm UTC

Hi Connor,

Thank you for the pointers. I agree the package is quite big but it was fine until we upgraded to 12R2.

Anyway, here's the tkprof output and there's this "PGA memory operation" with the big number. I also compared the plsql_ parameters and found no differences from the older Oracle releases.

thanks

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.03       0.02          0          0          0           0
Execute      1    268.30     285.98          0         22         14           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2    268.33     286.00          0         22         14           0

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 74  

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  PGA memory operation                        29553        0.10          0.36
  log buffer space                                2        0.02          0.04
  log file sync                                   1        0.08          0.08
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1       43.84         43.84



********************************************************************************

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        3      0.03       0.02          0          0          0           0
Execute      5    268.30     286.06          1         80         14           4
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        8    268.33     286.08          1         80         14           4

Misses in library cache during parse: 3
Misses in library cache during execute: 1

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       3        0.00          0.00
  SQL*Net message from client                     3       43.84         89.48
  PGA memory operation                        29965        0.10          0.37
  SQL*Net more data from client                 149        0.00          0.01
  log buffer space                                2        0.02          0.04
  log file sync                                   1        0.08          0.08


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       65      0.04       0.03          0          0         52           0
Execute    896      0.46       2.22          1       3538      16806       52049
Fetch      476      0.03       0.01          0        992          0         304
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     1437      0.54       2.26          1       4530      16858       52353

Misses in library cache during parse: 42
Misses in library cache during execute: 41

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  PGA memory operation                            1        0.00          0.00
  db file sequential read                         1        0.00          0.00

    5  user  SQL statements in session.
   58  internal SQL statements in session.
   63  SQL statements in session.
    0  statements EXPLAINed in this session.

Connor McDonald
August 23, 2017 - 2:44 am UTC

Whilst there is lots of "PGA memory operation" events, they still only contributed to 0.36 seconds of time, whereas out of the 285.98 total time, 268.30 of that (ie, almost all of it) was hard core CPU burn, and minimal recursive calls.

So there is nothing "out of the ordinary" besides of course the heavy CPU cost.

I think you'll need to have a chat to Support on this one - you may have hit a boundary case.

It might also be an interesting exercise to break the package into smaller chunks and see what the impact is, eg

Before

package body PKG is

  procedure P1(params) is
  begin
      lots of code code code
  end;

  procedure P2(params) is
  begin
      lots of code code code
  end;

  ...

  ...

  procedure P998(params) is
  begin
      lots of code code code
  end;  

  procedure P999(params) is
  begin
      lots of code code code
  end;  
end;


After

package body PKG is

  procedure P1(params) is
  begin
      code code code
  end;

  procedure P2(params) is
  begin
      code code code
  end;

  ...

  ...

  procedure P888(params) is
  begin
      child_pkg.p998(params)
  end;  
  procedure P999(params) is
  begin
      child_pkg.p999(params)
  end;  
end;



just so its split over several smaller packages.

I'm not necessarily suggesting that as a "solution" but in more terms of proving if its related to total package size or something else.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library