Oracle数据库数据恢复、性能优化»论坛 › Oracle › Oracle数据库管理 › 为什么hint无效

351 积分	0 好友	8 主题

发消息

为什么hint无效

1^#

发表于 2012-5-8 16:40:19 | 查看: 6776| 回复: 13

版本是11.2.0.3

create index IX_YW_HS_PAY on company.YW_HS_PAY(C_FLOW_INID);

alter session set events '10046 trace name context forever,level 12';

select /*+index_ffs(t IX_YW_HS_PAY)*/C_FLOW_INID  from company.YW_HS_PAY t;

alter session set events '10046 trace name context off';

select /*+index_ffs(t ix_yw_hs_pay)*/C_FLOW_INID
from
company.YW_HS_PAY t

call    count    cpu elapsed    disk    query current       rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       1    0.00    0.00       0       0       0          0
Execute    1    0.00    0.00       0       0       0          0
Fetch    3599    0.20    0.18       0    9460       1    53965
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    3601    0.20    0.18       0    9460       1    53965

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
   53965    53965    53965  TABLE ACCESS FULL YW_HS_PAY (cr=9460 pr=0 pw=0 time=104067 us cost=1057 size=431608 card=53951)

Elapsed times include waiting on following events:

  Event waited on                            Times Max. Wait  Total Waited
  ---------------------------------------- Waited  ----------  ------------
  SQL*Net message to client                   3599       0.00       0.00
  Disk file operations I/O                      1       0.00       0.00
  gc cr block 2-way                            6       0.00       0.00
  SQL*Net message from client                3599    77.15       98.04
  gc current block 2-way                         2       0.00       0.00

可以看出index_ffs hint没有生效，为什么呢？

分享0

收藏0 回复只看该作者道具举报

alicejapan

2^#

发表于 2012-5-8 17:24:08

你尝试加上where条件看看，不行的话再分析下表看看

回复只看该作者道具举报

不了峰

3^#

发表于 2012-5-8 17:27:23

是不是 /*+index_ffs(t IX_YW_HS_PAY)*/

/*+ 后面要加个空格……

表有多大

回复只看该作者道具举报

Maclean Liu(刘相兵

4^#

发表于 2012-5-8 21:44:56

永远不要忽略约束constraints 对optimizer的影响！！

SQL> create table test as select * from dba_objects;

Table created.

SQL> desc test;
Name                                     Null? Type
----------------------------------------- -------- ----------------------------
OWNER                                           VARCHAR2(30)
OBJECT_NAME                                     VARCHAR2(128)
SUBOBJECT_NAME                                  VARCHAR2(30)
OBJECT_ID                                        NUMBER
DATA_OBJECT_ID                                  NUMBER
OBJECT_TYPE                                     VARCHAR2(19)
CREATED                                           DATE
LAST_DDL_TIME                                     DATE
TIMESTAMP                                        VARCHAR2(19)
STATUS                                           VARCHAR2(7)
TEMPORARY                                        VARCHAR2(1)
GENERATED                                        VARCHAR2(1)
SECONDARY                                        VARCHAR2(1)
NAMESPACE                                        NUMBER
EDITION_NAME                                     VARCHAR2(30)

SQL> create  index ind_object_id on test(object_id);

Index created.

SQL> exec dbms_stats.gather_table_stats('','TEST',cascade=>TRUE);

PL/SQL procedure successfully completed.

SQL> explain plan for select OBJECT_ID from test;

Explained.

SQL> set linesize 200 pagesize 1400
SQL> @?/rdbms/admin/utlxplp

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1357081020

--------------------------------------------------------------------------
| Id  | Operation       | Name | Rows  | Bytes | Cost (%CPU)| Time    |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT  |    | 75434 | 368K| 294 (1)| 00:00:04 |
| 1 |  TABLE ACCESS FULL| TEST | 75434 | 368K| 294 (1)| 00:00:04 |
--------------------------------------------------------------------------

8 rows selected.

SQL> explain plan for select /*+ index_ffs(test ind_object_id) */  OBJECT_ID from test;

