monitor index usage (are my indexes being used at all?)

Indexes are the easiest solution for fast query performance improvement but the gain does not come without a cost. Indexes take up space and make inserts slower. In Addition, many times, the index is added to solve one slow query. If the query is not executed frequently, the performance improvement might not be worth the cost of making almost every insert and update slower. For example, even if the index is useful for a nightly or weekly report, the extra daily overhead might not be worth the few minutes it will save during the night.

therefore, it is important to know the if and how much the index is used. The tool for this job is index monitoring usage.

Basically the process is very simple. The first step is turning on monitoring since index monitoring usage is turned off by default. The next step is to let it run for a while. It is important to choose the monitoring period wisely as it supposed to correctly represent real life usage. After that you can turn off monitoring and query the usage view.

Turing on index monitoring for a specific index:

ALTER INDEX “YOUR-SCHMA-NAME”.”YOUR-INDEX-NAME” MONITORING USAGE;

If you get: ora-00054 resource busy and aquire with NOWAIT specified or timeout expired
This is because altering a table or an index requires an exclusive lock on the table therefore it will not work while DML operations are performed on the table

Turing on index monitoring for all indexes on all indexes for a specific schema:
declare
query_str varchar2(100);

begin

for i in ( select * from dba_indexes d where d.owner ='YOUR_SCHEMA' and index_type!='LOB' --and index_name='ASSIGNMENT_TERR'
) loop

query_str:='ALTER INDEX "' || i.owner || '"."' || i.index_name || '" NOMONITORING USAGE' ;
execute immediate query_str ;

end loop;

end;
Without adding the index_type!=’LOB’ you might get ORA-22864 cannot ALTER or DROP LOB indexes which means that you can not operate directly on a system-defined LOB index. You should perform operations on the corresponding LOB column.

Turing OFF index monitoring for a specific index:
ALTER INDEX "YOUR-SCHMA-NAME"."YOUR-INDEX-NAME" NOMONITORING USAGE;

You can analyze the results by running
select * from v$object_usage

several point to take into consideration:
don’t rush to drop the index if you see that it wasn’t used. It is possible that that it is used only once a month but it can still be crucial.

If you turn off monitoring and turning it on again, previous data is deleted. Therefore it might be wise to backup v$object_usage from time to time.

After you drop an index, the indesx data is deleted from v$object_usage

Using Union together with Cast and Multiset can cause your function to simply hang (even if the query works great in SQL Plus)

Many times, when an outside client is calling an oracle function it requires the results in a table format (a collection or an array or data set). The most common way to do so is using Cast and Multiset.
CAST tells Oracle the required datatype for the result of an expression, and MULTISET tells Oracle to put the multiple rows returned by the SELECT statement into a single collection object.

A few days ago I changed a query inside a function using Cast and Multiset. I had a working query with reasonable performance. However, once I compiled the function and tried to use it. The same query that worked great in SQLPlus and PL/SQL developer became painfully slow when using it inside a function.

I checked all the usual suspects like wrong binding of mixed types that could make the optimizer choose a wrong plan and make the query slow. Still, I could not find the reason for the slow performance of the function. After a while, I came to the conclusion that the slow performance is related to the fact that the query is using Union (after all that was the actual change in the function) only then I remembered that using Union together with Multiset will cause your function to simply hang.

one workaround is to use multiset union [all] command.
for example:

create type rec1 as object (rec_id number, text varchar2(20));
create type rec_collect as table of rec1;

SELECT
CAST(MULTISET(
select 1, 'first' from dual
union all
select 2, 'second' from dual
) AS rec_collect)
FROM dual;

ERROR at line 1:
ORA-03113: end-of-file on communication channel
or "Unable to send Break messsage"

Using Multiset Union:

select cast (multiset (select 1, 'first' from dual) as rec_collect)
multiset union -- ALL or distinct
cast (multiset (select 2, 'second' from dual) as rec_collect) rec_collect
from dual

The only problem is that this Multiset Union works only from Oracle 10. In addition it works only with nested table collections and not with varrays.

If your function is pre oracle 10 or you simply need a quick workaround all you need to do is wrap the query with an extra inline view and your function will perform just like the query in SQL Plus.

