Shulou(Shulou.com)06/01 Report--

In Oralce, a sql statement generates a lot of information when it is executed:

SQL unique ID

SQL text message

Bind variable information

Execution plan and cursor information

Statistical information

Performance information

Other information. For example, sql source, status, etc.

SQL_ID

In Oracle, the SQL optimizer is responsible for parsing the sql (including sql directly executed and sql in the stored procedure). Before submitting the sql optimizer for parsing, the sql does some preprocessing, including case, space, comment processing, and so on.

When parsing a sql, the sql optimizer allocates an ID (subcursor) that uniquely identifies a sql (stored in the v$sql view). The same sql text sql_id is the same (corresponding to the data in v$sqlarea, call it the parent cursor), even in different database instances, which will be described later. Other views refer to the sql through this id, which corresponds to the field hash_value in 9i. In 11g, v$sql has fields old_hash_value corresponding to 9i. The hash_value,sql_id and hash_value of 9i are calculated by a certain algorithm.

1. Sql executed directly:

Execute under sql_window

Select / * test*/* from scott.emp e where e.empno = 10 * select / * test*/* from scott.emp e where e.empno = 10 * select / * test*/* from scott.emp E where e.empno = 10

Query their sql information

Select * from v$sql v where v.SQL_TEXT like'% / * test*/%'

You can see that if there is any difference between two sql texts, different sql_id will be generated.

two。 Sql in the stored procedure:

There are two types of static sql and dynamic sql. Dynamic sql is the same as the first directly executed sql. Let's take a look at static sql.

Declare v number;begin select / * + test1*/e.sal into v from scott.emp e where e.empno = 7369; select / * + test1*/e.sal into v from scott.emp e where e.empno = 7369; select / * + test1*/e.sal into v from scott.emp E where e.empno = 7369; end;/

After execution, query sql information. Prompt / * + xxx*/, comments should be used here will be filtered out.

As you can see from the figure, only one sql_id is generated by the three sql statements, indicating that plsql did some preprocessing when it was submitted to the sql optimizer.

What if you use binding variables? Let's see what's changed.

Declare v dbms_sql.Number_Table; xx number;begin v (1): = 7369; v (1): = 7499; v (1): = 7521; for i in v.first. V.last loop select / * + test2*/e.sal into xx from scott.emp e where e.empno = v (I); end loop;end;/

Let's take a look at sql_id.

We see the sql_id message with only one sql statement. E.empno = v (I) is replaced with the binding variable: B, when the sql is committed to memory for execution, it is replaced with a specific value and the result is returned.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.