Detailed implementation plan

2011-03-09  来源:本站原创  分类:Database  人气:98 

First, what is the implementation plan An explain plan is a representation of the access path that is taken when a query is executed within Oracle. Second, how to access data At the physical level Oracle reads blocks of data. The smallest amount of data read is a single Oracle block, the largest is constrained by operating system limits (and multiblock i / o). Logically Oracle finds the data to read by using the following methods: Full Table Scan (FTS) - full table scan Index Lookup (unique & non -unique) - Index Scan (the only non-unique) Rowid - physical line id Third, the implementation plan hierarchy When looking at a plan, the rightmost (ie most inndented) uppermost operation is the first thing that is executed. - the most right-most on the first look at the implementation of the principle of hierarchy, with a child ID if an action does not execute on the first 1 to see a simple example: Query Plan ----------- ------------------------------ SELECT STATEMENT [CHOOSE] Cost = 1234 ** TABLE ACCESS FULL LARGE [: Q65001] [ANALYZED ] - [: Q65001] that is parallel, [ANALYZED] indicates that the object has been analyzed in the optimization mode is CHOOSE case, see Cost parameter values ​​to decide whether CBO or RBO: SELECT STATEMENT [CHOOSE] Cost = 1234 - Cost has a value, using the CBO SELECT STATEMENT [CHOOSE] Cost = - Cost is empty, the use of RBO 2. level of parent-child relationship, see more complex examples: PARENT1 ** FIRST CHILD **** FIRST GRANDCHILD ** SECOND CHILD Here the same principles apply, the FIRST GRANDCHILD is the initial operation then the FIRST CHILD followed by the SECOND CHILD and finally the PARENT collates the output. Fourth, the example will explain Execution Plan ------------ ---------------------------------------------- 0 ** SELECT STATEMENT Optimizer = CHOOSE (Cost = 3 Card = 8 Bytes = 248) 1 0 ** HASH JOIN (Cost = 3 Card = 8 Bytes = 248) 2 1 **** TABLE ACCESS (FULL) OF 'DEPT' (Cost = 1 Card = 3 Bytes = 36) 3 1 **** TABLE ACCESS (FULL) OF 'EMP' (Cost = 1 Card = 16 Bytes = 304) left two rows of data in front of the serial number ID, the back is the corresponding PID (parent ID). A shortened summary of this is: Execution starts with's child objects So it executes its first child step: 's child objects So it executes its first child step:' DEPT 'Then the second child step:' EMP 'Rows are returned to the parent step (s) until finished five table access method 1.Full Table Scan (FTS) full table scan In a FTS operation, the whole table is read up to the high water mark (HWM). The HWM marks the last block in the table that has ever had data written to it. If you have deleted all the rows then you will still read up to the HWM. Truncate resets the HWM back to the start of the table. FTS uses multiblock i / o to read the blocks from disk. - full table scan mode will read the data to the table high-water mark (HWM means that the tables have extended the last block), read speed depends on the Oracle initialization parameter db_block_multiblock_read_count Query Plan ----- ------------------------------- SELECT STATEMENT [CHOOSE] Cost = 1 ** INDEX UNIQUE SCAN EMP_I1 - if the index to find the desired data, it will not go visit a 2.Index Lookup table index scan There are 5 methods of index lookup: index unique scan - index unique scan Method for looking up a single key value via a unique index . always returns a single value, You must supply AT LEAST the leading column of the index to access data via the index. eg: SQL> explain plan for select empno, ename from emp where empno = 10; index range scan - index of local Scan Index range scan is a method for accessing a range values ​​of a particular column. AT LEAST the leading column of the index must be supplied to access data via the index. Can be used for range operations (eg>> = explain plan for select mgr from emp where mgr = 5; index full scan - a global index scan Full index scans are only available in the CBO as otherwise we are unable to determine whether a full scan would be a good idea or not. We choose an index Full Scan when we have statistics that indicate that it is going to be more efficient than a Full table scan and a sort. For example we may do a Full index scan when we do an unbounded scan of an index and want the data to be ordered in the index order. eg: SQL> explain plan for select empno, ename from big_emp order by empno, ename; index fast full scan - Fast Global index scan, without order by the case often occurs Scans all the block in the index, Rows are not returned in sorted order, Introduced in 7.3 and requires V733_PLANS_ENABLED = TRUE and CBO, may be hinted using INDEX_FFS hint, uses multiblock i / o, can be executed in parallel, can be used to access second column of concatenated indexes. This is because we are selecting all of the index. eg: SQL> explain plan for select empno, ename from big_emp; index skip scan - the index skip scan, Where conditions are listed non-leading column of the index case often occurs Index skip scan finds rows even if the column is not the leading column of a concatenated index. It skips the first column (s) during the search. Eg: SQL> create index i_emp on emp (empno, ename); SQL> select job from emp where ename = 'SMITH'; 3.Rowid physical ID scan This is the quickest access method available.Oracle retrieves the specified block and extracts the rows it is interested in. - Rowid scanning is the fastest way to access data in six tables connection with three connections: 1.Sort Merge Join (SMJ) - Since sort is a very resource-intensive, so this connection to avoid Rows are produced by Row Source 1 and are then sorted Rows from Row Source 2 are then produced and sorted by the same sort key as Row Source 1. Row Source 1 and 2 are NOT accessed concurrently. SQL> explain plan for select e.deptno, d.deptno from emp e, dept d where e.deptno = d.deptno order by e.deptno, d.deptno; Query Plan ------------------------ ------------- SELECT STATEMENT [CHOOSE] Cost = 17 ** MERGE JOIN **** SORT JOIN ****** TABLE ACCESS FULL EMP [ANALYZED] **** SORT JOIN ****** TABLE ACCESS FULL DEPT [ANALYZED] Sorting is an expensive operation, especially with large tables. Because of this, SMJ is often not a particularly efficient join method. 2.Nested Loops (NL) - more efficient a connection method Fetches the first batch of rows from row source 1, Then we probe row source 2 once for each row returned from row source 1. For nested loops to be efficient it is important that the first row source returns as few rows as possible as this directly controls the number of probes of the second row source. Also it helps if the access method for row source 2 is efficient as this operation is being repeated once for every row returned by row source 1. SQL> explain plan for select a.dname, b.sql from dept a, emp b where a.deptno = b.deptno; Query Plan ------------------------- SELECT STATEMENT [CHOOSE] Cost = 5 ** NESTED LOOPS **** TABLE ACCESS FULL DEPT [ANALYZED] **** TABLE ACCESS FULL EMP [ANALYZED] 3.Hash Join - a connection most efficient way New join type introduced in 7.3, More efficient in theory than NL & SMJ, Only accessible via the CBO. Smallest row source is chosen and used to build a hash table and a bitmap The second row source is hashed and checked against the hash table looking for joins. The bitmap is used as a quick lookup to check if rows are in the hash table and are especially useful when the hash table is too large to fit in memory. SQL> explain plan for select empno from emp, dept where emp.deptno = dept. deptno; Query Plan ---------------------------- SELECT STATEMENT [CHOOSE] Cost = 3 ** HASH JOIN **** TABLE ACCESS FULL DEPT **** TABLE ACCESS FULL EMP Hash joins are enabled by the parameter HASH_JOIN_ENABLED = TRUE in the init.ora or session. TRUE is the default in 7.3. 3.Cartesian Product - AQ product, not the real connection, sql sure to write the question A Cartesian Product is done where they are no join conditions between 2 row sources and there is no alternative method of accessing the data. Not really a join as such as there is no join! Typically this is caused by a coding mistake where a join has been left out. It can be useful in some circumstances - Star joins uses cartesian products.Notice that there is no join between the 2 tables: SQL> explain plan for select emp.deptno, dept, deptno from emp, dept Query Plan ------------------------------ SLECT STATEMENT [CHOOSE] Cost = 5 ** MERGE JOIN CARTESIAN **** TABLE ACCESS FULL DEPT **** SORT JOIN ****** TABLE ACCESS FULL EMP The CARTESIAN keyword indicate that we are doing a cartesian product. VII operator 1.sort - sort, very resource-consuming There are a number of different operations that promote sorts: order by clauses group by sort merge join 2.filter - filter, such as not in, min function, and so prone Has a number of different meanings, used to indicate partition elimination, may also indicate an actual filter step where one row source is filtering, another, functions such as min may introduce filter steps into query plans. 3.view - viewMost of the inline view produced When a view cannot be merged into the main query you will often see a projection view operation. This indicates that the 'view' will be selected from directly as opposed to being broken down into joins on the base tables . A number of constructs make a view non mergeable. Inline views are also non mergeable. eg: SQL> explain plan for select ename, tot from emp, (select empno, sum (empno) tot from big_emp group by empno) tmp where emp . empno = tmp.empno; Query Plan ------------------------ SELECT STATEMENT [CHOOSE] ** HASH JOIN ** TABLE ACCESS FULL EMP [ANALYZED ] ** VIEW **** SORT GROUP BY ****** INDEX FULL SCAN BE_IX 4.partition view - Partitioned Views Partition views are a legacy technology that were superceded by the partitioning option. This section of the article is provided as reference for such legacy systems. (This switched:

  • Detailed implementation plan 2011-03-09

    First, what is the implementation plan An explain plan is a representation of the access path that is taken when a query is executed within Oracle. Second, how to access data At the physical level Oracle reads blocks of data. The smallest amount of d

  • Detailed implementation plan is a process 2010-07-26

    1. Sys users are: check the current instance of the number of user sessions Particular attention to query the user's information, such as I am now Scott Users select sid, paddr, user #, username, serial # from v $ session; SID PADDR USER # USERNAME S

  • Oracle Implementation Plan Detailed 2011-05-06

    Oracle Implementation Plan Detailed --- Of: TTT BLOG This article addresses: --- Description: Full details of this oracle concepts related to the implementation plan, access to the data access

  • Analysis of the implementation plan optimization SQL <3> ORACLE implementation plan (transfer) 2010-05-27

    Background: In order to better carry out the following elements we must understand some conceptual terms: Sql statement to not share the same The SQL statement parsing Zhong Fu (Yinweijiexi Caozuobijiao costs of resources, can cause performance degra

  • Oracle Implementation Plan (Explain Plan) Description 2011-07-07 If you want to analyze the SQL performance on a particular issue, usually we have to look at the SQL execution plan to see each st

  • Oracle: implementation plan: access path connection table, suggesting Statistical information bind variables 2011-09-09

    SQL execution: Analysis: Hard analysis, soft analysis Implementation Plan: access path (access path), table join (table join), statistics (statistics), bind variable (bind variable), tips (hints) ================================================== ===

  • Oracle Implementation Plan 2010-03-09

    Summary: SQLPLUS under the automatic display, look at their statements or implementation plan will be implemented. Especially in the implementation of the UPDATE / DELETE statement, please do note, ORACLE is the first implementation of the script als

  • Read oracle's implementation plan 2010-04-28

    Read oracle's implementation plan Implementation plan for beginners is usually more difficult to read, often reading the implementation plan from the inside out, starting from the operation of the deepest indentation. General principles: 1. First of

  • MapReduce implementation plan to use some algorithm [Translation] 2010-05-07

    MapReduce implementation plan to use some algorithm With the growing size of treatment plan (such as complex network), which map the nodes and the side information can not be completely loaded into memory, which to implement the algorithm in the map

  • Analysis of the implementation plan optimization SQL <2> ORACLE optimizer (change) 2010-05-27

    Optimizer is sometimes called the query optimizer, which is affecting database query performance because the most important part, do not think that only the SELECT statement is the query. In fact, with any WHERE conditions DML (INSERT, UPDATE, DELETE

  • oraclesqlplus implementation plan 2010-06-17

    General can be seen through the many tools PL / SQL statement execution plan to analyze the performance. Here through the PL / SQL view sql implementation plan in several ways: Method 1. Set AutoTrace on; Then when your sql statement execution time,

  • Implementation plan to get through dbms_xplan.display_cursor 2010-08-04

    Used to view the implementation plan for the implementation of the sql: SQL> select count (*) from abc; COUNT (*) ---------- 10 SQL> select sql_id from v $ sql where sql_text = 'select count (*) from abc'; SQL_ID ------------- crrfjnb0y4mq1 SQL>

  • Oracle SQL with AutoTRACE analysis of the implementation plan 2010-09-06

    Turn * Environment: windowsXP + Oracle10gR2 * AutoTRACE is to analyze the SQL execution plan, the efficiency of a very simple and convenient tool * / AUTOTRACE is a function of

  • [Sql tuning of the implementation plan] merge semi join and merge anti join 2010-10-27

    [Sql tuning of the implementation plan] merge semi join and merge anti join Semi Join (also called half-connections) or more in the sub-query in the use of such exists, for the outer row set, look for the internal (ie, sub-query) row set, match back

  • scheduled task php php implementation plan tasks in ignore_user_abort 2010-12-09

    Function-ignore_user_abort, this function can help us to achieve the same as the linux implementation plan in the cron task, under the following terms with how to achieve. First have a look on the php manual explains the function Description int igno

  • MySQL hot backup implementation plan 2010-12-27

    MySQL hot backup implementation plan 1, MySQL database does not incremental backup mechanism, when the data is too big when the backup is a big problem. Fortunately, the main MySQL database provides a mechanism from a backup, in fact, the master data

  • How to see Oracle implementation plan 2011-01-14

    oracle explain an implementation plan. Related Concepts 1 · rowid, pseudo-column: is the system to add their own, each table has a pseudo-column is not physically exist. It can not be modified, deleted, and added, rowid in the life cycle of the line

  • Why Oracle will sometimes use the index to find data? - Force Oracle to use the best "implementation plan" 2011-06-01

    [Abstract] you use SQL, a query to the database when you release, Oracle will be generated with an "implementation plan" that is, what kind of data that the statement will search through the implementation of the program. Search for program sele

  • Implementation Plan (Execution Plan) process analysis 2011-01-11

    Simply put, the implementation plan is actually constituted by a series of data processing functions of a tree chain; those who have access to the physical data of the function is called the access path (Access Path, such as the Index Range Scan, Ful

  • ORACLE implementation plan of some basic concepts 2011-03-22

    ORACLE implementation plan of some basic concepts (1) One. Related Concepts Rowid concept: rowid is a pseudo-column, since it is pseudo column, this column is not user-defined, but the system itself to add the. For each table has a rowid pseudo-colum