Solved

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

Posted on 2009-05-13
7
491 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.

Question has a verified solution.

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

Suggested Solutions

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Via a live example, show how to take different types of Oracle backups using RMAN.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

864 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

19 Experts available now in Live!

Get 1:1 Help Now