Solved

statistics

Posted on 2012-03-17
6
424 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

Don't Miss ATEN at InfoComm 2017!

Visit booth #2167 to see the  new ATEN VM3200 32 x 32 Modular Matrix Switch. Other highlights include the VE8950 4K HDMI Over IP Extender, VS1912 12-Port DP Video Wall Media Player  and VK2100 ATEN Control System. Register now with Free Pass Code ATEN288!

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

752 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