Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 432
  • Last Modified:

statistics

statistics for a pl/sql objects like procedures and functions how will we know ..
0
b7dba
Asked:
b7dba
  • 3
1 Solution
 
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.
0
 
DavidSenior Oracle Database AdministratorCommented:
Hi b7, did my answer from last week answer your question?
0
 
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 ?
0
 
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.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now