The recent systematic study of Oracle SQL statement Performance Tuning has come to the conclusion that many performance tuning on the web is wrong or not comprehensive.
Now the DBA Daniel are too low-key, not come out treatise, the younger brother to take this opportunity to vomit trough, said wrong, welcome to shoot bricks, especially the version of the problem:
To the point:
Most of the findings on the Web "may" apply to ORACLE8 or previous versions (the younger brother didn't see Oracle 8 at the latest), but for 9i and later versions, many of the conclusions are not comprehensive.
Here are a few of the most common questions:
Wrong opinion 1, when to use in exists? The amount of subquery data is less with in-volume multi-use exists
If you say that the semi-concatenated subquery returns less data with in and returns more data with exists, then congratulations, you're wrong.
Correction: Do not believe online is how to say, learning Oracle's biggest feeling is not to remember the conclusion, his practice to know, create two tables to practice, because of the length of reason, I still give a conclusion-through the implementation plan to see, in most cases, in and exists efficiency is identical, Just sometimes exists can not SUBSTRING unnesting, resulting in the execution plan to go filter, execution plan once the filter, the driver table is not change (12C can I do not know), imagine that the main table is 1000W, the child table returned 20, Because the driver table can not change, it is likely that the large table drive small table.
Error views 2, not in and not EXISTS subquery less with not in otherwise with not EXISTS
Not in and not exists is also a reason, but to pay attention to NULL, NULL is easy to use the index, you can create a function index or with a constant to do a composite index.
False view 3, where conditions are sequential, followed by first execution or before the first execution if the amount of filtering data is basically flat, two different predicate filter conditions may be due to the script to be written in order, but do not infer that "where the condition is sequential, followed by the first or the previous execution ", which first executes the CBO according to the statistical information analysis after the decision, the following two statements of the logical reading of the execution plan are the same
You can test the performance with the following Lezilai
- CREATE TABLE TEST02 as SELECT * from dba_objects;
- SELECT COUNT(*) from TEST02 a WHERE a. OWNER=' SYS ' and A. object_id= ;
- SELECT COUNT(*) from TEST02 a WHERE a. object_id=+ A. OWNER=' SYS ' ;
Error point 4, the FROM statement has left and right order, so pay attention to the writing order
As with the 3rd, give an example-the execution plan for the following two statements is the same, and Oracle knows which one to make the driver table, so there is no difference
- CREATE table T1 as SELECT level as ID from DUAL CONNECT to level<=10000;--Large table
- CREATE table T2 as SELECT level as ID from DUAL CONNECT by level<=;--Small table
- SELECT COUNT(1) from T1,T2;
- SELECT COUNT(1) from T2,T1;
- Execution plan
- ----------------------------------------------------------
- Plan Hash value: 4259280259
- ----------------------------------------------------------------------
- | Id | Operation | Name | Rows | cost (%CPU) | Time |
- ----------------------------------------------------------------------
- |0 ||| 1 |60 (2) |00:00:01 |
- |1 ||| 1 || |
- |2 ||| 100k|60 (2) |00:00:01 |
- |3 || T2 | 10 |3 (0) |00:00:01 |
- |4 ||| 10000 |57 (2) |00:00:01 |
- | 5 | TABLE ACCESS Full | T1 | 10000 | 6 (0) | xx: |
- ----------------------------------------------------------------------
- Statistical information
- ----------------------------------------------------------
- 0 Recursive calls
- 0 db block gets
- consistent gets
- 0 Physical Reads
- 0 Redo size
- 527 Bytes Sent via SQL*Net to client
- 519 Bytes Received via SQL*Net from client
- 2 SQL*Net roundtrips to/from client
- 1 Sorts (memory)
- 0 Sorts (disk)
- 1 rows processed
Error point 5, avoid using or to join conditions, or cause the engine to abandon using the index for a full table scan
such as: SELECT ID from T WHERE num=10 OR num=11 full table scan
Here's an example,
- create TABLE TEST02 as SELECT * from Dba_objects
- create INDEX test_02_idx_01 on TEST02 (object_ Id
- alter SESSION SET optimizer_features_enable< Span class= "pun" >= ' 9.2.0 ' ;
- select count (*) from TEST02 A WHERE Aobject_id=28 OR a.< Span class= "PLN" >object_id=29
View execution plans
- Execution plan
- ----------------------------------------------------------
- Plan Hash value: 3430686514
- ---------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost |
- ---------------------------------------------------------------------
- |0 ||| 1 |5 |2 |
- | 1 | SORT AGGREGATE | | 1 | 5 | |
- | 2 | Inlist ITERATOR | | | | |
- |* 3 | INDEX RANGE SCAN| test_02_idx_01 | 2 | Ten | 2 |
- ---------------------------------------------------------------------
- predicate information (identified by operation ID):
- ---------------------------------------------------
- 3 - access("A"." object_id "=" OR "A"." object_id "=)
The index is actually used
Summary: Online experience may be that some people read some articles, and then test the effect in a specific scenario, and even without testing on the baseless assertion, easy to mislead people, Oracle script has a multi-optimized view of different versions and other scenarios due to the implementation of different plans to lead to the conclusion of the difference, so according to the actual set, It's better to do it yourself, and the DBA won't tell you that, because they're bread where to eat.
But maybe I have some conclusions in this paper is wrong, I have to test it again to know that in fact, Oracle optimization is still a lot of small details to note, there are many ways to have the opportunity to share with you
5 Common misconceptions about Oracle optimization online
