HardParse&SoftParse

DDL每次執(zhí)行都需要進行硬解析。 SQL 解析過程 Oracle對此SQL將進行幾個步驟的處理過程： 1、語法檢查(syntax check): 檢查此sql的拼寫是否語法。 2、語義檢查(semantic check): 諸如檢查sql語句中的訪問對象是否存在及該用戶是否具備相應的權限。 3、對sql語

DDL每次執(zhí)行都需要進行硬解析。

SQL 解析過程

Oracle對此SQL將進行幾個步驟的處理過程：

1、語法檢查(syntax check): 檢查此sql的拼寫是否語法。

2、語義檢查(semantic check): 諸如檢查sql語句中的訪問對象是否存在及該用戶是否具備相應的權限。

3、對sql語句進行解析(prase): 利用內部算法對sql進行解析，生成解析樹(parse tree)及執(zhí)行計劃(execution plan)。

4、執(zhí)行sql，返回結果(execute and return)

5個執(zhí)行步驟：

1：語法分析

2：權限與對象檢查

3： 在共享池中檢查是否有完全相同的之前完全解析好的. 如果存在，直接跳過4和5，運行Sql, 此時算soft parse.

4：選擇執(zhí)行計劃

5：產生執(zhí)行計劃

3的解釋：

Oracle將會對傳遞進來的SQL語句使用HASH函數(shù)運算得出HASH值，再與共享池中現(xiàn)有語句的HASH值進行比較看是否一一對應。現(xiàn)有數(shù)據(jù)庫中SQL語句的HASH值我們可以通過訪問v$sql、v$sqlarea、v$sqltext等數(shù)據(jù)字典中的HASH_VALUE列查詢得出。

如果SQL語句的HASH值一致，那么ORACLE事實上還需要對SQL語句的語義進行再次檢測，以決定是否一致。那么為什么Oracle需要再次對語句文本進行檢測呢？不是SQL語句的HASH值已經對應上了？事實上就算是SQL語句的HASH值已經對應上了，并不能說明這兩條SQL語句就已經可以共享了。

Dictionary Cache

The data dictionary is a collection of database tables and views containing reference information about the database, its structures, and its users. Oracle accesses the data dictionary frequently during SQL statement parsing. This access is essential to the continuing operation of Oracle.

The data dictionary is accessed so often by Oracle that two special locations in memory are designated to hold dictionary data. One area is called the data dictionary cache, also known as the row cache because it holds data as rows instead of buffers (which hold entire blocks of data). The other area in memory to hold dictionary data is the library cache. All Oracle user processes share these two caches for access to data dictionary information.

Parsing

Parsing is one stage in the processing of a SQL statement. When an application issues a SQL statement, the application makes a parse call to Oracle. During the parse call, Oracle:

Checks the statement for syntactic and semantic validity

Determines whether the process issuing the statement has privileges to run it

Allocates a private SQL area for the statement

Oracle also determines whether there is an existing shared SQL area containing the parsed representation of the statement in the library cache. If so, the user process uses this parsed representation and runs the statement immediately. If not, Oracle generates the parsed representation of the statement, and the user process allocates a shared SQL area for the statement in the library cache and stores its parsed representation there.

Note the difference between an application making a parse call for a SQL statement and Oracle actually parsing the statement. A parse call by theapplication associates a SQL statement with a private SQL area. After a statement has been associated with a private SQL area, it can be run repeatedly without your application making a parse call. A parse operation by Oracle allocates a shared SQL area for a SQL statement. Once a shared SQL area has been allocated for a statement, it can be run repeatedly without being reparsed.

Both parse calls and parsing can be expensive relative to execution, so perform them as seldom as possible.

聲明：本網頁內容旨在傳播知識，若有侵權等問題請及時與本網聯(lián)系，我們將在第一時間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com