Home IT Linux Windows Database Network Programming Server Mobile  
  Home \ Database \ SQL in the specific internal Oracle process     - Perl loop (Programming)

- The security administrator network analysis tools SATAN Introduction under Linux (Linux)

- Based on a complete solution RMI service to transfer large files (Programming)

- Django Signals from practice to source code analysis (Programming)

- Use preseed script to install Ubuntu 14.04 (Linux)

- Drawing from the Android source code analysis View (Programming)

- Java class HashSet (Programming)

- CentOS 6.x and CentOS7 install MPlayer (Linux)

- Incremental garbage collection mechanism for Ruby 2.2 (Programming)

- Quickly locate the mistakes by gdb location (Programming)

- using Docker Kitematic on windows (Linux)

- map and hash_map STL containers (Programming)

- The ORA-01113 error is handled with BBED without archiving (Database)

- CentOS installation Percona Server 5.5.42 compiling problem solve one case (Linux)

- 10 important Linux ps command combat (Linux)

- Role Object of registerNatives () method (Programming)

- PXE install CentOS 6.4 (Linux)

- Docker: Mirroring and container operations (Server)

- Linux firewall to prevent external network attacks (Linux)

- GAMIT 10.50 installed in Ubuntu 12.04 system (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.
- Zookeeper Rights Management and Quota Management (Server)
- How to use secure FTP file transfer (Server)
- Linux disk partition, format, mount the directory (Linux)
- Linux system security knowledge (Linux)
- Use SocketServer write a sockets program (Programming)
- Unsafe reboot start (Linux)
- New features of Java 9 HTTP2 and REPL (Programming)
- zBackup: A versatile tool to remove duplicate backup (Linux)
- Oracle Data Guard LOG_ARCHIVE_DEST_n optional attribute parameters (Database)
- How to install the Linux text editor Atom 0.124.0 (Linux)
- Linux (CentOS) directory file management and file system file compression packing (Linux)
- RHEL 6.6 install GNOME desktop environment (Linux)
- Why do I prefer Git (Linux)
- Android Studio Getting Started Hello World (Programming)
- RHEL7 system making use of OpenStack mirror (Linux)
- Introduction to Linux system process monitoring tools (Linux)
- Top command: the Task Manager under linux (Linux)
- Realization of Linux operating system illegal IP censorship (Linux)
- CentOS network configuration 7, and set the host name and IP-bound problems (Linux)
- Java input and output common class Scanner (Programming)
  CopyRight 2002-2016 newfreesoft.com, All Rights Reserved.