Oracle数据库数据恢复、性能优化

找回密码
注册
搜索
热搜: 活动 交友 discuz
Oracle数据库数据恢复、性能优化»论坛 › Oracle › Oracle数据库管理 › 为什么hint无效
发新帖
gdpr-dba

351

积分

0

好友

8

主题

为什么hint无效

1#
发表于 2012-5-8 16:40:19 | 查看: 6695| 回复: 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没有生效，为什么呢？
2#
发表于 2012-5-8 17:24:08
你尝试加上where条件看看，不行的话再分析下表看看

回复 只看该作者 道具 举报

3#
发表于 2012-5-8 17:27:23
是不是 /*+index_ffs(t IX_YW_HS_PAY)*/

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

表有多大

回复 只看该作者 道具 举报

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.

回复 只看该作者 道具 举报

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)

回复 只看该作者 道具 举报

6#
发表于 2012-5-8 22:04:07
提示在索引列上的条件才有效吗？

回复 只看该作者 道具 举报

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)

回复 只看该作者 道具 举报

8#
发表于 2012-5-9 09:17:02
分析得很好,分享了

回复 只看该作者 道具 举报

9#
发表于 2012-5-9 09:58:45
好贴，学习。支持

回复 只看该作者 道具 举报

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 |
----------------------------------------------------------------------------

回复 只看该作者 道具 举报

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?

回复 只看该作者 道具 举报

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 编辑 ]

回复 只看该作者 道具 举报

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 !

回复 只看该作者 道具 举报

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

回复 只看该作者 道具 举报

返回列表
高级模式
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 | 注册

QQ|手机版|Archiver|Oracle数据库数据恢复、性能优化

GMT+8, 2024-11-15 17:31 , Processed in 0.056155 second(s), 21 queries .

Powered by Discuz! X2.5

© 2001-2012 Comsenz Inc.

回顶部
TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569