1 Reply Latest reply: Oct 25, 2011 6:08 PM by Rich Headrick-Oracle RSS

    Exadata smart scan AWR

    User13512691-Oracle
      Hi,
      We have 2 node RAC running on X2-2 Full Rack system.

      I wanted to know- how can we confirm that our database is efficiently using Smart scan or storage index. Could this be checked in AWR report?

      Note - We don't have access to Exadata storage servers since is being managed by other party.
        • 1. Re: Exadata smart scan AWR
          Rich Headrick-Oracle
          Try this:

          VARIABLE sql_id VARCHAR2(32)
          VARIABLE sql_child_number NUMBER
          VARIABLE sql_exec_id NUMBER

          /* Execute your test SQL here */

          -- Past your select statement here

          BEGIN
          SELECT prev_sql_id,
          prev_child_number,
          prev_exec_id
          INTO :sql_id,
          :sql_child_number,
          :sql_exec_id
          FROM v$session
          WHERE sid =
          (SELECT sid FROM v$mystat WHERE rownum = 1
          );
          END;
          /

          SELECT ROUND(physical_read_bytes /1048576) phyrd_mb ,
          ROUND(io_cell_offload_eligible_bytes/1048576) elig_mb ,
          ROUND(io_interconnect_bytes /1048576) ret_mb ,
          (1-(io_interconnect_bytes/NULLIF(physical_read_bytes,0)))*100 "SAVING%"
          FROM v$sql
          WHERE sql_id = :sql_id
          AND child_number = :sql_child_number;

          SELECT plan_line_id id ,
          LPAD(' ',plan_depth)
          || plan_operation
          ||' '
          ||plan_options
          ||' '
          ||plan_object_name operation ,
          ROUND(physical_read_bytes /1048576) phyrd_mb ,
          ROUND(io_interconnect_bytes /1048576) ret_mb ,
          (1-(io_interconnect_bytes/NULLIF(physical_read_bytes,0)))*100 "SAVING%"
          FROM v$sql_plan_monitor
          WHERE sql_id = :sql_id
          AND sql_exec_id = :sql_exec_id;