RE: optimizer_secure_view

From: <oracle-l-bounce_at_freelists.org>
Date: Tue, 19 Dec 2006 10:57:50 -0600
Message-ID: <4E6AB84EE260DE49B1BCCBCAC72CE1B301656EB4@S01-VWFPS034VB.tripos.com>

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_at_trivadis.com] 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

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

Received on Tue Dec 19 2006 - 10:57:50 CST