Oracle_ Advanced Features (9) Performance optimization

1.oracle Optimizer
The goal of optimization is divided into 4 categories:
Choose (optional)
Rule (rules-based)
First rows (line one)
All rows (all rows)
Description: Describe the execution plan for SQL
Object owner: Objects Schema
Object Name: Objects name
Cost: Spent (in time)
Cardinality: cardinality, approximately equal to the number of rows
Bytes: space (access to storage space)

CREATE TABLE EMP1 as SELECT * from EMP;
CREATE TABLE DEPT1 as SELECT * from dept;
INSERT INTO EMP1 select * from EMP1;
Select COUNT (1) from EMP1;

How to access 2.table
2.1 All-table scan (table access full)
A full table scan is the sequential access to each record in the table.
Oracle optimizes full-table scanning in a way that reads multiple data blocks (database block).
Example:
SELECT * from EMP1;

2.2 Accessing the table via ROWID (table access by User/index rowID)
You can use ROWID-based access to improve the efficiency of your access tables.
The ROWID contains the physical location information for the records in the table.
Oracle employs an index to achieve the connection between data and the physical location (ROWID) where the data resides.
Usually the index provides a quick way to access rowid, so those queries based on indexed columns can get a performance boost.
Example:
Select rowid,emp.* from emp where rowid= ' aaar3saaeaaaacxaaa ';
SELECT * from EMP1 where empno=7788;
Create INDEX Idx_emp1_empno on EMP1 (empno);

3. Shared SQL statements
In order not to parse the same SQL statements repeatedly, Oracle stores the SQL statements in memory after the first resolution.
The shared buffer pool in the system global area of the SGA, which can be shared by all database users.
When executing an SQL statement (sometimes referred to as a cursor), if it is exactly the same as the previously executed statement,
Oracle can quickly get the parsed statement and the best execution path.
Limit:
Valid for single-table queries, not for multi-table connection queries.
SQL statements must be exactly the same (including spaces, line breaks, and so on), strictly matched (character-level comparisons).
Example:
SELECT * from EMP;
It's different from every one of the following.
SELECT * from EMP;
SELECT * from EMP;
SELECT * from EMP;

Test_all_objects

3. Selection of Base tables
The underlying tables (driving table) refer to the tables that are accessed first (typically accessed in the form of full-table scans).
Depending on the optimizer, the selection of the underlying table in the SQL statement is different.
If you are using the CBO (cost based optimizer), the optimizer checks the physical size of each table in the SQL statement, the state of the index, and then chooses the execution path that is the least expensive.
If you use RBO (rule based optimizer), and all the join conditions are indexed, the underlying table is the one that is listed in the FROM clause in the last table.

4. Select the most efficient table name order (valid only in the rule-based optimizer)
The parser for Oracle processes the table names in the FROM clause in a right-to-left order.
So the last table written in the FROM clause (the underlying table, driving tables) will be processed first.
In cases where the FROM clause contains more than one table, you must select the table with the lowest number of record bars as the underlying table.
When Oracle processes multiple tables, it uses sorting and merging to connect them.
First, the first table (the last table in the FROM clause) is scanned and the records are sequenced,
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 from the first table.
SELECT * from emp,dept where emp.deptno=dept.deptno;
SELECT * from EMP1,DEPT1 where emp1.deptno=dept1.deptno;

HASH join is more suitable for large table connections
A table (usually a small table) is hashed, and then the column data is stored in the hash list.
Extract the records from another table, do hash operations, and find the corresponding values in the hash list to match.
Sort merge JOIN sorting merge
The association columns of the associated tables are sorted first, then the data is extracted from the respective sort table, and the matching is done in the other sort table.
Because Meger joins need to do more sorting, they consume more resources.
The sort MERGE join is used in situations where there is no index and the data is sorted.
NESTED LOOPS Nested loops compare connections for small tables (less than 10,000)
First, read the data from one table, and access another table (accessed by index) to make a match.
NESTED loops is used when an association table is smaller, and the efficiency is higher.

If you have more than 3 tables connected to the query, you need to select the crosstab (intersection table) as the base table.
A crosstab refers to a table that is referenced by another table.
SELECT * from STUDENT,COURSE,SC where Student.sno=sc.sno and course.cno=sc.cno;

