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

Posted on 2009-05-13
Medium Priority
Last Modified: 2013-12-19
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,
Question by:bcarlis
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
  • 2
LVL 74

Accepted Solution

sdstuber earned 1000 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.


Expert Comment

ID: 24385013
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.

Author Comment

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.
Industry Leaders: 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!

LVL 74

Expert Comment

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

Author Comment

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.


Author Closing Comment

ID: 31580976
Thank you Sean!

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

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 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

801 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