Home » RDBMS Server » Performance Tuning » Why *is* my index used? (oracle 19c Linux)
Why *is* my index used? [message #686609] Sun, 23 October 2022 06:29 Go to next message
Andrey_R
Messages: 409
Registered: January 2012
Location: Israel
Senior Member
Hi all,
I've prepared a testcase, where I have a table, which I made sure to have the same value for column TABLE_NAME:

SQL> SET TIMING ON LINES 900 PAGES 20000
SQL>
SQL> DROP TABLE TEST PURGE;

Table dropped.

Elapsed: 00:00:02.82
SQL>
SQL>
SQL> CREATE TABLE TEST
  2  as select TABLE_NAME , NUM_ROWS, LAST_ANALYZED, IOT_NAME,PARTITIONED,LOGGING from dba_tables;

Table created.

Elapsed: 00:00:17.39
SQL>
SQL>
SQL> select count(*) from TEST;

  COUNT(*)
----------
     19212

Elapsed: 00:00:00.07
SQL>
SQL> UPDATE TEST set TABLE_NAME = 'COMMON';

19212 rows updated.

Elapsed: 00:00:00.43
SQL> commit;

Commit complete.

Then I've created an index on this column, and made sure statistics and histograms are collected on both table and indexes ( cascade ):

SQL>
SQL> CREATE INDEX TESTIND ON TEST ( TABLE_NAME ) ;

Index created.

Elapsed: 00:00:00.56
SQL>
SQL> EXEC dbms_stats.gather_table_stats ( ownname => user, tabname => 'TEST', method_opt => 'FOR COLUMNS TABLE_NAME' , estimate_percent => 100, CASCADE => TRUE );

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.19
SQL>
SQL>
SQL> select histogram from USER_TAB_COLUMNS WHERE COLUMN_NAME = 'TABLE_NAME' AND TABLE_NAME = 'TEST';

HISTOGRAM
---------------
FREQUENCY

Elapsed: 00:00:00.36
Now, I would expect that the plan table output will show me AN INDEX SCAN when I am querying the selective value:

SQL> SET AUTOTRACE TRACEONLY EXPLAIN
SQL>
SQL>
SQL> SELECT * FROM TEST WHERE TABLE_NAME = 'SUPER_SELECTIVE';
Elapsed: 00:00:00.25

Execution Plan
----------------------------------------------------------
Plan hash value: 2046642159

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

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

   2 - access("TABLE_NAME"='SUPER_SELECTIVE')

SQL>
SQL>

So far so good.

However, I would also expect it to perform A FULL TABLE SCAN when I give it the absolute non selective condition in the WHERE CLAUSE:

SQL> SELECT * FROM TEST WHERE TABLE_NAME = 'COMMON';
Elapsed: 00:00:00.55

Execution Plan
----------------------------------------------------------
Plan hash value: 2046642159

-----------------------------------------------------------------------------------------------
| Id  | Operation                           | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |         |    11 |   286 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TEST    |    11 |   286 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | TESTIND |       |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

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

   2 - access("TABLE_NAME"='COMMON')

SQL>



Still INDEX RANGE SCAN..

Questions:

1. Why ?
2. How can I ( or can I at all ) make the optimizer "realize" it's better off doing a FTS ( without plan stability tools usage ) ?


Many thanks in advance
Andrey
Re: Why *is* my index used? [message #686613 is a reply to message #686609] Mon, 24 October 2022 07:16 Go to previous messageGo to next message
John Watson
Messages: 8793
Registered: January 2010
Location: Global Village
Senior Member
Running your test, I get the result one would expect:
orclz> SELECT * FROM TEST WHERE TABLE_NAME = 'SUPER_SELECTIVE';

Execution Plan
----------------------------------------------------------
Plan hash value: 2046642159

-----------------------------------------------------------------------------------------------
| Id  | Operation                           | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |         |     1 |    25 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TEST    |     1 |    25 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | TESTIND |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

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

   2 - access("TABLE_NAME"='SUPER_SELECTIVE')

orclz> SELECT * FROM TEST WHERE TABLE_NAME = 'COMMON';

Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  2621 | 65525 |     7   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST |  2621 | 65525 |     7   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter("TABLE_NAME"='COMMON')

orclz>
Looking at your second result, two points jump out. Firstly, there is no cardinality estimate for the index scan; and secondly the estimate for the table access is absurd. So there has to be something wrong with your statistics. Or the way they are (not) being used. Do you have an unusual release or patch level? Odd instance parameters? An old version of the plan_table? Do you get the same result with EXPLAIN PLAN and DBMS_XPLAN.DISPLAY ?
Re: Why *is* my index used? [message #686615 is a reply to message #686613] Tue, 25 October 2022 03:44 Go to previous messageGo to next message
Andrey_R
Messages: 409
Registered: January 2012
Location: Israel
Senior Member
John Watson wrote on Mon, 24 October 2022 15:16
Looking at your second result, two points jump out. Firstly, there is no cardinality estimate for the index scan; and secondly the estimate for the table access is absurd. So there has to be something wrong with your statistics. Or the way they are (not) being used. Do you have an unusual release or patch level? Odd instance parameters? An old version of the plan_table? Do you get the same result with EXPLAIN PLAN and DBMS_XPLAN.DISPLAY ?
It's a Oracle 19c on AWS RDS managed database service

In my example shown above I have set the parameter optimizer_index_cost_adj to be 100 in the session ( as system-wide it's 20) .

If I would keep it as 20 - it does show correct cardinality, however, still wrong access method ( Index Scan instead of FTS ):

SQL> alter session set optimizer_index_cost_adj = 20;

Session altered.

SQL> explain plan for select * from test where table_name = 'COMMON';

Explained.

SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2046642159

-----------------------------------------------------------------------------------------------
| Id  | Operation                           | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |         |    11 |   286 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TEST    |    11 |   286 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | TESTIND | 19212 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------

   2 - access("TABLE_NAME"='COMMON')

14 rows selected.

Re: Why *is* my index used? [message #686622 is a reply to message #686609] Sat, 29 October 2022 08:37 Go to previous messageGo to next message
Andrey_R
Messages: 409
Registered: January 2012
Location: Israel
Senior Member
So respected DBAs,
Any suggestions/ideas what I should check to understand why my index is being used,
Despite correct statistics that would sensibly be read by the optimizer, and optimally supposed to lead to the desired Full Scan..

Thanks
Re: Why *is* my index used? [message #686623 is a reply to message #686622] Sat, 29 October 2022 10:51 Go to previous message
John Watson
Messages: 8793
Registered: January 2010
Location: Global Village
Senior Member
You must have something odd in your environment. I've already made couple of suggestions. You could try
1. Drop any copies of the plan_table
2. Delete all your object statistics
3. Gather stats with default sample size (you should NEVER use estimate_percent=100) and method_opt=>'for all columns size skewonly'
3. Run the statement, then use dbms_xplan.display_cursor to see what actually happens
Previous Topic: Understanding UNDO 11g
Next Topic: Sql having performance issue
Goto Forum:
  


Current Time: Sun Jan 29 10:21:37 CST 2023