SQL> @xi 4x2qck7u5m4mk %
eXplain the execution plan for sqlid 4x2qck7u5m4mk child %...
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 4x2qck7u5m4mk, child number 4
-------------------------------------
SELECT ATTRID, ATTRVALUE FROM CS_REC_ATTR
WHERE RECOID = :RECOID AND REGION = 315
Plan hash value: 3978804789
---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | Pstart| Pstop | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | | | |
|* 1 | VIEW | index$_join$_001 | 1387K| | | | | |
|* 2 | HASH JOIN | | | | | 1000K| 1000K| 927K (0)|
| 3 | PARTITION RANGE ITERATOR| | 1387K| 65 | 74 | | | |
|* 4 | INDEX RANGE SCAN | IDX_CS_REC_ATTR | 1387K| 65 | 74 | | | |
| 5 | PARTITION RANGE ITERATOR| | 1387K| 65 | 74 | | | |
|* 6 | INDEX FAST FULL SCAN | IDX_REC_ATTRID_VALUE | 1387K| 65 | 74 | | | |
---------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RECOID"=TO_NUMBER(:RECOID))
2 - access(ROWID=ROWID)
4 - access("RECOID"=TO_NUMBER(:RECOID))
6 - filter("REGION"=315)
Note
-----
- dynamic sampling used for this statement (level=2)
SQL> @ind tbcs.CS_REC_ATTR
Display indexes where table or index name matches %tbcs.CS_REC_ATTR%...
TABLE_OWNER TABLE_NAME INDEX_NAME POS# COLUMN_NAME DSC
-------------------- ------------------------------ ------------------------------ ---- ------------------------------ ----
TBCS CS_REC_ATTR IDX_CS_REC_ATTR 1 RECOID
IDX_REC_ATTRID_VALUE 1 ATTRID
2 ATTRVALUE
3 REGION
Implementation of the above plan is wrong, more efficient execution plan below, if the table is too late to collect statistical information, how to use sql profile fixed execution plan？
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 491038779
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 28 | 22 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE ITERATOR | | 1 | 28 | 22 (0)| 00:00:01 | 65 | 74 |
|* 2 | TABLE ACCESS BY LOCAL INDEX ROWID| CS_REC_ATTR | 1 | 28 | 22 (0)| 00:00:01 | 65 | 74 |
|* 3 | INDEX RANGE SCAN | IDX_CS_REC_ATTR | 1 | | 21 (0)| 00:00:01 | 65 | 74 |
----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("REGION"=315)
3 - access("RECOID"=1111)
DECLARE
SQL_FTEXT CLOB;
BEGIN
SELECT SQL_FULLTEXT INTO SQL_FTEXT FROM V$SQLAREA WHERE SQL_ID = '4x2qck7u5m4mk';
DBMS_SQLTUNE.IMPORT_SQL_PROFILE(
SQL_TEXT => SQL_FTEXT,
PROFILE => SQLPROF_ATTR('NO_INDEX(CS_REC_ATTR IDX_REC_ATTRID_VALUE)'),
NAME => 'PROFILE_4x2qck7u5m4mk',
REPLACE => TRUE,
FORCE_MATCH => TRUE
);
END;
I used no_index hint ,but not work for me, Where is incorrect？ please correct me， thanks. BTW, I want to use opt_param('_index_join_enabled','false') ,is ok?