Skip to Main Content
  • Questions

  • limits on number of lines of code in a package/procedure/function

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question.

Asked: April 18, 2007 - 11:01 pm UTC

Last updated: April 21, 2007 - 8:59 am UTC

Version: 10gR2

Viewed 1000+ times

You Asked

Hi Tom,

At last i got a chance to post questions here. please give your feedback on these questions.

1.Is there any limit on the number of lines of code present in a package/procedure/function. One of our DBA says that PL/SQL code that have more than 10,000 lines of code has performance issues. Is that correct. Please suggest.

2.We are doing performance testing of a new development project. From the database side, since we dont have data volumes, we are using DBMS_STATS.SET_TABLE_STATS,SET_INDEX_STATS to change stats for our 2008/2009 projected volumes and we are conducting the performance testing. Is this a proper way to conduct DB performance testing. please suggest.

3. We are using a 3rd party vendors products in our project. And when i trace the calls made from that product, i see some of the SQL come to Oracle with values and does not contain bind values. We have logged a service request, but they say that the SQLs are generated at their SQL engine and cannot pass bind values and will get only values at Oracle end. So we were thinking of using CURSOR_SHARING parameter so that we can BIND. please let us know if there are any overheads/issues if we use CURSOR_SHARING parameter.

Thanks in advance.

regards,
Vijaya Chander V.S

and Tom said...

1) there are only practical limits.

If you gave me a single piece of code with 10,000 lines in it - I would reject it out of hand as a software developer. I would send you back to modularize it. To make it maintainable, understandable. I would never accept that.

When something gets into the thousands of lines of code, you need to rethink that design.

It is not a performance thing, it is a "that doesn't make sense" thing.


2) No, it isn't.

so, you trick us and you tell us this table that has in reality 1,000 rows has 1,000,000. We decide to full scan (for whatever reason). You love the performance (full scanning 1,000 rows doesn't take very long). In real life however, you might not be as impressed.

Testing against representative data sets (and user populations) is the only way you'll get a realistic sort of view of what will happen.


3) there are many issues with it. Your vendor has lied to you however, you can ALWAYS bind - there are no cases whereby they could not - none. They can fix this.

cursor sharing will negatively impact well written applications (longer code paths)

cursor sharing force can introduce bind peeking issues - especially when you MEANT to put a literal in the code.

cursor sharing changes your programs behavior - it just came up this morning in fact! http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:228614500346120336


Rating

  (1 rating)

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

Comments

thanks

Vijaya Chander V.S, April 20, 2007 - 8:43 pm UTC

Thanks for your valueable inputs.

1. program limits: Let me give you some more details. I hava package of 10,000 lines with 8 procedures. We have these procedures divided based on the modularity. Do you still think the package is large enough.

2. Statistics update: mine is an OLTP DB(most of them index scans) and we have nearly 10% representative data with stats updated for future volumes. Do you think that i need to have more data to come up with performance figures. Is there any thumb rule for representative data to use for performance testing.

when i updated the stats to the future projections, i saw more CPU usage/memory usage on the Solaris box. Then identified bad SQLs, tuned them and the CPU usage is normal. do you think that what ever CPU spike that i saw after stats update is due to some other reason and not due to stats update.

please provide your feedback.

3. CURSOR_SHARING: If the vendor is building sqls at the execution time, we still get dynamic sql which does not use binds. The application will go live in June and i dont think something can be fixed in the product this soon. Please let me know what can be done in this situation.
Tom Kyte
April 21, 2007 - 8:59 am UTC

1) I think 10,000 lines in a single unit of code is pretty large. You have a package, with 10k lines of source code. That is, in my opinion, HUGE.

2) all of it

you tuned SQL destined to run against LARGE data sets, using small data sets. So what have you accomplished exactly? do you have any idea how this finely data SQL against 10% of your data will actually perform "in real life"

No, I think this is a bad idea


3) dynamic SQL, all SQL in Oracle is dynamic. All of it. 100% of it.

and it can always use binds, they can use binds.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

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