Schema Name: SYS
SQL ID     : 6astft6rzdfdr
SQL Text   : select
             sum(lastshares) as "ROSEN"
             from nyeo.fix_exec_reports fer, nyeo.placement_request_queue q,
             nyeo.nyeo_block_control bc
             where fer.clordid = q.sequence_number
             and q.blockid = bc.blockid
             and upper(bc.deskname) like '%ROSEN%'

DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_6ASTFT6RZDFDR')
----------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------
FINDINGS SECTION (2 findings)
-------------------------------------------------------------------------------

1- Statistics Finding
---------------------
  Optimizer statistics for table "NYEO"."PLACEMENT_REQUEST_QUEUE" are stale.

  Recommendation
  --------------

DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_6ASTFT6RZDFDR')
----------------------------------------------------------------------------------------------------
  - Consider collecting optimizer statistics for this table and its indices.
    execute dbms_stats.gather_table_stats(ownname => 'NYEO', tabname =>
            'PLACEMENT_REQUEST_QUEUE', estimate_percent =>
            DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE
            AUTO', cascade => TRUE);

  Rationale
  ---------
    The optimizer requires up-to-date statistics for the table and its indices
    in order to select a good execution plan.


DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_6ASTFT6RZDFDR')
--------------------------------------------------------
2- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
  A potentially better execution plan was found for this statement.

  Recommendation (estimated benefit: 19.55%)
  ------------------------------------------
  - Consider accepting the recommended SQL profile.
    execute dbms_sqltune.accept_sql_profile(task_name => 'sql_6astft6rzdfdr',
            replace => TRUE);

------------------------------------------------------------------------

DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_6ASTFT6RZDFDR')
-------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------

1- Original With Adjusted Cost
------------------------------
Plan hash value: 286341588

----------------------------------------------------------------------------------------------------
--
| Id  | Operation               | Name                       | Rows  | Bytes | Cost (%CPU)| Time
 |

DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_6ASTFT6RZDFDR')
|   0 | SELECT STATEMENT        |                            |     1 |    35 |   370   (2)| 00:00:05|
|   1 |  SORT AGGREGATE         |                            |     1 |    35 |            |         |
|*  2 |   HASH JOIN             |                            |     1 |    35 |   370   (2)| 00:00:05|
|*  3 |    HASH JOIN            |                            |     1 |    26 |   124   (2)| 00:00:02|
|*  4 |     TABLE ACCESS FULL   | NYEO_BLOCK_CONTROL         |     1 |    17 |    49   (0)| 00:00:01|
|   5 |     INDEX FAST FULL SCAN| PK_PLACEMENT_REQUEST_QUEUE | 54018 |   474K|    73   (0)| 00:00:01|
|   6 |    TABLE ACCESS FULL    | FIX_EXEC_REPORTS           | 70243 |   617K|   245   (1)| 00:00:03|


Predicate Information (identified by operation id):
---------------------------------------------------


DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_6ASTFT6RZDFDR')
----------------------------------------------------------------------------------------------------
   2 - access("Q"."SEQUENCE_NUMBER"=TO_NUMBER("FER"."CLORDID"))
   3 - access("Q"."BLOCKID"="BC"."BLOCKID")
   4 - filter(UPPER("BC"."DESKNAME") LIKE '%ROSEN%')

2- Using SQL Profile
--------------------
Plan hash value: 2456896891

---------------------------------------------------------------------------------------------------
| Id  | Operation            | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------

DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_6ASTFT6RZDFDR')
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                            |     1 |    35 |   297   (2)| 00:00:04 |
|   1 |  SORT AGGREGATE      |                            |     1 |    35 |            |          |
|*  2 |   HASH JOIN          |                            |     1 |    35 |   297   (2)| 00:00:04 |
|   3 |    NESTED LOOPS      |                            |     1 |    26 |    51   (0)| 00:00:01 |
|*  4 |     TABLE ACCESS FULL| NYEO_BLOCK_CONTROL         |     1 |    17 |    49   (0)| 00:00:01 |
|*  5 |     INDEX RANGE SCAN | PK_PLACEMENT_REQUEST_QUEUE |    13 |   117 |     2   (0)| 00:00:01 |
|   6 |    TABLE ACCESS FULL | FIX_EXEC_REPORTS           | 70243 |   617K|   245   (1)| 00:00:03 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_6ASTFT6RZDFDR')
----------------------------------------------------------------------------------------------------

   2 - access("Q"."SEQUENCE_NUMBER"=TO_NUMBER("FER"."CLORDID"))
   4 - filter(UPPER("BC"."DESKNAME") LIKE '%ROSEN%')
   5 - access("Q"."BLOCKID"="BC"."BLOCKID")

-------------------------------------------------------------------------------