Expensive SQL

From: Mladen Gogala <gogala.mladen_at_gmail.com>
Date: Tue, 23 Aug 2011 12:47:41 +0000 (UTC)
Message-ID: <pan.2011.08.23.12.47.39_at_gmail.com>

The newly upgraded 11.2.0.2 database is behaving strangely. The most expensive SQL, with respect to CPU consumption, is the following:

MERGE INTO sqlobj$auxdata USING dual ON (:1 IS NULL) WHEN MATCHED THEN UPDATE SET description = :2, creator = nvl(:3, creator), origin = :4, version = :5, created = :6, last_modified = :7, last_verified = nvl(:8, last_verified), parse_cpu_time = null, optimizer_cost = nvl(:9, optimizer_cost), module = nvl(:10, module), action = nvl(:11, action), priority = nvl(:12, priority), optimizer_env = nvl(:13, optimizer_env), bind_data = nvl(:14, bind_data), parsing_schema_name = nvl(:15, parsing_schema_name), executions = nvl(:16, executions), elapsed_time = nvl(:17, elapsed_time), cpu_time = nvl(:18, cpu_time), buffer_gets = nvl
(:19, buffer_gets), disk_reads = nvl(:20, disk_reads), direct_writes = nvl
(:21, direct_writes), rows_processed = nvl(:22, rows_processed), fetches
= nvl(:23, fetches), end_of_fetch_count = nvl(:24, end_of_fetch_count),

task_id = nvl(:25, task_id), task_exec_name = nvl(:26, task_exec_name), 
task_obj_id = nvl(:27, task_obj_id), task_fnd_id = nvl(:28, task_fnd_id), 
task_rec_id = nvl(:29, task_rec_id), flags = 0, spare1 = null, spare2 = 

null WHERE signature = :30 AND category = :31 AND obj_type = :32 AND plan_id = :33 WHEN NOT MATCHED THEN INSERT (signature, category, obj_type, plan_id, description, creator, origin, version, created, last_modified, last_verified, parse_cpu_time, optimizer_cost, module, action, priority, optimizer_env, bind_data, parsing_schema_name, executions, elapsed_time, cpu_time, buffer_gets, disk_reads, direct_writes, rows_processed, fetches,end_of_fetch_count, task_id, task_exec_name, task_obj_id, task_fnd_id, task_rec_id, flags, spare1, spare2) VALUES (:34, :35, :36, :37, :38, :39, :40, :41, :42, :43, null, null, :44, :45, :46, :47, :48, :49, :50, :51, :52, :53, :54, :55, :56, :57, :58, :59, :60, :61, :62, :63, :64, 0, null, null)

I am not capturing baselines, so I have no idea where is this coming from: SQL> show parameter baseline

NAME TYPE VALUE

------------------------------------ ----------- 
------------------------------
optimizer_capture_sql_plan_baselines boolean	 FALSE
optimizer_use_sql_plan_baselines     boolean	 TRUE

SQL> The next most expensive SQL alos belongs to the same strange type:

MERGE INTO sqlobj$ USING dual ON (:1 IS NULL) WHEN MATCHED THEN UPDATE SET name = :2, flags = :3, last_executed = :4 WHERE signature = :5 AND category = :6 AND obj_type = :7 AND plan_id = :8 WHEN NOT MATCHED THEN INSERT (signature, category, obj_type, plan_id, name, flags, last_executed) VALUES (:9, :10, :11, :12, :13, :14, :15)

Where are these expensive monsters coming from and is there any way to make them cheaper? Between the two of them, they are using more CPU than the next 7 of the application SQL statements combined.

-- 
http://mgogala.byethost5.com

Received on Tue Aug 23 2011 - 07:47:41 CDT