5. Connection order in the WHERE clause (only valid in rule-based optimizer)
Oracle parses the WHERE clause in a bottom-up order.
According to this principle, the connection between tables must be written before the other where conditions,
The criteria that can filter out the maximum number of records must be written at the end of the WHERE clause.
SELECT * from Emp,dept where Emp.deptno=dept.deptno and empno=7788;
SELECT * from EMP1,DEPT1 where Emp1.deptno=dept1.deptno and empno=7788;
SELECT * from STUDENT,COURSE,SC where Student.sno=sc.sno and Course.cno=sc.cno and student.sno=1;

6. Avoid using ' * ' in the SELECT clause
Use dynamic SQL column to refer to ' * ' When listing all columns in the SELECT clause
is a convenient way. Unfortunately, this is a very inefficient approach. In fact, in the process of parsing, Oracle
Converts the ' * ' to all column names, which is done by querying the data dictionary, which means more time is spent.
Select Empno,ename,job,mgr,hiredate,sal,comm,deptno from EMP;
-
SELECT * from EMP;

7. Reduce the number of Access databases
Oracle performs a lot of work internally when executing each SQL statement:
Parse SQL statements, estimate index utilization, bind variables, read data blocks, and more.
Thus, reducing the number of accesses to the database can actually reduce the workload of Oracle.
For example, retrieve an employee who has a number equal to 7369 or 7788.
Select Ename,sal,job from emp where empno = 7369;
Select Ename,sal,job from emp where empno = 7788;
-
Select Ename,sal,job from emp where empno in (7369,7788);

Select Ename,sal,job from emp where empno = 7369;
Select Ename,sal,job from emp where deptno = 20;
-
Select Ename,sal,job from emp where empno=7369 or deptno = 20;

8. Use the Decode function to reduce processing time
Use the Decode function to avoid duplicate scans of the same record or duplicate connections to the same table.
For example:
Select COUNT (*), sum (SAL) from the EMP where deptno=20 and ename like '%s% ';
Select COUNT (*), sum (SAL) from the EMP where deptno=30 and ename like '%s% ';
You can use the Decode function to get the same results efficiently
Select COUNT (Decode (deptno,20,1,null)) D20_count,
Count (Decode (deptno,30,1,null)) D30_count,
SUM (decode (deptno,20,sal,null)) D20_sal,
SUM (decode (deptno,30,sal,null)) d30_sal
From the EMP where ename like '%s% ';

9. Reduce the query on the table
In the SQL statement that contains the subquery, pay particular attention to reducing the query on the table.
Select Emp.*, (select Dname from dept where Deptno=emp.deptno) Dname,
(Select Loc from dept where Deptno=emp.deptno) Loc from EMP;
-
Select Emp.*,dname,loc from emp,dept where Emp.deptno=dept.deptno;

10. Replace Delete with truncate
When you delete a record in a table, the rollback segment (rollback segments) is used to hold the deleted information.
If there is no COMMIT transaction, Oracle restores the data to the state before it was deleted (accurately reverting to the condition before the delete command was executed)
When using truncate, the rollback segment no longer holds any information that can be recovered.
When the command is run, the data cannot be restored.
Restriction: Truncate can only delete the entire table data, cannot delete the partial data.

11. Use commit as much as possible
Use commit as much as possible to avoid over-occupancy of the rollback segment and redo log space.
Every 1000 data is submitted once during bulk operation.

