Plsql_ Performance Optimization Series 07_oracle parse bind variables parse bound variable

2014-09-25 Baoxinjian

I. Binding variable usage and usage scenarios

The importance of using bound variables: If you use constants without binding variables, you can cause a lot of hard parsing. Not using binding variables is often the biggest problem that affects Oracle performance and scalability due to hard-parsing hazards

Here are some examples of wrong writing and proper notation

1. Common query in Plsql

(1). Wrong wording

SELECT * from EMP WHERE empno=123;

(2). Correct notation (no binding variable used)

empno:=123;
seelct* from EMP WHERE empno=:empno;

2. Using dynamic SQL in Plsql

(1). The wrong wording

Sqlstr:= ' SELECT * from emp where empno= ' | | Empno Execute immediate for SQLSTR;

EXECUTE IMMEDIATE for Sqlstr;

(2). Correct wording

Sqlstr:= ' select * from empno= ' | | Empno

EXECUTE IMMEDIATE for Sqlstr;

Because the former uses string concatenation is easier, many people will use it.

Ii. How to judge and locate statements that do not use bound variables in the system

In the Load profile section of the AWR, there is a hard parses indicator that represents the hard parse per second.

In addition to the instance efficiency percentages section, the Soft parse% This indicator reflects the ratio of hard parsing to all parsing.

One of the two indicators is the absolute value, and the other is relative. Hard parse indicators per second should be lower, while soft parse% should be higher (some say should be greater than 95%).

The specific reasonable index and system size, business volume, business type are all related, can refer to Siebel system, these two values are 11 and 98%. If these two indicators exceed the reasonable range, then the hard parsing too much, should pay attention to the cause of the analysis.

Example: not using bound variables is the most common cause of hard parsing, so how to find these SQL

Step1. Which SQL can be found with the following statement:

SELECTSUBSTR (Sql_text,1, -) "SQL",Count(*),sum(executions) "Totexecs" fromV$sqlareaWHEREExecutions< 5 GROUP  bySUBSTR (Sql_text,1, -)    having Count(*)>  - ORDER  by 2;

Step2. Use the following statement to find the users and modules that run these SQL

SELECT service, module, Parsing_schema_name, Sql_text     from where  like ' Select RowNum as .... id% ';

Iii. reduced parsing, including hard parsing and soft parsing

1. The origin of the problem

SQL Optimization (vi) We introduced the concept of soft parse/hard parse and the technique of reducing hard parse by using binding variables.

In a production environment, we find that too much soft parse can also cause performance problems, such as a higher library cachelatch contention wait, although soft parse has a much smaller performance overhead than hard parse.

The highest state is no parse; the trick to reduce the parse is the cursor of Oracle.

2. The SQL execution process and the parse classification, when Oracle runs SQL, the process is as follows:

(1). is the SQL cursor open? If it is, skip to 5) This is no parse, for easy comparison, we also act as a type of parse

(2). If cursor is in session cache (PGA), if present, jump to 5) This situation Oracle expert Tom called its softer soft parse

(3). Perform syntax check and semantic check, then look in the hash table of the shared pool, if matched to step 5), this is called soft parse

(4). If the match fails, you need to security-check,optimize, generate query plan, and so on, which is called hard parse, and you can imagine that the source program is compiled and run first.

(5). Execute

3. The cost of all types of parse

We compare the overhead of several of the above types of parse, respectively

Hard parse is the most expensive, soft parse second. The latch contention caused by parse not only affects the speed of program operation, but also affects the program extensibility (scalable)

4. How to reduce hard parse

(1). Using Bound variables

This is one of the most performance-impacting factors in the programming approach, as described by other articles

(2). Programming specifications, good programming habits

The programming specification can specify the table name, whether the keyword is capitalized, how the space is used, and so on, all programmers follow the uniform specification.

As an example, the cursor_sharing default value in the database is exact, which means that when Oracle matches SQL, the following two sentences are mismatched, and the second sentence causes the hard parse

Select COUNT (*) from test_table where tracking_id=1234567688;

Select COUNT (*) from test_table where tracking_id=1234567688;

Of course the 1th is far more important than the 2nd, as you can imagine.

5. How to reduce soft parse

That is, no parse and softer soft parse, the trick is the cursor of Oracle, there are two ways

Step1. Skip Parse

In the subroutine, skip parse and use the following notation:

if (firsttime)

Parse

End If

Bind

Execute

Instead of writing this, because each invocation has a parse

Parse

Bind

Execute

Close

In Java, for example, each session is prepare to the SQL by preparestatement, rather than prepare once per call.

2. Plsql Auto Cache cursor

In Plsql, all static SQL is cache, and repeated calls do not soft parse. Note except for dynamic SQL.

DeclareI Number; J Number; K Number;beginI:=1; K:=12345678;  whileI<=10000LoopSelect Count(*) intoJ fromTest_tablewheretracking_id=K; I:=I+1; EndLoop;End;/

3. Session_cached_cursors Parameters

If this parameter is not 0, then in Sqlplus, when the same SQL has been soft for three times parse,oracle will move the cursor to the cache, and the 4th call will not soft parse, but will still be registered as parse,

The parse count (total) will still increase, and the session cursor cache hits will also increase.

This parameter affects the following tools:

• 1) Sqlplus
• 2) native dynamic SQL in Plsql
• 3) Bad syntax in Java, such as SQL without prepare and execute directly
• 4) Recursive SQL generated by Oracle

Different versions:

Oracle9i in session_cached_cursors default to 0,oracle 10g appears to be 20,ora11g default to 50, so in oracle9i, if you want to use this attribute, you need to modify the default value.

Another point to note is that soft parse indicates that after a session has been hard pasre, as long as it is still in the shared pool, no other session will need to parse again.

And Session_cached_cursor is for the same session. Therefore, if a program is frequently logon/logoff, this feature cannot be used.

Abalone New ********************

Plsql_ Performance Optimization Series 07_oracle parse bind variables parse bound variable