statistics

statistics for a pl/sql objects like procedures and functions how will we know ..
b7dbaAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DavidSenior Oracle Database AdministratorCommented:
Statistics for most of us refer to the collection of metadata for tables and indexes, not PL/SQL.  Current statistics are essential for the database to compute which optimization path is most efficient;  for more information see http://docs.oracle.com/cd/B19306_01/server.102/b14211/stats.htm.

For versions 10g and above, the preferred unit of performance measurement is wait-events rather than ratios.  Specifically for the DML / SQL commands within procedures and functions, Oracle provides the very useful EXPLAIN PLAN with documentation elsewhere here in E-E, and on their documentation pages.  If you still want to see statistics for fun and profit, get familiar with the Oracle STATSPACK product.

Now, if you have to work with service level agreements (SLA), such as keeping the database available at some percentage of total time -- I can see some manager wanting to prove the service met expectations.  However, that is an issue above and beyond your question on PL/SQL.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DavidSenior Oracle Database AdministratorCommented:
Hi b7, did my answer from last week answer your question?
dbmechanicCommented:
Hi b7dba,

if you need a more detailed answer, you need to post the OS/db version.  PL/SQL objects like procedure, function and packages in turn consists of select and DML statements.  if you are experiencing any slowness with one particular procedure, there should be an issue with the underlying SQL statements within the PL/SQL objects.  There are some techniques available to diagnose the slowness.  

dvz has advised you appropriately on this ?
DavidSenior Oracle Database AdministratorCommented:
Further thought -- statistics for PL/SQL breaks down to the statistics for the individual SQL statements.  Ensure the tables are appropriately optimized (dbms_stats); the tables and views have appropriate indexes and organization;  and that the statements have been worded to run in the least amount of time.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.