Use Explain for query and application optimization

Using an Explain Summary: Oracle RDBMS Executes each SQL statement must be evaluated by Oracle optimizer. To understand how the optimizer is selected (search) path and how index is used, it is very helpful to optimize the SQL statement. This article discusses a performance diagnostic tool for optimizing applications in detail: Explain is used. Introduction: Apply Optimization Not only needs to know what is applied, but also know how the application works and uses the database design to support, you must understand which type of SQL statement that uses the use of SQL statements, the structure of the statement and the structure of the view and these Table related various types of indexes. In addition, optimizing the entire application system is not required, understanding the various parts of the application allow us to understand which parts need to be optimized. We will focus on the optimization of the performance optimization tools provided using Oracle RDBMS for SQL levels. Explain can quickly and easily identify how the search path is obtained for how to get query data in a given SQL statement (we are often referred to as Access Path). Access path has a very big impact on performance. We will discuss the advantages and disadvantages of various access pats and use. Using Explain Use the Explain tool to create an Explain_PLAN table, you must first enter the account of the owner of the related application table, view, and indexes. Oracle's media contains SQL source programs that perform this work, for example: ORA_RDBMS: XPLAINPL.SQL (VMS) $ Oracle_Home / Rdbms / Admin / UTLXPLAN.SQL (UNIX) This SQL program should be with catalog.sql in the same directory. this program creates a file called plan_table table, the table structure is as follows: Name Type STATEMEN_ID VARCHAR2 (30) TIMESTAMP DATE REMARKS VARCHAR2 (80) OPERATION VARCHAR2 (30) OPTIONS VARCHAR2 (30) Object_node VARCHAR2 (128) object_owner VARCHAR2 (30) Object_name VARCHAR2 (30) Object_instance Number (38) Object_type varcha2 (30) Search_columns number (38) ID Number (38) Parent_id Number (38) Position Number (38) Other long This introduces some of the main concepts of the column we will discuss. If you need a detailed description of each column, please see the explain.doc file. Statement_ID: Determines a specific execution plan name for a specified SQL statement. This value will be set to NULL if you do not use Set Statement_ID in an Explan PLAN statement. Operation: The name of the operated name executed in a plan of the plan, for example: Filters, Index, Table, Marge Joins and Table, etc. Option: Supplements to Operation operations, such as: Operation to a table, Operation may be Table Access, but Option may be by RowID or FULL. Object_owner: The Schema name or Oracle account name owned by this Database Object. Object_name: Database Object Name Object_Type: Type, for example: table, view, index, etc. ID: Indicate a step in the execution plan. PARENT_ID: Indicates the previous operation of obtaining information from a certain operation.

We can query the entire execution plan tree by using the Connect By operation with ID and Parent_ID. Once this Plan table is created, users can use Explain in the app. Use the syntax as follows: Explain Plan [set statement_id [=] ] for where: statement_id is a unique string, other current execution plans and other in the same Plan table The implementation plan is distinguished. Table_name is a PLAN table name, and its structure is as indicated before you can set this name any. SQL_STATEMENT is a real SQL statement. For example: Explain Plan Set Statement_ID = 'Query1' for select from Emp where deptno = 10; The following information will be obtained after execution: Operation 50 succeeded, if the INTO sentence is ignored in the Explain statement, Explain will use Plan_Table as a table name. we can use the query plan table method to check the execution plan, such as: SELECT OPERATION, OPTIONS, OBJECT_NAME, OBJECT_TYPE, ID, PARENT_ID FROM PLAN_TABLE WHERE sTATEMENT_ID = 'QUERY1' ORDER bY ID; will return the following: OPERATION OPTION Object_name Object_type ID Parent_ID Table Access Full EMP TABLE 1 1 1 ROW SELECTED This means that full table scans will be used in this query. If you do not create an index on the EMP table, all queries for EMP will use full-table scanning, but if you are on deptno Create a non-unique index: create index Emp_idx on EMP (DePTNO); now, if we reinlease query: Explain Plan set statement_id = 'query2' for select * from Emp where deptno = 10; then check the schedule: Select Operation, OPTIONS, OBJECT_NAME, OBJECT_TYPE, ID, PARENT_ID FROM PLAN_TABLE WHERE sTATEMENT_ID = 'QUERY2' ORDER BY IB; returns: OPERATION OPTION Object_name Object_type ID Parent_ID TABLE ACCESS BY RAWID EMP TABLE 1 INDEX RANGE SCAN EMP_IDX NON_UNIQUE 2 1 2 row selected so that we You can see that the index Emp_IDX is used to get rows equal to 10, then obtain data according to RowID, and the index stores the ROWID of each line in the table, whenever found a line in an index, it will be based on Row The ID is queried to the rest of the line. If it is an operation of a large table, such search paths are obviously effective in reducing disk I / O operations than the previous (full table scan). However, if the index is "low selective", a full mete scan may be more effective.

