Oracle SQL Performance Optimization 1

1. select an appropriate ORACLE optimizer

2. Access Table

3. Share SQL statements

4. Select the most efficient table name sequence (only valid in the rule-based Optimizer)

5. The connection sequence in the WHERE clause.

6. Avoid '*' in the SELECT clause '*'

7. Reduce the number of database accesses

 

1. select an appropriate ORACLE optimizer
There are three optimizer types in ORACLE:

• A. RULE (based on Rules)
• B. COST (COST-based)
• C. CHOOSE (optional)

Set 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. Access Table
ORACLE uses two methods to access table records:

• A. Full table Scan
  Full table scan refers to sequential access to each record in the table. ORACLE optimizes full table scan by reading multiple data blocks at a time.
• B. Access the table through ROWID
  You can use ROWID-based access to improve the efficiency of accessing tables. 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. Share SQL statements
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:

• A. Character-level comparison:

  The statements currently executed must be the same as those in the shared pool.

  For example:

  SELECT * from emp;

  Different from each of the following

  SELECT * from EMP;

  Select * From Emp;

  SELECT * from emp;

• B. The objects referred to by the two statements must be identical:

  For example:

  How to access user object names

  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.

  SQL
  Can it be shared?
  Cause

  Select max (sal_cap) from sal_limit;
  No
  Each user has a private synonym-sal_limit, which is a different object.

  Select count (*) from work_city where sdesc like 'new % ';
  Yes
  The 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 table owner, with different objects.
  

• C. bind variables must be used in the two SQL statements)

  For example:
  The two SQL statements in the first group are the same (which can be shared), while the two statements in the second group are different (even if different bind variables have the same value at runtime)
  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 sequence (only valid in the rule-based Optimizer)

The ORACLE parser followsFrom right to leftFROM clause. Therefore, the base table driving table written in the FROM clause is first processed. If the FROM clause contains multiple tables,You must select a 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:

Table TAB1 16,384 records

Table TAB2 1 record

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 more than three tables are connected for query, You need to select an intersection table as the base table, which is the table referenced by other tables.

For example:

The EMP table describes the intersection between 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

It will be more efficient than the following SQL statements

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 <(select count (*) FROM EMP

Where mgr = E. EMPNO );

(Efficient, execution time: 10.6 seconds)

SELECT...

FROM EMP E

WHERE 25 <(select count (*) FROM EMP

Where mgr = E. EMPNO)

And sal> 50000

And job = 'manager ';

6. Avoid '*' 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 database accesses

When each SQL statement is executed, ORACLE performs a lot of internal work: parsing SQL statements, estimating index utilization, binding variables, and reading data blocks. 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 with employee numbers equal to 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 (low efficiency)

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 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;

Note:

You can reset the ARRAYSIZE parameter in SQL * Plus, SQL * Forms, and Pro * C to increase the retrieval data volume for each database access. The recommended value is 200.

 

> Next page>