An example of SQL optimization

Today to see AWR, found a SQL execution more than 40 seconds each time, the statement is as follows
?

1

SELECT A.bill_class as Pol_code, b.bill_name as Pol_name, a.bill_no as Card_no, ' Web site ' as Buy_path, A.rev_clerk_code as Agent_code, A.rev_clerk_type as Agent_type, To_char (a.regist_date, ' yyyy-mm-dd ') as Recip_date, To_char (a.chk_date, ' Y Yyy-mm-dd ') as Pay_date, To_char (a.invalid_date, ' yyyy-mm-dd ') as invalid_date, case when chk_stat = ' 0 ' THEN ' not write-off ' when Chk_stat = ' 1 ' and autochkflag = ' 1 ' THEN ' has been automatically written off ' when chk_stat = ' 1 ' and Autochkflag = ' 0 ' THEN ' has been manually written off ' when chk_stat = ' 2 ' THEN ' lost ' when chk_stat = ' 3 ' THEN ' void ' when chk_stat = ' 4 ' THEN ' back to pay the write-off ' when chk_stat = ' 5 ' THEN ' destroyed ' when chk_stat = ' A ' THEN ' to be cancelled ' end as Recip_stat, Rev_branch_no, B.bill_literal_price as Prem from Shcvms.bill_grant_check A, shcvms.b Ill_class b WHERE A.regist_date is isn't NULL and A.bill_class = B.bill_class and A.bill_class in (' 1001 ', ' 1093 ', ' 1096 ', ' 1097 ', ' 1098 ', ' 1099 ', ' 1100 ', ' 1302 ', ' 1303 ', ' 1305 ', ' 1306 ', ' 1910 ', ' 1911 ', ' 1912 ', ' 1913 ', ' 1914 ', ' 1915 ', ' 1916 ', ' 1 ' 917 ', ' 1918 ', ' 1919 ', '1922 ', ' 1923 ', ' 1924 ', ' 1925 ', ' 1926 ', ' 1927 ', ' 1928 ', ' 1929 ', ' 1930 ', ' 1931 ', ' 1935 ', ' 1936 ', ' 1937 ', ' 1938 ', ' 1939 ', ' 19 ' 40 ', ' 1941 ', ' 1942 ', ' 1943 ', ' 1944 ', ' 1945 ', ' 5232 ', ' 5233 ', ' 5234 ', ' 5252 ', ' 5255 ', ' 5258 ', ' 5265 ', ' 5260 ', ' 5276 ', ' 5277 ' ', ' 5278 ', ' 5285 ', ' 5290 ', ' 5301 ', ' 5304 ', ' 5309 ', ' 5311 ', ' 5329 ', ' 5330 ', ' 5359 ', ' 5376 ', ' 5507 ', ' 5512 ', ' 5514 ', ' 5516 ' , ' 5122 ', ' 5126 ', ' 5130 ', ' 5132 ', ' 5141 ', ' 5142 ', ' 5156 ', ' 5157 ', ' 5161 ', ' 5162 ', ' 5163 ', ' 5164, ' 5165 ', ' 5166 ', ' 5167 ', ' 5168 ', ' 5558 ', ' 5564 ', ' 5507 '

Look at this kind of SQL, a method, is through the Pl/sql Dev direct format, the second method, put into TXT file, search from, directly delete all the previous (non-standard quantum query).

SELECT ...

From Shcvms.bill_grant_check A, Shcvms.bill_class b

WHERE A.regist_date is not NULL and

A.bill_class = B.bill_class

and A.bill_class in (...);

There is also no binding variable, so the explain plan for execution is not error-free

The current execution plan is as follows

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24-25