Explained.

SQL> @?/rdbms/admin/utlxplp

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1357081020

--------------------------------------------------------------------------
| Id  | Operation       | Name | Rows  | Bytes | Cost (%CPU)| Time    |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT  |    | 75434 | 368K| 294 (1)| 00:00:04 |
| 1 |  TABLE ACCESS FULL| TEST | 75434 | 368K| 294 (1)| 00:00:04 |
--------------------------------------------------------------------------

8 rows selected.

SQL> explain plan for select /*+ index_ffs(test ind_object_id) */  OBJECT_ID from test where object_id is not null;

Explained.

SQL> @?/rdbms/admin/utlxplp

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2799590446

--------------------------------------------------------------------------------------
| Id  | Operation          | Name       | Rows  | Bytes | Cost (%CPU)| Time    |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT    |             | 75434 | 368K| 48 (3)| 00:00:01 |
|*  1 |  INDEX FAST FULL SCAN| IND_OBJECT_ID | 75434 | 368K| 48 (3)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("OBJECT_ID" IS NOT NULL)

13 rows selected.

SQL> explain plan for select OBJECT_ID from test where object_id is not null;

Explained.

SQL> @?/rdbms/admin/utlxplp

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2799590446

--------------------------------------------------------------------------------------
| Id  | Operation          | Name       | Rows  | Bytes | Cost (%CPU)| Time    |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT    |             | 75434 | 368K| 48 (3)| 00:00:01 |
|*  1 |  INDEX FAST FULL SCAN| IND_OBJECT_ID | 75434 | 368K| 48 (3)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("OBJECT_ID" IS NOT NULL)

13 rows selected.

回复只看该作者道具举报

liuxbgiant

5^#

发表于 2012-5-8 21:48:50

针对楼一，为什么hint无效？

The INDEX_FFS hint instructs the optimizer to perform a fast full index scan rather than a full table scan.
IF the number of table is big enough,the optimizer will perform a fast full index scan, or , it is a full table scan .
SELECT count(*) FROM big_table;
COUNT(*)
--------
  300000

SQL ID: dwr5hhd5rcgm8
Plan Hash: 1494883413
SELECT /*+ INDEX_FFS(BIG_TABLE IND_BIG_NAME) */ OBJECT_NAME
FROM
DNA.BIG_TABLE

call    count    cpu elapsed    disk    query current       rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       1    0.00    0.00       0       2       0          0
Execute    1    0.00    0.00       0       0       0          0
Fetch 12360    0.39    0.70       999    13295       0    185386
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total 12362    0.39    0.70       999    13297       0    185386

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 85

Rows    Row Source Operation
-------  ---------------------------------------------------
185386  INDEX FAST FULL SCAN IND_BIG_NAME (cr=13295 pr=999 pw=0 time=168101 us cost=420 size=7800000 card=300000)(object id 92041)

回复只看该作者道具举报

am196

6^#

发表于 2012-5-8 22:04:07

提示在索引列上的条件才有效吗？

回复只看该作者道具举报

