Oracle sqlprofile fixed execution plan and migrates the execution plan 02/25 Update SLTechnology News&Howtos

Oracle sqlprofile fixed execution plan and migrates the execution plan

2025-02-25 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

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

Sqlprofile fixed execution plan

Simulate the migration of 10g execution plan to 11g oracle database, and the 11g library uses 10g execution plan. Here is the migration of the execution plan for a full scan of hint.

-1. Preparation stage

Sqlplus / as sysdba

SQL > select * from v$version

BANNER

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Prod

PL/SQL Release 10.2.0.1.0-Production

CORE 10.2.0.1.0 Production

TNS for 32-bit Windows: Version 10.2.0.1.0-Production

NLSRTL Version 10.2.0.1.0-Production

Create user mouse identified by oracle

Grant create session,resource to mouse

Grant dba to mouse

Conn mouse/oracle

SQL > create table test_raugher as select * from dba_objects

The table has been created.

SQL > create index ind_objectid on test_raugher (object_id)

The index has been created.

SQL > select object_id from test_raugher where rownum exec dbms_stats.gather_table_stats (user,'TEST_RAUGHER',cascade= > true)

The PL/SQL process completed successfully.

-- the original sql implementation plan

SQL > set autot trace explain

SQL > select * from test_raugher where object_id=20

Carry out the plan

Plan hash value: 800879874

| | 0 | SELECT STATEMENT | | 1 | 95 | 2 (0) | 00:00:01 |

| | 1 | TABLE ACCESS BY INDEX ROWID | TEST_RAUGHER | 1 | 95 | 2 (0) | 00:00:01 |

| | * 2 | INDEX RANGE SCAN | IND_OBJECTID | 1 | | 1 (0) | 00:00:01 |

Predicate Information (identified by operation id):

2-access ("OBJECT_ID" = 20)

SQL >

-- New sql implementation Plan

SQL > select / * + full (test_raugher) * / * from test_raugher where object_id=20

Carry out the plan

Plan hash value: 3725671026

| | 0 | SELECT STATEMENT | | 1 | 95 | 16616 (2) | 00:00:02 |

| | * 1 | TABLE ACCESS FULL | TEST_RAUGHER | 1 | 95 | 166C (2) | 00:00:02 |

Predicate Information (identified by operation id):

1-filter ("OBJECT_ID" = 20)

-- 2. Get the sql_id of the new sql

SQL > col sql_id for A20

SQL > col sql_text for A100

SQL > select sql_id,sql_text from v$sql where sql_text like'% full (test_raugher)%'

SQL_ID SQL_TEXT

5nkhk378705z3 select sql_id,sql_text from v$sql where sql_text like'% full (test_raugher)%'

G23hbdmcsdahc select / * + full (test_raugher) * / * from test_raugher where object_id=20

Dqp79vx5pmw0k EXPLAIN PLAN SET STATEMENT_ID='PLUS4294967295' FOR select / * + full (test_raugher) * / * from test_raug

Her where object_id=20

-3. Get the outline of the new sql

SQL > set pagesize 1000

