Home IT Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ SQL in the specific internal Oracle process     - DataGuard add temporary data files bug (Database)

- Install Web-based monitoring tool: Linux-Dash (Server)

- Linux System Getting Started Learning: Fix ImportError: No module named scapy.all (Linux)

- Linux modify the system time (Linux)

- MySQL TIMESTAMP and DATETIME (Database)

- Servlet 3.0 interfaces of AsyncListener (Programming)

- CentOS6 5 Configure SSH password Free (Linux)

- Java Foundation - The relationship between abstract classes and interfaces (Programming)

- How to use Android Studio development / debugging Android source code (Linux)

- DataGuard a hardware issue warnings found (Database)

- Analyzing Linux server architecture is 32-bit / 64-bit (Server)

- Binary tree traversal: the first sequence in order preorder recursive and non-recursive and traversal sequence (Programming)

- Linux virtual machine how to access the Internet in a virtual machine when using NAT mode (Linux)

- Seven Steps to Help Google Chrome Speed - (Linux)

- Java objects are taking up much space (Programming)

- Oracle DATABASE LINK (DBLINK) Create (Database)

- Several start-up mode of Tomcat (Server)

- Ubuntu PPA install SMPlayer 14.9 (Linux)

- Mount NFS network file system (Linux)

- VNC connection VMware vSphere ESXi 5.5 (Linux)

 
         
  SQL in the specific internal Oracle process
     
  Add Date : 2018-11-21      
         
       
         
  1, SQL resolution

     The first stage of SQL processing is SQL parsing. When an application issues a SQL statement, the application issues a parsing call to the database to prepare for execution. The parsing call opens or creates a cursor that is a handle to a session-specific private SQL area that contains Parsed SQL statements, and other processing information. Cursors and private SQL fields are located in the PGA.
     During the parsing call, the database performs the following checks:
           Grammar check
           Semantic check - whether the object and column exist
           Shared pool check
     The database performs a shared pool check to determine when to skip statement processing steps that take up a lot of resources. To do this, the database uses a hashing algorithm to generate a hash for each SQL statement. The hash value of the statement is the SQL_ID shown in V $ SQL.SQL_ID (see note: distinguishing four sql-related fields: hash_value, sql_hash_value, plan_hash_value, and sql_id). When a user submits a SQL statement, the database search share SQL zone to see if there is an existing parsed statement that has the same hash value. The hash value of the SQL statement is different from the following:
      The statement of the memory address value (V $ sql address field value)
      The statement executes the plan hash value (V $ SQL_PLAN view of the plan_hash_value field value)
 
     Based on the type of statement being submitted and the results of the hash check, the resolution operation is divided into the following categories:
      Hard resolution
        If the database can not reuse existing code, it must generate a new executable version of the application code, the second operation is called a hard parse, or the library cache misses. The database always performs a hard parse of DDL.
        During hard resolution, the database accesses the library cache and data dictionary cache multiple times to check the data dictionary. When the database accesses these areas, it uses a serialization device called a latch on the desired object so that their definitions are not altered. The contention of the latch increases the execution time of the statement and reduces concurrency.
      Soft analysis
         Analysis of any discomfort is a soft analytical soft analysis. If the submitted statement is the same as a reusable SQL statement in the share, the database reuses the existing code. Reuse code is also called library cache hits.
         In general, soft parsing is preferable to hard parsing because the database can skip the optimization and row source generation steps and go directly to the straight-line phase. The following figure is a simplified representation of a shared pool check for an update statement in a dedicated server architecture.

(It appears that the hash of the SQL text is generated in the PGA).
          If you check that a statement in the shared library has the same hash value, then the database performs semantic and environmental checks (workspace size or optimizer settings, etc.) and, of course, the statement itself (case, space, comments, etc.) ).
     Details can be found in the note: "Oracle performance tuning hardware analysis and soft analysis"
 
2, SQL optimization
     Query optimization is the process of selecting the most efficient means of executing SQL statements. The database is optimized for queries based on statistical information about the actual data being accessed. The optimizer uses the number of rows, dataset size, and other factors to generate a variety of possible execution plans and assign a cost value to each plan. The database uses the plan with the lowest cost.
     The database must perform at least one hard resolution for each unique DML statement and perform optimizations during hard resolution. DDL will never be optimized unless it includes DML components that need to be optimized, such as subqueries.
 
3, SQL line source generation
     A line-source generator is software that receives an optimized execution plan from the optimizer and generates an iteration plan called a query plan that is used by the rest of the database. A query plan takes the form of combining multiple steps, each returning a rowset. The rows in the collection can be used in the next step, and the last step of the fire is returned to the application that issued the SQL statement.
     A row source is a set of rows returned from a step in the execution plan, with a control structure that iterates over the rowset, which can be the result of a table, view, or join or grouping operation.
     The row source generator generates a row source tree, which is a collection of row sources. (That is, we see the implementation plan)
 
4, SQL implementation
     During execution, the SQL engine executes each row source in the number generated by the row source generator. This step is the only mandatory step in DML processing. In the implementation plan, we often see is an implementation of the tree, showing the line from one source to another. Often, the order in which steps are performed is almost the reverse of the order, so we should read the plan from the bottom up. The initial space in the operation column represents the hierarchical relationship. For example, if the name of an operation is preceded by two spaces, this operation is a sub operation of an operation that precedes a space. In front of a blank space is the select statement itself sub-operation.
     
         
       
         
  More:      
 
- Access.log Apache access log analysis and how to import it into MySQL (Server)
- stat Usage: Get permission to file the corresponding figures (Linux)
- A script to make your Ubuntu 14.04 Memory screen brightness (Linux)
- OpenNMS compile under Linux (Server)
- Linux some lessons learned about network security (Linux)
- How to install Gnome 3.10 in Ubuntu 13.10 (Linux)
- Linux ban single-user mode to enhance system security (Linux)
- Save the database data files into Oracle Learning (Database)
- User and user group management Linux Command (Linux)
- These days have been tossing in the Linux under the ASP.NET 5, on the next in the other operating systems in the ASP.NET 5 or. NET applications, in order to complete the MS VM (CoreCLR) run is not far Reach, the effect of the application.

Cur
(Server)
- PHP call a Python program (Programming)
- Install Ubuntu 14.04 and Windows 8 / 8.1 dual-boot in UEFI mode (Linux)
- MySQL backup tool to back up mydumper (Database)
- Ubuntu 12.04 64bit Install Source Insight 3.5 and create desktop icons (Linux)
- Firewall chapter of Linux server security configuration (Linux)
- How to install and use the malware detection tool LMD and ClamAV antivirus engine on Linux (Linux)
- How to install MySQL on Linux Dock (Database)
- MySQL function: group_concat () function (Database)
- OpenSIPS offline messaging feature set (Server)
- Which file system is most suitable for your Linux system (Linux)
     
           
     
  CopyRight 2002-2016 newfreesoft.com, All Rights Reserved.