SELECT
CAST(MULTISET(
select * from
(
select 1, 'first' from dual union all
select 2, 'second' from dual
)
) AS rec_collect)
FROM dual;

A simple workaround for bug 13477790 – XML/SQL query hangs after Oracle 11

IF you will run the following query on an oracle 11 db

select xmlelement("root",
(xmlelement("sub_root",
xmlforest('value' as "some_tag",
(select xmlagg(xmlelement("test",
xmlforest('code_value' as
"Code")))
from dual) as "SOME_AGG_CODES"))))

from dual

you might find that the sq/xml query simply hangs for many minutes after the upgrade. Many times, the following message will be displayed ” end-of-file on communication channel” . In other cases, it is possible that your session will be disconnected or might not be able to send a break message because you client will simply freeze.

This is even more problematic if this query was written on earlier versions of oracle like oracle 9. The same query will work without a problem on Oracle 9 or oracle and this can be very challenging if you encounter the problem after the actual upgrade to oracle 11.

Oracle identified this problem as a known issue bug #13477790 and released a patch with the same number. It appears that this patch solves this issue but applying a patch is not always easy on large production systems.
Another option for solving this issue is rewriting the the query using an additional xmlelement instead of the xmlforest but this can be time consuming as well.

A quick workaround for overcoming this bug without applying the 13477790 patch is adding a simple hint to the query. Adding the /*+NO_XML_QUERY_REWRITE*/ hint before the first element in the query. Will solve this issue.

select /*+NO_XML_QUERY_REWRITE*/ xmlelement("root",
(xmlelement("sub_root",
xmlforest('value' as "some_tag",
(select xmlagg(xmlelement("test",
xmlforest('code_value' as
"Code")))
from dual) as "SOME_AGG_CODES"))))

from dual

OCI-21700: object does not exist or is marked for delete (a quick reminder)

While many reasons can cause the OCI-21700: object does not exist or is marked for delete message, many times the OCI-21700 is caused by difficulty to access resources that are checked only at runtime.

The most common reason for this error is granting execute privileges on a function without granting privileges on referenced object like types for example.

Giving the same grants to the referenced objects will solve the problem.

Update: it appears that this error message can be raised when using Weblogic Data Source to run a function as well.
Sometimes, mostly after a network failure/database restart/DB schema changes, the “OCI-21700: object does not exist or is marked for delete” will be raised. The weird thing is that the same function will fail when running it with Weblogic but it can run without a problem when you run it from the sql console using the same credentials.
It appears that some Data Base actions like importing or droping objects or even unexpected network failure can “corrupt the DataSource”. If this is the case, all you have to do to solve the problem is restarting the Data Source.

create a database link without changing the TNSNAMES.ORA

The basic syntax of creating a public database link is:

CREATE PUBLIC DATABASE LINK NAME.OF.DATABASE.LINK
CONNECT TO schema_name
IDENTIFIED BY "schema_password"
USING 'tnsnames entry';

However, many times even if you have the database permission to create a db link (CREATE DATABASE LINK system privilege), you have no access to the TNSNAMES.ORA file on the server.

Without adding the entry to the TNSNAMES.ORA file any attempt to select from this database link will result in a ORA-12154: TNS:could not resolve the connect identifier specified error.
The simple solution is to create a database link without changing the TNSNAMES.ORA file. The syntax is:

CREATE PUBLIC DATABASE LINK NAME.OF.DATABASE.LINK

CONNECT TO schema_name IDENTIFIED BY " schema_password"

USING

'(DESCRIPTION=

(ADDRESS=

(PROTOCOL=TCP)

(HOST=hostname-or-IP)

(PORT=1521))

(CONNECT_DATA=

(SID=sid-name)))'

;

Since Oracle 10, it is possible to use the abbreviated version

CREATE DATABASE LINK NAME.OF.DATABASE.LINK
CONNECT TO schema_name IDENTIFIED BY schema_password
USING '//hostname-or-IP:1521/sid';

It is important to note that while this trick might be great as temporary workaround, it is recommended to use the old fashion TNSNAMES method to create a constant database link. This is especially true in a multiple servers environment where a databases can be copied from one environment to another (prod to test for example). In such cases, the copied database might include links to prod machines while the tnsnames.ora file will still point to the correct machine.