Tag: baseline

Auto-Capture Baseline Behaviour

Published on by Advait1 Comment

This article is about demonstrating Auto-Capture baseline behavior, which I was surprised that many people are not aware.

Auto-Capture of baseline behaves in following way

Case 1) optimizer_capture_sql_plan_baselines parameter is TRUE

If you have optimizer_capture_sql_plan_baselines parameter set to TRUE, baselines for the SQLs will get captured automatically if SQL query runs 2 or more times. I believe all of us know about this feature

Case 2) optimizer_capture_sql_plan_baselines parameter is FALSE

If optimizer_capture_sql_plan_baselines parameter is set to FALSE, many people think that no new baseline will be captured in the database. This is WRONG. Even when this parameter is set to FALSE, baselines will be captured for following case

*** If a query has one or more baselines with ENABLED=YES ***

Lets take an example

SQL>create table T as select * from dba_objects;

Table created.

SQL>explain plan for 
  2  select count(1) from T where data_object_id between 1000 and 2000;

Explained.

SQL>@display

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2966233522

-----------------------------------
| Id  | Operation       | Name |
-----------------------------------
|   0 | SELECT STATEMENT   |      |
|   1 |  SORT AGGREGATE    |      |
|*  2 |   TABLE ACCESS FULL| T      |
-----------------------------------

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

   2 - filter("DATA_OBJECT_ID"<=2000 AND "DATA_OBJECT_ID">=1000)

Note
-----
   - rule based optimizer used (consider using cbo)

18 rows selected.

SQL>select count(1) from T where data_object_id between 1000 and 2000;

  COUNT(1)
----------
     0

SQL>/

  COUNT(1)
----------
     0
         
SQL>select sql_id from v$sql where sql_text like 'select count(1) from T where data_object_id between 1000 and%';

SQL_ID
-------------
7fbxxz894w0mg

SQL>@baseline
Enter sql ID:- 7fbxxz894w0mg

SQL_TEXT
----------------------------------------------------------------------------------------------------
select count(1) from T where data_object_id between 1000 and 2000


SQL_HANDLE               PLAN_NAME              CREATOR          ORIGIN         LAST_MODIFIED            ENA ACC FIX
------------------------------ ------------------------------ --------------- -------------- ------------------------------ --- --- ---
SYS_SQL_46f1e9bfbc331557       SYS_SQL_PLAN_bc3315573fdbb376  ORACLE_DBA      AUTO-CAPTURE   26-JUN-12 11.30.08.000000 AM   YES YES NO

SQL_HANDLE            PLAN_NAME                  PLAN_HASH_VALUE     ENABLED    ACCEPTED     FIXED
-------------------------   ----------------------------      --------------     -------    -------     -------
SYS_SQL_46f1e9bfbc331557    SYS_SQL_PLAN_bc3315573fdbb376     2966233522      YES         YES       NO

PL/SQL procedure successfully completed.

SQL>show parameters  capture

optimizer_capture_sql_plan_baselines    boolean         TRUE

SQL>alter system set optimizer_capture_sql_plan_baselines=false;

System altered.

SQL>create index T_IDX on T(data_object_id);

Index created.

SQL>select count(1) from T where data_object_id between 1000 and 2000;

  COUNT(1)
----------
     0

SQL>/

  COUNT(1)
----------
     0

         
SQL>@baseline
Enter sql ID:- 7fbxxz894w0mg

SQL_TEXT
----------------------------------------------------------------------------------------------------
select count(1) from T where data_object_id between 1000 and 2000


SQL_HANDLE               PLAN_NAME              CREATOR          ORIGIN         LAST_MODIFIED            ENA ACC FIX
------------------------------ ------------------------------ --------------- -------------- ------------------------------ --- --- ---
SYS_SQL_46f1e9bfbc331557       SYS_SQL_PLAN_bc3315573fdbb376  ORACLE_DBA      AUTO-CAPTURE   26-JUN-12 11.30.08.000000 AM   YES YES NO
SYS_SQL_46f1e9bfbc331557       SYS_SQL_PLAN_bc331557ded8ae2f  ORACLE_DBA      AUTO-CAPTURE   26-JUN-12 11.31.20.000000 AM   YES NO  NO

