Oracle SQL Performance 1

Oracle SQL Performance 1. ORACLE optimizer:. RULE (based on Rules) B. COST (COST-based) c. CHOOSE (optional) sets the default optimizer. various declarations of the OPTIMIZER_MODE parameter in the ora file, such as RULE, COST, CHOOSE, ALL_ROWS, FIRST_ROWS. of course, you also overwrite SQL statements or sessions. to use the Cost-Based Optimizer (CBO, Cost-Based Optimizer), you must run the analyze command frequently to increase the accuracy of object statistics in the database. if the optimizer mode of the database is set to CHOOSE, the actual optimizer mode is related to whether the analyze command has been run. if the table has been analyze, the optimizer mode will automatically become a CBO. Otherwise, the database will adopt a RULE optimizer. by default, ORACLE uses the CHOOSE optimizer. To avoid unnecessary full table scan, you must avoid using the CHOOSE optimizer, the optimizer based on rules or costs is directly used. 2. table Access: ORACLE uses two ways to access Table records:. full table scan full table scan accesses each record in the Table in sequence. ORACLE optimizes full table scanning by reading multiple data blocks at a time. b. you can use ROWID-based access to access a table through ROWID to improve the efficiency of table access. ROWID contains the physical location information recorded in the table .. ORACLE uses indexes to establish a connection between data and the physical location where data is stored (ROWID. generally, indexes provide a quick way to access ROWID, so those queries based on index columns can improve the performance. 3. to avoid repeated parsing of the same SQL statement, ORACLE stores the SQL statement in memory after the first parsing. the memory in the shared buffer pool of the SGA (system global area) can be shared by all database users. therefore, when you execute an SQL statement (sometimes called a cursor), if it is exactly the same as the previously executed statement, ORACLE can quickly obtain parsed statements and the best execution path. this function greatly improves SQL Execution performance and saves memory usage. unfortunately, ORACLE only provides high-speed buffer (cache buffering) for simple tables. This function is not applicable to multi-table join queries. the database administrator must. set appropriate parameters for this region in ora. When the memory area is larger, more statements can be reserved. Of course, the possibility of sharing is higher. when you submit an SQL statement to ORACLE, ORACLE will first find the same statement in the memory. it should be noted that ORACLE adopts a strict match between the two. To achieve sharing, the SQL statements must be identical (including spaces and line breaks ). the shared statement must meet three conditions:. character-level comparison: the statements currently executed must be identical to those in the sharing pool. for example:

SELECT * from emp; different from each of the following SELECT * From EMP; Select * FROM Emp; SELECT * from emp; B. the two statements must refer to the same object. For example, how can I access Jack sal_limit private synonym Work_city public synonym Plant_detail public synonym Jill sal_limit private synonym Work_city public synonym Plant_detail table owner

 

Consider whether the following SQL statements can be shared between the two users.

Select max (sal_cap) from sal_limit; each user cannot have a private synonym-sal_limit. They are different objects: select count (* 0 from work_city where sdesc like 'new % '; two users can access the same object public synonym-work_city select. sdesc, B. location from work_city a, plant_detail B where. city_id = B. city_id cannot be used by jack to access plant_detail through private synonym, while jill is the table owner with different objects. c. bind variables must be used in two SQL statements with the same name. For example, the two SQL statements in the first group are the same (which can be shared ), the two statements in the second group are different (even when running, different bind variables have the same value. select pin, name from people where pin =: blk1.pin; select pin, name from people where pin =: blk1.pin; B. select pin, name from people where pin =: blk1.ot _ ind; select pin, name from people where pin =: blk1.ov _ ind;

 

4. select the most efficient table name sequence (only valid in the rule-based Optimizer). The ORACLE parser processes the table names in the FROM clause in the order FROM right to left, therefore, the base table driving table written in the FROM clause will be processed first. when the FROM clause contains multiple tables, You must select the table with the least number of records as the base table. when ORACLE processes multiple tables, it uses sorting and merging to connect them. first, scan the first table (the last table in the FROM clause) and sort the records, and then scan the second table (the last second table in the FROM clause ), finally, all records retrieved from the second table are merged with the appropriate records in the first table. for example, TAB1 16,384 records TAB2 1 records TAB2 as the basic table (the best method) select count (*) from tab1, tab2 execution time: 0.96 seconds TAB1 is selected as the basic table (poor method) select count (*) from tab2, tab1 execution time: 26.09 seconds. If more than three tables are connected for query, you need to select an intersection table as the base table, which is referenced by other tables. for example, the EMP table describes the intersection of the LOCATION table and the CATEGORY table.

SELECT * from location l, category c, emp e where e. EMP_NO BETWEEN 1000 AND 2000 and e. CAT_NO = C. CAT_NO and e. LOCN = L. LOCN will be more efficient than SELECT * from emp e, location l, category c where e. CAT_NO = C. CAT_NO and e. LOCN = L. locn and e. EMP_NO BETWEEN 1000 AND 2000

 

5. the connection sequence in the WHERE clause. ORACLE uses the bottom-up sequence to parse the WHERE clause. According to this principle, the join between tables must be written before other WHERE conditions. The conditions that can filter out the maximum number of records must be written at the end of the WHERE clause. for example:

(Inefficient, execution time: 156.3 seconds) SELECT... From emp e where sal> 50000 and job = 'manager' AND 25 & lt; (select count (*) from emp where mgr = E. EMPNO );

 

(Efficient, execution time: 10.6 seconds) SELECT... From emp e where 25 & lt; (select count (*) from emp where mgr = E. EMPNO) and sal> 50000 and job = 'manager'; 6. avoid using '*' in the SELECT clause. When you want to list all columns in the SELECT clause, using dynamic SQL COLUMN reference '*' is a convenient method. unfortunately, this is a very inefficient method. in fact, ORACLE converts '*' into all column names in sequence during parsing. This task is done by querying the data dictionary, which means it takes more time. 7. reduce the number of visits to the database when each SQL statement is executed, ORACLE has performed a lot of work internally: parsing SQL statements, estimating index utilization, binding variables, reading data blocks, and so on. it can be seen that reducing the number of visits to the database can actually reduce the workload of ORACLE. for example, there are three ways to retrieve employees whose employee number is 0342 or 0291.

Method 1 (most inefficient) SELECT EMP_NAME, SALARY, grade from emp where EMP_NO = 342; SELECT EMP_NAME, SALARY, grade from emp where EMP_NO = 291; method 2 (inefficient) declare cursor C1 (E_NO NUMBER) is select EMP_NAME, SALARY, grade from emp where EMP_NO = E_NO; begin open C1 (342); FETCH C1 ...,..,.. ; OPEN C1 (291); FETCH C1 ...,..,.. ; CLOSE C1; END; Method 3 (efficient) select. EMP_NAME,. SALARY,. GRADE, B. EMP_NAME, B. SALARY, B. grade from emp a, emp B WHERE. EMP_NO = 342 and B. emp_no= 291;

 

Note: In SQL * Plus, SQL * Forms, and Pro * C, You can reset the ARRAYSIZE parameter to increase the retrieval data volume for each database access. The recommended value is 200. 8. Use the DECODE function to reduce processing time. Use the DECODE function to avoid repeated scanning of the same record or reconnecting to the same table. for example:

Select count (*), SUM (SAL) from emp where DEPT_NO = 0020 and ename like 'Smith % '; select count (*), SUM (SAL) from emp where DEPT_NO = 0030 and ename like 'Smith % '; you can use the DECODE function to efficiently get the same result select count (DECODE (DEPT_NO, 0020, 'x', NULL )) d0020_COUNT, COUNT (DECODE (DEPT_NO, 0030, 'x', NULL) D0030_COUNT, SUM (DECODE (DEPT_NO, 0020, SAL, NULL) D0020_SAL, SUM (DECODE (DEPT_NO, 0030, SAL, NULL) D0030_SAL from emp where ename like 'Smith % '; similarly, the DECODE function can also be used in group by and order by clauses. 9. simple Integration, non-associated database access if you have several simple database query statements, you can integrate them into a query (even if there is no relationship between them) for example: select name from emp where EMP_NO = 1234; select name from dpt where DPT_NO = 10; select name from cat where CAT_TYPE = 'RD '; the preceding three queries can be merged into one: select e. NAME, D. NAME, C. name from cat c, dpt d, emp e, dual x where nvl ('x', X. DUMMY) = NVL ('x', E. ROWID (+) and nvl ('x', X. DUMMY) = NVL ('x', D. ROWID (+) and nvl ('x', X. DUMMY) = NVL ('x', C. ROWID (+) and e. EMP_NO (+) = 1234 and d. DEPT_NO (+) = 10 and c. CAT_TYPE (+) = 'rd ';

 

(Translator's note: although this method is adopted, the efficiency is improved, but the readability of the program is greatly reduced, so readers still need to weigh the advantages and disadvantages) 10. The most efficient method for deleting duplicate records (because ROWID is used): delete from emp e where e. ROWID> (select min (X. ROWID) from emp x where x. EMP_NO = E. EMP_NO); 11. replace DELETE with TRUNCATE. When deleting a table record, a rollback segment is usually used to store recoverable information. if you do not have a COMMIT transaction, ORACLE will recover the data to the State before the deletion (which is precisely the State before the deletion command is executed). When TRUNCATE is used, rollback segments no longer store any recoverable information. after the command is run, the data cannot be restored. therefore, few resources are called and the execution time is short. (The translator Press: TRUNCATE applies only to deleting the entire table, and TRUNCATE is DDL rather than DML) 12. as long as possible, use COMMIT as much as possible in the program. In this way, the performance of the program is improved, and the demand for resources released by COMMIT will also be reduced: resources released by COMMIT: information used to restore data in a rollback segment. the space dORACLE In the credo log buffer space obtained by the Program Statement is used to manage the internal costs of the above three types of resources, in reality, efficiency and transaction integrity are often the opposite of the general view of the number of records calculated by the fish and the bear's paw. count (*) is slightly faster than count (1, of course, if you can search by index, the index column count is still the fastest. for example, COUNT (EMPNO) 14. replace HAVING clause with Where clause to avoid HAVING clause. HAVING filters the result set only after all records are retrieved. this process requires sorting, total, and other operations. if the WHERE clause can be used to limit the number of records, this overhead can be reduced. for example:

Inefficient: select region, AVG (LOG_SIZE) from location group by region having region! = 'Sydney 'and region! = 'Perth' efficient select region, AVG (LOG_SIZE) from location where region! = 'Sydney 'and region! = 'Perth' GROUP BY REGION

 

(The conditions in HAVING are generally used to compare some set functions, such as COUNT. in addition, the general conditions should be written in the WHERE clause.) 15 reduce the number of queries to a table in SQL statements containing subqueries. Pay special attention to reducing the number of queries to a table. for example:

Inefficient SELECT TAB_NAME from tables where TAB_NAME = (SELECT TAB_NAME FROM TAB_COLUMNS where version = 604) AND DB_VER = (SELECT DB_VER FROM TAB_COLUMNS where version = 604) Efficient SELECT TAB_NAME from tables where (TAB_NAME, DB_VER) = (SELECT TAB_NAME, DB_VER) FROM TAB_COLUMNS where version = 604) Update multiple Column examples: inefficient: update emp set EMP_CAT = (select max (CATEGORY) FROM EMP_CATEGORIES ), SAL_RANGE = (SELE Ct max (SAL_RANGE) FROM EMP_CATEGORIES) WHERE EMP_DEPT = 0020; efficiency: update emp set (EMP_CAT, SAL_RANGE) = (select max (CATEGORY), MAX (SAL_RANGE) FROM EMP_CATEGORIES) WHERE EMP_DEPT = 0020; 16 improve SQL efficiency through internal functions. select h. EMPNO, E. ENAME, H. HIST_TYPE, T. TYPE_DESC, COUNT (*) FROM HISTORY_TYPE T, emp e, EMP_HISTORY h where h. EMPNO = E. empno and h. HIST_TYPE = T. HIST_TYPE group by h. EMPNO, E. ENAME, H. HIST_TYPE, T. TYPE_DE SC; the efficiency can be improved by calling the following functions. FUNCTION LOOKUP_HIST_TYPE (typ in number) RETURN VARCHAR2 as tdesc VARCHAR2 (30); CURSOR C1 is select TYPE_DESC FROM HISTORY_TYPE WHERE HIST_TYPE = TYP; begin open C1; FETCH C1 into tdesc; CLOSE C1; RETURN (NVL (TDESC ,'? '); END; FUNCTION LOOKUP_EMP (emp in number) RETURN VARCHAR2 as ename VARCHAR2 (30); CURSOR C1 is select ename from emp where empno = EMP; begin open C1; FETCH C1 into ename; CLOSE C1; RETURN (NVL (ENAME ,'? '); END; select h. EMPNO, LOOKUP_EMP (H. EMPNO), H. HIST_TYPE, LOOKUP_HIST_TYPE (H. HIST_TYPE), COUNT (*) FROM EMP_HISTORY h group by h. EMPNO, H. HIST_TYPE;

 

You can often see in the forum that 'the SQL statement can be used to write .... But I do not know that complicated SQL statements often sacrifice execution efficiency. able to master the above method to solve the problem by using functions is very meaningful in actual work) 17. When using the table Alias (Alias) to connect multiple tables in an SQL statement, use the table alias and prefix the alias on each Column. in this way, the parsing time can be reduced and the syntax errors caused by Column ambiguity can be reduced. (Note: Column ambiguity refers to the fact that different SQL tables have the same Column name. When this Column appears in an SQL statement, the SQL parser cannot determine the attribute of this Column) 18 replacing IN with exists in many basic table-based queries, IN order to satisfy one condition, it is often necessary to join another table. in this case, using EXISTS (or not exists) usually improves the query efficiency.

Inefficient: SELECT * from emp (base table) where empno> 0 and deptno in (select deptno from dept where loc = 'melb') Efficient: SELECT * from emp (base table) where empno> 0 and exists (SELECT 'x' from dept where dept. DEPTNO = EMP. deptno and loc = 'melb ')

 

(Replace not in with not exists in relative terms will significantly improve the efficiency, as will be pointed out IN the next section) 19 replace not in with not exists in subqueries, the not in Clause executes an internal sorting and merging. IN either case, not in is the most inefficient (because it executes a full table traversal for the table IN the subquery ). to avoid the use of not in, we can rewrite it into an Outer join (Outer Joins) or not exists. for example:

SELECT... From emp where DEPT_NO not in (SELECT DEPT_NO from dept where DEPT_CAT = 'A'); to improve efficiency, rewrite it to (Method 1: efficient) SELECT .... From emp a, dept B WHERE. DEPT_NO = B. DEPT (+) and B. DEPT_NO is null and B. DEPT_CAT (+) = 'A' (Method 2: most efficient) SELECT .... From emp e where not exists (SELECT 'x' from dept d where d. DEPT_NO = E. DEPT_NO AND DEPT_CAT = 'A'); 20 replace EXISTS with table join. Generally, table join is more efficient than EXISTS: select ename from emp e where exists (SELECT 'x' from dept where DEPT_NO = E. DEPT_NO AND DEPT_CAT = 'A'); (more efficient) select ename from dept d, emp e where e. DEPT_NO = D. DEPT_NO AND DEPT_CAT = 'a ';

 

(In RBO, the execution path of the former includes FILTER, and the latter uses nested loop) 21 replace DISTINCT with EXISTS. When submitting a query that contains one-to-many table information (such as department table and employee table), avoid using DISTINCT in the SELECT clause. in general, you can consider replacing it with EXIST, for example:

Inefficient: select distinct DEPT_NO, DEPT_NAME from dept d, emp e where d. DEPT_NO = E. DEPT_NO efficiency: SELECT DEPT_NO, DEPT_NAME from dept d where exists (SELECT 'x' from emp e where e. DEPT_NO = D. DEPT_NO );

 

EXISTS makes the query more rapid, because the core module of RDBMS will return results immediately after the subquery conditions are met. 22 identify 'inefficient execution' SQL statements and use the following SQL tools to find inefficient SQL statements:

  SELECT EXECUTIONS , DISK_READS, BUFFER_GETS,          ROUND((BUFFER_GETS-DISK_READS)/BUFFER_GETS,2) Hit_radio,          ROUND(DISK_READS/EXECUTIONS,2) Reads_per_run,          SQL_TEXT FROM   V$SQLAREA WHERE  EXECUTIONS>0 AND     BUFFER_GETS > 0  AND (BUFFER_GETS-DISK_READS)/BUFFER_GETS < 0.8  ORDER BY 4 DESC; 

 

(Translator's note: although various graphical tools for SQL optimization are emerging, writing your own SQL tools is always the best way to solve the problem) 23. Use the TKPROF tool to query the SQL Performance status. The SQL trace tool collects the performance status data of the SQL statements being executed and records the data in a trace file. this trace file provides a lot of useful information, such as the number of resolutions. number of executions, CPU usage time, and so on. this data will be used to optimize your system. set SQL trace at the SESSION level: Valid alter session set SQL _TRACE TRUE sets SQL trace to valid imitation throughout the database. You must SET the SQL _TRACE parameter in init. the USER_DUMP_DEST parameter sets TRUE in ora to describe the directory of the generated TRACE file (in this section, the author did not mention TKPROF usage, and the SQL TRACE usage is not accurate enough, to set SQL TRACE, you must first go to init. set TIMED_STATISTICS in ora to get the important time states. the generated trace file is unreadable, so you must use the TKPROF tool to convert it. TKPROF has many execution parameters. you can refer to the ORACLE manual to learn the specific configuration .) 24 using explain plan to analyze SQL statements is a good tool for analyzing SQL statements. It can even analyze statements without executing SQL statements. through analysis, we can know how ORACLE connects to the table, how to scan the table (index scan or full table scan), and the index name used. you need to interpret the analysis results in the order from inside to outside. the results of the explain plan Analysis are arranged in indent format, and the most internal operations will be first interpreted. If the two operations are on the same layer, the operations with the minimum operation number will be executed first. the nested loop is a few operations that do not follow the above rules. The correct execution path is to check the operations that provide data to the nested loop. the operation with the smallest operation number will be processed first. it is easier to use the set trace function in SQLPLUS through practice. example:

SQL> list 1 SELECT * 2 FROM dept, emp 3 * WHERE emp. deptno = dept. deptno SQL> set autotrace traceonly/* traceonly can not display the execution result */SQL>/14 rows selected. execution Plan ------------------------------------------------------ 0 select statement Optimizer = CHOOSE 1 0 nested loops 2 1 table access (FULL) OF 'emp' 3 1 table access (by index rowid) OF 'dept' 4 3 INDEX (unique scan) OF 'pk _ dept' (UNIQUE) statistics defaults 0 recursive cballs 2 db block gets 30 consistent gets 0 physical reads 0 redo size 2598 bytes sent via SQL * Net to client 503 bytes received via SQL * Net from client 2 SQL * Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 14 rows processed

 

Through the above analysis, we can see that the actual execution steps are:

1TABLE ACCESS (FULL) OF 'EMP'  2INDEX (UNIQUE SCAN) OF 'PK_DEPT' (UNIQUE) 3TABLE ACCESS (BY INDEX ROWID) OF 'DEPT' 4NESTED LOOPS (JOINING 1 AND 3) 

 

Note: Currently, many third-party tools, such as TOAD and ORACLE tools, such as oms SQL Analyze, provide extremely convenient EXPLAIN PLAN tools. maybe friends who like the graphic interface can choose them. 25 using indexes to improve efficiency index is a conceptual part of the table, which is used to improve the efficiency of data retrieval. in fact, ORACLE uses a complex self-balancing B-tree structure. data Query by index is usually faster than full table scan. when ORACLE finds the optimal path for executing the query and Update statements, the ORACLE optimizer uses the index. using indexes when joining multiple tables can also improve efficiency. another advantage of using an index is that it provides uniqueness verification of the primary key. except for those LONG or long raw data types, You Can index almost all columns. generally, using indexes in large tables is particularly effective. of course, you will also find that using indexes to scan small tables can also improve efficiency. although the index can improve the query efficiency, we must pay attention to its cost. the index requires space for storage and regular maintenance. The index itself is also modified whenever a record is increased or decreased in the table or the index column is modified. this means that the INSERT, DELETE, and UPDATE operations for each record will pay four or five more disk I/O. because indexes require additional storage space and processing, unnecessary indexes will slow the query response time. regular index reconstruction is necessary. alter index & lt; INDEXNAME> REBUILD & lt; TABLESPACENAME> 26 There are two access modes for ORACLE indexes. index unique scan (index unique scan) In most cases, the optimizer accesses the INDEX through the WHERE clause. for example, the table LODGING has two indexes: the unique index LODGING_PK on the LODGING column and the non-unique index LODGING $ MANAGER on the MANAGER column.

SELECT *  FROM LODGING WHERE LODGING = ‘ROSE HILL’; 

 

Internally, the preceding SQL statement is executed in two steps. First, the LODGING_PK index is accessed by unique index scanning to obtain the corresponding ROWID, use ROWID to access the table and perform the next retrieval. if the returned columns are included in the INDEX column, ORACLE will not perform the second step (access the table through ROWID ). because the retrieval data is stored in the index, accessing the index alone can fully meet the query results. the following SQL statements only require the INDEX UNIQUE SCAN operation.

    SELECT LODGING          FROM  LODGING WHERE LODGING = ‘ROSE HILL’; 

 

Index range scan can be used in either of the following scenarios: 1. search based on a specific RANGE; 2. search based on a non-unique INDEX. Example 1:

  SELECT LODGING        FROM  LODGING WHERE LODGING LIKE ‘M%’; 

 

The WHERE clause condition includes a series of values. ORACLE will query LODGING_PK through index range query. the index range query returns a set of values, which is less efficient than the unique index scan. example 2:

      SELECT LODGING        FROM  LODGING WHERE MANAGER = ‘BILL GATES’; 

 

The execution of this SQL statement is divided into two steps: LODGING $ MANAGER's index range query (obtain the ROWID of all matching records) and the next step is to obtain the value of the LODGING column through the ROWID access table. because LODGING $ MANAGER is a non-unique index, the database cannot perform a unique index scan on it. because the SQL statement returns the LODGING column and it does not exist in the LODGING $ MANAGER index, an operation is executed to access the table through ROWID after the index range query. in the WHERE clause, if the first character of the value corresponding to the index column starts with the WILDCARD (WILDCARD), the index will not be used.

    SELECT LODGING        FROM  LODGING WHERE MANAGER LIKE ‘％HANMAN’; 

 

In this case, ORACLE uses full table scan.