Solved

statistics

Posted on 2012-03-17
6
384 Views
Last Modified: 2013-12-07
statistics for a pl/sql objects like procedures and functions how will we know ..
0
Comment
Question by:b7dba
  • 3
6 Comments
 
LVL 23

Accepted Solution

by:
David earned 500 total points
ID: 37733531
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.
0
 
LVL 23

Expert Comment

by:David
ID: 37768393
Hi b7, did my answer from last week answer your question?
0
 
LVL 1

Expert Comment

by:dbmechanic
ID: 38542620
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 ?
0
 
LVL 23

Expert Comment

by:David
ID: 38543347
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.
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Suggested Solutions

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now