SQL_HANDLE            PLAN_NAME                  PLAN_HASH_VALUE     ENABLED    ACCEPTED     FIXED
-------------------------   ----------------------------      --------------     -------    -------     -------
SYS_SQL_46f1e9bfbc331557    SYS_SQL_PLAN_bc3315573fdbb376     2966233522      YES         YES       NO
SYS_SQL_46f1e9bfbc331557    SYS_SQL_PLAN_bc331557ded8ae2f     293504097      YES        NO         NO

PL/SQL procedure successfully completed.

SQL>

This proves that even when optimizer_capture_sql_plan_baselines parameter is set to false we can have baselines captured for our old SQL.

If we think about this feature, this is perfect.

Imagine you are rolling out a new module and new queries are going to run against new tables.

At first, initially you will not be having enough data in new tables and your queries will go with FTS (Full Table Scan) and will not use index, even if you create them. This is because based on value of optimizer_index_cost_adj parameter optimizer might make a decision to get data from table rather than going to index and than table.

If you have optimizer_capture_sql_plan_baselines set to TRUE, this is going to create baselines on those queries (which will be automatically accepted as first baselines are automatically accepted).

At later point when data grows and you really want the indexes to get used, your query will still be doing FTS (because of the auto-capture baseline in ACCEPTED state). So even if new baselines are getting captured, they are not getting used.

So you have to manually (or automatically) evolve those baselines to correct the plans.

Instead, my approach would be to enable optimizer_capture_sql_plan_baselines parameter when your database is really baked and you have enough data available. This way it will create baselines which will be first time right.

If your database is still not baked enough and you are looking for plan stability, I would suggest getting baselines from other similar prod database which is baked for long time.

Once you have baselines in place for almost all queries turn off optimizer_capture_sql_plan_baselines parameter. This will prevent baseline capture for new queries, at the same time this doesn’t stop new baselines from getting captured for old SQLs.

Hope this helps !!

Advertisement

Oracle SQL Plan Management – Part 3

Published on by Advait1 Comment

SQL Plan Management – Oracle Database 11g

You have seen the first 2 parts of SQL plan baselines

Part 1 – https://avdeo.com/2011/06/02/oracle-sql-plan-management-part-1/
Part 2 – https://avdeo.com/2011/06/07/oracle-sql-plan-management-%e2%80%93-part-2/

In part 1 we have seen information about SPM and how to automatically capture the baseline
In part 2 we have seen how to manually capture the baseline and the affect of using FIXED variable
In part 3 we will now see, how to import the baselines for SQL from some remote database and also how to set a specific plan for a query using hint and then creating baseline for that

Importing baseline from remote database to our database

Lets say we have a database and a table T in the database.

SQL> desc T
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 COL1                                               NUMBER
 COL2                                               VARCHAR2(50)
 COL3                                               DATE

No Index exists on this table.

SQL> select count(1) from T;

  COUNT(1)
----------
   2097156

SQL> select col1, count(1) from T group by col1;

      COL1   COUNT(1)
---------- ----------
         1          1
         2          3
         3    2097152

SQL> explain plan for
  2  select * from T where col1 = 1;

Explained.

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

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  1048K|    11M|  1424   (6)| 00:00:18 |
|*  1 |  TABLE ACCESS FULL| T    |  1048K|    11M|  1424   (6)| 00:00:18 |
--------------------------------------------------------------------------

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

   1 - filter("COL1"=1)

13 rows selected.

SQL>

Lets create a baseline for this query manually.

Find the SQL ID using

SQL>select sql_id from v$sql where sql_text = 'select * from T where col1 = 1';

SQL_ID
-------------
5pvxxjg6n7mrp

SQL>

DECLARE
  l_plans_loaded  PLS_INTEGER;
BEGIN
  l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache(
    sql_id => '5pvxxjg6n7mrp');
END;
/

SQL> select SQL_HANDLE, PLAN_NAME, ENABLED, ACCEPTED, FIXED from dba_sql_plan_baselines where signature IN (SELECT exact_matching_signature FROM v$sql WHERE sql_id='&SQL_ID');
Enter value for sql_id: 5pvxxjg6n7mrp
old   1: select SQL_HANDLE, PLAN_NAME, ENABLED, ACCEPTED, FIXED from dba_sql_plan_baselines where signature IN (SELECT exact_matching_signature FROM v$sql WHERE sql_id='&SQL_ID')
new   1: select SQL_HANDLE, PLAN_NAME, ENABLED, ACCEPTED, FIXED from dba_sql_plan_baselines where signature IN (SELECT exact_matching_signature FROM v$sql WHERE sql_id='5pvxxjg6n7mrp')

SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX
------------------------------ ------------------------------ --- --- ---
SYS_SQL_1447ba3a1d83920f       SYS_SQL_PLAN_1d83920f94ecae5c  YES YES NO

SQL>

So now the baseline is created for this SQL.

If we check the plan it will show us Full Table Scan.

Now lets create an index.

SQL> create index T_IDX on T(col1);

Index created.

SQL> exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=> '&owner', TABNAME => '&tabname', DEGREE => 6, GRANULARITY => 'ALL' ,CASCADE => TRUE , METHOD_OPT => 'FOR ALL INDEXED COLUMNS SIZE 254');
Enter value for owner: ADVAITD_DBA
Enter value for tabname: T

PL/SQL procedure successfully completed.

SQL>

lets see the plan for same query again. Since we have created an index on col1 of table T, when using value 1 we expect an index to be used rather than FTS.

SQL> explain plan for
  2  select * from T where col1 = 1;

Explained.

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  1048K|    11M|  1424   (6)| 00:00:18 |
|*  1 |  TABLE ACCESS FULL| T    |  1048K|    11M|  1424   (6)| 00:00:18 |
--------------------------------------------------------------------------

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

   1 - filter("COL1"=1)

Note
-----
   - SQL plan baseline "SYS_SQL_PLAN_1d83920f94ecae5c" used for this statement

17 rows selected.

SQL>

Here we clearly see baseline is getting used and it still uses FTS. This is a plan stability feature in 11g. No matter what changes we do, if a baseline already exists for a query and even if you create an index, it wont use that index unless you create a new baseline for that. As you know by now, new baseline will get created automatically only if you set OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES parameters is set to true. If this is set to false you have to create baseline manaully using DBMS_SPM package as shown above.

At this point of time we have an option to create a baseline and using DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE

But instead of using that we will try to export and import a baseline from some other DB having same query and tables.

So lets say, I have another database having same table and running same query. But that query is using correct index on col1. So we will see how to get that baseline exported and imported to our DB.
In real time situation, we will get several cases where in same set of queries are running good in some other databases but thay are having bad plan in our databaes. So instead of trying to get the plan right using traditional methods, we can simply export and import the baseline of that query from some other DB where its performing better and plans will be fixed in our DB without much effort.
So on my new DB, I have a correct plan as indicated below

SQL> explain plan for
  2  select * from T where col1 = 1;

Explained.

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

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 470836197

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |  2359K|   139M|   307   (1)| 00:00:04 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |  2359K|   139M|   307   (1)| 00:00:04 |
|*  2 |   INDEX RANGE SCAN          | T_IDX |  2379K|       |    47   (3)| 00:00:01 |
-------------------------------------------------------------------------------------

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

   2 - access("COL1"=1)

Note
-----
   - SQL plan baseline "SYS_SQL_PLAN_1d83920fae82cf72" used for this statement

18 rows selected.

SQL>

Above query is using the baseline SYS_SQL_PLAN_1d83920fae82cf72

We will export and import this baseline in our database now. Following steps needs to be done to export and import the baseline

1) Create a staging table

exec DBMS_SPM.CREATE_STGTAB_BASELINE('STGTAB', 'ADVAITD_DBA');

STGTAB –> Name of staging table. You can give any name which is not used till now. A table with this name will get created.
ADVAITD_DBA –> Owner of the table. Table will get created in this schema.

2) Pack baseline into this table

declare
x number;
begin
x := DBMS_SPM.PACK_STGTAB_BASELINE('STGTAB', 'ADVAITD_DBA', sql_handle => 'SYS_SQL_1447ba3a1d83920f', plan_name => 'SYS_SQL_PLAN_1d83920fae82cf72' );
end;
/

3) Export the staging table from source DB where you have baseline packed
exp userid=advaitd_dba@new_db tables=’STGTAB’ file=STGTAB.dmp log=STGTAB.log

advaitd.desktop$ exp userid=advaitd_dba@new_db tables='STGTAB' file=STGTAB.dmp log=STGTAB.log

