OraFAQ Forum: Performance Tuning » Why *is* my index used?

Home » RDBMS Server » Performance Tuning » Why *is* my index used? (oracle 19c Linux)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Why *is* my index used? [message #686609]

Sun, 23 October 2022 06:29

Andrey_R
Messages: 441
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

Report message to a moderator

Re: Why *is* my index used? [message #686613 is a reply to message #686609]

Mon, 24 October 2022 07:16

John Watson
Messages: 8960
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 ?

Report message to a moderator

Re: Why *is* my index used? [message #686615 is a reply to message #686613]

Tue, 25 October 2022 03:44

Andrey_R
Messages: 441
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.

Report message to a moderator

Re: Why *is* my index used? [message #686622 is a reply to message #686609]

Sat, 29 October 2022 08:37

Andrey_R
Messages: 441
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

Report message to a moderator

Re: Why *is* my index used? [message #686623 is a reply to message #686622]

Sat, 29 October 2022 10:51

John Watson
Messages: 8960
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

Report message to a moderator

Previous Topic:	Understanding UNDO 11g
Next Topic:	Sql having performance issue

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Nov 21 07:35:42 CST 2024