Consider the following query execution plan and: EXPLAIN PLAN SET STATEMENT_ID = 'QUERY3' FOR SELECT DEPTNO FROM EMP WHERE DEPTNO = 10 program execution paths are: OPERATION OPTION Object_name Object_type ID Parent_ID INDEX RANGE_SCAN EMP_IDX NON_UNIQUE execution plan represents 1 or more without Take the data in Table, this query is only required to use an index. EXPLAIN Search Path Explanation Any SQL statement has followed some optimization principles, which have a detailed introduction in the Oracle Database Administrator's Manual. At the same time, these principles are also listed in text 100040.163. These principles have tried to find a best search path when data is taken out from the database. Once the optimizer evaluates the query and determines the search path, the optimizer creates an execution plan tree. We can use SQL * Plus to query Plan Table to see the execution plan tree: Column Plan Format A70 SELECT LPAD ('', 3 * Level) || Operation || ('|| Options ||') '|| Object_name || '' || object_type from plan_table connect by prior id = parent_id and statement_id = '& stmt_id'; for example, the following query SELECT ENAME fROM EMP WHERE DEPTNO = 10 ORDER bY ENAME; SQL statement to check the results of using the above plan table is: SORT ( ORDER BY) EMP INDEX (RANGE SCAN) EMP INDEX N0N_UNIQUE This execution plan represents an index scan on the EMP_IDX index, and then the ename data is taken out in the table, and the last data is ORDER BY. Operation classification. If the EMP table is large, then the last step of this execution plan can take a long time. Suppose we explain the following query: Select Deptno, Ename from Emp Where Deptno Between 1 and 30 ORDER BY Deptno; then executing the tree as: Table Access (by rowid) Emp index (Range scan) Emp_idx non_unique Please note that although used in the query ORDER BY, but there is no Sort (Order by) in the execution tree. why? There are two reasons for not using sort: 1) DeptNO columns have been established, which has been established, and sort; 2) Deptno is defined as NOT NULL (eg Deptno Null Number). Assuming this normal connection query: select * from Emp. Dept where Emp.deptno = Dept.deptno and Sal> 5000; Executive Tree is: NESTED LOOPS () Table Access (FULL) DEPT TABLE Access (by rowid) EMP INDEX Range scan) EMP_IDX NON_UNIQUE NESTED LOOPS means a sequence query on a table (DEPT), and each DEPTNO is found in the index EMP_IDX on the EMP table. This query is called a Driving Table. In this case, the drive table is DEPT.

In this type of connection, the drive table is listed behind the table. Because the two tables have the same level of search path (there is a non-unique index on the deptno column), since at least one of the records must be retrieved, then perform full menu scans on a table, at the same time Looking for a record-seeking record on the index of the table is more effective. In this case, we should put the table with the minimum column as the driving surface at the end of the FROM clause, note that in this type of connection, the order in the FROM clause determines the search path of the RDBMS. If the WHERE condition is one is returns a single record, the driver in the above query should use the connection query. Suppose there is a unique index on the EMPNO column of the EMP table, then the following SQL statement: select WHERE EMP.DEPTNO = Dept.deptno and Empno = 7735; the execution tree is: nested loops () Table Access (by RowID) Emp Index (Unique Scan) EMP_UIDX UNIQUE TABLE Access (By ROWID) DEPT INDEX (RANGE SCAN) DEPT_IDX NON_UNIQUE This execution plan indicates that nested loops will only be executed only because a unique index search is performed on the drive table EMP. At the same time, the index retrieval is performed on DEPT_IDX. If you can find record, you can get this record according to ROWID. Other operations can also be seen in the output of Explain. For example, when a "Connect By Prior ..." clause is included, the RDBMS performs tree traverses on the table based on the connection condition. The implementation plan of the question we just queried is a such example. We can use explain in this query and check the output. We can also use other operations, such as Filters, Project, Union, etc. Below this query makes a Filter action: SELECT Deptno, AVG (SAL), SUM (COM) from Emp Group By Deptno Having Sum (Comm)> 500; Executive Plan as follows: Filters () Sort (Group by) Table Access (Full) After the EMP is running SORT, Filters finds all COMMs greater than 500 records. Project operation is not common. You will not be used when you use Minus and Unions to query a subset of these columns on all columns. If Minus and Union are used in the SQL statement, we can also find these two operations in the execution plan. View: We can also explain virtual tables or views with EXPLAIN. So this is useful because it is used. We only need to use Explain with Explain for the search view, you can find the search path of the view.