Solved

How do you use tune PL/SQL? 1,2,3 - explain plan, etc?

Posted on 2009-05-13
7
490 Views
Last Modified: 2013-12-19
Hi,
I've been using PL/SQL for 10years in my work but never really learned the 1,2,3 ways of tuning PL/SQL or learning what is more effecient code over other code... (ex: BULK COLLECT verses regular cursor)
What is the best, quickest way to start getting the needed knowledge in these areas?
Maybe an awesome PL/SQL tutorial?
Link to How to use an Explain Plan?

Any help would be appreciated,
Bill
0
Comment
Question by:bcarlis
  • 3
  • 2
7 Comments
 
LVL 73

Accepted Solution

by:
sdstuber earned 250 total points
ID: 24374938
tuning is huge topic...

but here are some good places to start

great authors, books and web articles
Steven Feurstein - pl/sql is his thing,  google pl/sql obsession
Tom Kyte - asktom.oracle.com

learn to use dbms_profiler for pl/sql tuning.

0
 

Expert Comment

by:greyhuman
ID: 24385013
hi,
Its good that you have an urge to improve.
Tuning is not a small topic and also there are no Hard and Fast rules for it.
I will suggest start with code improvements.
Learn about explain plans- table analyse , dbms_profiler  etc.
Books are always helpfull - but more practice is needed.
0
 
LVL 2

Author Comment

by:bcarlis
ID: 24385388
Thanks Sean,
I watched a bunch of Steven Feurstein's videos and this week end will look at his tutorials.

Thanks greyhuman,
I was looking for Expert suggestions of books and tutorials for "explain plans- table analyse, dbms_profiler" as Sean suggested:
Steven Feurstein - pl/sql is his thing,  google pl/sql obsession
Tom Kyte - asktom.oracle.com
thank you for your response.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 73

Expert Comment

by:sdstuber
ID: 24385399
if you found my suggestions helpful, wouldn't a split have been in order?
0
 
LVL 2

Author Comment

by:bcarlis
ID: 24509341
Actually Sean, I just realized by looking back at this that somehow I have selected the wrong one.

How do I change it?

Sorry greyhuman, But I know that I need to "Learn about explain plans- table analyse , dbms_profiler  etc." and I know that "Books are always helpfull - but more practice is needed."

Sean's post gave me a way I got into "Steven Feurstein's pl/sql obsession" with many videos, trainings and examples.
I thought I was awarding the whole amount to Sean.. I'd like to correct that because I have been going through the tutorials ever since and learned about Encapsulating DML to take advantage of SGA memory access verses going to the database each time if your SQL is a bit different.

Please correct the posting of points if possible.


0
 
LVL 2

Author Closing Comment

by:bcarlis
ID: 31580976
Thank you Sean!
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

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…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Via a live example, show how to take different types of Oracle backups using RMAN.

746 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

15 Experts available now in Live!

Get 1:1 Help Now