{"id":11670,"date":"2019-10-29T12:26:34","date_gmt":"2019-10-29T12:26:34","guid":{"rendered":"https:\/\/ittutorial.org\/?p=11670"},"modified":"2019-11-27T11:43:30","modified_gmt":"2019-11-27T11:43:30","slug":"sql-statement-processing-in-oracle-database","status":"publish","type":"post","link":"https:\/\/ittutorial.org\/sql-statement-processing-in-oracle-database\/","title":{"rendered":"SQL Statement Processing in Oracle Database"},"content":{"rendered":"

Hi,<\/p>\n

I will explain\u00a0SQL Statement Processing in Oracle Database in this article.<\/p>\n

<\/p>\n

 <\/p>\n

You should read the following article before this to learn Oracle database architecture detailed.<\/p>\n

https:\/\/ittutorial.org\/oracle-database-architecture-3\/<\/p>\n

 <\/p>\n\n

 <\/p>\n

SQL statements processing are as follows.\u00a0 This steps are very important to understand architecture of Oracle database.<\/p>\n

 <\/p>\n

\"\"<\/p>\n

 <\/p>\n

SQL Statement Processing Steps are as follows.<\/span><\/p>\n

    \n
  1. Create a cursor. (\u00a0It\u00a0contains information for statement processing<\/span>)<\/li>\n
  2. Parse the statement. (\u00a0Representation of SQL created and moved into the\u00a0shared SQL area if there is no identical SQL in the shared\u00a0SQL area<\/span> )<\/li>\n
  3. Describe query results. ( This step provides information about the select list\u00a0items<\/span> )<\/li>\n
  4. Define query output.\u00a0\u00a0(it\u00a0defines location, size, and data type\u00a0information required to store fetched values in variables<\/span>\u00a0)<\/li>\n
  5. Bind variables.<\/li>\n
  6. Parallelize the statement.<\/li>\n
  7. Execute the statement.<\/li>\n
  8. Fetch rows of a query.<\/li>\n
  9. Close the cursor.<\/li>\n<\/ol>\n

     <\/p>\n

    When any SQL is sent Oracle for the first time, Oracle will create a plan by following the steps below. Or, if the plan has been created, the existing plan will be used or new plan will be created even if the SQL is the same (different literal values, or not using bind variable ).<\/p>\n

    \"\"<\/p>\n

     <\/p>\n

     <\/p>\n\n

     <\/p>\n

    If a new SQL is not exists in the Shared pool, it must be parsed from scratch.<\/p>\n

    The cost of creating the plan for the first time is very high for Oracle database.<\/p>\n

     <\/p>\n

    SQL Statement implementation figure is as follows.<\/p>\n

    \"\"<\/p>\n

     <\/p>\n

     <\/p>\n

    If any SQL will run for the first time, Oracle has to do a hard parse.<\/strong> If this SQL has been run at least once before, that is, Oracle does a Soft parse <\/strong>for this execution.<\/strong><\/p>\n

     <\/p>\n

    \"\"<\/p>\n

     <\/p>\n

     <\/p>\n

    Do you want to learn Oracle Database Performance Tuning detailed, then read the following articles.<\/span><\/h4>\n

    https:\/\/ittutorial.org\/oracle-database-performance-tuning-tutorial-12-what-is-the-automatic-sql-tuning-and-how-to-automated-sql-tuning\/<\/p>\n","protected":false},"excerpt":{"rendered":"

    Hi, I will explain\u00a0SQL Statement Processing in Oracle Database in this article.<\/p>\n","protected":false},"author":1,"featured_media":11668,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"_uf_show_specific_survey":0,"_uf_disable_surveys":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[5047,1994,3019,5085,6603],"tags":[6631,6627,2046,6629,6624,6617,6628,6621,6618,6626,6623,6619,6622,6630,6625,6620],"class_list":["post-11670","post","type-post","status-publish","format-standard","has-post-thumbnail","","category-architecture","category-oracle","category-oracle-sql","category-performance-tuning","category-sql-tuning","tag-how-to-find-hard-parse-sql-in-oracle","tag-life-cycle-of-sql-statement-in-oracle","tag-oracle-architecture","tag-oracle-logical-query-processing","tag-oracle-sql-parsing","tag-order-of-execution-of-sql-query-in-oracle","tag-parse-oracle-sql","tag-phases-of-query-processing","tag-query-execution-steps-in-oracle","tag-query-execution-steps-in-sql-server","tag-query-processing-and-optimization-in-dbms","tag-query-processing-in-dbms","tag-query-processing-steps","tag-sql-process-architecture","tag-sql-query-execution-steps-in-oracle","tag-sql-query-parsing"],"aioseo_notices":[],"jetpack_featured_media_url":"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/10\/oracle-sql-statement-processing.png","jetpack_sharing_enabled":true,"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/11670","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/comments?post=11670"}],"version-history":[{"count":4,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/11670\/revisions"}],"predecessor-version":[{"id":12832,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/11670\/revisions\/12832"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/media\/11668"}],"wp:attachment":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/media?parent=11670"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/categories?post=11670"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/tags?post=11670"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}