Oracle 10g SQL optimization and re-Learning

Author: Jiangnan Baiyi
From 8 I to 10g, Oracle constantly evolved its own SQL tuning intelligence, and some secret-level optimization tips have expired.
However, I like to be ineffective. I don't have to worry about it. I love to stick to toad for Oracle Xpert, and I love to do things based on the general direction of comfort and server optimization.

1. excution plan
Excution plan is the most basic optimization concept, no matter how complicated your optimization is, the results still need to be indicated by the excution plan, which indexes are used in Oracle, the order in which the tables are connected, full table scan or access by rowid index, and the bottleneck. Without its guidance, all tuning would be tricky.

2. toad for Oracle Xpert
It is really comfortable to use it for optimization. After acquiring Lecco, quest integrates it into Toad's SQL tunning: the clearest execution plan shows that it automatically generates n equivalent SQL statements and provides optimization suggestions, comparison of Different SQL Execution plans, as well as actual logical reads, physical reads, and so on.

3. Index
Most of the performance problems are actually indexing applications, where clauses, order by, and group by all use indexes.
Generally, developers think that they can go home from work after completing the index, but there are still a lot of ideas and traps.

3.1 do not calculate the index Column
This is the most common failure trap, such as where trunc (order_date) = trunc (sysdate), I + 2> 4. The reason for the index failure is also simple. The index is a binary tree built for the original value. After you toss the column value * 3/4 + 2, the original binary tree will certainly not be used. Solution:
1. Replace it with equivalent syntax, for example, replace trunc (order_date)

Where order_date> trunc (sysdate)-1 and order_date <trunc (sysdate) + 1

2. Create function indexes for computing.

Create index I _xxxx on shop_order (trunc (order_date ))

3. Move the calculation from the left side of the equal sign to the right side
This is an error-free correction. Change a * 2> 4 to a> 4/2. Change to_char (ZIP) = '20160301' to zip = to_number ('20160301 ');

3.2 CBO and index Selectivity
An index may not be used by Oracle, just like a picky child. The cost-based optimizer (CBO, cost-based Optimizer) first looks at the table size and index repetition, and then decides whether to use or not. There are 100 records in the table, and 80 of them are non-duplicate index key values. The selectivity of this index is 80/100 = 0.8. Note that toad displays the index selective and cardinailty. If you are not obedient, you must use hints for instruction.
In addition, when multiple indexes are available in the where statement, only one of them is selected. So the more indexes, the better :)

3.3 index Reconstruction
It is said that Oracle will discard this index when data updates frequently cause 20% fragmentation. Rather trusted, you should always alter index <indexname> rebuild.

3.4 other notes
Do not use not, such as goods_no! = 2. Change

Where goods_no> 2 or goods_no <2

Do not use is null. For example, change where dept_code is not null

Where dept_code> = 0;

3.5 if all columns in the SELECT statement are indexed columns
If there is no where condition or the where condition is all an index column, Oracle will directly obtain data from the index instead of reading the real data table, which theoretically will be much faster, such

Select order_no, order_time from shop_order where shop_no = 4

When order_no, order_time, and shop_no are all indexed columns, you will see an execution plan that is completely different from the normal execution plan.

3.6 bitmap Index
In the legend, when there are few data values, such as columns indicating classification and status, bitmap indexes should be created instead of ordinary binary tree indexes. Otherwise, the efficiency will be low. However, according to the execution plan, these Bitmap indexes are rarely favored by Oracle.

4. Reduce round-trip and query tables
This is also a very simple principle. The interaction cost between a program and Oracle is extremely high. Therefore, do not perform two separate queries if one query can be completed. If one query is executed cyclically for 10 thousand queries, why can't I go anywhere.

4.1 encapsulate PL/SQL stored procedures
The most advanced method is to encapsulate cyclic operations into the stored procedure written by PL/SQL. Because the stored procedure is executed on the server, there is no data round-trip consumption.

4.2 encapsulate PL/SQL internal functions
There is a chance to encapsulate some queries into functions, and using these functions in common SQL is also very effective optimization.

4.3 deCODE/case
But the stored procedure is also troublesome, so the case/decode statement combines duplicate queries with the same conditions into one statement:

Select
Count (case when price <13 then 1 else null end) low,
Count (case when price between 13 and 15 then 1 else null end) med,
Count (case when price> 15 then 1 else null end) high
From products;

4.4 Where/update syntax

Select tab_name from tables
Where (tab_name, db_ver) = (select tab_name, db_ver)
From tab_columns where version = 604)

Update EMP
Set (emp_cat, sal_range)
= (Select max (Category) from emp_categories)

5. Other Optimizations
5.1rowid and rownum
Even the new version of hibernate supports rowid, which proves to be very useful. For example, the fastest way to delete duplicate data is as follows:

Delete from EMP E
Where E. rowid> (select Min (X. rowid)
From EMP X
Where X. emp_no = E. emp_no );

 

6. Ultimate tips-Hints
This is a toy of Oracle DBA and a fuse weapon. For example, if Oracle's choice in CBO and RBO is not satisfactory, you can use it to strongly tune oracle. The results are often overjoyed.
If developers don't have much time to learn about it, they can rely on Toad SQL opmitzer to automatically generate these prompts, and then compare the actual effects of various prompts. However, with the evolution of 10g intelligence, hints has fewer surprises.

7. Find the top SQL to be optimized
After a long time, it would be depressing if the enemy could not be found.
Thanks to the excellent performance of 10 Gb. On the web management page, you can view the SQL list of the current or any day, sorted by performance.
With it, neither SQL trace nor tkprof can be used.