storage index 未起作用

syhnd

SQL> alter session set cell_offload_processing=true;
Session altered.
SQL>
SQL> alter session set "_kcfis_storageidx_disabled"=false;
Session altered.
SQL>
SQL> select name, value
  2    from v$mystat s, v$statname n
  3   where n.statistic# = s.statistic#
  4     and name like '%storage%';
NAME                                                              VALUE
------------------------------------------------------------ ----------
cell physical IO bytes saved by storage index                         0
SQL>
SQL> select count(id) from test.test where id is null;
COUNT(ID)
----------
         0
SQL> select name, value
  2    from v$mystat s, v$statname n
  3   where n.statistic# = s.statistic#
  4     and name like '%storage%';
NAME                                                              VALUE
------------------------------------------------------------ ----------
cell physical IO bytes saved by storage index                         0
SQL>

==========================================================
如下内容是10046：
=====================
PARSING IN CURSOR #140103488132680 len=48 dep=0 uid=84 oct=3 lid=84 tim=1334026232726628 hv=3535056065 ad='7ea4a3c8' sqlid='6vdjhb79b9b61'
select count(id) from test.test where id is null
END OF STMT
PARSE #140103488132680:c=0,e=879,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1950795681,tim=1334026232726626
EXEC #140103488132680:c=0,e=201,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1950795681,tim=1334026232727058
WAIT #140103488132680: nam='SQL*Net message to client' ela= 54 driver id=1650815232 #bytes=1 p3=0 obj#=75587 tim=1334026232728027
WAIT #140103488132680: nam='cell smart table scan' ela= 2173 cellhash#=1490775311 p2=0 p3=0 obj#=75587 tim=1334026232741951
WAIT #140103488132680: nam='cell smart table scan' ela= 56349 cellhash#=1490775311 p2=0 p3=0 obj#=75587 tim=1334026232804523
WAIT #140103488132680: nam='cell smart table scan' ela= 215501 cellhash#=1490775311 p2=0 p3=0 obj#=75587 tim=1334026233021253
WAIT #140103488132680: nam='cell smart table scan' ela= 8437 cellhash#=1490775311 p2=0 p3=0 obj#=75587 tim=1334026233030208
WAIT #140103488132680: nam='cell smart table scan' ela= 8212 cellhash#=1490775311 p2=0 p3=0 obj#=75587 tim=1334026233038867
*** 2012-04-10 10:50:33.288
WAIT #140103488132680: nam='cell smart table scan' ela= 249131 cellhash#=1490775311 p2=0 p3=0 obj#=75587 tim=1334026233288338
WAIT #140103488132680: nam='cell smart table scan' ela= 99749 cellhash#=1490775311 p2=0 p3=0 obj#=75587 tim=1334026233392375
WAIT #140103488132680: nam='cell smart table scan' ela= 40403 cellhash#=1490775311 p2=0 p3=0 obj#=75587 tim=1334026233433099
WAIT #140103488132680: nam='cell smart table scan' ela= 74107 cellhash#=1490775311 p2=0 p3=0 obj#=75587 tim=1334026233507598
。。。。。。。（略）
==========================================================

我们知道，要使用storage index生效，必须先满足三个条件：
(1).smart scan.
(2).At Least One Predicate.
(3).Simple Comparison Operators.

以上条件都已经满足，怎么storage index还是没起作用？

Maclean Liu(刘相兵

action plan:

imagehistory?
imageinfo              ==> on cell server?
select * from v$version?









==============================================================================================================>
ODM FINDING:

Reduced 'cell physical IO bytes saved by storage index' after storage cell upgrade to versions before 11.2.2.3.0  

Applies to:

Oracle Exadata Storage Server Software - Version: 11.2.1.3.0 to 11.2.2.2.0 - Release: 11.2 to 11.2
Information in this document applies to any platform.
Symptoms

This problem can only be seen on Exadata systems.

There was a performance degradation with database batch job runs after Exadata storage cells were patched from 11.2.1.2.6 to 11.2.2.2.0. Jobs were taking abut 50% longer after the patching.

Reduced number of bytes for statistic 'cell physical IO bytes saved by storage index' was observed after the upgrade (in AWR reports and v$sesstat/v$statname).

Changes

Exadata storage cells patched from version 11.2.1.2.6 to version 11.2.2.2.0.
Cause

Identify typical/problem query

Ideally we wanted to identify the problem query, but that was not easy/obvious. Hence there was a need to identify few typical queries and see if any or all of them were the problem ones.

The knowledge/understanding of the application/batch jobs was very important here.

Collect smart scan and storage index statistics

Once a few typical queries were identified, the following diagnostics were performed - for each query:
1. Smart scan and storage index statistics were collected - before running a typical query

SQL> set long 50000000
SQL> set pagesize 10000
SQL> select name, value
from v$sesstat a, v$statname b
where (a.statistic# = b.statistic#)
  and (a.sid) = userenv('sid')
  and (name in (
          'cell physical IO interconnect bytes returned by smart scan',
          'physical read IO requests',
          'physical read requests optimized',
          'cell physical IO bytes saved by storage index',
          'cell physical IO bytes eligible for predicate offload',
          'cell num smart IO sessions using passthru mode due to cellsrv',
          'cell num smart IO sessions using passthru mode due to user',
          'cell flash cache read hits',
          'db block gets',
          'db block gets from cache',
          'db block gets from cache (fastpath)',
          'db block gets direct')
)
order by name;

The statistics were noted/observed.
2. Autotrace was set to on to check/confirm the smart scan takes place

SQL> set autotrace on

The typical query was run.
It was confirmed that the smart scan was taking place.
3. Autotrace was turned off and the same statistics were collected again

SQL> set autotrace off
SQL> select name, value
from v$sesstat a, v$statname b
where (a.statistic# = b.statistic#)
  and (a.sid) = userenv('sid')
  and (name in (
  'cell physical IO interconnect bytes returned by smart scan',
  'physical read IO requests',
  'physical read requests optimized',
  'cell physical IO bytes saved by storage index',
  'cell physical IO bytes eligible for predicate offload',
  'cell num smart IO sessions using passthru mode due to cellsrv',
  'cell num smart IO sessions using passthru mode due to user',
  'cell flash cache read hits',
  'db block gets',
  'db block gets from cache',
  'db block gets from cache (fastpath)',
  'db block gets direct')
)
order by name;

The statistics were noted/observed.
4. Steps 1-3 were repeated for other typical queries

After running few typical queries, it was evident that queries with equality predicates were not making use of storage indexes. In other words statistic 'cell physical IO bytes saved by storage index' was not increasing for those queries. Queries with other predicates were making use of storage indexes.

As the problem manifested itself as reduced use of storage indexes overall and it was observed that storage indexes were not used for queries with equality predicated, this was enough evidence for the conclusion the problem was due to unpublished bug 9938937. Indeed, the bug was about a regression introduced in cell software versions later than 11.2.1.2.6.
Solution

The fix for unpublished bug 9938937 is included in storage cell software version 11.2.2.3.0, so the solution is to upgrade all storage cells to that or later version.

Oracle Support recommends to run the latest software versions on both compute nodes and storage cells as per Doc ID 888828.1.
References

BUG:12537038 - BATCH JOBS SLOW AFTER CELL UPGRADE FROM 11.2.1.2.6 TO 11.2.2.2.0