Author Archives: Asaf Tal

PLS-00801: internal error [string]

apparently, pls-00801: internal error [phdcsql_print_kge_errors::parm pos] is one of the most annoying oracle errors. off course, phdcsql_print_kge_errors::parm pos is just an example that can be replcaed with any other undecipherable set characters.

Oracle’s official error description is:

Cause: This is a generic internal error that might occur during compilation or execution. The first parameter is the internal error number.

Action: Report this error as a bug to your Customer Support representative.

While it might be very tempting to contact Oracle Support, many users will prefer to try and solve the problem without opening a service request and waiting for an answer.
Basically, what this error means is that there is a problem with this query but Oracle is having problem describing the error. The result is a generic (and not really helpful ) error message.
Like any generic error description, many reasons can cause the problem. The only solution is trying to identify the problem yourself by trial and error.

Copy the query part of the code to a separate window and try to tweak it without changing the basics: change tables order, change aliases names, comment what you can or run it without variables. Even if this will not solve the problem, sometimes this can provide a more informative error message.
Using this method I have found several issues that caused pls-00801 internal error. I am writing a few in order to provide some list of things that can cause this error:

1) The query used a remote database using a db link and the database link user did not have proper permissions.
2) Extra not visible control characters that were added to the code during a problematic copy-paste from the web or from Word.
3) Extra characters that might have different meaning in oracle like:&,”,_ etc.
4) Extra spaces
5) Invalid objects on remote database
6) Several known Oracle bugs (search metalink for the error)
7) An error on a remote database can not be displayed.
8) Your entry here …

Of course, there are countless other reasons for pls-00801. If you encounter any other reason please share it in the comments to help others.

Why is Oracle query not using my index? A cheklist

One of the most common question, when it comes to performance tuning is “Why oracle is not using my index?”. This question, together with her twin question, “Why Oracle is choosing full table scan even when the table is indexed?” cover large part of the performance tuning challenges.

Even well experienced developers and DBA’s can sometimes miss a very obvious reason causing the optimizer not to choose the expected plan. After finding myself time and time again looking for explanations for poor query performance i decided to create a small checklist that i can use to methodically check until I find the issue that is causing the optimizer not to use the index.

This is far from a complete list and the post is probably going to be constantly updated as I encounter ( ok , be reminded of) other performance killing reasons.

1) Are indexed column being changed ?
For example, the index will be used only if the value of the index is not changed in the where clause. Therefore, every manipulation on the “left side” of the where clause will prevent the index from being used. In order to check this you can add the /*+ INDEX(a, i_empno) */ hint if you can not see it being used in the explain plan then there is something in the query that prevent it from being used.

2) Are you using NOT?
The optimizer will not use the index if the query is based on the indexed columns and it contains NOT EQUAL and NOT IN.
3) Are you using LIKE (‘%% ‘) ?
if you use wildcard query with “%” in front of the string, the column index is not being used and a full table scan is required since the % can be replaces by any string. Therefore the optimizer needs to search the contents of every row of that field.
4) Are you using IS (NOT) NULL ?
– Null values are not included in the index. However, this could be worked around by using nvl when creating the index (function based index), adding the PK to the index or even adding a constant to the column: create index emp_dob_idx on emp (date_of_birth,1)
5) Are you using the leading columns in a concatenated index?
remember to put the column with the highest unique values first to make the result set smaller.
6) Are you selecting from a view?
Make sure that that the base tables have appropriate indexes
7) Are the statistics relevant and valid?
8) Does the index exists as all ?
you will be surprised how many times this is the problem
9) Is the query expected to return large portion of the table?
In this case the optimizer will prefer a full scan
10) If you are using subquery you must use only IN or = (you can workaround this by changing it to a join or a function )
11) Did you do a lot of changes on the table recently?
A large number of dml operations on the table might cause the statistics to become stale and stop using the index
12) It is possible that the data on the table is skewed?
The optimizer might expect normal distribution of values between the maximum and minimum values and might choose a wrong plan if this is not the case. For example, using 01/01/0001 or 31/12/4000 as a null values might confuse the optimizer
13) Does your query actually trying to use the index?
is the where clause contains the indexed column?
14) High degree of parallelism. High degree of parallelism skews the optimizer toward full table scans. select DEGREE from dba_tables where table_name='table-name' and owner='owner-name';
15) A full scan will be cheaper than using an index if the table is small.
16) Does it use Other indexes?
You may have other indexes that Oracle perceives as being “better” for the query.

17) Are you implicitly casting types?
Oracle sometimes cast implicitly. For example it might cast varchar2 to number when if the actual values allow (also for dates)
While it might work for a query, it will not use an index when you are joining a table on fields with different types.

18) Wrong Parameters:
optimizer_index_cost_adj – low value reduce the price of indexes use
all_rows access method – The first_rows optimizer mode is more likely to use an index than the all_rows mode.

Bonus advice:
Add the /*+ INDEX(a, col-name) */ hint. if you still can not see the index being used in the explain plan then there is something in the query that prevent it from being used.

Add the /*+ RULE */ hint. If the query uses the index with a RULE hint, you know that the problem is related to the cost-based optimizer (CBO)

Short Reminder – why you get ORA-00942: table or view does not exist inside a fuction while you can select from it in SQLPlus

This short and quick reminder explains why you can select from a a table or a view in SQLPlus but you get a ORA-00942: table or view does not exist when you try to compile a function using the same query.

This mostly happens when you try to use v$ views (v$session ,v$sesstat, v$statname etc.) inside a function.
in this case, even if the user is granted with dba role he can query the view but he can not use it inside plsql.

A user with the DBA role can access the v$ tables, because the select access is included in the DBA role. According to Oracle, privileges granted via roles are not inherited by stored objects. so, packages, procedures, views, and triggers need explicit grants, not grants via roles.

The solution is to grant access to object directly to the user. log in to the DB as sys and grant the privileges on a specific object (sys.v$instance ,for example ) to the user itself,
grant select on sys.v$instance to some_user;

ORA-02069: global_names parameter must be set to TRUE for this operation

the Oracle documentation description for “ORA-02069: global_names parameter must be set to TRUE for this operation” is:

“You tried to execute an operation that requires a remote mapping. This can not be done because the parameter called GLOBAL_NAMES is not set to TRUE. ”

This error message is one of the less understandable error messages even in the Oracle documentation standards.

Oracle resolution offer is to try setting the GLOBAL_NAMES parameter to TRUE with the following statement:

ALTER SESSION SET GLOBAL_NAMES = TRUE;

shortly, what this message means is that you are trying to perform an operation (update or insert, for example) on a remote database using a db link while trying to use a local function.
for example:
update remote_table@remote_data_base
set col1= local_function(value)

to resolve this, you can:

alter the session as oracle suggested:

ALTER SESSION SET GLOBAL_NAMES = TRUE;
or simply assign the value of the local function into a variable and use it in the update/insert command:

v_variable :=local_function(value);

update remote_table@remote_data_base
set col1= v_variable;

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