Solved

statistics

Posted on 2012-03-17
6
418 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Via a live example, show how to take different types of Oracle backups using RMAN.

763 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