This discussion is archived
12 Replies Latest reply: Feb 13, 2013 11:55 AM by mtefft RSS

Avoiding full table scan on a large table in a query

orausern Explorer
Currently Being Moderated
Hi Experts,

I am on Oracle 11.2.0.2 on Solaris 10. I have an issue with one query that does a full table scan in a table that has 62k rows in development environment, and about 220k rows in FIT/QA environment. The query has a NOT EXISTS clause. I will be thankful on any pointers/suggestions on what can be done to tune the query. The table definition ddl, indexes etc, the query and the query plan are as follows:
CREATE TABLE TEST_CLIENT_APP
(
  APP_ID         VARCHAR2(50 BYTE)              NOT NULL,
  CLNT_OID       VARCHAR2(16 BYTE)              NOT NULL,
  CREATED_BY     VARCHAR2(80 BYTE),
  CREATED_DATE   DATE,
  MODIFIED_BY    VARCHAR2(80 BYTE),
  MODIFIED_DATE  DATE
);


CREATE TABLE TEST_MSG_USER
(
  MESSAGE_USER_ID           NUMBER(12)          NOT NULL,
  MESSAGE_INSTANCE_ID       NUMBER(12)          NOT NULL,
  CLNT_OID                  VARCHAR2(16 BYTE)   NOT NULL,
  PROCESS_STEP_ID           NUMBER(12),
  USER_OID                  VARCHAR2(16 BYTE)   NOT NULL,
  MESSAGE_STATUS            VARCHAR2(3 BYTE)    NOT NULL,
  SEND_REMINDER_DATE        DATE,
  SEND_REMINDER_COUNT       NUMBER(2),
  HIDE_DETAILS_LINK         NUMBER(1),
  ARCHIVED                  NUMBER(1)           NOT NULL,
  READ                      NUMBER(1)           NOT NULL,
  START_DATE                DATE                NOT NULL,
  END_DATE                  DATE,
  MESSAGE_TYPE_NAME         VARCHAR2(80 BYTE)   NOT NULL,
  BASE_TYPE                 CHAR(3 CHAR)        NOT NULL,
  GROUP_OID                 NUMBER(12),
  DUE_DATE                  DATE,
  REMINDER_TYPE             NUMBER(1),
  IS_DUE_DATE_SYS_ASSIGNED  NUMBER(1),
  TRACKED                   NUMBER(1)           NOT NULL
);


CREATE TABLE TEST_PROPERTIES
(
  PROPERTY_NAME           VARCHAR2(30 BYTE)     NOT NULL,
  CLIENT_OID              VARCHAR2(16 BYTE)     NOT NULL,
  PROPERTY_VALUE          VARCHAR2(1024 BYTE),
  APP_ID                  VARCHAR2(50 BYTE)     NOT NULL,
  PROPERTY_VALUE_LANG_ID  NUMBER(12)
);


CREATE UNIQUE INDEX PK_TEST_CLIENT_APP ON TEST_CLIENT_APP
(APP_ID, CLNT_OID);


CREATE UNIQUE INDEX PK_TEST_PROPERTIES ON TEST_PROPERTIES
(PROPERTY_NAME, CLIENT_OID);


CREATE INDEX TEST_MSG_USER_IDX01 ON TEST_MSG_USER
(USER_OID, MESSAGE_STATUS, CLNT_OID);


CREATE INDEX TEST_MSG_USER_IDX02 ON TEST_MSG_USER
(SEND_REMINDER_DATE);


CREATE INDEX TEST_MSG_USER_IDX03 ON TEST_MSG_USER
(PROCESS_STEP_ID);


CREATE INDEX TEST_MSG_USER_IDX04 ON TEST_MSG_USER
(CLNT_OID);


CREATE INDEX TEST_MSG_USER_IDX05 ON TEST_MSG_USER
(MESSAGE_TYPE_NAME);


CREATE INDEX TEST_MSG_USER_IDX06 ON TEST_MSG_USER
(MESSAGE_INSTANCE_ID);


CREATE INDEX TEST_MSG_USER_IDX07 ON TEST_MSG_USER
(CLNT_OID, USER_OID, MESSAGE_INSTANCE_ID);


ALTER TABLE TEST_CLIENT_APP ADD (
  CONSTRAINT PK_TEST_CLIENT_APP
  PRIMARY KEY
  (APP_ID, CLNT_OID)
  USING INDEX PK_TEST_CLIENT_APP
  ENABLE VALIDATE);