Maclean Liu(刘相兵

7^#

发表于 2012-5-8 23:27:04

“提示在索引列上的条件才有效吗？”

不是的 select col_name from tab 是对于 col_name is NULL的情况也是要返回空结果的

SQL> create table tv(t1 int);

Table created.

SQL> insert into tv values (NULL);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from tv;

      T1
----------

SQL> insert into tv values(1);

1 row created.

SQL> commit;

Commit complete.

SQL>  select * from tv;

      T1
----------

      1

因为单纯的 single column index 并不存放NULL值，所以对于NULLABLE的column 是不能使用INDEX FFS 来获取其全部记录的。

SQL> create index pk_tv on tv(t1);

Index created.

SQL> explain plan for  select /*+index_ffs(tv pk_tv)*/ t1 from tv;

Explained.

SQL> set linesize 200 pagesize 1400
SQL> @?/rdbms/admin/utlxplp

PLAN_TABLE_OUTPUT
----------------------------------------------
Plan hash value: 1723968289

--------------------------------------------------------------------------
| Id  | Operation       | Name | Rows  | Bytes | Cost (%CPU)| Time    |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT  |    |    2 | 26 |    2 (0)| 00:00:01 |
| 1 |  TABLE ACCESS FULL| TV |    2 | 26 |    2 (0)| 00:00:01 |
--------------------------------------------------------------------------

SQL> explain plan for  select t1 from tv where t1 is not null;

Explained.

SQL> @?/rdbms/admin/utlxplp

PLAN_TABLE_OUTPUT
-------------------------------------------

--------------------------------------------------------------------------
| Id  | Operation       | Name  | Rows  | Bytes | Cost (%CPU)| Time    |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT |    |    1 | 13 |    1 (0)| 00:00:01 |
|*  1 | INDEX FULL SCAN | PK_TV |    1 | 13 |    1 (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("T1" IS NOT NULL)

这个演示也可以用来说明  在 HINT适用的场景中，HINT 是无法被忽略ignore的，除非你使用了其他更强力的hint  或者隐藏参数

SQL> explain plan for  select /*+index_ffs(tv pk_tv)*/ t1 from tv where t1 is not null;

Explained.

SQL>  @?/rdbms/admin/utlxplp

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------

------------------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time    |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT    |    |    1 | 13 |    2 (0)| 00:00:01 |
|*  1 | INDEX FAST FULL SCAN| PK_TV |    1 | 13 |    2 (0)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("T1" IS NOT NULL)

回复只看该作者道具举报

majuthink

8^#

发表于 2012-5-9 09:17:02

分析得很好,分享了

回复只看该作者道具举报

xteitxu

9^#

发表于 2012-5-9 09:58:45

好贴，学习。支持

回复只看该作者道具举报

orafans

10^#

发表于 2012-5-9 21:06:21

21:00:59 SQL> SELECT /*+ INDEX_FFS(TEST IDX_OWNER) */ OBJECT_TYPE FROM TEST WHERE OBJECT_TYPE IS NOT NULL;

Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020

--------------------------------------------------------------------------
| Id  | Operation       | Name | Rows  | Bytes | Cost (%CPU)| Time    |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT  |    | 202K|  1775K| 616 (2)| 00:00:08 |
|*  1 |  TABLE ACCESS FULL| TEST | 202K|  1775K| 616 (2)| 00:00:08 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("OBJECT_TYPE" IS NOT NULL)
这个是怎么回事呢？OBJECT_TYPE本身没有not null约束
21:02:44 SQL> c/INDEX_FFS/INDEX
  1* SELECT /*+ INDEX(TEST IDX_OWNER) */ OBJECT_TYPE FROM TEST WHERE OBJECT_TYPE IS NOT NULL
21:03:01 SQL> /

Execution Plan
----------------------------------------------------------
Plan hash value: 3577366606

-----------------------------------------------------------------------------------------
| Id  | Operation                | Name    | Rows  | Bytes | Cost (%CPU)| Time    |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT          |          | 202K|  1775K|  5820 (1)| 00:01:10 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| TEST    | 202K|  1775K|  5820 (1)| 00:01:10 |
| 2 | INDEX FULL SCAN          | IDX_OWNER | 202K|    | 474 (2)| 00:00:06 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
  1 - filter("OBJECT_TYPE" IS NOT NULL)
---------------------------------------------------
这个表有40W行记录。index_ffs貌似不起作用。
21:03:45 SQL>  SET AUTOT ON EXP
21:04:08 SQL> SELECT COUNT(*) FROM TEST;
  COUNT(*)
----------
404088
Execution Plan
----------------------------------------------------------
Plan hash value: 1232025837
----------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Cost (%CPU)| Time    |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT    |          |    1 | 102 (3)| 00:00:02 |
| 1 |  SORT AGGREGATE    |          |    1 |          |       |
| 2 | INDEX FAST FULL SCAN| IDX_OBJ_ID | 202K| 102 (3)| 00:00:02 |
----------------------------------------------------------------------------

回复只看该作者道具举报

Maclean Liu(刘相兵

11^#

发表于 2012-5-9 21:13:48

upload the DDL definition of IDX_OWNER , this index's name sounds like it only index the OWNER column but not the object_type , why you expect the FFS of an owner associated index to return FULL result of OBJECT_TYPE?

回复只看该作者道具举报

orafans

12^#

发表于 2012-5-9 22:02:39

1* SELECT /*+ INDEX(TEST IDX_OWNER) */ OBJECT_TYPE FROM TEST WHERE OBJECT_TYPE IS NOT NULL
21:03:01 SQL> /

Execution Plan
----------------------------------------------------------
Plan hash value: 3577366606

-----------------------------------------------------------------------------------------
| Id  | Operation                | Name    | Rows  | Bytes | Cost (%CPU)| Time    |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT          |          | 202K|  1775K|  5820 (1)| 00:01:10 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| TEST    | 202K|  1775K|  5820 (1)| 00:01:10 |
| 2 | INDEX FULL SCAN          | IDX_OWNER | 202K|    | 474 (2)| 00:00:06 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
  1 - filter("OBJECT_TYPE" IS NOT NULL)

well,Y does this hint could generate INDEX FULL SCAN?I just query with a "not null " constraint,So,can i just put it this way that if there's neither "NOT NULL" constriant on that column  nor an index on that column,then,the index_ffs will not function. But with the "NOT NULL" constraint and a hint  "index",it then will execute as the hint indicates,a index full scan?just as following:
21:59:01 SQL>  select /*+ index_ffs(test idx_obj_id) */ object_type from test where object_type is not null;
Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id  | Operation       | Name | Rows  | Bytes | Cost (%CPU)| Time    |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT  |    | 202K|  1775K| 616 (2)| 00:00:08 |
|*  1 |  TABLE ACCESS FULL| TEST | 202K|  1775K| 616 (2)| 00:00:08 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_TYPE" IS NOT NULL)
21:59:12 SQL>  select /*+ index(test idx_obj_id) */ object_type from test where object_type is not null;
Execution Plan
----------------------------------------------------------
Plan hash value: 143832116
------------------------------------------------------------------------------------------
| Id  | Operation                | Name    | Rows  | Bytes | Cost (%CPU)| Time    |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT          |          | 202K|  1775K| 202K  (1)| 00:40:33 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| TEST    | 202K|  1775K| 202K  (1)| 00:40:33 |
| 2 | INDEX FULL SCAN          | IDX_OBJ_ID | 202K|    | 451 (2)| 00:00:06 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_TYPE" IS NOT NULL)

[ 本帖最后由 orafans 于 2012-5-9 22:03 编辑 ]

回复只看该作者道具举报

Maclean Liu(刘相兵

13^#

发表于 2012-5-9 22:09:40

Let us know how you make the below index

IDX_OWNER idx_obj_id , upload the ddl statement !

回复只看该作者道具举报

orafans

14^#

发表于 2012-5-9 22:21:28

As following:
CREATE INDEX IDX_OBJ_ID ON TEST(OBJECT_ID);
CREATE INDEX IDX_OWNER ON TEST(OWNER);
ALTER TABLE TEST MODIFY(OBJECT_ID NOT NULL);
ALTER TABLE TEST MODIFY(OWNER NOT NULL);
sql> select INDEX_NAME, INDEX_TYPE, TABLE_OWNER, TABLE_NAME from user_indexes where table_name='TEST';

INDEX_NAME INDEX_TYPE                TABLE_OWNER                   TABLE_NAME
---------- --------------------------- ------------------------------ ------------------------------
IDX_OBJ_ID NORMAL                   SYS                         TEST
IDX_OWNER  NORMAL                   SYS                         TEST

回复只看该作者道具举报

返回列表

		自动登录	找回密码
密码			注册