Database Reference
In-Depth Information
ID PAD
---------- ----------
1 DrMLTDXxxq
4 AszBGEUGEL
id=1 pad=DrMLTDXxxq
id=2 pad=XOZnqYRJwI
id=3 pad=nlGfGBTxNk
id=4 pad=AszBGEUGEL
id=5 pad=qTSRnFjRGb
With the
optimizer_secure_view_merging
initialization parameter set to
TRUE
, the second query returns the
following output. As you can see, the function and the query display the same data:
SQL> SELECT id, pad
2 FROM v
3 WHERE id BETWEEN 1 AND 5
4 AND spy(id, pad) = 1;
ID PAD
---------- ----------
1 DrMLTDXxxq
4 AszBGEUGEL
id=1 pad=DrMLTDXxxq
id=4 pad=AszBGEUGEL
Note that if the view is owned by the same user that issues the query, the
optimizer_secure_view_merging
initialization parameter is ignored (because there's no point in preventing a user from seeing information that he can
already see by reading the tables referenced in the view directly).
A similar example, but showing the impact on predicate move around applied to Virtual Private Database (VPD)
predicates, is available in the
optimizer_secure_view_merging_vpd.sql
script.
In summary, with the
optimizer_secure_view_merging
initialization parameter set to
TRUE
, the query optimizer
checks whether query transformations could lead to security issues. If this is the case, those transformations won't be
performed, and performance could be suboptimal as a result. For this reason, if you're neither using views nor VPD
for security purposes, I advise you to set the
optimizer_secure_view_merging
initialization parameter to
FALSE
.
The
optimizer_secure_view_merging
initialization parameter is dynamic and can be changed at the instance
level. In a 12.1 multitenant environment, it can also be set at the PDB level. It can't be changed at the session level.
Instead, users having either the
MERGE VIEW
object privilege or the
MERGE ANY VIEW
system privilege aren't subject to
the restrictions imposed by this initialization parameter. Be aware that by default the
dba
role provides the
MERGE ANY
VIEW
system privilege.
PGA Management
SQL operations that store data in memory (for example, sort operations and hash joins) use
work areas
in order to be
executed. These work areas are allocated in the private memory of each server process (PGA). This section describes
the initialization parameters devoted to the configuration of these work areas.
Usually, larger work areas provide better performance. Therefore, you should devote the unused memory that's
available on the system to the allocation of work areas. Be careful, though, when changing it. The size of the work
areas has an influence on the estimations of the query optimizer as well. You should expect changes not only in
performance but also in execution plans. In other words, any modification should be carefully tested if you want to
avoid surprises.
