[oracle@oracleontario ~]$ !sql
sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Apr 27 00:13:08 2022
Version 19.15.0.0.0

Copyright (c) 1982, 2022, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.15.0.0.0

SQL>
SQL> create table new_test (id number, DOB date, text varchar2(40));

Table created.

SQL> insert into new_test select rownum, sysdate-trunc(dbms_random.value(0, 20000)), 'PRASHANT DIXIT'
from dual connect by level <= 2000000;  2

2000000 rows created.

SQL>
SQL> commit;
Commit complete.

SQL> create index idx_newtest on new_test(dob);

Index created.

SQL> EXEC DBMS_STATS.gather_table_stats('DIXDROID', 'NEW_TEST', estimate_percent => null, method_opt=>'FOR ALL COLUMNS SIZE 1');

PL/SQL procedure successfully completed.



-- Here you can see the extremely bad CLUSTERING FACTOR of the Index. 
SQL> SELECT t.table_name, i.index_name, t.blocks, t.num_rows, i.clustering_factor FROM user_tables t, user_indexes i
WHERE t.table_name = i.table_name AND i.index_name='IDX_NEWTEST';  2

TABLE_NAME INDEX_NAME               BLOCKS   NUM_ROWS CLUSTERING_FACTOR
---------- -------------------- ---------- ---------- -----------------
NEW_TEST   IDX_NEWTEST               10097    2000000           1989246

SQL>

SQL> explain plan for select * from new_test where dob between '01-JUN-2017' and '30-JUN-2017';

Explained.

SQL> 


SQL> SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------
Plan hash value: 1847055510

------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |  3100 | 86800 |  2750   (1)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| NEW_TEST |  3100 | 86800 |  2750   (1)| 00:00:01 |
------------------------------------------------------------------------------

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

   1 - filter("DOB">=TO_DATE(' 2017-06-01 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss') AND "DOB"<=TO_DATE(' 2017-06-30 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss'))

15 rows selected.

SQL> alter table NEW_TEST add clustering by linear order(DOB) without materialized zonemap;

Table altered.

SQL> alter table NEW_TEST move online;

Table altered.

-- Now if you check you will the improved CF of the Index.
SQL> SELECT t.table_name, i.index_name, t.blocks, t.num_rows, i.clustering_factor FROM user_tables t, user_indexes i
WHERE t.table_name = i.table_name AND i.index_name='IDX_NEWTEST';  

TABLE_NAME INDEX_NAME               BLOCKS   NUM_ROWS CLUSTERING_FACTOR
---------- -------------------- ---------- ---------- -----------------
NEW_TEST   IDX_NEWTEST               10097    2000000              9277

SQL> explain plan for select * from new_test where dob between '01-JUN-2017' and '30-JUN-2017';

Explained.


SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------
Plan hash value: 1446839462

---------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |             |  3100 | 86800 |    26   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| NEW_TEST    |  3100 | 86800 |    26   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | IDX_NEWTEST |  3100 |       |    11   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------

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

   2 - access("DOB">=TO_DATE(' 2017-06-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "DOB"<=TO_DATE(' 2017-06-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

15 rows selected.

SQL>
SQL>

-- Lets create a sample table
SQL> CREATE TABLE bigtab (
   id         NUMBER,
   weight     NUMBER,
   adate      DATE
);  

Table created.

-- Insert some dummy rows into it
SQL> INSERT INTO bigtab (id, weight, adate)
SELECT MOD(ROWNUM,1000),
       DBMS_RANDOM.RANDOM,
       SYSDATE-1000+DBMS_RANDOM.VALUE(0,1000)
 FROM all_objects
/  

84217 rows created.

SQL> 


-- Lets query the table with our conditions 
SQL> select count(*) from bigtab where ID between 280 and 500;

  COUNT(*)
----------
     18564

SQL>
SQL> select count(*) from bigtab where ID not between 280 and 500;

  COUNT(*)
----------
     65653


-- All set lets get the SQLID for both of the SQLs that we ran
SQL> set linesize 400 pagesize 400
SQL> select sql_id, sql_text from v$sqlarea where sql_text like '%from bigtab where ID%';

SQL> 

SQL_ID        SQL_TEXT
------------- --------------------------------------------------------------------------------
3k1b0y7scc8bd select count(*) from bigtab where ID not between :"SYS_B_0" and :"SYS_B_1"
51tfy4wqb5sg3 select count(*) from bigtab where ID between :"SYS_B_0" and :"SYS_B_1"

SQL>
SQL> VARIABLE v_rep CLOB
SQL> 
SQL> BEGIN
:v_rep := DBMS_XPLAN.COMPARE_PLANS(
reference_plan => cursor_cache_object('3k1b0y7scc8bd', NULL),
compare_plan_list => plan_object_list(cursor_cache_object('51tfy4wqb5sg3', NULL)),
type => 'TEXT',
level => 'TYPICAL',
section => 'ALL');
END;
/  

PL/SQL procedure successfully completed.

SQL>



-- Now lets call the variable to see contents inside, the compare report.
SQL> set long 100000
SQL> COLUMN report FORMAT a200
SQL>
SQL> SELECT :v_rep REPORT FROM DUAL;

REPORT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

COMPARE PLANS REPORT
---------------------------------------------------------------------------------------------
  Current user           : SYS
  Total number of plans  : 2
  Number of findings     : 2
---------------------------------------------------------------------------------------------

COMPARISON DETAILS
---------------------------------------------------------------------------------------------
 Plan Number            : 1 (Reference Plan)
 Plan Found             : Yes
 Plan Source            : Cursor Cache
 SQL ID                 : 3k1b0y7scc8bd
 Child Number           : 0
 Plan Database Version  : 19.0.0.0
 Parsing Schema         : "SYS"
 SQL Text               : select count(*) from bigtab where ID not between
                        :"SYS_B_0" and :"SYS_B_1"

Plan
-----------------------------

 Plan Hash Value  : 2140185107

--------------------------------------------------------------------------
| Id  | Operation            | Name   | Rows  | Bytes  | Cost | Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |        |       |        |   82 |          |
|   1 |   SORT AGGREGATE     |        |     1 |     13 |      |          |
| * 2 |    TABLE ACCESS FULL | BIGTAB | 54028 | 702364 |   82 | 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 2 - filter(("ID"<:SYS_B_0 OR "ID">:SYS_B_1))


Notes
-----
- Dynamic sampling used for this statement ( level = 2 )


---------------------------------------------------------------------------------------------
 Plan Number            : 2
 Plan Found             : Yes
 Plan Source            : Cursor Cache
 SQL ID                 : 51tfy4wqb5sg3
 Child Number           : 0
 Plan Database Version  : 19.0.0.0
 Parsing Schema         : "SYS"
 SQL Text               : select count(*) from bigtab where ID between
                        :"SYS_B_0" and :"SYS_B_1"

Plan
-----------------------------

 Plan Hash Value  : 3865534252

---------------------------------------------------------------------------
| Id  | Operation             | Name   | Rows  | Bytes  | Cost | Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |        |       |        |   79 |          |
|   1 |   SORT AGGREGATE      |        |     1 |     13 |      |          |
| * 2 |    FILTER             |        |       |        |      |          |
| * 3 |     TABLE ACCESS FULL | BIGTAB | 17969 | 233597 |   79 | 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 2 - filter(:SYS_B_1>=:SYS_B_0)
* 3 - filter(("ID">=:SYS_B_0 AND "ID"<=:SYS_B_1))


Notes
-----
- Dynamic sampling used for this statement ( level = 2 )


Comparison Results (2):
-----------------------------
 1. Query block SEL$1, Alias "BIGTAB"@"SEL$1": Some lines (id: 2) in the
    current plan are missing in the reference plan.
 2. Query block SEL$1, Alias "BIGTAB"@"SEL$1": Some columns (ID, PARENT_ID,
    DEPTH) do not match between the reference plan (id: 2) and the current plan
    (id: 3).


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


SQL>
SQL>

SQL> select count(*) from TABLE_GROWTH_MB;

  COUNT(*)
----------
      2868

SQL> select sql_id, plan_hash_value, sql_text from  v$sqlarea where sql_text like '%TABLE_GROWTH_MB%';

SQL_ID        PLAN_HASH_VALUE SQL_TEXT
------------- --------------- --------------------------------------------------
4n3x6w1vhn1k3      1134407048  select count(*) from TABLE_GROWTH_MB

SQL>
SQL> select output from table (dbms_workload_repository.awr_sql_report_text(l_dbid => 2592296819 , l_inst_num => 2, 
l_bid => 36708, l_eid => 36718, l_sqlid => '4n3x6w1vhn1k3'));

OUTPUT
--------------------------------------------------------------------------------
WORKLOAD REPOSITORY SQL Report

Snapshot Period Summary

DB Name         DB Id    Unique Name DB Role          Edition Release    RAC CDB
------------ ----------- ----------- ---------------- ------- ---------- --- ---
FATDBACA      2592296819 FATDBACA    PRIMARY          EE      12.2.0.1.0 YES NO

Instance     Inst Num Startup Time
------------ -------- ---------------
FATDBACA2           2 26-Oct-21 23:10

              Snap Id      Snap Time      Sessions Curs/Sess
            --------- ------------------- -------- ---------
Begin Snap:     36708 10-Mar-22 16:00:37       190       2.5
  End Snap:     36718 10-Mar-22 18:30:06       181       2.6
   Elapsed:              149.48 (mins)
   DB Time:               91.13 (mins)

SQL Summary                    DB/Inst: FATDBACA/FATDBACA2  Snaps: 36708-36718

                  No data exists for this section of the report.
          -------------------------------------------------------------

SQL ID: 4n3x6w1vhn1k3          DB/Inst: FATDBACA/FATDBACA2  Snaps: 36708-36718

                  No data exists for this section of the report.
          -------------------------------------------------------------

Full SQL Text

                  No data exists for this section of the report.
SQL>
SQL>
SQL>
SQL>

-- to PIN the SQLID in question
SQL> exec dbms_workload_repository.add_colored_sql('4n3x6w1vhn1k3');

PL/SQL procedure successfully completed.

SQL>

-- To UNPIN or remove the COLOR SQLID from repo
SQL> exec dbms_workload_repository.remove_colored_sql('<SQL_ID>');

-- Query below view to see list of all colored SQLs
SQL>  select * from DBA_HIST_COLORED_SQL;

   DB Id    SQL_ID        CREATE_TI     CON_ID
----------- ------------- --------- ----------
 2592296819 4n3x6w1vhn1k3 10-MAR-22          0


-- Re ran the SQL
SQL> select count(*) from TABLE_GROWTH_MB;

  COUNT(*)
----------
      2868



-- Let me generate a manual snap to capture fresh data
SQL>
SQL>
SQL> var n_snapid number
set feed off term on head off
set serveroutput on size unlimited
begin
        :n_snapid := dbms_workload_repository.create_snapshot();
        dbms_output.put_line('snap_id: ' || to_char(:n_snapid));
end;
/
snap_id: 36719
SQL> 


-- Let me retry again to see if the SQLID details are there in the AWR after I have colored it
SQL> SQL> select output from table (dbms_workload_repository.awr_sql_report_text(l_dbid => 2592296819 , l_inst_num => 2, 
l_bid => 36718, l_eid => 36719, l_sqlid => '4n3x6w1vhn1k3'));

OUTPUT
---------------------------------------------------------------------------------------------------------------------------------
WORKLOAD REPOSITORY SQL Report

Snapshot Period Summary

DB Name         DB Id    Unique Name DB Role          Edition Release    RAC CDB
------------ ----------- ----------- ---------------- ------- ---------- --- ---
FATDBACA      2592296819 FATDBACA    PRIMARY          EE      12.2.0.1.0 YES NO

Instance     Inst Num Startup Time
------------ -------- ---------------
FATDBACA2           2 26-Oct-21 23:10

              Snap Id      Snap Time      Sessions Curs/Sess
            --------- ------------------- -------- ---------
Begin Snap:     36708 10-Mar-22 16:00:37       190       2.5
  End Snap:     36719 10-Mar-22 18:39:40       177       2.6
   Elapsed:              159.06 (mins)
   DB Time:               93.43 (mins)

SQL Summary                    DB/Inst: FATDBACA/FATDBACA2  Snaps: 36708-36719

                Elapsed
   SQL Id      Time (ms)
------------- ----------

 Container DB
 ------------
4n3x6w1vhn1k3          0
Module: SQL*Plus
select count(*) from TABLE_GROWTH_MB
   2592296819

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

SQL ID: 4n3x6w1vhn1k3          DB/Inst: FATDBACA/FATDBACA2  Snaps: 36708-36719
-> 1st Capture and Last Capture Snap IDs
   refer to Snapshot IDs witin the snapshot range
-> select count(*) from TABLE_GROWTH_MB

    Plan Hash           Total Elapsed                 1st Capture   Last Capture
#   Value                    Time(ms)    Executions       Snap ID        Snap ID
--- ---------------- ---------------- ------------- ------------- --------------
1   1134407048                      0             1         36719          36719
          -------------------------------------------------------------


Plan 1(PHV: 1134407048)
-----------------------

Plan Statistics                DB/Inst: FATDBACA/FATDBACA2  Snaps: 36708-36719
-> % Total DB Time is the Elapsed Time of the SQL statement divided
   into the Total Database Time multiplied by 100

Stat Name                                Statement   Per Execution % Snap
---------------------------------------- ---------- -------------- -------
Elapsed Time (ms)                                 0            0.2     0.0
CPU Time (ms)                                     0            0.2     0.0
Executions                                        1            N/A     N/A
Buffer Gets                                      22           22.0     0.0
Disk Reads                                        0            0.0     0.0
Parse Calls                                       1            1.0     0.0
Rows                                              1            1.0     N/A
User I/O Wait Time (ms)                           0            N/A     N/A
Cluster Wait Time (ms)                            0            N/A     N/A
Application Wait Time (ms)                        0            N/A     N/A
Concurrency Wait Time (ms)                        0            N/A     N/A
Invalidations                                     0            N/A     N/A
Version Count                                     1            N/A     N/A
Sharable Mem(KB)                                 19            N/A     N/A
          -------------------------------------------------------------

Execution Plan
------------------------------------------------------------------------------
| Id  | Operation          | Name            | Rows  | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                 |       |    10 (100)|          |
|   1 |  SORT AGGREGATE    |                 |     1 |            |          |
|   2 |   TABLE ACCESS FULL| TABLE_GROWTH_MB |  2624 |    10   (0)| 00:00:01 |
------------------------------------------------------------------------------



Full SQL Text

SQL ID        SQL Text
------------- -----------------------------------------------------------------
4n3x6w1vhn1k3 select count(*) from TABLE_GROWTH_MB