Differences

This shows you the differences between two versions of the page.

--- oracle_sql_processing [2019/08/11 07:49] – [Overview] andonovj
+++ oracle_sql_processing [2020/10/19 07:29] (current) – 86.49.253.137
@@ Line 1: / Line 1: @@
 =====Overview=====
+The SQL processing like any other processing of code. It comes from a human and needs to go back to a human :) The problem is, through how many stops it will pass
+Below you can see a basic representation on the possible wait times for any query:
+{{ :sqlprocessing.jpg?500 |}}
+This picture, clearly displays why it isn't ALWAYS A DATABASE FAULT :) Often the database is intermediary point between the User and the data.
+=====Calls=====
+Before we start speaking of SQL processing, we should discuss something very important and that is how ORACLE communicate and operate in general.
+Oracle kernel is using requests called "calls". These "calls" represent request to certain structures and are generally devided into two groups:
+DB Calls:
+  * Parse
+  * Execute
+  * Fetch
+Operation System (OS) Calls:
+  * WAIT
+  * STAT
+These calls are accomplished using different functions:
+{{ :sql_process_calls.gif?600 |}}
+=====SQL Processing =====
 SQL Processing is one of the biggest problems with any database because it is the hearth of performance tuning. In that chapter I will try to summarize, how Oracle processes an SQL.
 Firstly the statement has to be processed and there are several steps which should be executed.
+Below you can check a brief picture of Cursor Processing:
+{{ :cursorparsing.jpg?600 |}}
+[[oracle_sql_processing_parse| Parsing]]
   * Open Cursor
   * Syntax Check
   * Semmantic Check
-  * Shared Pool check
+  * [[oracle_sql_processing_shared_pool_check| Shared Pool check]]
   * Binding
   * *Optimization
   * *Row source generation
+[[oracle_sql_processing_execute| Execute]]
   * Execution
+[[oracle_sql_processing_fetch| Fetching]]
   * Fetch results
   * Close Cursor
-*-Optional steps in case of hard parse
+*- Only when hard parse is needed
-Below you can check a brief picture of Cursor Processing:
-{{ :cursorparsing.jpg?600 |}}
-=====Parsing=====
-Parsing can start and end the processing of SQL, depending on type of parse. Generally there are 4 types of parsing:
-  - No Parse
-  - Soft-Soft Parse
-  - Soft Parse
-  - Hard Parse
-If the query doesn't need parsing, the processing of SQL will go directly to EXECUTING phase (however that is rarely the case, we will see in a sec:
-====No Parse====
-The most significant difference of this one from the previous three is it will not increase the parse count. The reason is obviously “No parse at all ” – execute the code by using given bind variables. It the best if a same SQL run multiple times.
-This is the same example as presviouly but moved the open/parse/close cursor outside.
-===Example===
-<sxh bash>
-m_cursor := dbms_sql.open_cursor;
-dbms_sql.parse(
-               m_cursor,
-               'select n1 from t1 where id = :n',
-               dbms_sql.native
-               );
-dbms_sql.define_column(m_cursor,1,m_n);
-for i in 1..1000 loop
-  dbms_sql.bind_variable(m_cursor, ':n', i);
-  m_rows_processed := dbms_sql.execute(m_cursor);
-  if dbms_sql.fetch_rows(m_cursor) > 0 then
-    dbms_sql.column_value(m_cursor, 1, m_n);
-  end if;
-end loop;
-dbms_sql.close_cursor(m_cursor);
-</sxh>
-====Soft-Soft Parse====
-This one is still called soft parse in Oracle’s document. I don’t think Oracle give it a proper name to make it easy to understand. Sometimes DBA call it soft-soft parse. When you run a SQL a few times in the same session (with session_cache_cursors enabled), Oracle will create a short cut in your session memory, so when you run it next time, oracle don’t need to do the search work. It knows where it’s already. It’s cheaper in cost than soft parse.
-Here is an example in the book(although it’s using pl/sql cursor cache, the effect is the same):
-===Example===
-<sxh bash>
-for i in 1..1000 loop
-  m_cursor := dbms_sql.open_cursor;
-  dbms_sql.parse(
-                 m_cursor,
-                 'select n1 from t1 where id = :n',
-                 dbms_sql.native
-                 );
-  dbms_sql.define_column(m_cursor,1,m_n);
-  dbms_sql.bind_variable(m_cursor, ':n', i);
-  m_rows_processed := dbms_sql.execute(m_cursor);
-  if dbms_sql.fetch_rows(m_cursor) > 0 then
-    dbms_sql.column_value(m_cursor, 1, m_n);
-  end if;
-  dbms_sql.close_cursor(m_cursor);
-end loop;
-</sxh>
-====Soft Parse====
-This one is the second easiest to understand. It’s also the second expensive one. When you submit a SQL, oracle searches the library cache, and it found a plan matches your request exactly. Oracle didn’t need to create a plan but need to do the search work. Some notes from the book about the work oracle need to do:
-When you pass a piece of text to Oracle it will do a syntax check to decide if it is legal, then it will search the library cache for a matching text (using a hash value computed from the text).
-If it finds a textual match Oracle starts the semantic check—checking to see if the new text actually means the same as the existing text (same objects, same privileges, etc.); this is known as cursor authentication.
-If everything matches, then the session need not optimize the statement again.
-====Hard Parse====
-This one is the easiest one to understand. It’s also the most expensive one.
-Whenever the fist time oracle run a SQL, there is no plan about this SQL in the memory.
-Oracle needs to create a plan(optimizing) and save it in library cache. That’s the hard parse. Oracle will go through the entire three and generate all possible plans. Then Oracle will compare each of the plans to find the  best.
-As you can imagine this requires A LOT OF resources, we will discuss in a bit.
-{{ :hard_parse.gif?500 |}}