BULK COLLECT %FOUND and %NOTFOUND behaviour

When using the BULK COLLECT feature in PL/SQL, a lot of programmers get confused on using the %FOUND and %NOTFOUND in their code. As it is said multiple times (On various sites by various people), it seems better to avoid this by using .count, but I think it is good to point out the behaviour of %FOUND and %NOTFOUND in these cases.

Usually, you will have a code block similar to this:

  OPEN c_cur;
  LOOP
    FETCH c_cur BULK COLLECT INTO t_cur [limit <n>];
    l_found := c_cur%FOUND;
    CLOSE c_dual;
    [PL/SQL code here...]
    EXIT WHEN NOT l_found;
  END LOOP;

When you are determined to use %FOUND or %NOTFOUND, note the meaning of these when using BULK COLLECT:

%FOUND will return TRUE if exactly [limit] rows were retrieved

If %FOUND returns TRUE than there might be more rows to be fetched

%FOUND will return FALSE if less than [limit] rows were retrieved

%FOUND will always return FALSE if [limit] is not set (you retrieve less than unlimited rows)

%NOTFOUND will always return the opposite as %FOUND (as is to be expected...)

Encryption Wallet for TDE: can not change password

Today, I set up Transparent Data Encryption (TDE) on an 11gR2 test environment. The steps are well documented and not at all hard to do, so there was no problem in the initial setup:

1. Edit the sqlnet.ora file and include the following:
  ENCRYPTION_WALLET_LOCATION=
    (SOURCE=(METHOD=FILE)(METHOD_DATA=
      (DIRECTORY=/home/oracle/app/oracle/product/11.2.0/dbhome_2/dbs)))

2. Create the encryption wallet from SQL*plus (sqlplus / as sysdba)
  SQL> ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY oracle;

3. Create a table with an encrypted column, or create an encrypted tablespace
  SQL> CREATE TABLE employees (name varchar2(30), salary number encrypt);
  SQL> CREATE TABLESPACE encrypted
  2 DATAFILE '/u01/oradata/encrypted01.dbf' SIZE 100M
  3 ENCRYPTION DEFAULT STORAGE (ENCRYPT);

This works fine, as expected. However, I would like to change the password for the wallet, as "oracle" isn't that strong a password after all...

Using Oracle Wallet manager (OWM) from the (Linux) command line, I try to open the wallet. It asks for the password, and the message OWM gives me after providing the password "oracle" is "The password is incorrect. Try again?". After retrying the password, I suddenly think about the double quotes that should enclose the password. Because I did not enclose the password, the actual password that got stored is ORACLE, and not oracle. This can be seen by trying the following in SQL*Plus:

  SQL> ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "oracle";
  alter system set encryption key identified by "oracle"
  * ERROR at line 1:
  ORA-28353: failed to open wallet

  SQL> ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "ORACLE";
  
  System altered

So, I should use ORACLE as a password for OWM. However, this too gives me the message "The password is incorrect. Try again?".



After searching for quite a while, I discovered that this message is NOT about the password being invalid as such, but more about the password not adhering to the password criteria for OWM:



I managed to change the password using orapki:

  orapki wallet change_pwd
    -wallet /home/oracle/app/oracle/product/11.2.0/dbhome_2/dbs
    -oldpwd ORACLE -newpwd Oracle.01

This statement returns without error and after that, the wallet can be maintained succesfully using OWM (with the new password). Should you provide a password that doesn't conform to the OWM standards, you will get an "PKI-01002: Invalid password." error from orapki. This is because of the NEW password. If you misspelled the old password, you would get "PKI-02003: Unable to load the wallet ...".

In OWM:



So, be carefull when choosing your password when creating the TDE wallet, because if it is not a strong enough password, you will not be able to open and maintain the wallet with OWM, which can lead to much confusion.

Oracle Easy Connect - no password in the commandline

When using Oracle's Easy Connect feature, I (re)discovered some strange behaviour. The easy connect does not always translate the easy connect string to the right connect descriptor.

