Hi All -
We've had some custom code in our ETL for a while (close to 2 years). It would typically finish in 10 minutes, or less. We partitioned a table back in late July and have noticed that the SQL now runs much longer. Often it runs for over an hour, sometimes over 2 hours.
Here is the SQL:
MERGE INTO w_gl_other_f t1 USING
(SELECT DISTINCT X_ATTRIBUTE1 ,
X_ATTRIBUTE2 ,
X_ATTRIBUTE3 ,
X_ATTRIBUTE4 ,
X_ATTRIBUTE5 ,
X_ATTRIBUTE6 ,
X_ATTRIBUTE7 ,
X_ATTRIBUTE8 ,
X_ATTRIBUTE9 ,
X_ATTRIBUTE10 ,
X_ATTRIBUTE11 ,
X_ATTRIBUTE12,
X_ATTRIBUTE13,
x_je_header_id,
journal_line_num
FROM
w_gl_other_fs tMP
) t2 ON (t1.x_je_header_id=t2.x_je_header_id AND t1.journal_line_num=t2.journal_line_num )
WHEN MATCHED THEN
UPDATE
SET T1.X_ATTRIBUTE1 = T2.X_ATTRIBUTE1,
T1.X_ATTRIBUTE2 =t2.X_ATTRIBUTE2,
T1.X_ATTRIBUTE3 =t2.X_ATTRIBUTE3,
T1.X_ATTRIBUTE4 =t2.X_ATTRIBUTE4,
T1.X_ATTRIBUTE5 =t2.X_ATTRIBUTE5,
T1.X_ATTRIBUTE6 =t2.X_ATTRIBUTE6,
T1.X_ATTRIBUTE7 =t2.X_ATTRIBUTE7,
T1.X_ATTRIBUTE8 =t2.X_ATTRIBUTE8,
T1.X_ATTRIBUTE9 =t2.X_ATTRIBUTE9,
T1.X_ATTRIBUTE10 =T2.X_ATTRIBUTE10,
T1.X_ATTRIBUTE11 =T2.X_ATTRIBUTE11,
T1.X_ATTRIBUTE12 =T2.X_ATTRIBUTE12,
T1.X_ATTRIBUTE13 =T2.X_ATTRIBUTE13
I did pull an execution plan and can see that it is going for a full table scan on the partitioned table, W_GL_OTHER_F. The other table involved in the query, W_GL_OTHER_FS is a staging table and is not partitioned. Unfortunately, I don't have any execution plans available for when this ran better (i.e. pre-partitioning). Also, I didn't get the actual row counts because I'd need to add the GATHER_PLAN_STATISTICS hint (or, alter session syntax) into the ETL load plan itself somehow (may involve recompiling some objects as well). This is possible, but I'll need more time to work on it.
I'm wondering if there may be a better way to write this query now that the table is partitioned? Or, anything else that may be possible to help with the performance.
SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_AWR(SQL_ID=>'9pw6xqkbqz7jt', FORMAT=>'ALL +OUTLINE'));
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 9pw6xqkbqz7jt
--------------------
MERGE INTO w_gl_other_f t1 USING (SELECT DISTINCT X_ATTRIBUTE1 ,
X_ATTRIBUTE2 , X_ATTRIBUTE3 , X_ATTRIBUTE4 , X_ATTRIBUTE5 ,
X_ATTRIBUTE6 , X_ATTRIBUTE7 , X_ATTRIBUTE8 , X_ATTRIBUTE9 ,
X_ATTRIBUTE10 , X_ATTRIBUTE11 , X_ATTRIBUTE12, X_ATTRIBUTE13,
x_je_header_id, journal_line_num FROM w_gl_other_fs tMP ) t2 ON
(t1.x_je_header_id=t2.x_je_header_id AND
t1.journal_line_num=t2.journal_line_num ) WHEN MATCHED THEN UPDATE
SET T1.X_ATTRIBUTE1 = T2.X_ATTRIBUTE1, T1.X_ATTRIBUTE2
=t2.X_ATTRIBUTE2, T1.X_ATTRIBUTE3 =t2.X_ATTRIBUTE3,
T1.X_ATTRIBUTE4 =t2.X_ATTRIBUTE4, T1.X_ATTRIBUTE5
=t2.X_ATTRIBUTE5, T1.X_ATTRIBUTE6 =t2.X_ATTRIBUTE6,
T1.X_ATTRIBUTE7 =t2.X_ATTRIBUTE7, T1.X_ATTRIBUTE8
=t2.X_ATTRIBUTE8, T1.X_ATTRIBUTE9 =t2.X_ATTRIBUTE9,
T1.X_ATTRIBUTE10 =T2.X_ATTRIBUTE10, T1.X_ATTRIBUTE11
=T2.X_ATTRIBUTE11, T1.X_ATTRIBUTE12 =T2.X_ATTRIBUTE12,
T1.X_ATTRIBUTE13 =T2.X_ATTRIBUTE13
Plan hash value: 129270570
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------
| 0 | MERGE STATEMENT | | | | | 105M(100)| | | |
| 1 | MERGE | W_GL_OTHER_F | | | | | | | |
| 2 | VIEW | | | | | | | | |
| 3 | HASH JOIN | | 645K| 1076M| 640M| 105M (1)| 01:08:56 | | |
| 4 | VIEW | | 645K| 632M| | 60086 (2)| 00:00:03 | | |
| 5 | SORT UNIQUE | | 645K| 23M| 44M| 60086 (2)| 00:00:03 | | |
| 6 | TABLE ACCESS FULL| W_GL_OTHER_FS | 645K| 23M| | 49914 (2)| 00:00:02 | | |
| 7 | PARTITION RANGE ALL| | 523M| 351G| | 42M (3)| 00:27:26 | 1 | 35 |
| 8 | TABLE ACCESS FULL | W_GL_OTHER_F | 523M| 351G| | 42M (3)| 00:27:26 | 1 | 35 |
----------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - MRG$1
3 - SEL$1
4 - SEL$2 / T2@SEL$1
5 - SEL$2
6 - SEL$2 / TMP@SEL$2
8 - SEL$1 / T1@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
DB_VERSION('12.1.0.2')
OPT_PARAM('_b_tree_bitmap_plans' 'false')
OPT_PARAM('star_transformation_enabled' 'true')
OPT_PARAM('_fix_control' '17376322:0')
ALL_ROWS
OUTLINE_LEAF(@"SEL$2")
OUTLINE_LEAF(@"SEL$1")
OUTLINE_LEAF(@"SEL$3")
OUTLINE_LEAF(@"MRG$1")
NO_ACCESS(@"MRG$1" "from$_subquery$_007"@"MRG$1")
NO_ACCESS(@"MRG$1" "T2"@"MRG$1")
FULL(@"MRG$1" "T1"@"MRG$1")
LEADING(@"MRG$1" "from$_subquery$_007"@"MRG$1" "T2"@"MRG$1" "T1"@"MRG$1")
USE_MERGE_CARTESIAN(@"MRG$1" "T2"@"MRG$1")
USE_MERGE_CARTESIAN(@"MRG$1" "T1"@"MRG$1")
NO_ACCESS(@"SEL$1" "T2"@"SEL$1")
FULL(@"SEL$1" "T1"@"SEL$1")
LEADING(@"SEL$1" "T2"@"SEL$1" "T1"@"SEL$1")
USE_HASH(@"SEL$1" "T1"@"SEL$1")
FULL(@"SEL$3" "TMP"@"SEL$3")
USE_HASH_AGGREGATION(@"SEL$3")
FULL(@"SEL$2" "TMP"@"SEL$2")
END_OUTLINE_DATA
*/
77 rows selected.
SQL> SELECT version FROM V$INSTANCE;
VERSION
-----------------
12.2.0.1.0
As always, thank in advance to those who contribute here!
DB version 12.2.0.1
Regards,
Charles