SQL optimization example

When I read awr today, I found that an SQL statement is executed for more than 40 seconds. The statement is as follows? 1SELECTa. bill_classASpol_code, B. bill_nameASpol_name, a. bill_noAScard_no, website ASbuy_path, a. rev_clerk_codeASagent_code, a. Alias, to_char (a. re

When I read awr today, I found that an SQL statement is executed for more than 40 seconds. The statement is as follows? 1 SELECT. bill_class AS pol_code, B. bill_name AS pol_name,. bill_no AS card_no, 'website' AS buy_path,. rev_clerk_code AS agent_code,. rev_clerk_type AS agent_type, to_char (. re

Read awr and find that an SQL statement is executed for more than 40 seconds at a time. The statement is as follows:
?

1

SELECT. bill_class AS pol_code, B. bill_name AS pol_name,. bill_no AS card_no, 'website' AS buy_path,. rev_clerk_code AS agent_code,. rev_clerk_type AS agent_type, to_char (. regist_date, 'yyyy-mm-dd') AS recip_date, to_char (. chk_date, 'yyyy-mm-dd') AS pay_date, to_char (. invalid_date, 'yyyy-mm-dd') AS invalid_date, case when chk_stat = '0' then' is not written 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' voided 'when chk_stat = '4' then' 'WHEN chk_stat = '5' then' has been destroyed. 'When chk_stat = 'A' then' to be written off 'end AS recip_stat, rev_branch_no, B. bill_literal_price AS prem FROM shcvms. bill_grant_check a, shcvms. bill_class B WHERE. regist_date is not null and. bill_class = B. bill_class AND. bill_class IN ('123', '123', '123', '123', '123', '123', '123', '123', '123', '123 ', '123', '123', '123', '123', '123', '123', '123', '123', '123', '123', '123 ', '123', '123', '123', '123', '123', '123', '123', '123', '123', '123', '123 ', '123', '123', '123', '123', '123', '123', '123', '123', '123', '123', '123 ', '123', '123', '123', '123', '123', '123', '123', '123', '123', '123', '123 ', '123', '123', '123', '123', '123', '123', '123', '123', '123', '123', '123 ', '123', '123', '123', '123', '123', '123', '123', '123', '123', '123', '123 ', '123', '123', '123', '123', '123', '123', '123', '123', '123', '123', '123 ', '123', '123', '123', '123', '123', '123', '123 ')

One way to look at this SQL statement is to directly format it through PL/SQL DEV. The second method is to put it in a txt file and search for FROM ,, directly delete all the preceding items (for non-standard quantum queries ).

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 (......);

No variables are bound here, so there is no error in the explain plan for execution plan.

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

Certificate -----------------------------------------------------------------------------------------------------------------------------------------

Plan hash value: 2990887684

Bytes ---------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

Bytes ---------------------------------------------------------------------------------------

| 0 | select statement | 6452K | 553M | 151 K (5) | 00:30:19 |

| * 1 | hash join | 6452K | 553M | 151 K (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 | 151 K (5) | 00:30:19 |

Bytes ---------------------------------------------------------------------------------------

Predicate Information (identified by operation id ):

---------------------------------------------------

1-access ("A". "BILL_CLASS" = "B". "BILL_CLASS ")

2-filter ("B". "BILL_CLASS" = '000000' OR "B". "BILL_CLASS" = '000000' OR

"B". "BILL_CLASS" = '000000' OR "B". "BILL_CLASS" = '000000' OR "B". "BILL_CLASS" = '000000'

OR "B". "BILL_CLASS" = '000000' OR "B". "BILL_CLASS" = '000000' OR

"B". "BILL_CLASS" = '000000' OR "B". "BILL_CLASS" = '000000' OR "B". "BILL_CLASS" = '1

.

.

.

.

This looks very simple. The result set 7321 k = 7.3 M 7.3 M/453 M = 1.6% can be indexed, And the in is used here, and the constant is used, you can use INList ITERATOR.

I will create a virtual index and check the execution plan. If the results are really good, I will submit another application to create this index.

?

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 explain plan for is used to parse the execution plan.

?

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

Certificate -----------------------------------------------------------------------------------------------------------------------------------------

Plan hash value: 2116188717

Bytes --------------------------------------------------------------------------------------------------