SQL statement optimization for orcle (1)

1. Select the appropriate Oracle optimizer
There are 3 types of Oracle Optimizer:
A. Rule (rule-based) B. Cost (based on costs) c. CHOOSE (selectivity)
The default optimizer can be set by various declarations of the Optimizer_mode parameter in the Init.ora file, such as Rule,cost,choose,all_rows,first_rows. You can certainly overwrite it at the SQL sentence level or at the session level.
In order to use the cost-based optimizer (CBO, cost-based Optimizer), you must frequently run the Analyze command to increase the accuracy of the object statistics (objects statistics) in the database.
If the optimizer mode of the database is set to selective (CHOOSE), then the actual optimizer mode will be related to whether the Analyze command has been run. If the table has been analyze, the optimizer mode will automatically become the CBO, whereas the database will use the rule-form optimizer.
By default, Oracle uses the Choose Optimizer, and to avoid unnecessary full table scans, you must try to avoid using the Choose Optimizer directly, using either a rule-based or cost-based optimizer.

2. How to Access table

ORACLE uses two ways to access records in a table:
A. Full table scan
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).
B. Accessing the table through ROWID
You can use ROWID-based access to improve the efficiency of your Access tables, ROWID contains the physical location information that is recorded 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.

3. Shared SQL statements

In order not to parse the same SQL statements repeatedly, after the first resolution, Oracle stores the SQL statements in memory. This is a shared pool located in the system global area of the SGA (total buffer pool) Memory can be shared by all database users. So, when you execute an SQL statement (sometimes referred to as a cursor), if it is exactly the same as the previous executed statement, Oracle can quickly get the parsed statement and the best execution path. This feature of Oracle greatly improves the performance of SQL execution and saves memory usage.
Unfortunately, Oracle only provides caching (cache buffering) for simple tables, which does not apply to multi-table connection queries.
The database administrator must set the appropriate parameters for this region in Init.ora, and when the memory area is larger, more statements can be kept, and the likelihood of sharing is greater.
When you submit an SQL statement to Oracle, Oracle will first look for the same statement in this block of memory.
It is important to note that Oracle takes a strict match between the two, and to achieve sharing, the SQL statement must
Exactly the same (including spaces, line breaks, and so on).
A shared statement must meet three conditions:
A. Character-level comparisons:
The statements that are currently executed and those in the shared pool must be identical.
For example:
SELECT * from EMP;
It's different from every one of the following.
SELECT * from EMP;
Select * from EMP;
SELECT * from EMP;
B. The object that the two statement refers to must be exactly the same:
For example:
How user object names are accessed
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 these two users.


Sql
Can I share
Reason
Select Max (sal_cap) from Sal_limit;
No
Each user has a private synonym-sal_limit, which are different objects

Select count (*0 from work_city where Sdesc like ' new% ';
Yes
Two users access the same object public synonym-work_city

Select A.sdesc,b.location from Work_city A, plant_detail b where a.city_id = b.city_id
No
User Jack accesses plant_detail through private synonym and Jill is the owner of the table, with different objects.
C. Binding variables in two SQL statements that must use the same name (bind variables)
For example:
The first group of two SQL statements are the same (can be shared), while two statements in the second group are different (even at run time, the same values are assigned to different binding variables)
A.
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 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 table that is written in the FROM clause (base table driving tables) will be processed first. In cases in which the FROM clause contains more than one table, you must select the table with the fewest number of record bars as the underlying table. When Oracle processes multiple tables, it connects them in a sort and merge manner. First, scan the first table (the last table in the FROM clause) and dispatch the records, and then scan the second table ( The last second table in the FROM clause), and finally merges all records retrieved from the second table with the appropriate records from the first table.


For example:
Table TAB1 16,384 Records
Table TAB2 1 Records
Select TAB2 as the base table (the best method)
Select COUNT (*) from TAB1,TAB2 execution time 0.96 seconds
Select TAB2 as the base table (poor method)
Select COUNT (*) from TAB2,TAB1 execution time 26.09 seconds
If you have more than 3 tables connected to the query, you need to select the crosstab (intersection table) as the underlying table, which refers to the table that is referenced by the other table.
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 and 2000
and e.cat_no = C.cat_no
and E.LOCN = L.LOCN
will be more efficient than the following SQL
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 and 2000

5. The connection order in the WHERE clause.
Oracle uses a bottom-up sequential parsing where clause, according to which the connection between tables must be written before other where conditions, and those that can filter out the maximum number of records must be written at the end of the WHERE clause.
For example:
(Low efficiency, execution time 156.3 seconds)
SELECT ...
From EMP E
WHERE SAL > 50000
and JOB = ' MANAGER '
< (SELECT COUNT (*) from EMP
WHERE mgr=e.empno);
(High efficiency, execution time 10.6 seconds)
SELECT ...
From EMP E
WHERE < (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, it is a convenient way to refer to ' * ' Using a dynamic SQL column. Unfortunately, this is a very inefficient approach. In fact, in the process of parsing, Oracle translates ' * ' into all column names, which is done by querying the data dictionary, which means more time is spent.

7. Reduce the number of Access databases
Oracle performs a lot of work internally when executing each SQL statement: Parsing SQL statements, estimating index utilization, binding variables, reading blocks, and so on. Thus, reducing the number of accesses to the database can actually reduce the workload of Oracle.
For example
There are three ways to retrieve employees with an employee number equal to 0342 or 0291.
Method 1 (Minimum effect)
SELECT Emp_name, SALARY, GRADE
From EMP
WHERE emp_no = 342;
SELECT Emp_name, SALARY, GRADE
From EMP
WHERE emp_no = 291;
Method 2 (sub-inefficiency)
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 into ...,..,.. ;
.....
OPEN C1 (291);
FETCH C1 into ...,..,.. ;
CLOSE C1;
END;
Method 3 (efficient)
SELECT A.emp_name, A.salary, A.grade,
B.emp_name, B.salary, B.grade
From EMP a,emp B
WHERE A.emp_no = 342
and b.emp_no = 291;
Attention:
The ArraySize parameter is reset in Sql*plus, Sql*forms, and pro*c to increase the amount of data retrieved per database access, with a recommended value of 200

SQL statement optimization for orcle (1)