SQL optimization example

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 partition ---------- Plan hash value: 2990887684 Bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | -------------------------------------------------------------------- ------------------- | 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 | descripredicate Information (iden Tified by operation id): --------------------------------------------------- 1-access ("". "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. regist_date is not null and. bill_class = B. bill_class AND. 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 tables ---------- Plan hash value: 2116188717 Bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | latency | 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 | descripredicate Information (identified by operation id ): ------------------------------------------------- 3-filter ("". "REGIST_DATE" is not null) 4-access ("". "BILL_CLASS" = '000000' OR "". "BILL_CLASS" = '000000' OR "". "BILL_CLASS" = '000000' OR "". "BILL_CLASS" = '000000' OR "". "BILL_CLASS" = '000000' OR "". "BILL_CLASS" = '000000' OR "". "BILL_CLASS" = '000000' OR "". "BILL_CLASS" = '000000' OR

See Cost. From the previous 151 k to the current 207, the speed has increased to 1/1000. Cost has reference value when the statistics are correct (if the statistics are incorrect, do not look at cost)

You can submit an application now. Create this index.

A simple example is complete (although it's so simple, I don't want to post it here, but I am in the blog "Oracle optimization SQL optimization" branch space, write something to enrich it)