Shulou(Shulou.com)06/01 Report--

The coe_load_sql_profile_v2.sql script uses the profile principle and is only used in a semi-automatic form. Here is the test process.

Coe_load_sql_profile_v2.txt

Create the environment and build the test table:

SQL > create table T1 as select * from dba_objects where object_id is not null

Table created.

SQL > alter table T1 modify object_id not null

Table altered.

SQL > create index idx_t1_obj_id on T1 (object_id)

Index created.

The index does not store null values

SQL > analyze table T1 compute statistics

Table analyzed.

SQL > select count (*) from T1

COUNT (*)

-

87070

SQL > select * from table (dbms_xplan.display_cursor (null,0))

PLAN_TABLE_OUTPUT

-

SQL_ID 12c0v4my7dvr3, child number 0

-

Select count (*) from T1

Plan hash value: 1657298618

-

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

-

| | 0 | SELECT STATEMENT | 54 (100) | |

| | 1 | SORT AGGREGAT | | 1 |

PLAN_TABLE_OUTPUT

-

| | 2 | INDEX FAST FULL SCAN | IDX_T1_OBJ_ID | 87070 | 54 (0) | 00:00:01 |

14 rows selected.

You will find that the index is scanned quickly.

Using the hint prompt, force a full table scan, and then generate an execution plan.

SQL > select / * + full (T1) * / count (*) from T1

COUNT (*)

--

87070

SQL > select * from table (dbms_xplan.display_cursor (null,0))

PLAN_TABLE_OUTPUT

-

SQL_ID 20pat9zfypprh, child number 0

-

Select / * + full (T1) * / count (*) from T1

Plan hash value: 3724264953

-

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

-

| | 0 | SELECT STATEMENT | 347100 | | |

| | 1 | SORT AGGREGATE | | 1 | | |

PLAN_TABLE_OUTPUT

-

| | 2 | TABLE ACCESS FULL | T1 | 87070 | 347 (1) | 00:00:05 |

-

14 rows selected.

SQL > @ coe_load_sql_profile_v2.sql

Parameter 1:

SQL_ID (required)

Enter value for 1: 12c0v4my7dvr3

PLAN_HASH_VALUE AVG_ET_SECS

--

1657298618. 141

Parameter 2:

PLAN_HASH_VALUE (required)

Enter value for 2: 3724264953

Values passed to coe_xfr_sql_profile:

~ ~

SQL_ID: "12c0v4my7dvr3"

PLAN_HASH_VALUE: "3724264953"

SQL > BEGIN

2 IF: sql_text IS NULL THEN

3 RAISE_APPLICATION_ERROR (- 20100, 'SQL_TEXT for SQL_ID & & sql_id. Was not found in memory (gv$sqltext_with_newlines) or AWR (dba_hist_sqltext).')

4 END IF

5 END

6 /

SQL > SET TERM OFF

SQL > BEGIN

2 IF: other_xml IS NULL THEN

3 RAISE_APPLICATION_ERROR (- 20101, 'PLAN for SQL_ID & & sql_id. And PHV & & plan_hash_value. Was not found in memory (gv$sql_plan) or AWR (dba_hist_sql_plan).)

4 END IF

5 END

6 /

SQL > SET TERM OFF

Execute coe_xfr_sql_profile_12c0v4my7dvr3_3724264953.sql

On TARGET system in order to create a custom SQL Profile

With plan 3724264953 linked to adjusted sql_text.

. Omit

COE_XFR_SQL_PROFILE_12c0v4my7dvr3_3724264953 completed

COE_XFR_SQL_PROFILE completed.

SQL > explain plan for select count (*) from T1

Explained.

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

PLAN_TABLE_OUTPUT

-

Plan hash value: 3724264953

-

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

-

| | 0 | SELECT STATEMENT | | 1 | 347 (1) | 00:00:05 |

| | 1 | SORT AGGREGATE | | 1 |

| | 2 | TABLE ACCESS FULL | T1 | 87070 | 347 (1) | 00:00:05 |

-

Note

PLAN_TABLE_OUTPUT

-

-

-SQL profile "coe_12c0v4my7dvr3_3724264953" used for this statement

13 rows selected.

SQL > select name,category,status,sql_text from dba_sql_profiles

NAME CATEGORY STATUS SQL_TEXT

-

Coe_12c0v4my7dvr3_3724264953 DEFAULT ENABLED select count (*) from T1

You can see that the script coe_load_sql_profile_v2.sql fixes the execution plan.

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.