I set up Easy Connect by modifying my sqlnet.ora file on the client. Modifying the line with the directory_path in it should be enough:

  names.directory_path = (TNSNAMES,EZCONNECT)

After that, I tried to connect using easy connect:

  sqlplus myuser@myhost:1521/myservice

This returned an ORA-12541 Error: "TNS:listener does not currently know of service requested in connect descriptor". At first, I looked this up in the sqlnet.log file to see what he was trying to do:

  Fatal NI connect error 12514, connecting to:
   (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=myhost)(CID=(PROGRAM=sqlplus@myclient)(HOST=myclient)(USER=myuser)))(ADDRESS=(PROTOCOL=TCP)(HOST=)(PORT=1521)))

So, it just messes up the entire connect string and tries to connect to a service called "myhost". This all disappears when you connect using a password on the commandline:

  sqlplus myuser/mypassword@myhost:1521/myservice

According to the documentation, this should not be a problem, but when searching for this on Oracle support, I came across Note 274757.1, which states that without specifying a password, you must enclose the connect descriptor in double quotes (and depending on the OS escaping these with \):

  sqlplus myuser@"myhost:1521/myservice"

And after that, SQL*Plus asks for the password as expected. In this case, I think the documentation could use an update, specifying this behaviour...

Enhancing Oracle Reports - create a custom type

Usually, the report types (destination types) provided by Oracle Reports are sufficient. You can create your report as a file, in the cache, send it to a printer etc. See the list of standard types in the Oracle Documentation.

It is also possible to create your own destination, but the documentation is a bit vague about this, to put it mildly. Once you know where to look though, it is quite easy to accomplish.

First of all, you will need to modify the reportsserver configuration file. You can do this through Enterprise Manager, or modify the file directly ($ORACLE_HOME/reports/conf/<repserv>.conf). You must add a destination tag, just like there are many others in this file. It would be sufficient to add something like this:
  <destination destype="myDest" class="myDestClass"/>

