Using SQL Tuning Advisor from command line

I recently imported a large table which contained user log data. After import i found that there was no index and table was not analyzed.

I ran a select query

SELECT * FROM USER_LOG WHERE LOG_USER_ID ='JOHN';

This took 6 seconds to retrieve 70000+ rows

Then i ran

SELECT count(*) FROM USER_LOG WHERE LOG_USER_ID ='JOHN';

Now i want to use sql tuning advisor to get tuning recommendations and plan to tune this sql.

I created below tuning task.

#############################################################################################

declare
    tempstring  varchar2(900);
    task_id   varchar2(200);
    begin
       tempstring := 'SELECT * FROM USER_LOG WHERE LOG_USER_ID =''JOHN''';
       task_id := dbms_sqltune.create_tuning_task(sql_text => tempstring, task_name=>'SQLTUNE3');
       dbms_sqltune.execute_tuning_task('SQLTUNE3');
    end;
    /

###############################################################################################

select dbms_sqltune.report_tuning_task('SQLTUNE3') from dual; 

Below is the out put

###############################################################################################

GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : SQLTUNE3
Tuning Task Owner  : FARHAT
Workload Type      : Single SQL Statement
Scope              : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status  : COMPLETED
Started at         : 07/09/2015 13:41:03
Completed at       : 07/09/2015 13:42:40

-------------------------------------------------------------------------------
Schema Name: FARHAT
SQL ID     : bpby9tjfzn5x3
SQL Text   : SELECT * FROM USER_LOG WHERE LOG_USER_ID ='JOHN'

-------------------------------------------------------------------------------
FINDINGS SECTION (3 findings)
-------------------------------------------------------------------------------

1- Statistics Finding
---------------------
  Table "FARHAT"."USER_LOG" was not analyzed.

  Recommendation
  --------------
  - Consider collecting optimizer statistics for this table.
    execute dbms_stats.gather_table_stats(ownname => 'FARHAT', tabname =>
            'USER_LOG', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
            method_opt => 'FOR ALL COLUMNS SIZE AUTO');

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

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

  Recommendation (estimated benefit: 96.53%)
  ------------------------------------------
  - Consider accepting the recommended SQL profile to use parallel execution
    for this statement.
    execute dbms_sqltune.accept_sql_profile(task_name => 'SQLTUNE3',
            task_owner => 'FARHAT', replace => TRUE, profile_type =>
            DBMS_SQLTUNE.PX_PROFILE);

  Executing this query parallel with DOP 32 will improve its response time
  96.53% over the original plan. However, there is some cost in enabling
  parallel execution. It will increase the statement's resource consumption by
  an estimated 10.97% which may result in a reduction of system throughput.
  Also, because these resources are consumed over a much smaller duration, the
  response time of concurrent statements might be negatively impacted if
  sufficient hardware capacity is not available.

  The following data shows some sampled statistics for this SQL from the past
  week and projected weekly values when parallel execution is enabled.

                                 Past week sampled statistics for this SQL
                                 -----------------------------------------
  Number of executions                                                   0
  Percent of total activity                                              0
  Percent of samples with #Active Sessions > 2*CPU                       0
  Weekly DB time (in sec)                                                0

                              Projected statistics with Parallel Execution
                              --------------------------------------------
  Weekly DB time (in sec)                                                0

3- Index Finding (see explain plans section below)
--------------------------------------------------
  The execution plan of this statement can be improved by creating one or more
  indices.

  Recommendation (estimated benefit: 54.72%)
  ------------------------------------------
  - Consider running the Access Advisor to improve the physical schema design
    or creating the recommended index.
    create index FARHAT.IDX$$_497A0001 on FARHAT.USER_LOG("LOG_USER_ID");

  Rationale
  ---------
    Creating the recommended indices significantly improves the execution plan
    of this statement. However, it might be preferable to run "Access Advisor"
    using a representative SQL workload as opposed to a single statement. This
    will allow to get comprehensive index recommendations which takes into
    account index maintenance overhead and additional space consumption.

-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------

1- Original
-----------
Plan hash value: 3169137855

----------------------------------------------------------------------------------
| Id  | Operation         | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |              | 77112 |   449M|   146K  (1)| 00:29:18 |
|*  1 |  TABLE ACCESS FULL| USER_LOG | 77112 |   449M|   146K  (1)| 00:29:18 |
----------------------------------------------------------------------------------

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

   1 - filter("LOG_USER_ID"='JOHN')

2- Using New Indices
--------------------
Plan hash value: 2445436647

----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                | 77112 |   449M| 66307   (1)| 00:13:16 |
|   1 |  TABLE ACCESS BY INDEX ROWID| USER_LOG   | 77112 |   449M| 66307   (1)| 00:13:16 |
|*  2 |   INDEX RANGE SCAN          | IDX$$_497A0001 | 77112 |       |  1704   (1)| 00:00:21 |
----------------------------------------------------------------------------------------------

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

   2 - access("LOG_USER_ID"='JOHN')

3- Using Parallel Execution
---------------------------
Plan hash value: 858866093

------------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name         | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |              | 77112 |   449M|  5079   (1)| 00:01:01 |        |      |            |
|   1 |  PX COORDINATOR      |              |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)| :TQ10000     | 77112 |   449M|  5079   (1)| 00:01:01 |  Q1,00 | P->S | QC (RAND)  |
|   3 |    PX BLOCK ITERATOR |              | 77112 |   449M|  5079   (1)| 00:01:01 |  Q1,00 | PCWC |            |
|*  4 |     TABLE ACCESS FULL| USER_LOG | 77112 |   449M|  5079   (1)| 00:01:01 |  Q1,00 | PCWP |            |
------------------------------------------------------------------------------------------------------------------

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

   4 - filter("LOG_USER_ID"='JOHN')

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

lets implement above recommendations

Create index

create index FARHAT.IDX$$_497A0001 on FARHAT.BAS_USER_LOG("LOG_USER_ID");

Gather Statistics

execute dbms_stats.gather_table_stats(ownname => 'FARHAT', tabname =>
            'BAS_USER_LOG', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
            method_opt => 'FOR ALL COLUMNS SIZE AUTO');

SELECT * FROM USER_LOG WHERE LOG_USER_ID ='JOHN'

Now the query time reduces to 158m seconds

SELECT COUNT(*) FROM USER_LOG WHERE LOG_USER_ID='JOHN'

Now the query time reduces to 46m seconds

#############################################################################################