Shulou(Shulou.com)05/31 Report--

This article mainly introduces oracle how to use outline fixed implementation plan examples, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let Xiaobian take you to understand.

1. View the database wait event now

SQL > select event,count (*) from v$session_wait group by event

EVENT COUNT (*)

-

SQL*Net message from client 85

SQL*Net message to client 1

Buffer busy waits 3

Db file scattered read 2

Enqueue 1

Pmon timer 1

Rdbms ipc message 7

Smon timer 1

8 rows selected.

Compared with the previous waiting events, enqueue,buffer busy waits has dropped a lot.

two。 Take a look at the sql statements that are causing enqueue to wait.

SQL > SELECT DECODE (request,0,'Holder:', 'Waiter:') | | sid sess, id1, id2

Lmode, request, type FROM V$LOCK WHERE (id1, id2, type) IN (SELECT id1

Id2, type FROM V$LOCK WHERE request > 0) ORDER BY id1, request

SESS ID1 ID2

LMODE REQUEST TYPE

-

-

Holder: 28 720940 432

6 0 TX

Waiter: 59 720940 432

0 6 TX

SQL > SELECT / * + ORDERED * / sql_text FROM v$sqltext a WHERE (a.hash_value

A.address) IN (SELECT

DECODE (sql_hash_value,0,prev_hash_value,sql_hash_value), DECODE (sql_hash_value,0,prev_sql_addr)

Sql_address) FROM v$session b WHERE b.sid = 28) ORDER BY piece ASC

SQL_TEXT

Update tab_test_sshz set jksj =: 1, clockfacktor =: 2 where

Yhsbh =: 3 and skph =: 4 and fplx_dm =: 5

SQL > /

SQL_TEXT

Update tab_test_sshz set jksj =: 1, clockfacktor =: 2 where

Yhsbh =: 3 and skph =: 4 and fplx_dm =: 5

Look at the waiting event after a few minutes.

SQL > /

EVENT COUNT (*)

-

SQL*Net message from client 91

SQL*Net message to client 1

Db file scattered read 1

Db file sequential read 1

Pmon timer 1

Rdbms ipc message 7

Smon timer 1

7 rows selected.

You can see that the two enqueue,buffer busy waits wait events have disappeared, and the previous update statement

The blockage has been automatically released

The update statement was previously blocked for the following delete statement

Delete from tab_test_hz_zb a where exists (select 'y' from tab_test_sshz b where a.kphzjs_id = b.kphzjs_id and b.skph =: 1

And b.fplx_dm =: 2 and b.kpqssj = TO_Date (: 3, 'yyyy-mm-dd'))

Update tab_test_sshz set jksj =: 1, clockfacktor =: 2 where

Yhsbh =: 3 and skph =: 4 and fplx_dm =: 5

3. View the execution plan of sql that used to block frequently before

Delete from SKSKJ.tab_test_hz_zb a

Where exists (select 'y')

From SKSKJ.tab_test_hz b

Where a.kphzjs_id = b.kphzjs_id

And b.skph =: 1

And b.fplx_dm =: 2

And b.kpqssj = TO_Date (: 3, 'yyyy-mm-dd'))

PLAN_TABLE_OUTPUT

-

-

| | Id | Operation | Name | Rows | Bytes |

| | Cost |

-

| | 0 | DELETE STATEMENT |

| | |

| | 1 | DELETE | tab_test_hz_ZB |

| | |

| | * 2 | FILTER |

| | |

| | 3 | TABLE ACCESS FULL | tab_test_hz_ZB |

| | |

| | * 4 | TABLE ACCESS BY INDEX ROWID | tab_test_hz |

| | |

| | * 5 | INDEX UNIQUE SCAN | PKtab_test_hz |

| | |

-

PLAN_TABLE_OUTPUT

-

Predicate Information (identified by operation id):

