Home » Infrastructure » Hardware » Exadata smart scan for an index range scan operation (X2-2 11.2.whatever)
Exadata smart scan for an index range scan operation [message #524407] Fri, 23 September 2011 09:13 Go to next message
John Watson
Messages: 4569
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 #524437 is a reply to message #524407] Fri, 23 September 2011 11:06 Go to previous messageGo to next message
Michel Cadot
Messages: 59147
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Here's what I got:
_cell_index_scan_enabled          enable CELL processing of index FFS
_cell_range_scan_enabled          enable CELL processing of index range scans

Not know more.

Regards
Michel
Re: Exadata smart scan for an index range scan operation [message #524585 is a reply to message #524437] Sun, 25 September 2011 03:43 Go to previous message
John Watson
Messages: 4569
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>

Previous Topic: Oracle to buy SUN Microsystems
Next Topic: Using ZFS volume manager for ASM
Goto Forum:
  


Current Time: Sun Sep 21 11:11:02 CDT 2014

Total time taken to generate the page: 0.05811 seconds