SQL > select * from table (dbms_xplan.display_cursor ('g23hbdmcsdahc')

PLAN_TABLE_OUTPUT

SQL_ID g23hbdmcsdahc, child number 0

Select / * + full (test_raugher) * / * from test_raugher where object_id=20

Plan hash value: 3725671026

| | 0 | SELECT STATEMENT | 166100 | | |

| | * 1 | TABLE ACCESS FULL | TEST_RAUGHER | 1 | 95 | 166C (2) | 00:00:02 |

Outline Data

/ * +

BEGIN_OUTLINE_DATA

IGNORE_OPTIM_EMBEDDED_HINTS

OPTIMIZER_FEATURES_ENABLE ('10.2.0.1')

ALL_ROWS

OUTLINE_LEAF (@ "SEL$1")

FULL (@ "SEL$1"TEST_RAUGHER" @ "SEL$1")

END_OUTLINE_DATA

, /

Predicate Information (identified by operation id):

1-filter ("OBJECT_ID" = 20)

31 lines have been selected.

-- 4. Create sql profile (SQLPROFILE_001)

SQL > declare

2 v_hints sys.sqlprof_attr

3 begin

4 v_hints:=sys.sqlprof_attr (

5 'BEGIN_OUTLINE_DATA'

6 'IGNORE_OPTIM_EMBEDDED_HINTS'

7 'OPTIMIZER_FEATURES_ENABLE (' 10.2.0.1')'

8 'ALL_ROWS'

9 'OUTLINE_LEAF (@ "SEL$1")'

10 'FULL (@ "SEL$1"TEST_RAUGHER" @ "SEL$1")'

11 'END_OUTLINE_DATA')

12 dbms_sqltune.import_sql_profile (

13 'select * from test_raugher where object_id=20'

14 vogue hintsrec SQLPROFILEX 001'

15 force_match= > true,replace= > false)

16 end

17 /

The PL/SQL process completed successfully.

-5. Check to see if sql profile is used

SQL > set autot trace explain

SQL > select * from test_raugher where object_id=20

Carry out the plan

Plan hash value: 3725671026

| | 0 | SELECT STATEMENT | | 1 | 95 | 16616 (2) | 00:00:02 |

| | * 1 | TABLE ACCESS FULL | TEST_RAUGHER | 1 | 95 | 166C (2) | 00:00:02 |

Predicate Information (identified by operation id):

1-filter ("OBJECT_ID" = 20)

Note

-SQL profile "SQLPROFILE_001" used for this statement

SQL > select * from test_raugher where object_id=200

Carry out the plan

Plan hash value: 3725671026

| | 0 | SELECT STATEMENT | | 1 | 95 | 16616 (2) | 00:00:02 |

| | * 1 | TABLE ACCESS FULL | TEST_RAUGHER | 1 | 95 | 166C (2) | 00:00:02 |

Predicate Information (identified by operation id):

1-filter ("OBJECT_ID" = 200)

Note

-SQL profile "SQLPROFILE_001" used for this statement

Package the profile in preparation for migration.

Generate a TEST_SQLPRO1 table

Exec DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF (table_name= > 'TEST_SQLPRO1',schema_name= >' MOUSE')

SQL > select * from tab

T1 TABLE

TEST_SQLPRO1 TABLE

Insert the execution plan into the TEST_SQLPRO1 table

Exec DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name = > 'TEST_SQLPRO1',profile_name= >' SQLPROFILE_001')

Export table and package execution plan

Export all the objects below the user or only export the table that releases the execution plan

Expdp system/oracle dumpfile=mouse.dmp directory=expdir schemas=mouse

Expdp system/oracle dumpfile=mouse.dmp directory=expdir tables=mouse.TEST_SQLPRO1

[oracle@nod ~] $expdp system/123456 dumpfile=mouse.dmp directory=expdp tables=mouse.TEST_SQLPRO1,mouse.TEST_RAUGHER

Export: Release 10.2.0.1.0-64bit Production on Saturday, 17 June, 2017 23:58:38

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-64bit Production

With the Partitioning, OLAP and Data Mining options

Starting "SYSTEM". "SYS_EXPORT_TABLE_01": system/* dumpfile=mouse.dmp directory=expdp tables=mouse.TEST_SQLPRO1,mouse.TEST_RAUGHER

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 320 KB

Processing object type TABLE_EXPORT/TABLE/TABLE

. . Exported "MOUSE". "TEST_SQLPRO1" 8.937 KB 1 rows

Exported mouse.TEST_RAUGHER 2.2MB 4000rows

Master table "SYSTEM". "SYS_EXPORT_TABLE_01" successfully loaded/unloaded

Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:

/ home/oracle/expdp/mouse.dmp

Job "SYSTEM". "SYS_EXPORT_TABLE_01" successfully completed at 23:58:45

Scp mouse.dmp 192.168.0.13:/home/oracle/expdp/

11g database

[oracle@oracle expdp] $sqlplus-v

SQL*Plus: Release 11.2.0.4.0 Production

Sqlplus / as sysdba

Create user mouse identified by oracle

Grant create session,resource to mouse

Grant dba to mouse

Conn mouse/oracle

Import tables and packaged execution plans in 11G environment

Impdp system/oracle dumpfile=mouse.dmp directory=expdp

[oracle@oracle expdp] $impdp system/oracle dumpfile=mouse.dmp directory=expdp

Import: Release 11.2.0.4.0-Production on Fri May 5 15:38:27 2017

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Master table "SYSTEM". "SYS_IMPORT_FULL_01" successfully loaded/unloaded

Starting "SYSTEM". "SYS_IMPORT_FULL_01": system/* dumpfile=mouse.dmp directory=expdp

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

. . Imported "MOUSE". "TEST_SQLPRO1" 8.937 KB 1 rows

Imported mouse.TEST_RAUGHER 2.2MB 4000rows

Job "SYSTEM". "SYS_IMPORT_FULL_01" successfully completed at Fri May 5 15:38:51 2017 elapsed 0 00:00:20

Unpack the sqlprofile and change the execution plan to the same execution plan as the 10G library.

EXEC DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF (replace = > TRUE,staging_table_name = > 'TEST_SQLPRO1')

SQL > conn mouse/oracle

Connected.

SQL > EXEC DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF (replace = > TRUE,staging_table_name = > 'TEST_SQLPRO1')

PL/SQL procedure successfully completed.

SQL > set autot trace explain

SQL > set lines 200

SQL > select * from test_raugher where object_id=20

Execution Plan

Plan hash value: 3725671026

| | 0 | SELECT STATEMENT | | 1 | 93 | 192 (0) | 00:00:03 |

| | * 1 | TABLE ACCESS FULL | TEST_RAUGHER | 1 | 93 | 192 (0) | 00:00:03 |

Predicate Information (identified by operation id):

1-filter ("OBJECT_ID" = 20)

Note

-SQL profile "SQLPROFILE_001" used for this statement

Col CREATED for a10

Col SQL_TEXT for a30

SELECT name, created,category,sql_Text from dba_sql_profiles ORDER BY created DESC

NAME CREATED CATEGORY SQL_TEXT

SQLPROFILE_00105-MAY-17 DEFAULT select * from test_raugher where object_id=20

Here we see that this statement uses SQLPROFILE_001 sql profile

In many cases, when we collect statistics in the new library or using HINT does not meet our purpose, sqlprofile can be used as the preferred solution

Exec DBMS_SQLTUNE.DROP_SQL_PROFILE (name = > 'SQLPROFILE_001')

SQL > set autot trace explain

SQL > set lines 200

SQL > select * from test_raugher where object_id=20

Execution Plan

Plan hash value: 800879874

| | 0 | SELECT STATEMENT | | 1 | 93 | 2 (0) | 00:00:01 |

| | 1 | TABLE ACCESS BY INDEX ROWID | TEST_RAUGHER | 1 | 93 | 2 (0) | 00:00:01 |

| | * 2 | INDEX RANGE SCAN | IND_OBJECTID | 1 | | 1 (0) | 00:00:01 |

Predicate Information (identified by operation id):

2-access ("OBJECT_ID" = 20)

SELECT name, created,category,sql_Text from dba_sql_profiles ORDER BY created DESC

No rows selected

If you delete sqlprofile and want to use sqlprofile, execute EXEC DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF directly (replace = > TRUE,staging_table_name = > 'TEST_SQLPRO1')

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.

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.