Oracle's execution plan
How to get the plan executed
Autotrace Example
Using explain
Explain plan set statement_id= ' Testplan ' for select * from dual;
Select Lpad (", 5* (level-1)) | | Operation operation, Options, object_name, cost,position from plan_table start with id=0 and statement_id= ' Testplan ' connect by prior id=parent_id;
How to see the execution plan
Understand some of the knowledge you need to know before executing a plan
Pseudo-Column-rowid
rowID is a pseudo-column, since it is a pseudo-column, then this column is not user-defined, but the system itself to add. There is a ROWID pseudo-column for each table, but the value of the ROWID column is not physically stored in the table. However, you can use it as you would with other columns, but you cannot delete the column or modify or insert the value of the column. Once a row of data is inserted into the database, the ROWID is unique throughout the life of the row, that is, even if the row produces a row migration, the rowid of the row does not change.
Recursive SQL
Sometimes in order to execute a user-issued SQL statement, Oracle must execute some extra statements, which we call ' recursive calls ' or ' recursive SQL statements '. When a DDL statement is issued, Oracle always implicitly emits some recursive SQL statements to modify the data dictionary information so that the user can successfully execute the DDL statement. Recursive calls often occur when the data dictionary information that is required is not in shared memory, and these recursive calls read the data dictionary information from the hard disk into memory. The user is not more concerned with the execution of these recursive SQL statements, and Oracle automatically executes the statements internally when needed. Of course, DML statements can also cause recursive SQL. Simply put, we can think of a trigger as recursive SQL.
Row Source and predicate
- Row Source: The collection of qualifying rows returned by the previous operation in a query, which can be a collection of all row data for a table, or a collection of partial row data for a table, or a collection of row data after a connection operation, such as a join connection, to the last 2 row source.
- predicate (predicate): where restriction in a query
Driving Table
Driving table (Driver table): This table is also known as the outer table (OUTER tables). This concept is used in nested and hash joins. If the row source returns more row data, it has a negative effect on all subsequent operations. Note that although this is translated as a driver table, it is actually translated to drive the line source (driving row sources) more precisely. In general A, a table that returns fewer row sources as the driver table after applying the constraints of the query, so if a large table has a constraint (such as an equivalence limit) in the Where condition, then the large table is appropriate for the driver table, so not only the smaller tables can be used as the driver table, The correct argument should be that the table that returns fewer rows from the source is used as the driver table after applying the query's throttling criteria. In the execution plan, it should be the top row source, followed by a specific description.
Probed Table
Probed table (probed): This table is also known as the inner-layer table (INNER tables). After we get the data for a specific row from the driver table, we look for rows in the table that match the join criteria. So the table should be a large table (it should actually be a table that returns a larger row source) and the corresponding column should have an index on it.
A composite index (concatenated index) is an index of multiple columns, such as CREATE INDEX idx_emp on EMP (col1, col2, col3, ...), then we call the Idx_emp index a combined index. There is an important concept in the composite index: the boot column (leading), in the example above, Col1 is listed as the boot column. When we make a query, we can use the WHERE col1 =? , you can also use the WHERE col1 =? and col2 =? ", this restriction will use the index, but" where col2 =? " "The index is not used by the query. The combined index is used by the constraint when the pilot column is included in the constraint.
Selectable (selectivity)
You can determine the selectivity of a column by comparing the number of unique keys in one of the following and the number of rows in the table. If the ratio of the number of unique keys/rows in a table of the column is closer to 1, the higher the selectivity of the column, the better the column is to create the index, and the higher the selectivity of the same index. When you query on a column that is optionally high, the data that is returned is less and more appropriate for using an index query.
Access to Oracle accessing data (High combat)
Segmentation of Index Scan (index scans)
Table connection (High Combat 1)
Table Connection (High Combat 2)
Table connection (High combat 3)
Table Connection (High combat 4)
Relative speeds for different table joins
A simple execution plan (press F5 in Plsql-developer, not F4 ha)
Take a second look at 2 execution plans (1)
Take a second look at 2 execution plans (2)
Hints in Oracle (hint)
Optimizer Tips
Table Connection Hints
Index hints
Parallel hints
Table Access Tips
Correct use of indexes and SQL statements
- Index-See the index section in "Plsql Wings-plsql Optimization"
- SQL statements-see the SQL Where and Table Connections section of "Plsql Wings-plsql Optimization"
Using Oracle's own Sqlplus
How to make sqlplus with Autotrace function
- Connect to SYS user;
- Run the Plustrace.sql script under the $oracle_home/sqlplus directory;
- Grant Plustrace to public, valid for all users;
- Running the set Autot on command in Sql*plus will automatically track the SQL execution plan and provide SQL statistics;
Consistent Gets
A 1th SQL without order by is certainly more efficient than the 2nd SQL.
But why is the 2nd SQL consistent so little?
The reasons are as follows two points:
- Typically, data that is not in logical RAM buffer is read by physical reads, and physical reads is usually followed by a consistent gets. Therefore generally consistent gets is bigger than the physical reads. However, there is one exception, if the data obtained by physical reads is used directly for hash or sort, it is only recorded as physical reads not recorded as consistent. So with the order by it is possible to physical reads more but consistent gets less. But this is not the reason why I am here, because I have no physical reads in this experiment.
- The impact of ArraySize. ArraySize refers to the number of rows that are read at a time when data is being read. This value defaults to 15 and can be viewed using the show ArraySize command. A data block for example, there are 100 records, then not read this block can fetch all the data once, take arraysize=15 as an example, there will be 100/15=7 times consistent gets. Setting the ArraySize a bit larger can reduce consistent, but it can sometimes consume more resources. If we do select COUNT (0) from test, then Oracle will temporarily set ArraySize as the number of rows for test, so consistent gets very little. Rarely:
Several common options for autotrace
Set Autotrace off: Do not generate Autotrace report, this is the default mode set Autotrace on explain:autotrace only show optimizer execution path report set autotrace on statistics: Show only Execution statistics set Autotrace on: Contains execution plan and statistics set Autotrace traceonly: Same set autotrace on, but does not display query output
Set Autotrace on explain
Set Autotrace on statistics
Set Autotrace traceonly
Set Autotrace traceonly explain
Another way to view SQL plans-explain plan
Description: The execution plan used to view the SQL statement
Get ready:
Run the Utlxplan.sql script under the $oracle_home/rdbms/admin directory
Create a plan_table table
Execution scenario: Explain plan for SQL
Explain plan-View Execution Scenarios
Oracle performance Optimization-Read execution plan