Export: Release 10.2.0.2.0 - Production on Sun Aug 7 06:15:39 2011

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

Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses UTF8 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
. . exporting table                         STGTAB          1 rows exported
Export terminated successfully without warnings.
advaitd.desktop$

4) Import the table into the DB where you want to put the baseline
imp userid=advaitd_dba@our_db file=STGTAB.dmp log=STGTAB.log full=y

advaitd.desktop$ imp userid=advaitd_dba@our_db file=STGTAB.dmp log=STGTAB.log full=y

Import: Release 10.2.0.2.0 - Production on Sun Aug 7 06:20:54 2011

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

Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options

Export file created by EXPORT:V10.02.01 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses UTF8 character set (possible charset conversion)
. importing ADVAITD_DBA's objects into ADVAITD_DBA
. importing ADVAITD_DBA's objects into ADVAITD_DBA
. . importing table                       "STGTAB"          1 rows imported
Import terminated successfully without warnings.
advaitd.desktop$

5) Unpack the staging table

declare
x number;
begin
x := DBMS_SPM.UNPACK_STGTAB_BASELINE('STGTAB', 'ADVAITD_DBA');
end;
/

Now we can see 2 baseline.
– One baseline which was already exisitng and doing FTS
– One we imported just now which is using index

SQL> select SQL_HANDLE, PLAN_NAME, ENABLED, ACCEPTED, FIXED from dba_sql_plan_baselines where signature IN (SELECT exact_matching_signature FROM v$sql WHERE sql_id='&SQL_ID');
Enter value for sql_id: 5pvxxjg6n7mrp
old   1: select SQL_HANDLE, PLAN_NAME, ENABLED, ACCEPTED, FIXED from dba_sql_plan_baselines where signature IN (SELECT exact_matching_signature FROM v$sql WHERE sql_id='&SQL_ID')
new   1: select SQL_HANDLE, PLAN_NAME, ENABLED, ACCEPTED, FIXED from dba_sql_plan_baselines where signature IN (SELECT exact_matching_signature FROM v$sql WHERE sql_id='5pvxxjg6n7mrp')

SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX
------------------------------ ------------------------------ --- --- ---
SYS_SQL_1447ba3a1d83920f       SYS_SQL_PLAN_1d83920f94ecae5c  YES YES NO
SYS_SQL_1447ba3a1d83920f       SYS_SQL_PLAN_1d83920fae82cf72  YES YES NO

Now, if we check the plan for same query, we should be able to see index getting used and it will use the plan SYS_SQL_PLAN_1d83920fae82cf72 which we imported from other DB

SQL> explain plan for
  2  select * from T where col1 = 1;

Explained.

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

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 470836197

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |  1048K|    11M|    46   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |  1048K|    11M|    46   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T_IDX |  1048K|       |    21   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

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

   2 - access("COL1"=1)

Note
-----
   - SQL plan baseline "SYS_SQL_PLAN_1d83920fae82cf72" used for this statement

18 rows selected.

SQL>

Creating baseline for user created plans

Lets go back to original situation where query on the table is doing a FTS. At this point instead of getting a profile from some other DB, lets create a correct profile using hints.

I am flushing the shared_pool so as to remove any exising plans from shared_pool

SQL> alter system flush shared_pool;

System altered.

SQL> /

System altered.

SQL>

Lets use Index hint here and see if the query uses index.

SQL> select /*+ index(T_IDX T) +*/ * from T where col1 = 1;

      COL1 COL2                                               COL3
---------- -------------------------------------------------- -----------
         1                                                    10-JUL-2011

SQL> explain plan for
  2  select /*+ index(T_IDX T) +*/ * from T where col1 = 1;

Explained.

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

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 470836197

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |  1048K|    11M|   465   (2)| 00:00:06 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |  1048K|    11M|   465   (2)| 00:00:06 |
|*  2 |   INDEX RANGE SCAN          | T_IDX |  1048K|       |   209   (2)| 00:00:03 |
-------------------------------------------------------------------------------------

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

   2 - access("COL1"=1)

14 rows selected.

SQL>

So using a hint serve the purpose.
In real life situation SQLs are usually part of modules and codes. So using hint at SQL level, its not possible to implement the hint in code. Also, using hints in SQL at code level is not a good practice for obvious reason.

