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

It might be a side effect of adaptive cursor sharing. When an query is
re-optimized to cater for variations in bind variable inputs I think it
will store the resulting plans and bind values. Do you see any statements
with a significant number of child cursors ? There is a parameter setting
to disable the feature.


--
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com


"Mladen Gogala" <gogala.mladen (AT) gmail (DOT) com> wrote

It's a bug - # 11719151

See http://orastory.wordpress.com/2011/0...ne-capture-bu/

Patch.

In the interim, you can disable baseline usage and/or delete existing
baselines (dba_sql_plan_baselines & dbms_spm).

BTW whilst that bug description mentions baseline capture as the area
of the bug (or it did when I had cause to have it patched), that's
only part of it.
It's very much baseline usage that is affected as well.

What that bug does illustrate is some of the recursive work involved
with baselines even with capture off and usage on.

So whilst the patch fixes the original issues of the full table scans
against the sqlobj$auxdata and sqlobj$ objects, using INDEX hints by
the look of it,
just the execution of a baselined sql statement involves a couple of
look ups against these tables and merge operations to update all the
associated metadata.

On Tue, 23 Aug 2011 08:07:54 -0700, dombrooks wrote:

That despite not capturing baselines?



--
http://mgogala.byethost5.com

Yep - I did just post some additional information but it's not
appeared yet.

Although the headline description of the bug mentioned baseline
capture, it's very much baseline usage that is also the problem.

I assume you must have some baselined statements in
dba_sql_plan_baselines.

The performance bug concerns full table scans on recursive merges into
sqlobj$, sqlobj$data and sqlobj$auxdata.

When you execute a baselined statement (even if capture is off), you
get at least one lookup on SQLOBJ$ and SQLOBJ$AUXDATA plus a MERGE
into the same objects to update the baseline metadata on those
statements.

Post patch for 11715191 you still get these statements, but they've
been hinted with an INDEX hint

Cheers.
Dominic

On Tue, 23 Aug 2011 08:53:14 -0700, dombrooks wrote:

I've turned off using the plan baselines altogether. I am also running
the following:

SQL> declare
2 cursor spm is select sql_handle from dba_sql_plan_baselines;
3 stat number;
4 begin
5 for c in spm loop
6 stat:=dbms_spm.drop_sql_plan_baseline(c.sql_handle ,null);
7 end loop;
8 commit;
9 end;
10 /

Hopefully, that will clean things up.



--
http://mgogala.byethost5.com

On Tue, 23 Aug 2011 09:02:46 -0700, dombrooks wrote:

That patch will probably make it into the October PSU. I am reluctant to
apply one-off patches. I will rather turn off baselines altogether.



--
http://mgogala.byethost5.com

On Tue, 23 Aug 2011 16:20:52 +0000, Mladen Gogala wrote:

This is running exceptionally slowly.



--
http://mgogala.byethost5.com

On Wed, 24 Aug 2011 15:41:49 +0000, Mladen Gogala wrote:


Is there anything to bulk delete all collected baselines?



--
http://mgogala.byethost5.com