Plan_table_output--------------------------------------------------------------------------------------------- --------------------------------------------Plan Hash value:2990887684  ------------------------------------ --------------------------------------------------- | id  | operation      | name         | rows  | Bytes | Cost (%CPU) | time     | ---------------------------------------------------------------------------------------|   0 | SELECT statement   |              |   6452k|   553m|   151k  (5) | 00:30:19 | |*  1 |  HASH join     |               |  6452k|   553m|   151k  (5) | 00:30:19 | |*  2 |   TABLE ACCESS full| Bill_class       |   206 |  5150 |     3   (0) | 00:00:01 | |*  3 |   TABLE ACCESS full| Bill_grant_check |  7321k|   453m|   151k  (5) | 00:30:19 | ---------------------------------------------------------------------------------------  Predicate Information (identified by Operation ID):---------------------------------------------------      1-access ("A".) Bill_class "=" B "." Bill_class ")    2-filter (" B "). Bill_class "= ' 1001 ' OR" B "." Bill_class "= ' 1093 ' OR            ' B '." Bill_class "= ' 1096 ' OR" B "." Bill_class "= ' 1097 ' OR" B "." Bill_class "= ' 1098 '           or" B "." Bill_class "= ' 1099 ' OR" B "." Bill_class "= ' 1100 ' OR            ' B '." Bill_class "= ' 1302 ' OR" B "." Bill_class "= ' 1303 ' OR" B "." Bill_class "= ' 1。 。 。 。

This looks very simple, 7321k=7.3m 7.3m/453m =1.6% This result set, can walk the index completely, and here uses in, and inside uses is the constant, can go completely INList iterator

I create a virtual index, look at the execution plan, and if the effect is really good, ask for an application to create the index real.

?

1 2 3 4 5 6 7 8

Sql> CREATE index Billgrant_class on Shcvms.bill_grant_check (Bill_class) nosegment;   Index created.   Sql> alter session Set "_use_nosegment_indexes" =true; Session altered.

Then use explain plan to resolve the execution plans

?

1 2 3 4 5

Explain plan for SELECT/*+ Index (a billgrant_class) */n multiple columns from Shcvms.bill_grant_check A, Shcvms.bill_class b WHERE A.regist_date is isn't NULL and A.bill_class = B.bill_class and A.bill_class in (..... N Multiple constants)

Let's look at the execution plan.

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22-23

Plan_table_output--------------------------------------------------------------------------------------------- --------------------------------------------Plan Hash value:2116188717  ------------------------------------ -------------------------------------------------------------- | id  | operation             | name         | rows  | Bytes | Cost (%CPU) | time     | --------------------------------------------------------------------------------------------------|    0 | SELECT statement          |           |  6452k|   553m|   207   (1) | 00:00:03 | |   1 |  MERGE join           |           |  6452k|   553m|   207   (1) | 00:00:03 | |   2 |   INLIST iterator         |           |   |   |         |      | |*  3 |    TABLE ACCESS by INDEX rowid| Bill_grant_check |  7321k|   453m|   203   (1) | 00:00:03 | |*  4 |     INDEX RANGE scan           | billgrant_class  |  7321k|   |    16   (0) | 00:00:01 | |*  5 |   SORT join           |           |   206 |  5150 |     4  (25 )| 00:00:01 | |*  6 |    TABLE ACCESS full          | bill_class   |   206 |  5150 |     3   (0) | 00:00:01 | --------------------------------------------------------------------------------------------------  predicate information (identified by Operation ID):---------------------------------------------------   & Nbsp; 3-filter ("A".) Regist_date "is not NULL"    4-access ("A".) Bill_class "= ' 1001 ' OR" A ". Bill_class "= ' 1093 ' OR           " A ". Bill_class "= ' 1096 ' OR" A ". Bill_class "= ' 1097 ' OR" A ". Bill_class "= ' 1098 ' OR           " A ". Bill_class "= ' 1099 ' OR" A ". Bill_class "= ' 1100 ' OR" A ". Bill_class "= ' 1302 ' OR

Look at the cost. From the previous 151k to the current 207, the speed increase to the original 1/1000. In the case of correct statistics, the cost is of reference value (if not correct, don't look at the price)

Now it's time to ask for an application. True to create this index.

A simple example is done, (although so simple, do not want to go to this post, but my blog "Oracle Optimization SQL Optimization" Branch space, write something to enrich it)