VPD / FGAC / RLS

That’s “Virtual Private Database”, “Fine Grained Access Control”, or “Row-level Security” – three different names for the same feature. (Four if you include the separately licensed Oracle Label Security (OLS) which is a product built on top of RLS).

I’ve just seen a nice presentation from John Batchelor of Sopra hitting the key points of intent, implementation and threat from this (free) product and wondered how many sites used it. So I’ve put up a little poll about usage, with a follow-up for those who do use it about the impact on their system.

Collection Costs

Here’s an extract from an execution plan I found on a client site recently. I’ve collapsed lines 5 to 42 into a single line representing the rowsource produced by a fairly messy execution plan, leaving just the last three stages of execution on view. Each of three operations joins the same collection variable (using the table() operator) to the row source – once through a hash join, then twice more (joining to two other columns) through nested loop outer joins:

The resulting estimates of row counts and costs are quite entertaining and, fortunately, not very accurate:

Index Join – 2

In an earlier article introducing the index join I raised a question that came up at the first ES2N virtual conference:

“If you hint an index hash join, is there any way of telling Oracle the order in which it should use the indexes?”

Consider the following example:

Distributed Queries – 2

I have often said that the optimizer “forgets” that it is dealing with a distributed query once it has collected the stats that it can about the objects in the query, and that as a consequence the driving site for a distributed query will be the local database unless you use the /*+ driving_site */ hint to change it.

While investigating an oddity with a distributed query between two 11.1.0.7 databases a few days, I noticed something in the 10053 trace file that made me change my mind, and go back to look at earlier versions of Oracle.

Subscribers

Some time ago I added the “subscribe” option to the right-hand panel to allow people to register for automatic email whenever I posted a new article. (I’ve also checked how easy it is to unsubscribe if you change your mind  - and it’s very easy)

Since then I haven’t been paying attention to how many people have subscribed – but I noticed today that the total number of active subscribers has gone over 250, which is quite nice.

Index Join

One of the less well known access paths available to the optimizer is the “index join” also known as the “index hash join” path. It’s an access path that can be used when the optimizer decides that it doesn’t need to visit a table to supply the select list because there are indexes on the table that, between them, hold all the required columns. A simple example might look something like the following:

Quiz Night

Apart from the fact that the “Rows” figure for the FILTER operation at line 6 is blank, what’s the obvious error in this extract from an execution plan:

Autotrace oddity

I got an unexpected error message from SQL*Plus a couple of days ago while trying to enable autotrace with statistics:

SQL> set autotrace  traceonly statistics;
ERROR:
ORA-24315: illegal attribute type

SP2-0619: Error while connecting
SP2-0611: Error enabling STATISTICS report
SQL>

This was a little puzzling, but since I get through a lot of different sites, databases, and instances, I did a quick check to make sure that the plustrace role had been created on this system and that the schema I was using to log on had been granted the role. Everything looked okay … except for this error message. So I reconnected (almost as good as rebooting as a method for solving problems) and got the following error message:

SQL> connect XXXXXXXX/XXXXXXXX@XXXXXXXX       -- no names, no pack drill
ERROR:
ORA-28002: the password will expire within 7 days

Problem solved. When you enable autotrace statistics SQL*Plus tries to create a second session through your current process (which is one of the reasons why v$session can end up with more rows than v$process) . By an unfortunate coincidence or timing, my password had reached the expiry warning point while my initial session was logged on, so the recursive session got error message ORA-28002 when it tried to connect, and SQL*Plus reported this back in the only way that seemed appropriate.

Once I’d worked out what the problem was all I did was change my password, end all my active sessions, and then logon again. If you want a demonstration of the effect, here’s a simple demo of a very similar problem (cut-n-paste from an SQL*Plus session connected to an 11gR1 database – using schema test_user, when the password wasn’t “fred”):

SQL> alter user test_user identified by fred;

User altered.

SQL> set autotrace traceonly statistics;
ERROR:
ORA-01017: invalid username/password; logon denied

SP2-0619: Error while connecting
SP2-0611: Error enabling STATISTICS report
SQL>

Local Indexes – 2

In the previous note on local indexes I raised a couple of questions about the problems of different partitions holding different volumes of data, and supplied a script to build some sample data that produced the following values for blevel across the partitions of a list-partitioned table.

Autumn

… (or “Fall” for speakers of American) has arrived in the UK – and once again I am reminded how gardening and trouble-shooting are just two aspects of the same problem.

I have several trees in and around my garden, including two rather large Oak trees, and at this time of year it takes a couple of hours at the weekend to rake up the fallen leaves. The comparison with solving performance problems is obvious:

Every Saturday, I look at the leaves on the ground and the leaves still on the trees and quite often manage to persuade myself that there’s no point in doing anything just yet.

On the Saturdays when I decide that I really do have to rake up the leaves I aim to clear about 99% of the problem – there’s no point in clearing to 100% because if I go for perfection it’s only going to last a couple of minutes before more leaves start coming down or blowing in. Of course, after I’ve spent ages clearing 99% of the mess, my wife (the end user) is quite likely to say: “you haven’t finished yet”

After I’ve done a really good job raking up enough leaves I look up at the trees and know that all those leaves are going to be heading my way and I’m probably going to have to do it all over again next week, and there’s nothing appropriate that I can do to stop it happening.

 

Local Indexes

In a recent article about list partitioned tables I raised some questions about the cases where the optimizer can’t (yet) do partitioning pruning even when the opportunity is clearly visible to the human eye. The most important example was the case where each partition was defined to hold rows for just one partition key value – but the optimizer could not prune out the redundant partition for a query like: “partition_key != {constant}”.

I recently came across a situation where this really made a big difference. The system had a huge table that had been list partitioned as follows (with some camouflage):

List partitions

Despite the title and content, this article is more about thinking and testing than it is about list partitions as such. It’s just based on a little thought experiment with list partitioning.

I have a list-partitioned table, with no indexes, defined as follows: