Toad World® Forums

pkg with 16,000 lines of code took about 5 min to compile


#1

ok maybe it was more like 3 min but that is a crazy long time for a package to compile. Are ya’ll sure the new editor can handle large chuncks of code? We have insane sized packages that we migrate.


#2

Well scratch that - Nav7.0 is equally slow on small packages also. 2600 lines over 3 min…
zzzzzzzzz


#3

15k lines of code. …

zzzzzzzzzzzz


#4

Hi Henry,

Did you compare the performance against v6.7? I suspect, it should be about the same (on the same database). Nav doesn’t compile packages, Oracle does. Yes, we are confident that 7.0 can handle large packages just as well, if not better, than 6.7. We will do more comparisons, but that’s my feeling so far.

Regards,
Roman


#5

Hi,
have You tried compile under sqlplus?.


#6

yes compiled on 6.7 and it did not take a long time. We never have packages compile over a long period of time. But in 7.0 we do.

As far as compile on sqlplus no we don’t and we never have to.

I am going to leave that test to you. I am telling you that sql nav 7.0 is very slow when it comes to compiling packages and I don’t know why it’s so slow. But there is no way we would use nav 7.0 because of this issue.


#7

10:04:05 Start Script Execution …
10:04:05 **** SCRIPT STARTED: 23-May-2013 10:04:05 ****
10:04:05 select * from dba_objects where object_name like ‘%CBT%’ AND OBJECT_TYPE =‘TABLE’
10:04:47 Data Set is Read-Only; 6 row(s) fetched
10:04:47 **** SCRIPT ENDED 23-May-2013 10:04:47 ****
10:04:47 End Script Execution

42 Seconds to run a very simple query in Nav 7.0

10:10:43 Start Script Execution …
10:10:43 **** SCRIPT STARTED: 23-May-2013 10:10:43 ****
10:10:43 select * from dba_objects where object_name like ‘%CBT%’ AND OBJECT_TYPE =‘TABLE’
10:10:43 Data Set is Read-Only; 6 row(s) fetched
10:10:43 **** SCRIPT ENDED 23-May-2013 10:10:43 ****
10:10:44 End Script Execution

Same query on same server with nav 6.7 returned in under 1 second.

I don’t know what it is but some times 7 just looses it’s mind and does a bunch of crunching for some unknown reason. The end result is sit and wait.


#8

Try to set optimizer goal -> RULE in nav preferences/session.
Queries on oracle dictionary are usually slow, because of construction of SYS views, and missinf SYS statistics. Ususally hint RULE helps with that.

Check this setting in nav 6.7.


#9

Will look at those settings in a min. This is the result from just trying to compile a package.

3:31:18 PM Start Script Execution …
3:32:03 PM **** SCRIPT STARTED: 05/23/2013 3:32:03 PM ****
3:32:03 PM CREATE OR REPLACE
3:32:03 PM PACKAGE ipodadmin.pkg_aigr_enroll_books
3:32:03 PM …
3:32:06 PM SQL statement executed
3:32:06 PM CREATE OR REPLACE
3:32:06 PM PACKAGE BODY ipodadmin.pkg_aigr_enroll_books
3:32:06 PM …
3:32:12 PM SQL statement executed
3:32:12 PM **** SCRIPT ENDED 05/23/2013 3:32:12 PM ****
3:32:12 PM End Script Execution

Nav becomes unresponsive at 3:31:18 to 3:32:03. Then it finally compiles the package.


#10

The optimizer goal/mode is set to default on both 6.7 and 7.0.

I have changed the setting to rule in 7.0 and will see how it goes.


#11

Please explain what the default setting is for the optimizer goal/mode in NAV?

We don’t like the idea of using the rule based optimizer. Looks like oracle is not going to support the rule based optimizer any longer anyway.

http://www.dba-oracle.com/oracle_tips_rbo_cbo.htm

I am changing back to default.

We are updating stats on the dictionaries - but we have not noticed any slow down issues with 6.7.

Is there any kind of preprocessing that nav is doing before it runs the sql?

Message was edited by: OracleDBA


#12

I think default - means no change for default optimiser goal for session. Your goal is derived from instance parameters. But this is my meaning, I’m not a Developer of nav.
Rule hint is not supportet it means - Oracle is not supporting ( as a service request) no longer problems with rule based optimiser.
But take some time and look into system views there are rule hint mostly :).
U haven’t wrote if rule goal helped?

BTW. Do not take Your opinion only on Burlesson webs -for me- he is just theorist with no practise experience. Of course I don;t know him, just his articles are amateur.

Regards P.

Message was edited by: piortek


#13

I like Burleson.

10:19:23 **** SCRIPT STARTED: 24-May-2013 10:19:23 ****
10:19:23 CREATE OR REPLACE
10:19:23 PACKAGE birpt.cr_mei_group_qpd_pkg
10:19:23 …
10:19:24 SQL statement executed
10:19:24 CREATE OR REPLACE
10:19:24 PACKAGE BODY birpt.cr_mei_group_qpd_pkg
10:19:24 …
10:19:49 SQL statement executed
10:19:49 **** SCRIPT ENDED 24-May-2013 10:19:49 ****
10:19:49 End Script Execution

We updated the STATS on the dictionary but that did nothing for the speed.


#14

Hi, can U attach Your package?
I can try to compile on my instance…

Regards P.


#15

Hi Henry,

Could you please check the latest beta build #2962 Shirly posted yesterday. Roman has made changes to improve the performance saving/compiling objects.

Please let us know how it goes.

Thanks and regards,

Bruce


#16

I’m encountering this problem as well, even in build #2962.

It seems to be more about the total number of lines in the file than the number of lines in what you are actually compiling.

We maintain both spec and body in the same file, and have one that is 17,946 lines in total, while the body only is 15,751 lines if stored separately.

When I try to compile the body from the separate file, it compiles fine in less than 9 seconds. When I compile the body only from within the full file (body and spec), it takes so long I haven’t been able to see it complete, having to kill the session instead. The last test i ran just a few minutes ago was still hung after 10+ minutes before I gave up.

Here are some specific times:

SQL Nav version 6.7 7.0.2962

separate file 7.5 - 9 secs 7 - 8 secs

(15,751 lines)

full file 7.5 - 9 secs 10 + mins (never completed)

(17,946 lines)

All of this is against the same oracle 11r2 instance, with nothing else going on or blocking in any way at the time the tests were run.

I have not tried this in the object editor, since we almost never use those. Preferring to work with files.

Peter S


#17

Hi Peter,

Thanks for your posts.

Roman has made more changes in area of loading large scripts. The underline issues is not the time taking of compiling, the performance issue relates to the reloading the object content after compiling. We have seen a big improvement in our test envi. We will provide you a new beta build soon to verify.

Thanks and regards,

Bruce


#18

Hi Peter and Henry,

Could you please check it for us again with the new beta build 2987.

Please let us know if the performance issue you reported has been fixed.

Thank you again for your help and feedback.

Regards,

Bruce


#19

I just tried the same package again, int he full file version 17,946 lines … though only the last 15,751 are the body, and only the body was compiled. It compiled and returned in about 9 seconds, so this appears fixed.

Do you reload the object even when not compiling from an object editor? Since I was compiling from a script, not the object editor, the comment re reloading didn’t seem to apply to my test case. Especially since the part I was actually compiling (body only) compiled fine in the previous beta if separated from the spec, which wasn’t getting compiled in any case.

Either way, it works fine now.

Thanks,

Peter S