And now, your reportserver will not start, or at least give you an error that the specified class does not exist. This is the hard part. You must create a Java Class that extends the Destination Class. So, you find yourself a Java Programmer (shouldn't be that hard to find, these days) and let him create your own class. In this class, you can do (almost) anything you can imagine. You will have the XML provided by the reports engine avaiable and you will need some imagination as to what you want to do with this report.
After the class is created, compiled and packaged, you add it to the classpath of the reports server. Easiest way to do this is to use Enterprise Manager. After that, the reports server will be able to find the new class and start the engine.

You could for example create an RTF document from the standard XML using a stylesheet and then mail it to one or more recipients. You could send it to a printer and e-mail it both in the same class. Any requirement you might have for your reports is now available. It will require your effort to implement all your requirements though.

This is a good way to enhance the output options of reports, it is just too bad that it is not so very well documented...
When you start working with this, there will undoubtedly be some other obstacles, but if you have any questions, you know where to find me (i.e. right here ;-)

Using too many indexes

When writing about the use of indexes (or rather, the lack of) it soon occurred to me that when you tell the developers to use indexes, some of them really take that too far. I once saw a 18-column table with a total of 20 indexes on it. That might be useful in a certain DWH database, but this was in a OLTP database.

But why is it not good to have a lot of indexes? Is there such a thing as too many indexes? From the optimizer point of view, it just means a few extra access paths to consider, that should not be too difficult. On the other hand, from a DML perspective, when you insert a record or update certain columns, all related indexes must be modified as well. So, instead of inserting one row (1 I/O operation), you could end up with inserting one row and inserting 20 index entries (21 I/O operations!).

So yes, you can certainly have too many indexes.

What to do? Well, the obvious thing to do is to get rid of all the unnecessary indexes. But not knowing which indexes are useful, you might accidentally drop the wrong ones and end up with terrible performance for your application.

The best course of action would be to let the database determine what indexes are being used and which indexes are candidates for deletion. You do this with the following statement:

    ALTER INDEX index MONITORING USAGE;

This starts monitoring if the index is being use by any statement in the database. You can let this monitoring run as long as you like. I would advise to use a representative period of time, so you can be sure that each and every part of your application has been used (every statement out there should have run at least once). You should consider special cases like an end-of-month report that will run. It would be a shame to drop any indexes and then discover that this crucial report takes two days to complete…

After you have monitored long enough, you should check the V$OBJECT_USAGE table. This table contains the rows for the monitored indexes and contains a column “USED”. If this is set to TRUE, the index was used during to monitoring period. If not, the column value will be FALSE.

Now you can determine if there are unused indexes in your datamodel. These are the candidates for deletion. Please remember that index usage is not restricted to query performance. You should not drop primary key or unique ket indexes, just because they are not used in your monitored workload. They have a different (but important) reason for being there.

After you complete your analysis, you can disable monitoring:

    ALTER INDEX index NOMONITORING USAGE;

This way, you keep the minimum number of indexes while retaining optimal performance. You (or rather, the database) will save a lot of effort updating indexes that will most likely never be used and thus optimizing your performance just that bit more.

Also see the Oracle documentation for some more details…

Why won't developers use indexes

It keeps turning up again and again. The application is released to the customer and after a few weeks, or even a few days, the first issues arrive on the slow performance.

Time and again, this is due to the application queries not using the right indexes, or even using any indexes at all. The queries can be well written, as I usually keep repeating to the developers how important that is. On the other hand, I keep telling everybody about the usefulness of indexes, and somehow that doesn't seem to stick...

So, let's try this one more time ;-)

If you write a query (any query), it is important to ask yourself a few questions:

• What data will this query access
• How are the tables in this query connected. Am I using the right connections (comparing the right attributes)
• Am I using the right filter conditions on the data

These are questions of a more logical nature. This has everything to do with constructing a good query. The "hard" part is the technical part of the puzzle:

• How will the database access my data
• Are there enough fast access paths (at least one) to choose from
• Will this query perform when retrieving the needed amount of data

These questions are really not that tough. Most of the time, when writing a query, you will have quite a good idea about what data and what amount of data is to be retrieved. Try to picture this query in the datamodel. How would the database be able to retrieve your data in the fastest way possible? On many occasions, the answer would be to use an index. When accessing a table with an index, only a portion of the table will have to be scanned and the access can be much, much faster.

So, to all developers out there, please consider your queries carefully. After creating the query, try to anticipate how it will function. If there is any doubt if the query will perform adequately, at least test it with a representative amount and distribution of data.

Of course, I am fully aware of the many types of queries out there, not to mention the many types of indexes you can use. Usually, it pays off to invest some time reading up on the use of indexes. There are a great number of resources to use out here on the internet, so I won't even try to begin explaining them. As a developer, do yourself a favor and spend some time getting to know the workings of the most common types of indexes.

And if all else fails: please contact your DBA...

Using PL/SQL tables as IN- and OUTPUT parameters can be bad for performance

Sometimes, when you start a new project, you want to go that extra mile and "do thing right this time". That is just what one of our programmers thought when setting up a new package to manipulate large amounts of data. Instead of repeating the code to merge two PL/SQL tables, he wrote a procedure to do that for him. Nice and clean, modular programming, etc, etc.

First day of testing: everything works fine.
Second day: still going strong.
Third day: the load test. With amounts of data not even close to the amounts to be processed in production, the system slows to a crawl. Processing seems to take forever and memory usage is going through the roof.

In this case, the separate procedure for merging two PL/SQL tables was the problem. When creating this procedure, the developer created something like this, stripped of any irrelevant code (Seven of Nine: Code is irrelevant...)

procedure concat_plsql_table
          ( p_tab1  IN      t_test_tab
          , p_tab2  IN OUT  t_test_tab
          )
IS
BEGIN
  --
  -- LOOP through all records of the first table
  -- and place them in the output table
  --
  FOR i IN 1..p_tab1.count LOOP
    p_tab2(p_tab2.count+1) := p_tab1(i);
  END LOOP;
END;

The problem with this, is that the parameters are referenced by value. This means that the compiler creates a copy of the variable, starts working on that variable, and returns a copy to the calling procedure when finished. It is all this copying that wreaks havoc on your performance and memory.

The solution can be very simple:
* either do not use a separate procedure
* or use the NOCOPY compiler hint in your code

procedure concat_plsql_table
          ( p_tab1  IN            t_test_tab
          , p_tab2  IN OUT NOCOPY t_test_tab
          )
IS
BEGIN
  --
  -- LOOP through all records of the first table
  -- and place them in the output table
  --
  FOR i IN 1..p_tab1.count LOOP
    p_tab2(p_tab2.count+1) := p_tab1(i);
  END LOOP;
END;

With the NOCOPY compiler hint, the compiler is instructed not to copy the variable, but to use a reference to the original variable and work from there. There are some limitations on the usage (see the Oracle Documentation), so be careful on how you use this.

In this case, the performance went up by about 200 times, from minutes to seconds prcessing time. For any procedure using large amounts of data in arrays, it is worthwhile to consider this option.





For a more complete example, see the code below:

create or replace package test_plsql_table
as
  --
  -- Main procedure
  --
  procedure test_response;
  --
end;
/

create or replace package body test_plsql_table
as
  --
  -- pl/sql table
  --
  type t_test_rec IS RECORD ( id       number
                            , desc_col varchar2(100)
                            );
  type t_test_tab IS TABLE of t_test_rec
       index by binary_integer;
  --
  -- Concatenate two pl/sql tables into a third
  --
  procedure concat_plsql_table
            ( p_tab1  IN     t_test_tab
            , p_tab2  IN OUT t_test_tab
            )
  IS
  BEGIN
    --
    -- LOOP through all records of the first table
    -- and place them in the output table
    --
    FOR i IN 1..p_tab1.count LOOP
      p_tab2(p_tab2.count+1) := p_tab1(i);
    END LOOP;
  END;
  --
  -- Main procedure
  --
  procedure test_response
  IS
    l_tab1  t_test_tab;
    l_tab2  t_test_tab;
    l_tab3  t_test_tab;
  BEGIN
    --
    -- Display the start time
    --
    dbms_output.put_line('Procedure started at '||to_char(systimestamp,'hh24:mi:ss'));
    --
    -- Initial content for the first table: 50 records
    --
    FOR i IN 1..500 LOOP
      l_tab1(i).id       := i;
      l_tab1(i).desc_col := 'Record '||i||' in initial table';
    END LOOP;
    --
    -- LOOP over records and in each LOOP, concatenate the initial table with a second one
    --
    FOR i IN 1..1000 LOOP
      concat_plsql_table( l_tab1
                        , l_tab2);
      IF mod(i,100) = 0 THEN
        dbms_output.put_line('.. '||lpad(i*500,6,' ')||' records at '||to_char(systimestamp,'hh24:mi:ss'));
      END IF;
    END LOOP;
    --
    -- tab2 should now contain a lot of records
    --
    dbms_output.put_line('Number of records created in result table 2 = '||l_tab2.count);
    --
    -- Display the end time
    --
    dbms_output.put_line('.');
    dbms_output.put_line('Procedure started at '||to_char(systimestamp,'hh24:mi:ss'));
    --
    -- LOOP over records and in each LOOP, concatenate the initial table with a second one
    --
    FOR i IN 1..1000 LOOP
      FOR i IN 1..l_tab1.count LOOP
        l_tab3(l_tab3.count+1) := l_tab1(i);
      END LOOP;
      IF mod(i,100) = 0 THEN
        dbms_output.put_line('.. '||lpad(i*500,6,' ')||' records at '||to_char(systimestamp,'hh24:mi:ss'));
      END IF;
    END LOOP;
    --
    -- tab3 should now contain a lot of records (same amount as tab2)
    --
    dbms_output.put_line('Number of records created in result table 3 = '||l_tab3.count);
    --
    -- Display the end time
    --
    dbms_output.put_line('.');
    dbms_output.put_line('Procedure finished at '||to_char(systimestamp,'hh24:mi:ss'));
  END;
  --
end;
/