Different Database Behavior for UAT and PRO in same DB
627071Mar 10 2008 — edited Mar 10 2008Hi All,
The UAT and Production Documentum schemas reside in the same database instance referring to different applications.
Problem is when I run the same query to production it send me result in a second but in UAT it takes for ever and also its shows the different plan if do explain in UAT and PRO.
OS windows 64Bit
DB 10.2.0.3
There is frequent, automated data load.
The indexes are rebuilt every other day.
------------------------------------------------------------------------
SQL on UAT
--------------------
select all dm_folder.r_object_id, dm_folder.object_name, dm_repeating.i_folder_id, dm_repeating.r_folder_path from dm_folder_sp dm_folder, dm_folder_rp dm_repeating where (dm_folder.r_object_id in (select all dm_repeating.i_ancestor_id from dm_folder_sp dm_folder, dm_folder_rp dm_repeating where ((dm_folder.r_object_id='0b01b3f18000b897') and (dm_folder.a_is_hidden=0)) and (dm_folder.i_has_folder = 1 and dm_folder.i_is_deleted = 0) and dm_repeating.r_object_id=dm_folder.r_object_id )) and (dm_folder.i_has_folder = 1 and dm_folder.i_is_deleted = 0) and dm_repeating.r_object_id=dm_folder.r_object_id order by dm_folder.r_object_id
Explain Plan on UAT
This sql ran forever. An execution plan was gotten for the SQL. Note the MERGE JOIN CARTESIAN with 404 million rows and 34 GB of data highlighted in red.
Execution Plan
----------------------------------------------------------
Plan hash value: 1131176958
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 132 | 21M (1)| 70:20:41 |
| 1 | NESTED LOOPS SEMI | | 1 | 132 | 21M (1)| 70:20:41 |
| 2 | NESTED LOOPS | | 15087 | 1723K| 21M (1)| 70:08:37 |
| 3 | MERGE JOIN CARTESIAN | | 404M| 34G| 802K (1)| 02:40:32 |
| 4 | NESTED LOOPS | | 9854 | 606K| 382 (1)| 00:00:05 |
|* 5 | TABLE ACCESS BY INDEX ROWID | DM_SYSOBJECT_S | 22358 | 1026K| 381 (1)| 00:00:05 |
| 6 | INDEX FULL SCAN | D_1F01B3F180000109 | 26813 | | 5 (0)| 00:00:01 |
|* 7 | INDEX UNIQUE SCAN | D_1F01B3F180000143 | 1 | 16 | 1 (0)| 00:00:01 |
| 8 | BUFFER SORT | | 41065 | 1162K| 802K (1)| 02:40:32 |
| 9 | TABLE ACCESS FULL | DM_FOLDER_R | 41065 | 1162K| 81 (0)| 00:00:01 |
| 10 | TABLE ACCESS BY INDEX ROWID | DM_SYSOBJECT_R | 1 | 25 | 1 (0)| 00:00:01 |
|* 11 | INDEX UNIQUE SCAN | D_1F01B3F18000010A | 1 | | 1 (0)| 00:00:01 |
|* 12 | VIEW | VW_NSO_1 | 1 | 15 | 4 (0)| 00:00:01 |
| 13 | NESTED LOOPS | | 2 | 190 | 4 (0)| 00:00:01 |
| 14 | NESTED LOOPS | | 4 | 304 | 3 (0)| 00:00:01 |
| 15 | NESTED LOOPS | | 1 | 42 | 2 (0)| 00:00:01 |
|* 16 | INDEX UNIQUE SCAN | D_1F01B3F180000143 | 1 | 16 | 1 (0)| 00:00:01 |
|* 17 | TABLE ACCESS BY INDEX ROWID| DM_SYSOBJECT_S | 1 | 26 | 1 (0)| 00:00:01 |
|* 18 | INDEX UNIQUE SCAN | D_1F01B3F180000109 | 1 | | 1 (0)| 00:00:01 |
| 19 | TABLE ACCESS BY INDEX ROWID | DM_FOLDER_R | 4 | 136 | 1 (0)| 00:00:01 |
|* 20 | INDEX RANGE SCAN | DM_FOLDER_R_IND_UT | 4 | | 1 (0)| 00:00:01 |
|* 21 | INDEX UNIQUE SCAN | D_1F01B3F18000010A | 1 | 19 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - filter("YB_"."I_HAS_FOLDER"=1 AND "YB_"."I_IS_DELETED"=0)
7 - access("YB_"."R_OBJECT_ID"="OG_"."R_OBJECT_ID")
11 - access("ZB_"."R_OBJECT_ID"="PG_"."R_OBJECT_ID" AND
"ZB_"."I_POSITION"="PG_"."I_POSITION")
filter("ZB_"."R_OBJECT_ID"="YB_"."R_OBJECT_ID")
12 - filter("YB_"."R_OBJECT_ID"="$nso_col_1")
16 - access("OG_"."R_OBJECT_ID"='0b01b3f18000b897')
17 - filter("YB_"."I_HAS_FOLDER"=1 AND "YB_"."I_IS_DELETED"=0 AND "YB_"."A_IS_HIDDEN"=0)
18 - access("YB_"."R_OBJECT_ID"='0b01b3f18000b897')
20 - access("PG_"."R_OBJECT_ID"='0b01b3f18000b897')
21 - access("ZB_"."R_OBJECT_ID"='0b01b3f18000b897' AND "ZB_"."I_POSITION"="PG_"."I_POSITION")
SQL on Production
The following sql was run through sqlplus on production. It is identical to the one run on UAT with the exception that a different r_object_id is used.
select all dm_folder.r_object_id, dm_folder.object_name, dm_repeating.i_folder_id, dm_repeating.r_folder_path from dm_folder_sp dm_folder, dm_folder_rp dm_repeating where (dm_folder.r_object_id in (select all dm_repeating.i_ancestor_id from dm_folder_sp dm_folder, dm_folder_rp dm_repeating where ((dm_folder.r_object_id='0b01b3f080011726') and (dm_folder.a_is_hidden=0)) and (dm_folder.i_has_folder = 1 and dm_folder.i_is_deleted = 0) and dm_repeating.r_object_id=dm_folder.r_object_id )) and (dm_folder.i_has_folder = 1 and dm_folder.i_is_deleted = 0) and dm_repeating.r_object_id=dm_folder.r_object_id order by dm_folder.r_object_id
Explain Plan on Production
This query ran very quickly. The query plan does not contain a MERGE JOIN CARTESIAN.
Execution Plan
----------------------------------------------------------
Plan hash value: 3553258581
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 650 | 10 (20)| 00:00:01 |
| 1 | SORT ORDER BY | | 5 | 650 | 10 (20)| 00:00:01 |
| 2 | NESTED LOOPS | | 5 | 650 | 9 (12)| 00:00:01 |
| 3 | NESTED LOOPS | | 9 | 936 | 8 (13)| 00:00:01 |
| 4 | NESTED LOOPS | | 3 | 234 | 7 (15)| 00:00:01 |
| 5 | NESTED LOOPS | | 3 | 186 | 6 (17)| 00:00:01 |
| 6 | VIEW | VW_NSO_1 | 3 | 48 | 4 (0)| 00:00:01 |
| 7 | HASH UNIQUE | | 3 | 279 | | |
| 8 | NESTED LOOPS | | 3 | 279 | 4 (0)| 00:00:01 |
| 9 | NESTED LOOPS | | 5 | 365 | 3 (0)| 00:00:01 |
| 10 | NESTED LOOPS | | 1 | 38 | 2 (0)| 00:00:01 |
|* 11| INDEX UNIQUE SCAN | D_1F01B3F080000143 | 1 | 16 | 1 (0)| 00:00:01 |
|* 12| TABLE ACCESS BY INDEX ROWID| DM_SYSOBJECT_S | 1 | 22 | 1 (0)| 00:00:01 |
|* 13| INDEX UNIQUE SCAN | D_1F01B3F080000109 | 1 | | 1 (0)| 00:00:01 |
| 14| TABLE ACCESS BY INDEX ROWID | DM_FOLDER_R | 5 | 175 | 1 (0)| 00:00:01 |
|* 15| INDEX RANGE SCAN | DM_FOLDER_R_IND | 5 | | 1 (0)| 00:00:01 |
|* 16| INDEX UNIQUE SCAN | D_1F01B3F08000010A | 1 | 20 | 1 (0)| 00:00:01 |
|* 17| TABLE ACCESS BY INDEX ROWID | DM_SYSOBJECT_S | 1 | 46 | 1 (0)| 00:00:01 |
|* 18| INDEX UNIQUE SCAN | D_1F01B3F080000109 | 1 | | 1 (0)| 00:00:01 |
|* 19| INDEX UNIQUE SCAN | D_1F01B3F080000143 | 1 | 16 | 1 (0)| 00:00:01 |
| 20| TABLE ACCESS BY INDEX ROWID | DM_SYSOBJECT_R | 3 | 78 | 1 (0)| 00:00:01 |
|* 21| INDEX RANGE SCAN | D_1F01B3F08000010A | 3 | | 1 (0)| 00:00:01 |
| 22| TABLE ACCESS BY INDEX ROWID | DM_FOLDER_R | 1 | 26 | 1 (0)| 00:00:01 |
|* 23| INDEX UNIQUE SCAN | D_1F01B3F080000144 | 1 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
11 - access("TGB_"."R_OBJECT_ID"='0b01b3f080011726')
12 - filter("GJ_"."A_IS_HIDDEN"=0 AND "GJ_"."I_HAS_FOLDER"=1 AND "GJ_"."I_IS_DELETED"=0)
13 - access("GJ_"."R_OBJECT_ID"='0b01b3f080011726')
15 - access("VGB_"."R_OBJECT_ID"='0b01b3f080011726')
16 - access("IJ_"."R_OBJECT_ID"='0b01b3f080011726' AND "IJ_"."I_POSITION"="VGB_"."I_POSITION")
17 - filter("GJ_"."I_HAS_FOLDER"=1 AND "GJ_"."I_IS_DELETED"=0)
18 - access("GJ_"."R_OBJECT_ID"="$nso_col_1")
19 - access("GJ_"."R_OBJECT_ID"="TGB_"."R_OBJECT_ID")
21 - access("IJ_"."R_OBJECT_ID"="GJ_"."R_OBJECT_ID")
23 - access("IJ_"."R_OBJECT_ID"="VGB_"."R_OBJECT_ID" AND "IJ_"."I_POSITION"="VGB_"."I_POSITION")
Message was edited by:
user624068