If we create a baseline based on hints than baseline will be automatically used for that SQL and plan will be same as that using hint.

Procedure is simple, just use the hint and run sql couple of times. One you do that, the plan will be automatically stored in shared_pool.

Find a SQL ID

SQL> select sql_id from v$sql where sql_text like 'select /*+ index(T_IDX T) +*/ * from%';

SQL_ID
-------------
50tsvs88gx382

Use DBMS_SPM.load_plans_from_cursor_cache to create baseline from cursor cache.

DECLARE
  l_plans_loaded  PLS_INTEGER;
BEGIN
  l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache(
    sql_id => '50tsvs88gx382');
END;
/

At this point we have 2 SQLs –

50tsvs88gx382 – using index hint
5pvxxjg6n7mrp – Original query without using hint. This is doing FTS

SQL> select SQL_HANDLE, PLAN_NAME, ENABLED, ACCEPTED, FIXED from dba_sql_plan_baselines ;

SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX
------------------------------ ------------------------------ --- --- ---
SYS_SQL_1447ba3a1d83920f       SYS_SQL_PLAN_1d83920f94ecae5c  YES YES NO
SYS_SQL_1447ba3a1d83920f       SYS_SQL_PLAN_1d83920fae82cf72  YES NO  NO
SYS_SQL_3f0d350a1c24a8d4       SYS_SQL_PLAN_1c24a8d4ae82cf72  YES YES NO

SQL>

SQL_HANDLE – SYS_SQL_1447ba3a1d83920f is for original query without hint. This was having only 1 baseline, but when we loaded the baseline for query 50tsvs88gx382 which is using hint, it automatically created the baseline for original query. 11g is smart enought to filter the hints and create a baseline for original query.

At this stage you have 2 option

1) Accept plan SYS_SQL_PLAN_1d83920fae82cf72 for SQL_HANDLE SYS_SQL_1447ba3a1d83920f
2) Link sql plan SYS_SQL_PLAN_1c24a8d4ae82cf72 of SQL_HANDLE SYS_SQL_3f0d350a1c24a8d4 to SQL_HANDLE SYS_SQL_1447ba3a1d83920f

You already know option 1.

Lets see option 2.

To link the plan of some other SQL_HANDLE (SYS_SQL_3f0d350a1c24a8d4) to our SQL_HANDLE (SYS_SQL_1447ba3a1d83920f), you can use same procedure dbms_spm.load_plans_from_cursor_cache

DECLARE
  l_plans_loaded  PLS_INTEGER;
BEGIN
  l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache(
    sql_id => '50tsvs88gx382',
    PLAN_HASH_VALUE => 470836197,
    SQL_HANDLE => 'SYS_SQL_1447ba3a1d83920f');
END;
/

PL/SQL procedure successfully completed.

In above procedure,
– SQL_ID is the new SQL_ID having hints.
– PLAN_HASH_VALUE is the plan hash value of SQL with hints. This is the plan we want to assign to our SQL_HANDLE
– SQL_HANDLE is the name of our SQL Handle to which we want to assign the plan

After assigning the plan, you will see the status as below.

SQL> select SQL_HANDLE, PLAN_NAME, ENABLED, ACCEPTED, FIXED from dba_sql_plan_baselines ;

SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX
------------------------------ ------------------------------ --- --- ---
SYS_SQL_1447ba3a1d83920f       SYS_SQL_PLAN_1d83920f94ecae5c  YES YES NO
SYS_SQL_1447ba3a1d83920f       SYS_SQL_PLAN_1d83920fae82cf72  YES YES NO
SYS_SQL_3f0d350a1c24a8d4       SYS_SQL_PLAN_1c24a8d4ae82cf72  YES YES NO

SQL> explain plan for
  2  select * from T where col1 = 1;

Explained.

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

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 470836197

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |  1048K|    11M|   465   (2)| 00:00:06 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |  1048K|    11M|   465   (2)| 00:00:06 |
|*  2 |   INDEX RANGE SCAN          | T_IDX |  1048K|       |   209   (2)| 00:00:03 |
-------------------------------------------------------------------------------------

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

   2 - access("COL1"=1)

Note
-----
   - SQL plan baseline "SYS_SQL_PLAN_1d83920fae82cf72" used for this statement

18 rows selected.

SQL>

Hope this helps !!