ALTER TABLE TEST_PROPERTIES ADD (
  CONSTRAINT PK_TEST_PROPERTIES
  PRIMARY KEY
  (PROPERTY_NAME, CLIENT_OID)
  USING INDEX PK_TEST_PROPERTIES
  ENABLE VALIDATE);

--following are the count of rows in these three tables:
SQL> select count(*) from test_msg_user;

  COUNT(*)
----------
     62701

SQL> select count(*) from test_properties;

  COUNT(*)
----------
         8

SQL> select count(*) from test_client_app;

  COUNT(*)
----------
       174


--the sql query is as follows:
variable SYS_B_1 VARCHAR2(30);
variable SYS_B_2 NUMBER;
variable SYS_B_3 VARCHAR2(30);
variable SYS_B_4 VARCHAR2(30);

                                                       
exec :SYS_B_1:='COM' ;
exec :SYS_B_2:=180;
exec :SYS_B_3:='mcEnhanced';
exec :SYS_B_4:='true';


SELECT TMU.MESSAGE_USER_ID
  FROM    TEST_MSG_USER TMU
        WHERE TMU.ARCHIVED = 0 AND TMU.MESSAGE_STATUS = :SYS_B_1
       AND NOT EXISTS
                  (SELECT 1
                     FROM TEST_PROPERTIES tp, TEST_CLIENT_APP tca
                    WHERE     tp.app_id = tca.app_id
                          AND tp.property_name = :SYS_B_3
                          AND tp.property_value = :SYS_B_4
                          AND tca.CLNT_OID = TMU.CLNT_OID);

--Note the query returns 251 rows as output.
The query plan:
Plan hash value: 2291474835

----------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                    |       |       |   254 (100)|          |
|*  1 |  HASH JOIN RIGHT ANTI          |                    | 10223 |   409K|   254   (1)| 00:00:04 |
|   2 |   VIEW                         | VW_SQ_1            |     1 |    10 |     3   (0)| 00:00:01 |
|   3 |    NESTED LOOPS                |                    |     1 |    59 |     3   (0)| 00:00:01 |
|*  4 |     TABLE ACCESS BY INDEX ROWID| TEST_PROPERTIES    |     1 |    33 |     2   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN          | PK_TEST_PROPERTIES |     1 |       |     1   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
|*  6 |     INDEX RANGE SCAN           | PK_TEST_CLIENT_APP |     7 |   182 |     1   (0)| 00:00:01 |
|*  7 |   TABLE ACCESS FULL            | TEST_MSG_USER      | 10450 |   316K|   251   (1)| 00:00:04 |
----------------------------------------------------------------------------------------------------

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

   1 - access("ITEM_1"="TMU"."CLNT_OID")
   4 - filter("TP"."PROPERTY_VALUE"=:SYS_B_4)
   5 - access("TP"."PROPERTY_NAME"=:SYS_B_3)
   6 - access("TP"."APP_ID"="TCA"."APP_ID")
   7 - filter(("TMU"."MESSAGE_STATUS"=:SYS_B_1 AND "TMU"."ARCHIVED"=:B0))


34 rows selected.
The issue is the full table scan on TEST_MSG_USER. Is there any way to avoid it?

Thanks,
OrauserN

