| Exadata smart scan for an index range scan operation [message #524407] |
Fri, 23 September 2011 09:13  |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
There is a parameter _cell_range_scan_enabled, which sounds as though a range scan should be offloadable. It defaults to TRUE. I've tried guessing other values (ALWAYS, FORCE, ONLY, etc) but it seems that TRUE and FALSE are the only options. This parameter is mentioned by Kerry Osborne in "Expert Oracle Exadata": he describes it on page 63 as "enable CELL processing of index range scans" but I don't think it gets mentioned again.
Osborne says previously (page 45) that the smart scan code is implemented by the kcfis_read function, which is called by the direct path read function kcbldrget, which is called by "one of the full scan functions"; so the only way to get a smart scan is through a full scan with direct reads. That corresponds to the way I see it: smart scan is possible only for mutli-block reads, ie, table full scan and index fast full scan.
Does anyone know if it is possible to get a smart scan for anything other than TABLE FULL SCAN and INDEX FAST FULL SCAN ? Any comments on this underscore parameter?
|
|
|
|
|
|
| Re: Exadata smart scan for an index range scan operation [message #524585 is a reply to message #524437] |
Sun, 25 September 2011 03:43  |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
THanks for replying. The only other suggestion I have had is that it may be to do with features that are promised but not yet implemented, such as operations on IOTs. For completeness, note that at present smart scan doesn't work on a FFS of an IOT, even though it does work on a FFS of an index:
orcl> create table jiot(c1 number, constraint jpk primary key(c1)) organization index;
Table created.
orcl> set autotrace traceonly exp
orcl> select * from jiot;
Execution Plan
----------------------------------------------------------
Plan hash value: 166792654
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 2 (0)| 00:00:01 |
| 1 | INDEX FAST FULL SCAN| JPK | 1 | 13 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
orcl> select deptno from scott.dept;
Execution Plan
----------------------------------------------------------
Plan hash value: 2578398298
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 52 | 2 (0)| 00:00:01 |
| 1 | INDEX STORAGE FAST FULL SCAN| PK_DEPT | 4 | 52 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
orcl>
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
