Hello
In a third-party software there is an overnight job that insert 500K of rows using something like this
declare
l_clob clob;
l_n1 number;
l_v1 varchar2(10);
etc..
begin
-- fill above bind variables including the CLOB
insert into t1 values (:l_clob, l_n1, l_v1,…..);
end;
When this overnight batch is launched, we have found that
· 98% is spent burning CPU using a NULL SQL_ID
· 2% the above insert
Tracing the session launching the batch shows that 98% of time is due to "unaccounted for". The trace file shows however several lines of this type
LOBARRTMPFRE
LOBTMPCREATE
LOBWRITE
LOBTRIM
And the following query applied during the batch period shows
SQL> SELECT
2 a.top_level_call#
3 , a.top_level_call_name
4 , a.top_level_sql_opcode
5 , s.command_name
6 , COUNT(*)
7 FROM
8 v$active_session_history a
9 , v$sqlcommand s
10 WHERE
11 a.top_level_sql_opcode = s.command_type
12 GROUP BY
13 a.top_level_call#
14 , a.top_level_call_name
15 , a.top_level_sql_opcode
16 , s.command_name
17 ORDER BY
18* COUNT(*) DESC
19 /
90% on LOB/FILE operations
A last remark is that the same performance symptoms appear when we've changed the datatype of the column in t1 table from CLOB to varchar2 while keeping the CLOB bind variable as is (we can't change the code of the third party software)
Do you think that this NULL SQL_ID corresponds to the different LOB operations we’ve observed into the trace file?
And if yes, do they correspond to the client part in which the clob bind variables are filled?
Thanks