2-filter (EXISTS (SELECT 0 FROM "SKSKJ". "tab_test_hz"B" WHERE)

"B". "KPHZJS_ID" =: B1 AND "B". "KPQSSJ" = TO_DATE.

AND "B". "FPLX_DM" =: Z

AND "B". "SKPH" =: Z)

4-filter ("B". "KPQSSJ" = TO_DATE AND "B". "FPLX_DM" =: Z

AND

"B". SKPH "=: Z)

5-access ("B". "KPHZJS_ID" =: B1)

PLAN_TABLE_OUTPUT

-

Note: rule based optimization

23 rows selected.

It is found that the tab_test_hz_ZB table is a full table scan, a.kphzjs_id has an index, and the database is optimized.

The device mode is rule

Query the oracle document and learn that in the rule-based optimizer mode

If the a.kphzjs_id = b. Kphzjsexpressiid.kphzjsSecretid expression acts on a field, regardless of the word

RBO scans all tables with or without indexes.

After setting the optimizer for CHOOSE at the session level, the tab_test_hz_ZB table is indexed.

SQL > alter session set optimizer_mode = CHOOSE

Session altered.

SQL > explain plan for

2 delete from SKSKJ.tab_test_hz_zb a

3 where exists (select 'y')

4 from SKSKJ.tab_test_hz b

5 where a.kphzjs_id = b.kphzjs_id

6 and b.skph =: 1

7 and b.fplx_dm =: 2

8 and b.kpqssj = TO_Date (: 3, 'yyyy-mm-dd'))

Explained.

SQL > select plan_table_output from table (dbms_xplan.display ())

PLAN_TABLE_OUTPUT

-

-

| | Id | Operation | Name | Rows | |

Bytes | Cost |

-

| | 0 | DELETE STATEMENT | | 1 |

101 | 11 |

| | 1 | DELETE | tab_test_hz_ZB |

| | |

| | 2 | NESTED LOOPS | | 1 |

101 | 11 |

| | 3 | SORT UNIQUE |

| | |

| | 4 | TABLE ACCESS BY INDEX ROWID | tab_test_hz | 1 |

57 | 4 |

| | * 5 | INDEX RANGE SCAN | C01 $SKPH_FPLXDM_KPQSSJ | 1 |

| | 3 |

| | * 6 | INDEX RANGE SCAN | PKtab_test_hz_ZB | 1 |

44 | 2 |

PLAN_TABLE_OUTPUT

-

-

Predicate Information (identified by operation id):

5-access ("B". "SKPH" =: Z AND "B". "FPLX_DM" =: Z AND

"B". "KPQSSJ" = TO_DATE (: Z _ maxiyyyy _ MMMI _ ddd')

6-access ("A". "KPHZJS_ID" = "B". "KPHZJS_ID")

Note: cpu costing is off

21 rows selected.

Due to historical reasons, the database optimizer mode cannot be changed, nor can the program be modified using HINT prompts. It is recommended that outline be used to change the execution plan fixed to indexed.

4. The fixed implementation plan is as follows:

4.1. View the original statement execution plan:

SQL > explain plan for

Delete from SKSKJ.tab_test_hz_zb a

Where exists (select 'y')

From SKSKJ.tab_test_hz b

Where a.kphzjs_id = b.kphzjs_id

And b.skph =: 1

And b.fplx_dm =: 2

And b.kpqssj = TO_Date (: 3, 'yyyy-mm-dd'))

SQL > select plan_table_output from table (dbms_xplan.display ()); view the original statement

Execute the plan:

View the execution plan of the index after adding the hist prompt

SQL > explain plan for

2 delete from SKSKJ.tab_test_hz_zb a

3 where exists (select / * + index (SKSKJ.tab_test_hz)

C01 $SKPH_FPLXDM_KPQSSJ) * /'y'

4 from SKSKJ.tab_test_hz b

5 where a.kphzjs_id = b.kphzjs_id

6 and b.fplx_dm =: 2

7 and b.skph =: 1

8 and b.kpqssj = TO_Date (: 3, 'yyyy-mm-dd'))

SQL > select plan_table_output from table (dbms_xplan.display ())

4.2. Create outlines

Create two public stroed outline, the first is currently running, the second is to add hints.

Create or replace outline tab_test_hz_zb_full on delete from

SKSKJ.tab_test_hz_zb a

Where exists (select 'y')

From SKSKJ.tab_test_hz b

Where a.kphzjs_id = b.kphzjs_id

And b.skph =: 1

And b.fplx_dm =: 2

And b.kpqssj = TO_Date (: 3, 'yyyy-mm-dd'))

