RE: optimizer_secure_view_merging

  • To: "Christian Antognini" <Christian.Antognini@xxxxxxxxxxxx>
  • Date: Tue, 19 Dec 2006 10:57:50 -0600

Hey Chris,

The problem is one of performance not functionality. One of the problems
that we are having is that when we commit, the recursive calls (oracle's
generated DML to populate the MVs) always have the following
characteristics (we are not using query rewrite):
1) CPU on the execute step is the largest expense
2) These statements always miss the library cache on the parse step
3) These statements always miss the library cache on the execute step

OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current
rows
------- ------  -------- ---------- ---------- ---------- ----------
----------
Parse     4190      1.46       1.54          0        396          0
0
Execute   5978      9.11       9.17          7      13663       8049
1412
Fetch     5558      0.96       1.36         24      13551         46
7725
------- ------  -------- ---------- ---------- ---------- ----------
----------
total    15726     11.53      12.08         31      27610       8095
9137

Misses in library cache during parse: 607
Misses in library cache during execute: 545

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total
Waited
  ----------------------------------------   Waited  ----------
------------
  SQL*Net message to client                       1        0.00
0.00
  HS message to agent                           987        0.00
0.07
  db file sequential read                        21        0.01
0.10
  direct path write                               2        0.00
0.00

If optimizer_secure_view_merging will only help on the parse, then I am
barking up the wrong tree and will need to find a better solution.

Thanks for the help!

Todd

-----Original Message-----
From: Christian Antognini [mailto:Christian.Antognini@xxxxxxxxxxxx] 
Sent: Tuesday, December 19, 2006 10:24 AM
To: Carlson, Todd
Cc: oracle-l
Subject: RE: optimizer_secure_view_merging

Todd

> We are using materialized views with refreash on
> commit (don't ask...) on 10.2.0.2. 

There is no problem at all about that if correctly set up.

> We have some performance problems and I have been 
> researching optimizer_secure_view_merging, to
> reduce the CPU load of parsing and executing
> statements against the views based on the materialized
> views.

Some questions...

Which kind of performance problems? 
Do you really have very high parse time? 
Do you use query rewrite?

In any case optimizer_secure_view_merging should have an impact only
during parse. 

> Has anyone set this parameter to false in a production
> system? If so, what were the results?

I had to disable it only due to a bug (I don't remember which one, see
Metalink for more information...). It was not due to performance.


HTH
Chris


--
//www.freelists.org/webpage/oracle-l


Other related posts: