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

First, set up a test table.

Create table a nologging as select * from all_objects

2. Preparatory work

Find sql_Id='aq03p7muwgvq5'.

Select * from V$sql where sql_text like'% from a where object_id=3%'

Find the outline of the full table:

Method one: dba_hist_sql_plan/v$sql_plan is fine.

Select extractvalue (value (d), / hint') as outline_hintsfromxmltable ('/ * / outline_data/hint'passing (selectxmltype (other_xml) as xmlvalfromdba_hist_sql_planwheresql_id ='& sql_id'and plan_hash_value=&plan_hash_valueand other_xml is not null)) d

Method 2:

Select * from dbms_xplan.display_awr ('aq03p7muwgvq5',0,'outline'); / * + BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE (' 11.2.0.4') DB_VERSION ('11.2.0.4') ALL_ROWS OUTLINE_LEAF (@ "SEL$1") FULL (@ "SEL$1"A" @ "SEL$1") END_OUTLINE_DATA*/declare v_hints sys.sqlprof_attr; v_sqltext clob Begin select sql_fulltext into v_sqltext from v$sql where sql_id='aq03p7muwgvq5' and rownumtrue,replace= > true); end

Build an index

Create index I_ind_object_id_com on a (object_id,object_name) nologging

Check the execution plan and do not go through the index:

Execution Plan---Plan hash value: 2248738933-- -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |-- | 0 | SELECT STATEMENT | | 1 | 98 | 177 (1) | 00:00:01 | | * 1 | TABLE ACCESS FULL | A | 1 | 98 | 177 (1) | 00:00:01 |- -Predicate Information (identified by operation id):-1-filter ("OBJECT_ID" = 3) Note--SQL profile "sql_full" used for this statementStatistics- -7 recursive calls 0 db block gets 1254 consistent gets 1246 physical reads 0 redo size 1606 bytes sent via SQL*Net to client 519 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 Sorts (memory) 0 sorts (disk) 1 rows processed

Delete profile

Begin dbms_sqltune.drop_sql_profile ('sql_full'); end

Execute sql again to find the outline that is indexed

Select * from dbms_xplan.display_awr ('aq03p7muwgvq5',1,'outline') / * + BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE ('11.2.0.4') DB_VERSION ('11.2.0.4') ALL_ROWS OUTLINE_LEAF (@ "SEL$1") INDEX_RS_ASC (@ "SEL$1"A" @ "SEL$1" ("A". "OBJECT_ID"A". "OBJECT_NAME") END _ OUTLINE_DATA * / declare v_hints sys.sqlprof_attr V_sqltext clob;begin select sql_fulltext into v_sqltext from v$sql where sql_id='aq03p7muwgvq5' and rownumtrue,replace= > true); end

Build a better index

Create index I_ind_object_id on a (object_Id) nologging

Looking at the execution plan again, there is no better index.

Execution Plan---Plan hash value: 3075844428-- | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |-- -| 0 | SELECT STATEMENT | | 1 | 98 | 3 (0) | 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID | A | | 1 | 98 | 3 (0) | 00:00:01 | | * 2 | INDEX RANGE SCAN | I_IND_OBJECT_ID_COM | 1 | | 2 (0) | 00:00:01 |-- | -Predicate Information (identified by operation id):-2-access ("OBJECT_ID" = 3) Note--SQL profile "sql_comp_ind" used for this statement Statistics 7 recursive Calls 0 db block gets 10 consistent gets 1 physical reads 0 redo size 1609 bytes sent via SQL*Net to client 519 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed

Of course, you can also use coe_xfr_sql_profile.sql or create_sql_profile.sql in SQLT to generate sql_profile

A bit of nonsense here is to use a complete outline, write it in and do not report errors, but the execution plan does not follow the content agreed in the sql_profile

Declare v_hints sys.sqlprof_attr; v_sqltext clob;begin select sql_fulltext into v_sqltext from v$sql where sql_id='aq03p7muwgvq5' and rownum

In this paragraph,

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.