Tuning pl sql Where to start?

[marcelo0906]

Good morning people!

As I have always received help when I post here, thank you for it.

I am a beginner in Oracle, and with this it was assigned a function to do the tuning of some procedures packages etc ... but I do not know where to start ...
Any idea where to start ???? which pon to evaluate etc ...

[dr_gori]

This is an area that I find more interesting in Oracle. Because there is no cake recipe to make tuning. If it existed, Oracle itself "implement" this in the new version and ready ...

So: The way is to understand the functioning of the bank, as it consulted, as it uses the indexes, understand the optimizer ... is a long path, type, do not learn this by reading a little tutorial bag?

Suggestion:
take a look at this manual: [url=http://download.oracle.com/docs/cd/B105 ... 33/toc.htm]Oracle9i Database Performance Tuning Guide and Reference
(I think it has 10g on the internet).

But there goes a suggestion for you to start:
* Perform the implementation plan of the queries used to see their cost, whether or not you are using the indexes. (Do not always use index means it's faster).
* See if the tables are properly "updated" in the data dictionary. I'm talking about collecting statistics, histograms, etc.
* Understand the operation of the package, because it is often done unnecessary things or that can be taken advantage of better.
* Changing strategy Sometimes it's a way out. Creating materialized or global views Temporary Tables ..
* Using bulk collections, sometimes helps. Pl / tables ...

All that I said is just the tip of the iceberg .. still has bench tuning, operating system tuning, etc. (But these will only be made after the tuning of the procedures and SQL - that is, if there is nothing that can improve, because it is much more expensive to do these).

The business and study and go deep ... Send us your doubts that we learn along here in the forum!

: -O

[fsitja]

Perfect the Dr Gori post. There really is no cake recipe for tuning, but over time you develop that "gut feeling", a sense that triggers when you see PL / SQL code and suspicious SQL queries.

adding to what has already been said, two things I think of paramount importance.

1 - Understand a little complexity of algorithms. As "measuring" the worst case in loops and especially loops nested in your PL / SQL. There is the subtlety to understand that SQL and PL / SQL are not the same thing, and know how to weigh the burden of context switching, resource consumption (memory, disk and network) and how to avoid using PL / SQL when possible . SQL alone is a very powerful and much safer and bug-free language than PL / SQL, because it is declarative, and non-procedural.

2 - Have at least a basic view of Oracle's internal implementation. It is very important to know how Oracle solves for you everyday problems, such as function calls within SQL, as the execution plan rides, as the BD retrieves the data for you (Joins, Inline Views, Predicated Assessment, Transaction Insulation , Triggers processing, the importance of reducing network traffic and consistent disk gets, and so on ...).

The challenge of doing tuning is to break with that maxim of Angus MacgyVer who preaches that "it worked, it's right." There are several ways to do one thing, some certain, others not so much and yet others who are right at first sight only.

[marcelo0906]

HMN. I understand that there is no recipe for cake and such, but so they played in the "bonfire" now I have to leave ... hehehehe As I spoke before, I am a beginner and left this pump ...

I wanted to know:

What points are important ... check the merthal of application?

How to check the performance of a procedure, I tried some command but I did not learn how to use correctly.?qual command and example of use ... these things are much lost in it ...
But as soon as doubts emerts I post here hehehevlw !!

[fsitja]

On the practical side of analyzing PL / SQL, the first thing is to understand what the code is wanting to do. Enter the business requirement. Then the second step and get out if there is no alternative approach to reach the same result. It is the practice of refactoring, as in vogue nowadays, in its Oracle flavor.
Some very basic guidelines, Tom Kyte's reference: http://asktom.oracle.com/pls/asktom/f?p ... 4517459743
Simplified general rules:
- Avoid using PL / SQL. When you can resolve one SQL, the server takes the processing account in a step only. This means studying a lot of SQL, syntax and different ways to do things. Do not be happy with bean-with-rice just because it is more comfortable.
- If you are to do in PL / SQL, make cursors with small batches of lines, never in Slow-by-Slow (example: cursor for loop, line line). Here comes algorithms complexity and the context switching problem. Here it also applies Pipelined Function, especially for data warehousing.
- Use BIND VARIABLES in the SQLs of procedure cursors to reuse the parse of a single statement.
- In a SQL you should avoid multiple scans in the same table, or read more rows and columns that required. This is done by adjusting the predicates of Join and Where, allowing reading less blocks, and often direct index access, without fetching the table. Analytical SQL functions allow you to refer several distinct lines, often saving one or more sub-catering.

What is used in practice is tracing through TKProf, Profiling and Explain Plan Analysis. If none of this says a lot for you, it's because there is a gap of concepts to cover, and the way is to study a lot.

The trace will return a file showing number of disc readings, low-level calls (so the recommendation to understand Oracle implementation). The great goal here is to decrease disc accesses.
Explain Plan is the "map" that Oracle uses to reach the data you want: if you are going to search the table using index or not, which join technique will be used.

The difficult here is to understand and learn to read the trace and the access plan. How do you make the access plan decision a better than B? My Recommendation: Be proactive, seek information, especially on Oracle's official website, referring to your version of the database: http://tahiti.oracle.com
When you have specific questions, type, "Why My Consistent Gets on this query are so high compared to this other that is similar "," How I simplify these 100 code lines of procedure X in a SQL only, without having to make nested loops and line-to-line processing "and etc ...

Hugs,
Francisco.

[fsitja]

First question is: How do you know that a procedure is a candidate for a job aimed at improving performance? The first symptom is confusing code, repetitive logic (copy / paste), delay of transactions, especially in heavy tables, with several millions of lines.

A good place to start is in the SQLS Explain Plan: http://www.oracle.com/pls/db112/to_URL? ... 23PFGRF009
The most basic thing is to avoid full table scans in large tables unless you are Reading a large percentage of her lines, type 5% for more, where the index will lose performance by requiring more block readings. The TKProf will tell you exactly how many blocks Oracle needed to read on the disk along the processing. Usually the bottlenecks are on the disc, hence the importance.
http://www.oracle.com/pls/db112/to_URL? ... PFGRF01020
This disregard, of course, the need to re-evaluate the reason for that SQL to be there first.
If you have several auxiliary and temporary tables, transformations that spread through various procedures, consider understanding what they are trying to get and redesign the code. This is where the biggest gain gets.

Oracle has a specific book on tuning perfomance available here:
http://download.oracle.com/docs/cd/E118 ... 21/toc.htm

[reuber]

Can I read about tunning using books using version 9i when my bank is 10g? Change a lot between the version of a bank to another?

[dr_gori]

The main functionalities or the basics is equal or very similar. No problem you read from 9i while you have Banco 10g.