Edited by: orausern on Feb 12, 2013 10:57 AM
  • 1. Re: Avoiding full table scan on a large table in a query
    Martin Preiss Expert
    Currently Being Moderated
    Hi,

    it depends: the FTS could be a sensible strategy - at least the CBO thinks so...

    There are some indexes on TEST_MSG_USER that look a little bit strange (because the column names don't signalize a good selectivity) - but to avoid the FTS you could add another index on (CLNT_OID, ARCHIVED, MESSAGE_STATUS, MESSAGE_USER_ID): this one includes all the relevant data for your query (as a fat or covering index) and should at least bring an INDEX FAST FULL SCAN in the HASH JOIN. Perhaps the CBO would also think about an NL Anti Join - but that's a question of data distribution and arithmetic.

    I am not sure that this index will improve the query performance significantly (not to mention the DML performance) but it should avoid the FTS.

    Regards

    Martin
  • 2. Re: Avoiding full table scan on a large table in a query
    mtefft Journeyer
    Currently Being Moderated
    If you think about it, you really should not expect anything other than a FTS here.

    Look at your query. You need TEST_MSG_USER rows that are NOT FOUND in your subselect. So you can't start with the subselect to get your answer - you have to start with TEST_MSG_USER.

    How could you limit the rows needed when you access TEST_MSG_USER? You have predicates on TMU.ARCHIVED = 0 AND TMU.MESSAGE_STATUS = :SYS_B_1. I don't have your data but the optimizer thinks that these predicates reduce the rows from 62K to 10k. So, even if you had indexes on these columns, they might not be useful because 10k accesses via index probably aren't as fast as a 62k row tablescan.

    Are these row counts representative of what your final data will be?
  • 3. Re: Avoiding full table scan on a large table in a query
    orausern Explorer
    Currently Being Moderated
    Thanks to you both! I dont' know the inner details but the full table scan went away after adding the suggested index and the cost reduced from 254 to 105!
    Here is the revised query plan after the new index:

    SQL> create index test_idx1 on test_msg_user(CLNT_OID, ARCHIVED, MESSAGE_STATUS,
    MESSAGE_USER_ID) tablespace index1;

    Index created.

    --execute the query..
    --revised plan below...wow!!!
    Plan hash value: 496895031
    
    ----------------------------------------------------------------------------------------------------
    | Id  | Operation                      | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
    ----------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT               |                    |       |       |   105 (100)|          |
    |*  1 |  HASH JOIN RIGHT ANTI          |                    | 10223 |   409K|   105   (1)| 00:00:02 |
    |   2 |   VIEW                         | VW_SQ_1            |     1 |    10 |     3   (0)| 00:00:01 |
    |   3 |    NESTED LOOPS                |                    |     1 |    59 |     3   (0)| 00:00:01 |
    |*  4 |     TABLE ACCESS BY INDEX ROWID| TEST_PROPERTIES    |     1 |    33 |     2   (0)| 00:00:01 |
    |*  5 |      INDEX RANGE SCAN          | PK_TEST_PROPERTIES |     1 |       |     1   (0)| 00:00:01 |
    
    PLAN_TABLE_OUTPUT
    ----------------------------------------------------------------------------------------------------
    |*  6 |     INDEX RANGE SCAN           | PK_TEST_CLIENT_APP |     7 |   182 |     1   (0)| 00:00:01 |
    |*  7 |   INDEX FAST FULL SCAN         | TEST_IDX1          | 10450 |   316K|   102   (1)| 00:00:02 |
    ----------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       1 - access("ITEM_1"="TMU"."CLNT_OID")
       4 - filter("TP"."PROPERTY_VALUE"=:SYS_B_4)
       5 - access("TP"."PROPERTY_NAME"=:SYS_B_3)
       6 - access("TP"."APP_ID"="TCA"."APP_ID")
       7 - filter(("TMU"."MESSAGE_STATUS"=:SYS_B_1 AND "TMU"."ARCHIVED"=:B0))
  • 4. Re: Avoiding full table scan on a large table in a query
    mtefft Journeyer
    Currently Being Moderated
    Please make sure you understand what you have done here.

    You have replaced a tablescan with an index fast-full scan. It's really the same thing, except you are dragging through all of the blocks of an index segment instead of a table segment. It will be somewhat faster than a tablescan, but not necessarily noticeably so.

    In return, you have added an eighth (!) index to this poor table. And you have indexed columns (ARCHIVED, MESSAGE_STATUS) that sound like they are likely to change. This additional index will slow down inserts, deletes, and updates that touch those columns.

    Never rmind the 'cost'. This is what you should really be thinking about:
    - Did the query actually run faster? How much faster?
    - Was it really something that needed to be tuned in the first place? Or was this targeted just because of the tablescan?
    - Have you tested the impact on the other inserts, deletes and updates to the table?
    - How often does it run? Does it run often enough to justify slowing down the other activity on this table?
  • 5. Re: Avoiding full table scan on a large table in a query
    orausern Explorer
    Currently Being Moderated
    Unluckily I was wrong...the full table scan came back - what I posted here was a smaller example of a slightly larger sql and in that sql when I applied the new index, it didnot do away the FTS....because there was an outer join there!! So what do I do? How do I convey this to my manager that the full table scan on the large table can't be avoided - the query runs only once in midnight but it is taking 6 seconds and our SLA is two seconds!!

    Thanks
    OrauseN
  • 6. Re: Avoiding full table scan on a large table in a query
    mtefft Journeyer
    Currently Being Moderated
    You have a query that runs once a day (at midnight, no less), and it has a 2-second SLA? Seriously?

    Why was an SLA like that established? Who is actually going to be impacted if it is missed? I could understand a batch SLA that is measured in minutes, but this makes no sense to me.
  • 7. Re: Avoiding full table scan on a large table in a query
    Richard Harrison . Expert
    Currently Being Moderated
    Hi,
    How many rows in test_msg_usr have a status of 'COM' ?

    Cheers,
    Harry
  • 8. Re: Avoiding full table scan on a large table in a query
    jgarry Guru
    Currently Being Moderated
    I'm wondering about the property_value field. That seems an odd thing to have an equivalency on, unless you have some special meaningful value. So you get a range scan in the join that creates the view, with nested loops. Not sure how you can fix that without a definitional change, like maybe playing index games with nulls. The anti-join is the efficient way to do things, see http://www.dbspecialists.com/files/presentations/semijoins.html so maybe changing the sla is the right move.
  • 9. Re: Avoiding full table scan on a large table in a query
    ***Anuj*** Journeyer
    Currently Being Moderated
    OrauseN,

    Lets take baby steps..try below options in sequence.it is upto you if you want to try one by one and check query time or do all and check.

    1. rebuild all the indexes on table doing FTS
    2. update stats for table with cascade
    3. cache small tables in memory (they are very small should not take lot of memory)

    Plan may not change but response time should improve.Run your qury in midnight ;) and take time.

    If it it does not help remove tables from cache.

    HTH
    Anuj
    http://www.oracle-12c.com/
  • 10. Re: Avoiding full table scan on a large table in a query
    Randolf Geist Oracle ACE Director
    Currently Being Moderated
    orausern wrote:
    Unluckily I was wrong...the full table scan came back - what I posted here was a smaller example of a slightly larger sql and in that sql when I applied the new index, it didnot do away the FTS....because there was an outer join there!! So what do I do? How do I convey this to my manager that the full table scan on the large table can't be avoided - the query runs only once in midnight but it is taking 6 seconds and our SLA is two seconds!!

    Thanks
    OrauseN
    It probably would be a good idea to use your actual query for troubleshooting. Then, first of all, you should determine where the actual time is spent, by either tracing the statement execution, or by using DBMS_XPLAN.DISPLAY_CURSOR with the "ALLSTATS LAST" formatting option (requires to use the GATHER_PLAN_STATISTICS hint, or even better, by setting STATISTICS_LEVEL to ALL).

    If you happen to have Enterprise Edition + Diagnostic + Tuning Pack license, you could also use Real-Time SQL Monitoring for that purpose, which doesn't require any settings / hint in general. In case your query requires less than 5 seconds, you would need however to use the MONITOR hint to force the monitoring of the statement via Real-Time SQL Monitoring.

    Once you know where the time is spent, further actions can be discussed.

    So please post the corresponding information here - there is also this thread here:

    HOW TO: Post a SQL statement tuning request - template posting

    that gives some instructions how and what information to provide - it links to this blog post with more details: http://oracle-randolf.blogspot.de/2009/02/basic-sql-statement-performance.html

    Randolf
  • 11. Re: Avoiding full table scan on a large table in a query
    PavanKumar Guru
    Currently Being Moderated
    Hi,

    Your outer query is selective - that is your requirement.( think in reverse direction where hash table can be build in either -way)
    I think, Oracle optimizer is performing swapping_inputs during join order


    try hint no_swap_join_inputs(TEST_MSG_USER tmu)

    or

    SELECT rowid                         --- records which required
    FROM TEST_MSG_USER TMU
    WHERE TMU.ARCHIVED = 0 AND TMU.MESSAGE_STATUS = :SYS_B_1
    minus
    SELECT tca.row_id                    -- records which doesn't required
    FROM TEST_PROPERTIES tp, TEST_CLIENT_APP tca,TEST_MSG_USER tca
    WHERE tp.app_id = tca.app_id
    AND tp.property_name = :SYS_B_3
    AND tp.property_value = :SYS_B_4
    AND tca.CLNT_OID = TMU.CLNT_OID

    Not sure of result.... I just thinking the query results are correct or not... not sure.... still checking how to swap hash_join inputs

    - Pavan Kumar N
  • 12. Re: Avoiding full table scan on a large table in a query
    mtefft Journeyer
    Currently Being Moderated
    I urge you to revisit the SLA and make sure there was not a misunderstanding. 2 seconds sounds like an SLA for an interactive transaction. But you have a nightly batch process here, and a transaction-response-time SLA is not appropriate to apply for that.

    (An SLA for a batch job might be in the range of hours or minutes. 2 seconds for a once-a-day batch query makes no sense.)

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points