Home » RDBMS Server » Performance Tuning » Full table scan - Queries (11g, 11.2.0.3, solaris10)
Full table scan - Queries [message #613355] Mon, 05 May 2014 07:51 Go to next message
ashishkumarmahanta80
Messages: 231
Registered: October 2006
Location: kolkatta
Senior Member
Dear All,

One query is executed in two different servers with same set up but while executing the query. One database is executed with TABLE ACCESS BY INDEX ROWID and other is with TABLE ACCESS FULL.

Both of them is having same date (today's) analyzed.
Both of them having same indexes.
Plan is re-created in both the databases.

Even after doing the same, query is executing with full table scan in one of the databases.

Below is the query
SELECT *
  FROM (  SELECT  A.TXT_WORKFLOW_GUID,
                 CASE
                    WHEN TXT_NOTIF_TO_GRP = :B1
                    THEN
                       CASE
                          WHEN TXT_NOTIFI_STATUS = 'PENDING' THEN 'IN'
                          ELSE 'OUT'
                       END
                    ELSE
                       CASE
                          WHEN TXT_NOTIFI_STATUS = 'PENDING' THEN 'OUT'
                          ELSE 'IN'
                       END
                 END
                    NOT_TYPE,
                 TXT_NOTIF_FROM_GRP,
                 TXT_NOTIF_TO_GRP,
                 TXT_NOTIFI_COMMENT,
                 (SELECT DIRCAPTION
                    FROM CONFSYS.CNFGTR_DIR_MSTR C
                   WHERE C.DIRINDX = A.TXTLOB_ID)
                    DIRCAPTION,
                 (SELECT TXT_PROCESS_NAME
                    FROM CONFSYS.WORKFLOW_PROCESS_MASTER B
                   WHERE B.TXT_PROCESS_ID = A.TXT_PROCESS_ID)
                    TXT_PROCESS_NAME,
                 (SELECT TXT_STATE_DISPLAY_NAME
                    FROM CONFSYS.WORKFLOW_STATE_MASTER D
                   WHERE D.TXT_STATE_ID = A.TXT_STATE_ID)
                    TXT_STATE_DISPLAY_NAME,
                 TXT_NOTIFI_STATUS,
                 TXT_WORKFLOW_CAPTION,
                 TO_DATE (DT_NOTIFICATION_DATE, 'DD/MM/YYYY') NOTIFICATION_DATE,
                 TXT_OFFICE,
                 (SELECT TXT_WORKFLOW_COMPLETE
                    FROM INS.WORKFLOW_TRANSACTION
                   WHERE TXT_WF_TRANS_ID = A.TXT_WORKFLOW_GUID
                         AND TXT_WORKFLOW_COMPLETE <> 1)
                    TXT_WORKFLOW_COMPLETE
            FROM INS.WORKFLOW_NOTIFICATION_TRANS A
           WHERE (TXT_NOTIF_FROM_GRP = :B1 OR TXT_NOTIF_TO_GRP = :B1)
                 AND DT_NOTIFICATION_DATE IS NOT NULL
        ORDER BY A.DT_NOTIFICATION_DATE DESC, A.TXT_WORKFLOW_GUID DESC)
 WHERE ROWNUM <= :B2;


results from both the databases-

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

SYS@lvgidev_30_22>set lines 125 pages 200
SYS@lvgidev_30_22>explain plan for
  2  SELECT *
  3    FROM (  SELECT  A.TXT_WORKFLOW_GUID,
  4                   CASE
  5                      WHEN TXT_NOTIF_TO_GRP = :B1
  6                      THEN
  7                         CASE
  8                            WHEN TXT_NOTIFI_STATUS = 'PENDING' THEN 'IN'
  9                            ELSE 'OUT'
 10                         END
 11                      ELSE
 12                         CASE
 13                            WHEN TXT_NOTIFI_STATUS = 'PENDING' THEN 'OUT'
 14                            ELSE 'IN'
 15                         END
 16                   END
 17                      NOT_TYPE,
 18                   TXT_NOTIF_FROM_GRP,
 19                   TXT_NOTIF_TO_GRP,
 20                   TXT_NOTIFI_COMMENT,
 21                   (SELECT DIRCAPTION
 22                      FROM CONFSYS.CNFGTR_DIR_MSTR C
 23                     WHERE C.DIRINDX = A.TXTLOB_ID)
 24                      DIRCAPTION,
 25                   (SELECT TXT_PROCESS_NAME
 26                      FROM CONFSYS.WORKFLOW_PROCESS_MASTER B
 27                     WHERE B.TXT_PROCESS_ID = A.TXT_PROCESS_ID)
 28                      TXT_PROCESS_NAME,
 29                   (SELECT TXT_STATE_DISPLAY_NAME
 30                      FROM CONFSYS.WORKFLOW_STATE_MASTER D
 31                     WHERE D.TXT_STATE_ID = A.TXT_STATE_ID)
 32                      TXT_STATE_DISPLAY_NAME,
 33                   TXT_NOTIFI_STATUS,
 34                   TXT_WORKFLOW_CAPTION,
 35                   TO_DATE (DT_NOTIFICATION_DATE, 'DD/MM/YYYY') NOTIFICATION_DATE,
 36                   TXT_OFFICE,
 37                   (SELECT TXT_WORKFLOW_COMPLETE
 38                      FROM INS.WORKFLOW_TRANSACTION
 39                     WHERE TXT_WF_TRANS_ID = A.TXT_WORKFLOW_GUID
 40                           AND TXT_WORKFLOW_COMPLETE <> 1)
 41                      TXT_WORKFLOW_COMPLETE
 42              FROM INS.WORKFLOW_NOTIFICATION_TRANS A
 43             WHERE (TXT_NOTIF_FROM_GRP = :B1 OR TXT_NOTIF_TO_GRP = :B1)
 44                   AND DT_NOTIFICATION_DATE IS NOT NULL
 45          ORDER BY A.DT_NOTIFICATION_DATE DESC, A.TXT_WORKFLOW_GUID DESC)
 46   WHERE ROWNUM <= :B2;

Explained.

SYS@lvgidev_30_22>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------
Plan hash value: 3257347968

---------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                             |     9 | 25164 |    12   (9)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID    | CNFGTR_DIR_MSTR             |     1 |    24 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN             | PK_DIR_MSTR                 |     1 |       |     0   (0)| 00:00:01 |
|*  3 |  TABLE ACCESS FULL              | WORKFLOW_PROCESS_MASTER     |     1 |    31 |     4   (0)| 00:00:01 |
|   4 |  TABLE ACCESS BY INDEX ROWID    | WORKFLOW_STATE_MASTER       |     1 |    14 |     2   (0)| 00:00:01 |
|*  5 |   INDEX RANGE SCAN              | IDX_WORKFLOW_STATE_MASTER   |     1 |       |     1   (0)| 00:00:01 |
|*  6 |  TABLE ACCESS BY INDEX ROWID    | WORKFLOW_TRANSACTION        |     1 |    17 |     2   (0)| 00:00:01 |
|*  7 |   INDEX RANGE SCAN              | IDX_TXT_WF_TRANS_ID         |     1 |       |     1   (0)| 00:00:01 |
|*  8 |  COUNT STOPKEY                  |                             |       |       |            |       |
|   9 |   VIEW                          |                             |     9 | 25164 |    12   (9)| 00:00:01 |
|* 10 |    SORT ORDER BY STOPKEY        |                             |     9 |   891 |    12   (9)| 00:00:01 |
|  11 |     CONCATENATION               |                             |       |       |            |       |
|* 12 |      TABLE ACCESS BY INDEX ROWID| WORKFLOW_NOTIFICATION_TRANS |     4 |   396 |     5   (0)| 00:00:01 |
|* 13 |       INDEX RANGE SCAN          | IDX_TXT_NOTIF_TO_GRP        |     4 |       |     1   (0)| 00:00:01 |
|* 14 |      TABLE ACCESS BY INDEX ROWID| WORKFLOW_NOTIFICATION_TRANS |     5 |   495 |     6   (0)| 00:00:01 |
|* 15 |       INDEX RANGE SCAN          | IDX_TXT_NOTIF_FROM_GRP      |     6 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------

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

   2 - access("C"."DIRINDX"=:B1)
   3 - filter("B"."TXT_PROCESS_ID"=:B1)
   5 - access("D"."TXT_STATE_ID"=:B1)
   6 - filter(TO_NUMBER("TXT_WORKFLOW_COMPLETE")<>1)
   7 - access("TXT_WF_TRANS_ID"=:B1)
   8 - filter(ROWNUM<=TO_NUMBER(:B2))
  10 - filter(ROWNUM<=TO_NUMBER(:B2))
  12 - filter("DT_NOTIFICATION_DATE" IS NOT NULL)
  13 - access("TXT_NOTIF_TO_GRP"=:B1)
  14 - filter("DT_NOTIFICATION_DATE" IS NOT NULL AND LNNVL("TXT_NOTIF_TO_GRP"=:B1))
  15 - access("TXT_NOTIF_FROM_GRP"=:B1)

37 rows selected.

SYS@lvgidev_30_22>exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

C:\Users\Lenovo>sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Mon May 5 18:15:56 2014

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

Enter user-name: sys@lvgiuat_30_27 as sysdba
Enter password:

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

SYS@lvgiuat_30_27>set pages 200 lines 125
SYS@lvgiuat_30_27>explain plan for
  2  SELECT *
  3    FROM (  SELECT  A.TXT_WORKFLOW_GUID,
  4                   CASE
  5                      WHEN TXT_NOTIF_TO_GRP = :B1
  6                      THEN
  7                         CASE
  8                            WHEN TXT_NOTIFI_STATUS = 'PENDING' THEN 'IN'
  9                            ELSE 'OUT'
 10                         END
 11                      ELSE
 12                         CASE
 13                            WHEN TXT_NOTIFI_STATUS = 'PENDING' THEN 'OUT'
 14                            ELSE 'IN'
 15                         END
 16                   END
 17                      NOT_TYPE,
 18                   TXT_NOTIF_FROM_GRP,
 19                   TXT_NOTIF_TO_GRP,
 20                   TXT_NOTIFI_COMMENT,
 21                   (SELECT DIRCAPTION
 22                      FROM CONFSYS.CNFGTR_DIR_MSTR C
 23                     WHERE C.DIRINDX = A.TXTLOB_ID)
 24                      DIRCAPTION,
 25                   (SELECT TXT_PROCESS_NAME
 26                      FROM CONFSYS.WORKFLOW_PROCESS_MASTER B
 27                     WHERE B.TXT_PROCESS_ID = A.TXT_PROCESS_ID)
 28                      TXT_PROCESS_NAME,
 29                   (SELECT TXT_STATE_DISPLAY_NAME
 30                      FROM CONFSYS.WORKFLOW_STATE_MASTER D
 31                     WHERE D.TXT_STATE_ID = A.TXT_STATE_ID)
 32                      TXT_STATE_DISPLAY_NAME,
 33                   TXT_NOTIFI_STATUS,
 34                   TXT_WORKFLOW_CAPTION,
 35                   TO_DATE (DT_NOTIFICATION_DATE, 'DD/MM/YYYY') NOTIFICATION_DATE,
 36                   TXT_OFFICE,
 37                   (SELECT TXT_WORKFLOW_COMPLETE
 38                      FROM INS.WORKFLOW_TRANSACTION
 39                     WHERE TXT_WF_TRANS_ID = A.TXT_WORKFLOW_GUID
 40                           AND TXT_WORKFLOW_COMPLETE <> 1)
 41                      TXT_WORKFLOW_COMPLETE
 42              FROM INS.WORKFLOW_NOTIFICATION_TRANS A
 43             WHERE (TXT_NOTIF_FROM_GRP = :B1 OR TXT_NOTIF_TO_GRP = :B1)
 44                   AND DT_NOTIFICATION_DATE IS NOT NULL
 45          ORDER BY A.DT_NOTIFICATION_DATE DESC, A.TXT_WORKFLOW_GUID DESC)
 46   WHERE ROWNUM <= :B2;

Explained.

SYS@lvgiuat_30_27>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------
Plan hash value: 445935749

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                             |   124 |   338K|    20   (5)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| CNFGTR_DIR_MSTR             |     1 |    24 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | PK_DIR_MSTR                 |     1 |       |     0   (0)| 00:00:01 |
|*  3 |  TABLE ACCESS FULL          | WORKFLOW_PROCESS_MASTER     |     1 |    31 |     3   (0)| 00:00:01 |
|   4 |  TABLE ACCESS BY INDEX ROWID| WORKFLOW_STATE_MASTER       |     1 |    14 |     2   (0)| 00:00:01 |
|*  5 |   INDEX RANGE SCAN          | IDX_WORKFLOW_STATE_MASTER   |     1 |       |     1   (0)| 00:00:01 |
|*  6 |  TABLE ACCESS BY INDEX ROWID| WORKFLOW_TRANSACTION        |     1 |    19 |     2   (0)| 00:00:01 |
|*  7 |   INDEX RANGE SCAN          | IDX_TXT_WF_TRANS_ID         |     1 |       |     1   (0)| 00:00:01 |
|*  8 |  COUNT STOPKEY              |                             |       |       |            |       |
|   9 |   VIEW                      |                             |   124 |   338K|    20   (5)| 00:00:01 |
|* 10 |    SORT ORDER BY STOPKEY    |                             |   124 | 13144 |    20   (5)| 00:00:01 |
|* 11 |     TABLE ACCESS FULL       | WORKFLOW_NOTIFICATION_TRANS |   124 | 13144 |    19   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------

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

   2 - access("C"."DIRINDX"=:B1)
   3 - filter("B"."TXT_PROCESS_ID"=:B1)
   5 - access("D"."TXT_STATE_ID"=:B1)
   6 - filter(TO_NUMBER("TXT_WORKFLOW_COMPLETE")<>1)
   7 - access("TXT_WF_TRANS_ID"=:B1)
   8 - filter(ROWNUM<=TO_NUMBER(:B2))
  10 - filter(ROWNUM<=TO_NUMBER(:B2))
  11 - filter(("TXT_NOTIF_FROM_GRP"=:B1 OR "TXT_NOTIF_TO_GRP"=:B1) AND "DT_NOTIFICATION_DATE" IS
              NOT NULL)

31 rows selected.

SYS@lvgiuat_30_27>



last_analyzed is same for both the databases -
SYS@lvgiuat_30_27>select table_name, last_analyzed from dba_tables
2 where table_name in ('CNFGTR_DIR_MSTR','WORKFLOW_PROCESS_MASTER','WORKFLOW_STATE_MASTER','WORKFLOW_TRANSACTION','WORKFLOW_NOTIFICATION_TRANS');

TABLE_NAME LAST_ANAL
------------------------------ ---------
WORKFLOW_NOTIFICATION_TRANS 05-MAY-14
WORKFLOW_TRANSACTION 05-MAY-14
WORKFLOW_STATE_MASTER 05-MAY-14
WORKFLOW_PROCESS_MASTER 05-MAY-14
CNFGTR_DIR_MSTR 05-MAY-14

[/code]

Need your valuable suggestion on the same... Unable to find out the root cause of it.

Regards,
Ashish Kumar Mahanta
Re: Full table scan - Queries [message #613357 is a reply to message #613355] Mon, 05 May 2014 08:28 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
Is it actually running slowly?

Those numbers look sufficiently low that it might just not care how it accesses such a small table.
Re: Full table scan - Queries [message #613358 is a reply to message #613357] Mon, 05 May 2014 08:49 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Do you suffer from Compulsive Tuning Disorder?
With both SQL completing in 1 seconds why are you wasting your & our time on this?
Re: Full table scan - Queries [message #613366 is a reply to message #613358] Mon, 05 May 2014 19:54 Go to previous messageGo to next message
ashishkumarmahanta80
Messages: 231
Registered: October 2006
Location: kolkatta
Senior Member
Dear BlackSwan,

I am executing it in our local databases where records are low. My worries is at production database. Here, in both the databases, records are almost same but showing different behavior. I just want to know why it is behaving like strange?

I have to provide explanation on this. That's why i need your assistance on it.

Regards,
Ashish Kumar Mahanta
Re: Full table scan - Queries [message #613367 is a reply to message #613366] Mon, 05 May 2014 20:25 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
The data is different between the 2 databases.
GOOGLE " Oracle bind variable peaking"
Re: Full table scan - Queries [message #613370 is a reply to message #613367] Mon, 05 May 2014 23:39 Go to previous messageGo to next message
ashishkumarmahanta80
Messages: 231
Registered: October 2006
Location: kolkatta
Senior Member
Dear BlackSwan,

You are correct. Data are different in both the databases.

select count(*) FROM INS.WORKFLOW_NOTIFICATION_TRANS >> 412 records
# which is executed with TABLE ACCESS FULL.

select count(*) FROM INS.WORKFLOW_NOTIFICATION_TRANS >> 23 records
# which is executed with TABLE ACCESS BY INDEX ROWID.

Why table access full is appearing on 412 records? Due to this cost is higher that the TABLE ACCESS BY INDEX ROWID.

Request you to clarify my doubt. I will also check "Oracle bind variable peaking".

Regards,
Ashish Kumar Mahanta
Re: Full table scan - Queries [message #613385 is a reply to message #613355] Tue, 06 May 2014 04:03 Go to previous message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
ashishkumarmahanta80 wrote on Mon, 05 May 2014 18:21

Enter user-name: sys@lvgiuat_30_27 as sysdba


How could you be sure about the results and statistics when you are logging in as "sysdba"? Behaviour might change and would be different from what you expect since it is for maintainance purpose and not for regular DB tasks.

Read this SYSDBA is special

Edit : Provided link to read about why sysdba is special and not for regular DB tasks.

[Updated on: Tue, 06 May 2014 04:04]

Report message to a moderator

Previous Topic: After updating memory_target, performance is degraded
Next Topic: slow sql
Goto Forum:
  


Current Time: Thu Mar 28 23:57:30 CDT 2024