Create or replace outline tab_test_hz_zb_index on delete from

SKSKJ.tab_test_hz_zb a

Where exists (select / * + index (SKSKJ.tab_test_hz C01 $SKPH_FPLXDM_KPQSSJ)

) * /'y'

From SKSKJ.tab_test_hz b

Where a.kphzjs_id = b.kphzjs_id

And b.fplx_dm =: 2

And b.skph =: 1

And b.kpqssj = TO_Date (: 3, 'yyyy-mm-dd'))

Session parameters environment settings:

Alter session set query_rewrite_enabled = true

Alter session set star_transformation_enabled = true

Create an outline table in the current mode to ensure that OUTLN users exist:

Exec dbms_outln_edit.create_edit_tables

Prepare the execution plan for exchanging two stored outline

Create or replace private outline PRIV_tab_test_hz_ZB_F from

Tab_test_hz_zb_full

Create or replace private outline PRIV_tab_test_hz_ZB_I from

Tab_test_hz_zb_index

-- must use the same session as the above command

UPDATE OL$HINTS

SET

OL_NAME=DECODE (OL_NAME,'PRIV_tab_test_hz_ZB_F','PRIV_tab_test_hz_ZB_I','PRIV_tab_test_hz_ZB_I','PRIV_tab_test_hz_ZB_F')

WHERE OL_NAME IN ('PRIV_tab_test_hz_ZB_F','PRIV_tab_test_hz_ZB_I')

Commit

SQL > set line 200

SQL > select OL_name,HINT_TEXT from ol$hints

-- refresh outline information in memory

Alter session set use_private_outlines=true; refresh

Execute

Dbms_outln_edit.refresh_private_outline ('PRIV_tab_test_hz_ZB_F')

Execute

Dbms_outln_edit.refresh_private_outline ('PRIV_tab_test_hz_ZB_I'); publish to

Public outline

-- create or update public outline

Create or replace outline tab_test_hz_zb_full from private

PRIV_tab_test_hz_ZB_F

Create or replace outline tab_test_hz_zb_index from private

PRIV_tab_test_hz_ZB_I; setting is used. Adjustment completed.

Alter system set use_stored_outlines=true; enables outlines

4.3 Verification:

Check whether the execution plan of the statement is the execution plan of the index after the hist prompt was added before. If so, it means fixed.

Successful execution of the plan

SQL > explain plan for

Delete from SKSKJ.tab_test_hz_zb a

Where exists (select 'y')

From SKSKJ.tab_test_hz b

Where a.kphzjs_id = b.kphzjs_id

And b.skph =: 1

And b.fplx_dm =: 2

And b.kpqssj = TO_Date (: 3, 'yyyy-mm-dd'))

SQL > select plan_table_output from table (dbms_xplan.display ())

5 Summary

5.1 you can first use outline to fix the execution plan of the delete statement to walk the index

5.2 because of the initrans=1 of the tab_test_ SSHZ table and the tab_test_hz_zb table, if these two tables

Visits are frequent. It is recommended to change it to 4 or 5.

5.3 current database db_cache_size=128M, in view of the large amount of buffer busy when the database is slow in the past

Waits is waiting, and it is recommended to increase it.

Thank you for reading this article carefully. I hope the article "oracle how to use outline fixed execution Plan" shared by the editor will be helpful to you. At the same time, I also hope you will support us and follow the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.