Oracle execution plan from the start view

2010-08-29  来源:本站原创  分类:Database  人气:150 

Consider the following situation

(1) If your data table to manage 40 million video information;

(2) If you manage 10 million kinds of product data sheets;

A simple paging query, you truly feel its efficiency over it? If users complain about slow response to your management system, in addition to open architecture, design, should also look at your sql.

Steps】 【Oracle Sql

http://gao-20022002.javaeye.com/blog/256939

SQL statements SQL statement execution steps of a process to go through the following steps.
A grammatical analysis of sentence syntax is compliant.
2 Semantic analysis of inspection statement of all database objects related to the existence of, and the user has the appropriate permissions.
3 view transformation will involve the view query is converted to the corresponding query the base table.
4 expression is converted to a complex SQL expression into a simpler expression equivalent connection.
5 Select a different optimizer optimizer generally have different "implementation plan"
6 Select the connection method to connect Oracle There are three ways to connect Oracle on multiple tables select the appropriate connection.
7 Select the connection order of the multi-table joins Oracle will choose the first link on the table, select the two tables in which the table as the source data table.
8 Select the data search path according to the above conditions select the appropriate data in the search path, so use full table search or use the index or other ways.
9 run "implementation plan"

Oracle execution plan from the start view

】 【Optimizer

a. RULE (rule) b. COST (based on cost)

http://q.yesky.com/group/review-17556550.html

】 【Oracle implementation plan

View the execution plan: pl / sql: (1) sql query window, press F5 (2) New Explain Plan Window

Under the implementation plan can be adjusted sql, analysis of consumption of resources, time, and find more optimized sql

Such as:

(1) to do computing in the data table field can not effectively use the index

Oracle execution plan from the start view

(2) with <>,!=, is not null, is null, can not effectively use the Index

Oracle execution plan from the start view

(3) default CBO case! = And or the use of the same,

Oracle execution plan from the start view

More differences can watch at work to see, there is always rewarding

】 【Sql optimization

http://zmaze.org/?p=193

相关文章
  • Oracle execution plan from the start view 2010-08-29

    Consider the following situation (1) If your data table to manage 40 million video information; (2) If you manage 10 million kinds of product data sheets; A simple paging query, you truly feel its efficiency over it? If users complain about slow resp

  • Oracle execution plan in order to see how the 2011-01-13

    Oracle execution plan ways to read: Look to the right start at the very beginning has been, until you see the place where the far right side by side, for non-parallel, right, the first run: For side by side, relying on the first run. Namely, in the i

  • Oracle execution plan to see how the order is 2011-01-13

    Reading oracle execution plan approach: Start at the very beginning has been to right, side by side until you see the most right place for non-parallel, right, the first execution: For side by side, relying on the first run. That is parallel to inden

  • Oracle execution plan from the beginning to view 2010-08-29

    Consider the following scenario, (1) If your data tables to manage 30 million line of card user information; (2) If you manage 10 million kinds of product data sheets; A simple paging query, you truly felt it efficient? If users complain about slow r

  • (Installed) oracle execution plan 2011-07-20

    One. Related Concepts Rowid concept Recursive Sql concept Predicate (verb) DRiving Table (table-driven) Probed Table (by probe table) Combination index (concatenated index) Selectivity (selectivity) II. oracle access to the data access methods 1) Ful

  • oracle execution plan 3 2010-03-02

    Environment: oracle 817 + linux + array cabinet swd_billdetail Table 50 million data SUPER_USER Table 2800 data Connect columns are indexed, and super_user in a table corresponds to swd_billdetail many records Table and Index are also analyzed. Pract

  • oracle execution plan 4 2010-03-02

    The statement by adding hints, so that the optimizer oracle nested loop, and a large table as the driving table, generate a new execution plan: select / * + ORDERED USE_NL (A) * / count (a.CHANNEL | | B.user_class) from swd_billdetail B, SUPER_USER A

  • oracle execution plan cost, card meaning 2011-03-19

    This card is a step in the planned number of rows processed. cost refers to the cbo in the resources spent on this step, this value is a relative value. This step refers to the cbo bytes to process all records of the number of bytes, is estimated fro

  • oracle execution plan 1 2010-03-02

    1. Related concepts Rowid concept: rowid is a pseudo-column, since it is pseudo-column, then this column is not user-defined, but the system itself to add the. Each table has a rowid pseudo-column, but the table does not physically store the value of

  • oracle execution plan 2 2010-03-02

    3. Form the connection between Join an attempt to combine the two tables predicate, one can only connect two tables, table join can also be referred to as worksheets. In the following description, we will use the "row source" instead of "ta

  • oracle execution plan explained 2011-03-31

    [Transfer] http://www.iteye.com/topic/586256 I. Concepts 1 · rowid, pseudo-column: is the system to add their own, and each table has a pseudo-column, not the physical presence. It can not be modified, deleted, and added, rowid in the life cycle of t

  • oracle execution plan 2010-07-19

    Set the implementation of the plan: set autotrace on; set timing on; Execution time format: Elapsed: 00:00:00.30 Hours : Minutes : Seconds : Seconds /100 Switch User: SQL> connect username/[email protected]

  • Oracle using sql plus view the execution plan (reprint) 2011-04-26

    In sql * plus, enter set autotrace on, and then directly execute sql statements; will automatically display the execution plan and statistics. Disadvantages: This method is used to view the sql statement execution time is longer, you need to wait for

  • How to view the execution plan oracle 2011-07-07

    See individual SQL method I. using explain plan If PLAN_TABLE table does not exist, execute $ ORACLE_HOME / rdbms / admin / utlxplan.sql create plan_table table. The first step: explain plan for select * from ...... Step two: select * from table (DBM

  • oracle view the SQL execution plan parameters analysis 2010-04-14

    sql_trace been tracking the implementation plan: Call Count CPU Time Elapsed Time Disk Query Current Rows ------- ------ -------- ------------ ---------- ------- --- ---------- ---------- Parse 1 0.000 0.000 0 0 0 0 Execute 1 0.000 0.000 0 0 0 0 Fetc

  • How do I view the execution plan oracle sql 2011-04-09

    Method One The first step: login sql / plus the command (no order) set time on; (Note: Open time display) set autotrace on; (Note: Turn on Automatic statistical analysis, and display the results of an SQL statement) set autotrace traceonly; (Note: Op

  • oracle sql statement execution plan in the analysis method 2011-01-14

    How to generate explain plan? Answer: Run utlxplan.sql. The establishment plan table Specific SQL statement, using the explain plan set statement_id = 'tst1' into plan_table for sql statement Run explain plan look utlxplp.sql or utlxpls.sql (Select *

  • Oracle: hint to manually change the sql execution plan 2011-07-06

    Tips (hint) from Oracle7 introduced, aimed at cost-based optimizer to make up for deficiencies. Tips are usually used to manually change the SQL execution plan to improve the efficiency of enforcement. hints is the oracle to provide a mechanism to te

  • View the execution plan mysql 2010-07-16

    During the development process as data volumes grow and feel the difference comparing the performance of your database so as to extend to respond slower, if you are a developer often estimated to be in a State recognized , Or directly to the DBA to d

  • SQL query execution plan oracle 2011-08-03

    Explain SQL SQL> explain plan for select * from dual; View the execution plan select * from table(DBMS_XPLAN.display);