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  |
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 #613366 is a reply to message #613358] |
Mon, 05 May 2014 19:54  |
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 #613370 is a reply to message #613367] |
Mon, 05 May 2014 23:39 |
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 |
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 |
|
|
|
Goto Forum:
Current Time: Sat Nov 23 06:27:28 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