12. Replace in with exists
SELECT * from Emp1 where Deptno in (select Deptno from dept1 where Dname= ' "" --7.302s
-
SELECT * from EMP1 where exists (select 1 from dept1 where Dname= ' the "and Deptno=emp1.deptno); --7.284s

Create INDEX Idx_emp1_deptno on EMP1 (DEPTNO);
SELECT * from Dept1 where Deptno in (select Deptno from EMP1); --0.016s
-
SELECT * from DEPT1 where exists (select 1 from emp1 where Deptno=dept1.deptno); --0.016s

SELECT * FROM All_tables
SELECT * FROM All_tab_columns
CREATE TABLE Temp_tab as SELECT * from All_tables;
CREATE TABLE Temp_col as select owner,table_name,column_name,data_type,data_length,column_id from All_tab_columns;

SELECT * FROM Temp_tab where table_name in (select table_name from TEMP_COL); --0.343s
-
SELECT * from Temp_tab where exists (select 1 from temp_col c where table_name=c.table_name); --0.282s

Create INDEX Idx_table_name on TEMP_TAB (table_name);

13. Replace not in with not exists
Update EMP1 set deptno=10 where empno=7839;

SELECT * from DEPT1 where deptno not in (select Deptno from EMP1); --0.156s
-
SELECT * from DEPT1 where NOT exists (select 1 from emp1 where Deptno=dept1.deptno); --0.015s

SELECT * FROM TEMP_TAB where table_name not IN (select table_name from TEMP_COL); --0.031s
-
SELECT * from Temp_tab where NOT exists (select 1 from temp_col c where table_name=c.table_name); --0.015s

14. Replace the exists with a table connection
In general, table joins are more efficient than exists
Create INDEX Idx_dept1_deptno on DEPT1 (DEPTNO);

Select emp1.* from EMP1 where exists (select 1 from dept1 where Deptno=emp1.deptno);--18.9s
-
Select emp1.* from emp1,dept1 where Emp1.deptno=dept1.deptno; --18.002s

SELECT * from Temp_tab where exists (select 1 from temp_col c where table_name=c.table_name); --0.063s
-
Select t.* from Temp_tab t,temp_col C where t.table_name=c.table_name; --0.062s

15. Replace distinct with exists
Avoid using DISTINCT in the SELECT clause when submitting a query that contains one-to-many table information, such as a departmental table and an employee table.
You can generally consider replacing with exist
Select distinct dept1.deptno,dname from DEPT1,EMP1 where Dept1.deptno=emp1.deptno; --0.764s
-
Select Deptno,dname from dept1 where exists (select 1 from emp1 where Deptno=dept1.deptno);--0.312s

Use of more selective indexes under the CBO
The cost-based optimizer (CBO, cost-based optimizer) determines the selectivity of the index to determine whether the use of the index can improve efficiency.
If the index is highly selective, it means that for each index key value that is not duplicated, only a small number of records are corresponding.
For example, there are 100 records in the table and there are 80 index key values that are not duplicated. The selectivity of this index is 80/100 = 0.8.
The higher the selectivity, the fewer records are retrieved from the index key value.
If the selectivity of the index is low, retrieving the data requires a large number of indexed range query operations and ROWID access to the table.
Might be less efficient than a full-scale scan.
If the number of records in a table that has more than 30% data is retrieved. Using indexes will have no significant efficiency gains.
In certain cases, using an index may be slower than a full table scan, but this is the same order of magnitude difference.
In general, the use of indexes than the full table scan to block several times or even thousands of times!
Drop index idx_object_id;
SELECT * from tab_all_objects where object_id=75237; --2.964s
Create INDEX idx_object_id on tab_all_objects (object_id)
SELECT * from tab_all_objects where object_id=75237; --0.422s

Select Column_name,count (1) from Temp_col GROUP BY COLUMN_NAME have count (1) =1
SELECT * from Temp_col where column_name= ' cluster_name '; --0.109s
Create INDEX Idx_column_name on Temp_col (column_name); --0.046s

--Clear the database memory buffer
alter system flush Buffer_cache;

16. Improve efficiency with indexes
An index is a conceptual part of a table that is used to improve the efficiency of retrieving data.
In fact, Oracle uses a complex self-balancing b-tree structure.
In general, querying data through an index is faster than a full table scan.
In general, using indexes in large tables is particularly effective.
Although using an index can improve query efficiency, the index requires space to store it.
The index is modified whenever a record is insert\update\delete in the table.
Because indexes require additional storage space and processing, those unnecessary indexes can slow query response time.
It is necessary to periodically refactor the index.
Alter index <indexname> rebuild <tablespacename>

The access mode of Oracle to an index.
17.1. Fully indexed scan index full scan
Select emp.* from emp,dept where Emp.deptno=dept.deptno;
--first row
17.2 Fast full index Scan index fast fully scan
Select View_name from Tab_all_views;
Select object_id from Test_all_objects;
--all rows
17.3 Index Range Scanning index range scan
CREATE INDEX Idx_emp_deptno on EMP (DEPTNO);
SELECT * from EMP where deptno=10;

17.4 Unique index Scan index unique scan
SELECT * from EMP where empno=7788;
17.5 Index Hop Scan

18. Uniqueness Index and non-uniqueness Index
Prioritize the use of uniqueness indexes
Select ename from emp where empno=7788 and deptno=20;

19. Multiple Equal indexes
When the execution path of an SQL statement can use multiple indexes that are distributed across multiple tables,
Oracle uses multiple indexes at the same time and merges their records at run time.
Retrieves a record that is valid only for all indexes.
SELECT * from Emp,dept where Emp.deptno=dept.deptno and ename= ' SCOTT ';

When Oracle chooses the execution path, the level of the uniqueness index is higher than the non-unique index.
SELECT * from emp where job= ' ANALYST ';
SELECT * from emp where empno=7788 and job= ' ANALYST ';

However, this rule only works if the index column and the constant comparison in the WHERE clause are valid.
The rank in the optimizer is very low if the index column is compared to the index column of another table.
SELECT * from Emp,dept where Emp.deptno=dept.deptno and job= ' ANALYST ';

If two indexes of the same rank in a different table are to be referenced, the order of the tables in the FROM clause will determine which one will be used first;
The index of the last table in the FROM clause will have the highest precedence.
--select * from emp,dept where emp.deptno=dept.deptno;
If two indexes of the same rank in the same table are to be referenced, the different optimizer may have different precedence for the index.
SELECT * FROM EMP e1,emp E2 where e1.deptno=20 and e2.job= ' clerk ';

20. Equality comparison and range comparison
Equality comparisons and range comparisons will prefer equality comparisons.
SELECT * from emp where deptno>10 and job= ' clerk ';
SELECT * from emp where job= ' clerk ' and deptno>10;

21. The Numeric column index is better than the character column index
Preferential use of highly selective indexes
Create INDEX Idx_emp_job on EMP (JOB);
SELECT * from emp where deptno=10 and job= ' clerk ';
SELECT * from emp where deptno>10 and job> ' A ';

Create INDEX idx_emp_job_sal on EMP (JOB, SAL)
21. The same rank character index, the selectivity is higher than the selectivity low
Create INDEX Idx_emp_ename on EMP (ename);
SELECT * from emp where job= ' clerk ' and ename= ' SMITH ';
SELECT * from emp where ename= ' SMITH ' and job= ' clerk ';
Under the Rule optimizer, two indexes can be used, and precedence is distinguished by the order in which they are in.
SELECT * from emp_1 where job= ' clerk ' and ename= ' SMITH ';
SELECT * from emp_1 where ename= ' SMITH ' and job= ' clerk ';

22. Avoid using calculations on indexed columns.
An operation on an indexed column causes the index to fail, and the operation can be moved to the variable side equivalently.
Drop index idx_emp_sal;
Create INDEX idx_emp_sal on EMP (SAL);
SELECT * FROM EMP where sal*12>25000;
-
SELECT * FROM EMP where sal>25000/12;

23. Forced Index invalidation
With multiple equal indexes, the rule optimizer uses multiple indexes to deliberately invalidate a low-sensitivity index.
SELECT * from emp where job= ' clerk ' and ename= ' SMITH ';
-
SELECT * from emp where job| | " = ' Clerk ' and ename= ' SMITH ';

25. Avoid using not on indexed columns
Not will stop using the index and perform a full table scan.
SELECT * from emp where not ename= ' SMITH ';
For numeric comparison operations, the Oracle optimizer automatically converts not to the corresponding comparison operator, which does not affect the use of the index.
Not > to <=
Not >= to <
Not < to >=
Not <= to >
Select ename from emp where not deptno>20;
-
Select ename from emp where deptno<=20;

26. Replacing > with >=
If there is an index on the Deptno,
Select ename from emp where not deptno>20;
-
Select ename from emp where not deptno>=30;
The difference between the two is that the former DBMS jumps directly to the first record that Deptno equals 30.
The latter will first locate the record of the deptno=20 and scan forward to the first record with a dept greater than 20.

27. Replace or with union (for indexed columns)
Using or on an indexed column causes a full table scan.
--choose
SELECT * from emp where deptno=20 or ename= ' SMITH ';
-
SELECT * FROM EMP where deptno=20
Union
SELECT * from emp where ename= ' SMITH ';
Note that the above rules are valid only for multiple indexed columns. If a column is not indexed, the query efficiency may be reduced because you did not select or.

28. Replace or with in
--The database is smart enough to replace the
SELECT * from emp where deptno=10 or deptno=20 or deptno=30;
-
SELECT * from EMP where deptno in (10,20,30);

29. Avoid using is null and is not NULL on indexed columns
To avoid using any nullable columns in the index, Oracle will not be able to use the index.
SELECT * from emp where ename is null;
SELECT * from EMP where ename are NOT null;
For single-column indexes, this record will not exist in the index if the column contains null values.
For composite indexes, if each column is empty, the same record does not exist in the index, and if at least one column is not empty, the record exists in the index.
If the uniqueness index is established on column A and column B of the table, and there is a record in the table with a A, a, or a value of (123,null),
Oracle will not accept the next record (insert) with the same A, B value (123,null).
CREATE TABLE Test (a number,b number);
Create unique index idx_test on test (A, b);
INSERT into test values (123,null);
--insert into test values (123,null);

However, if all the index columns are empty, Oracle will assume that the entire key value is empty and null is not equal to NULL.
So you can insert 1000 records with the same key value, of course they are empty!
Delete from test;
INSERT into test values (null,null);
Because null values do not exist in the index column, a null comparison of indexed columns in the WHERE clause causes Oracle to deactivate the index.
SELECT * FROM test where A is null;

30. Composite indexes always use the first column of an index
If the index is built on more than one column, the optimizer chooses to use the index only if its first column is referenced by a WHERE clause.
Create INDEX Idx_emp_job on EMP (JOB);
Create INDEX idx_emp_job_sal on EMP (JOB, SAL);
Create INDEX idx_emp_sal on EMP (SAL);
Drop index Idx_emp_job;
Drop index idx_emp_sal;
SELECT * from emp where job= ' clerk ';
SELECT * from EMP where sal=5000;
When referencing only the second column of an index, the optimizer uses a full table scan and ignores the index

31. Replace union with Union-all
When the SQL statement requires a union of two query result sets, the two result sets are merged in a union-all manner.
It is then sorted before the final result is output.
If you use UNION ALL instead of union, the order is not necessary and efficiency is improved.
SELECT * FROM Stu_oracle
UNION ALL
SELECT * from Stu_java;

32.order by using index conditions:
All columns in an order by must be contained in the same index and kept in the order of the index;
All columns in the order by must be defined as non-empty.
The index used in the WHERE clause and the index used in the ORDER BY clause cannot be tied.
SELECT * from emp order by empno;
--select * from emp order by Empno,ename;

33. Avoid changing the type of indexed columns
Oracle automatically makes simple type conversions to columns when comparing data of different data types.
SELECT * from emp where empno= ' 7788 ';
The ability to use indexes is because Oracle automatically makes simple type conversions for columns, and the converted statements are:
SELECT * from emp where empno=to_number (' 7788 ');
The type conversion does not occur on the index column, and the index is not invalidated.
CREATE TABLE Emp_test (Empno varchar2 (Ten) Primary key,ename VARCHAR2 (10));
Insert INTO Emp_test select Empno,ename from emp;

SELECT * from Emp_test where empno=7788;
SELECT * from Emp_test where to_number (empno) = 7788;
-
SELECT * from emp_test where empno= ' 7788 ';
This index will not be used because of the type conversions that occur internally!
To avoid Oracle's implicit type conversion of SQL, it is best to explicitly express the type conversions.

The WHERE clause adds the following symbol to the column without using the index
'! = ', ' | | ', ' + '
Indexes can only record key values, and cannot record no key values
SELECT * from EMP where empno!=0;
SELECT * from EMP where empno+0=7788;
-
SELECT * FROM EMP where empno>0;

36. Avoid using resource-intensive operations
SQL statements with Distinct,union,minus,intersect,order by will start the SQL engine to perform the resource-intensive sorting (sort) function.
Distinct requires a sort operation, while the others need to perform at least two sorting.
For example, a union query, where each query has a GROUP BY clause, and GROUP by triggers an embedded sort (nested sort);
In this way, each query needs to be sorted once, and then another unique sort (sort unique) operation is executed when the union is executed
And it can only begin execution after the previous embedded sort has ended.
SQL statements with Union,minus,intersect can be overridden in other ways.

37. Separating tables and indexes
The tables and indexes are built into different tablespaces (tablespaces).
Never store objects that are not part of an Oracle internal system in the systems table space.
Also, make sure that the data table space and the index table space are placed on different hard disks.

Oracle_ Advanced Features (9) Performance optimization