Quote from
Difference Between Soft Parse and Hard Parse
- Thanks for the Question Regarding "Difference Between Soft Parse and Hard Parse", Version 8.1.7.2ORIGINALLY Submitted On 26-JAN-2002 19:17 Eastern US Time, Last Updated 5-Aug-2004 13:07
Hi Tom
Can You Explain Briefly The Difference Between Soft and Hard Parse?
Can you give me a detailed introduction of soft resolution and hard ethnas?
And We Said ...
Here Is A Long Winded Answer, IT IS Extracted In Part From A New Book Oming Out Soon "Beginning Oracle Programming" That i Collaborate ON:
This is a very long explanation, which is extracted from the new book I combined with a few colleagues << began to learn Oracle Programming >>.
Parsing
Analyze
THIS The First Step in The Processing of any Statement in Oracle. Parsing IS
THE ACT OF BREAKING THE SUBMITTED DOWN ITO ITS Component Parts? Determining What Type of Statement It Is (Query, DML, DDL) and Performing Various Checks on It.
This is the first step in Oracle handles all statements. The resolution is to divide the statement submitted into different components (according to certain rules) to determine what type of statement (Query, query, DML, data manipulation language, DDL or data definition languages) to perform a variety of different syntax tests on this statement.
The Parsing Process Performs Two Main Functions:
The parsing process mainly has two functions.
SYNTAX CHECK: IS THE STATEMENT A VALID ONE? Does It make Sense Given The SQL Reference Manual. Does It Follow All of the rules for sql.1. Syntax check, is this statement reasonable? Is it compliant with SQL? Grammar documentation <
> The relevant grammar is not all rules of the SQL statement.
o Semantic Analysis:? Going beyond the syntax is the statement valid in light of the objects in the database (do the tables and columns referenced exist) Do you have access to the objects are the proper privileges in place Are there ambiguities in.?? The Statement? for EXAMPLE IF THERE TABLES T1 and T2 and Both Have a Column X, The Query? SELECT X from T1, T2 WHERE ?? IS AmbIGUOUS, WE DON? T Know Which Table To Get X from. And so on .
2. Semantic Analysis: Have you handled syndrome? This statement is correctly referenced in the database (all tables and fields in the statement exist). Does there have access to these objects? Is it a corresponding permissions? It is effective (mainly referring to the authority corresponding to the ROLE,
), Such as whether there are two tables T1, T2, and the two forms have the same field name column x, and query statement
? SELECT X from T1, T2 WHERE ??, (there is no specified column name), we cannot know which table gets the value of the field x, and a similar series of issues.
So, You Can Think of Parsing as Basically A Two Step Process, That of A Syntax
check to check the validity of the statement and that of a semantic check? to ensure the statement can execute properly. The difference between the two types of checks are hard for you to see? Oracle does not come back and say? it failed the syntax Check ?, rather it returns the statement with a error code and message. so for example, this Statement Fails with a syntax error:
Therefore, you can think that parsing is basically composed of these two steps: checking statements validity
Syntax check and ensure that statements can run normally
Semantic check. The difference between these two checks is that you have to say. Oracle does not specifically point out this statement does not pass the syntax check, it returns the error code and error message corresponding to this statement. For example: below This statement does not have a syntax check.
Code: [Copy to CLIPBOARD]
SQL> Select from where 2; select from where 2 * error at line 1: ORA-00936: Missing Expression
While this Statement Failed with a semantic error? If the table not_a_table existed and we had permission to access it, this statement would succeeded:
And this statement does not pass semantics? If the table not_a_table exists, and we have permissions to access this table, this statement will be implemented correctly.
Code: [Copy to CLIPBOARD]
SQL> Select * from not_a_table; select * from not_a_table * error at line 1: ORA-00942: Table or view does not exist
That is the thing way to real tell the Difference Between a semantic and
syntactic error? if the statement COULD have executed given the proper objects and privileges, you had a semantic error, otherwise if the statement could not execute under any circumstances, you have a syntax error. Regardless? Oracle will not execute the statement for you!
It is only the only way I can use to explain the symptom error and semantic errors. If this statement is in the case of imparting the appropriate object and permissions
If it is indeed possible, we call it a semantic error, otherwise, if this statement cannot be executed under any conditions, we call it
The syntax error. In any case, Oracle will not run this statement for you.
The next step in the parse operation is to see if the statement we are currently parsing has already in fact been processed by some other session. If it has? We may be in luck here, we can skip the next two steps in the process, that of optimization and row source generation. If we can skip these next two steps in the process, we have done what is known as a Soft Parse? a shorter process to getting our query going. If we can not, if we must do all of the steps, we are performing what is known as a Hard Parse? we must parse, optimize, generate the plan for the query. This distinction is very important. When developing our applications we want a very high percentage of our queries to be Soft Parsed ? to be able to skip the optimize / generate phases? as they are very CPU intensive as well as a point of contention (serialization). If we have to Hard Parse a large percentage of our queries, our system will function slowly and in some Case? Not at all.
The next step in parsing is to check if the statement we are analyzing is already being performed by other sessions. In this step we may be lucky, we can skip the following two steps: statements optimization (generated the implementation plan) and Generate execution coding. If we can skip these two steps, we have achieved the general sense.
Soft parsing, we must resolve, optimize, and generate an execution plan for this statement. This feature is very important. When we develop applications, we will want most of the statements to be soft analytical, to skip Steps to optimize and generate the encoding, because they are the same as the contention (serialization,), are very consuming CPU operations. If we must
Hardly analyzes most of our statement, in some cases, our system is very slow,
Not any time (in OLAP, the DSS system should be an exception
)
The way this sharing of SQL in Oracle is accomplished is via the shared pool, a piece of memory in the SGA maintained by Oracle. We covered this topic in chapter 5 but will revisit it again in the context of processing a query. After Oracle parses ?. the query and it passes the syntax and semantic checks it will look in the shared pool component of the SGA to see if that same exact query has already been processed by another session Since it has performed the semantic check it has already figured out: The way to share SQL in Oracle is to achieve a piece of memory in SGA (maintained by the Oracle system) through the shared pool. We discussed this topic in Chapter 5 and discussed again in the chapter of the query. This topic. When Oracle has paid this statement, and the Oracle will query in SGA's Shared Pool components through syntax and semantic inspection, see if there is a exactly the same statement. Executed by another session. Because the statement has passed the semantic check of Oracle, and Oracle has calculated:
o Exactly What Tables Are INVOLVED
What tables are specifically involved.
o That We Have Access To The Tables (The Proper Privileges Are The There)
We have a corresponding permission to access the corresponding table. Wait ...
And So on. Now, IT CAN Look at all of the queries in the shared pool..
Now, you can see if the corresponding statement has been parsed at all that has been parsed and optimized and generated.
Soft Parse and session_cashed_cursor parameter January 28, 2002 Reviewer: a Reader
Soft parsing with session_cashed_cursor parameters
CAN you explain what means the default value (0) of session_ casched_cursor parameter? Oracle Always Has A Cache of Sqls in SGA And Size of this cache is determinated through init.ora parameter shared_pool_size.
Tom:
Can you explain it to me, the meaning of session_cashed_cursor (default 0) parameter, Oracle always maintains a cache of a SQL statement in SGA. This cache is determined by the size of the initialization parameter shared_pool_size.
FOLLOWUP:
The default value of zero means this by default, zero cursors will be cached for your session.
The default is 0, indicating that Oracle does not give you the SESSION cache by default.
They will be cached in the shared pool -. But your session will have to findthem there Session Cached Cursors can remove the need to have to "find" them Consider this example that shows the difference spent when soft parsing without session cached cursors vs. Soft Parsing with session cached capors:
They will be cached in shared pool, but your session must go to find them in the shared pool, Session Cached Cursors can omit the steps to find this step in the shared pool. Below this example
Give you a demonstration
No cache cursor and
Cache the difference between the soft parsing after the cursor and the system of system consumption resources
Code: [Copy to CLIPBOARD]
Ops $ [email] tkyte@ora817dev.us.racle.com [/ email]> CREATE TABLE EMP AS SELECT * from Scott.emp; Table Created.Ops $ [email] tkyte@ora817dev.us.oracle.com [/ email ]> CREATE TABLE RUN_STATS (Runid Varchar2 (15), Name Varchar2 (80), Value Int; Table Created.ops $ [Email] Tkyte@ora817dev.us.racle.com [/ email]> Create or Replace View Stats 2 As SELECT 'Stat ...' || A.Name Name, B.Value 3 from V $ STATNAME A, V $ MyStat B 4 where a.statistic # = B.Statistic # 5 Union All 6 Select 'Latch.' | | Name, Gets 7 from v $ latch; view created.ops $ [email] tkyte@ora817dev.us.racle.com [/ email]> Column name format a40Ops $ [email] tkyte@ora817dev.us.racle.com [ / email]> declare 2 l_start number; 3 l_cnt number; 4 begin 5 execute immediate 'alter session set session_cached_cursors = 0'; 6 insert into run_stats select 'before', stats * from stats; 7 8 l_start:. = dbms_utility.get_time ; 9 for i in 1 .. 100010 loop11 Execute Immediate ' select count (*) from emp 'into l_cnt; 12 end loop; 13 dbms_output.put_line ((dbms_utility.get_time-l_start) ||' hsecs'); 14 15 execute immediate 'alter session set session_cached_cursors = 100'; 16 insert into Run_stats select 'after 1', stats. * from stats; 17 18 l_start: = dbms_utility.get_time; 19 for i in 1 .. 100020 loop21 execute immediate 'select count (*) from EMP' INTO L_CNT; 22 end loop; 23 DBMS_OUTPUT.PUT_LINE ((DBMS_UTILITY.GET_TIME-L_START) || 'HSECS'); 24 25 INSERT INTO RUN_STATS SELECT 'AFTER 2', Stats. * from stats; 26 End;
27/45 HSECS35 HSECSPL / SQL Procedure SuccessFully Completed.so, Session Cached Cursors Ran Faster (I Ran this a couples, there is........................................... ..
Therefore, in the session cached cursor, it can run faster (I have run several times, there is no hard analysis), and the truly promising news is:
Code: [Copy to CLIPBOARD]
Ops $ [email] tkyte@ora817dev.us.racle.com [/ email]> SELECT A.NAME, B.Value-a.Value Run1, C.Value-B.Value Run2, 2 ((C.Value-B) .value) - (B.Value-a.value) Diff 3 from run_stats a, run_stats b, run_stats c 4 where a.name = B.Name 5 and b.name = c.name 6 and a.runid = ' Before '7 and b.runid =' after 1 '8 and c.Runid =' after 2 '9 and (c.Value-a.value)> 010 and (C.Value-B.Value) <>
(B.Value-a.value) 11 ORDER BY ABS ((C.Value-b.Value) - (B.Value-a.value) 12 / Name Run1 Run2 DIFF ---------- ------------------------------------------------------------------------------------------------------------------------------------------ ---------- Latch.checkpoint Queue Latch 3 4 1LATCH.REDO Allocation 30 31 1Stat ... consistent gets 5088 5089 1stat ... Deferred (Current) Block Cleanout 2 3 1ApplicationSstat ... Calls to get Snapshot SCN:
KCMGSS 5019 5018 -1STAT ... Enqueue Releases 10 9-1stat ... Execute Count 1015 1014-1stat ... Opened Cursors Cumulative 1015 1014 -1stat ... Parse Count (Total) 1015 1014 -1stat ... session cursor Cache count 0 1 1st ... Redo Entries 28 27-1stat ... Recursive Calls 1180 1179-1stat ... Physical Reads 1 0-1latch.direct MSG LATCH 2 0 -2latch.session Queue Latch 2 0 -2latch.done Queue Latch 2 0 -2Stat ... free buffer Requested 8 6 -2stat ... enqueue Requests 11 9 -2latch.Messag ES 3 0 -3STAT ... DB Block Changes 47 44 -3latch.redo Writing 3 0 -3latch.ksfv Messages 4 0 -4Stat ... session logical reads 17128 17123 -5latch.row cache objects 184 178 -6stat ... DB Block Gets 12040 12034-6Stat ... Parse Time Elapsed 9 3 -6Stat ... Parse Time CPU 13 4 -9Stat.
..recursive cpu usage 51 38 -13LATCH.cache buffers chains 34315 34335 20STAT ... redo size 23900 24000 100STAT ... session cursor cache hits 3 1002 999LATCH.shared pool 2142 1097 -1045LATCH.library cache 17361 2388 -1497334 rows selected .ops $ [email] tkyte@ORA817DEV.US.ORACLE.COM [/ email]> see the significantly REDUCED number of LATCH counts on the library and shared pool. Since a latch is a lock, a lock is a serialization device, serialization Implies Waits - Using the session cached caded Cursors Will Increase Scalability and Performance As you add more and more users. Its Not Only Faster, But More Scalable As Well ...
See the obvious number of latches in the library cache and shared pool
Did you fall, because
Latch is
Lock,
Lock (LOCK) is only
Serial execution equipment,
Serial means
Waiting - the cursor using the session cache can improve the system
Scalence (Scalability) and
Performance, when your system's number of users is increasing, it can not only run more quickly, but also improve system scalability.
Original address: http://www.oracle.com.cn/viewthread.php? TID = 26895
