Viewing index usage results in another user’s schema

The Oracle command ALTER INDEX … MONITORING USAGE can be used to turn on index monitoring for the specified index. After this change, a query on the V$OBJECT_USAGE view can be used to determine if the index has been utilized by virture of a YES value in the USED column of the V$OBJECT_USAGE view.

However, in order to see these results in the V$OBJECT_USAGE view, you must be logged in as the index owner. What if you cannot log in as the index owner, but still want to use index monitoring?

I found a work-around for this by creating a query on the OBJ$, OBJECT_USAGE, and DBA_USERS views. A full example of this technique is shown here.

Determining Versions of EM Grid Technology Stack Components

On 15-SEPT-11 Oracle released Security Alert CVE-2011-3192, advocating the application of a patch for a vulerability for the Apache listener. As per the note:

 “Oracle Enterprise Manager includes the Oracle Fusion Middleware component that is affected by this vulnerability. Oracle Enterprise Manager is affected only if the affected Oracle Fusion Middleware version (noted above) is being used. Since a vulnerability affecting Oracle Fusion Middleware versions may affect Oracle Enterprise Manager, Oracle recommends that customers apply the fix for this vulnerability to the Oracle Fusion Middleware component of Oracle Enterprise Manager.”

However, determining the version of each EM Grid technology stack component can be a little tricky as each component has a different way of finding this information. My Oracle Support document 1151602.1 comes to the rescue, showing you the commands used to find the installed version of each of these components:

• WebLogic
• JDeveloper
• Fusion Middleware
• Metadata Services (MDS)

Refer to this document for complete details.

Function Based Indexes: Parenthesis are important

I was testing an Oracle function-based index the other day and discovered that the parenthesis that are part of the index definition are required, even though the CREATE INDEX command works fine without them.

Here’s an example:

SQL> -- create a test table and load it with some rows
SQL> create table EMP
  2  ( id number,
  3    name varchar2(20)
  4  );

Table created.

SQL> declare
  2  begin
  3  for i in 1..500000 loop
  4    insert into emp values (i,'Johnson');
  5  end loop;
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL> insert into emp values (500001,'Joe');

1 row created.

SQL> commit;

Commit complete.

SQL> -- notice no parenthesis around upper(name)
SQL> create index emp_func_idx on emp upper(name);

Index created.

SQL> -- notice that the resulting index is NOT a function-based index
SQL> select index_type from user_indexes where index_name = 'EMP_FUNC_IDX'; 

INDEX_TYPE
---------------------------
NORMAL

1 row selected.

SQL> drop index emp_func_idx;

Index dropped.

SQL> -- note the extra enclosing parenthesis (in red) are
SQL> -- part of the index definition
SQL> create index emp_func_idx on emp (upper(name)); 

Index created.

SQL> -- now the index is a function-based index
SQL> select index_type from user_indexes where index_name = 'EMP_FUNC_IDX'; 

INDEX_TYPE
---------------------------
FUNCTION-BASED NORMAL

1 row selected.

SQL> -- confirm the functionality of the index by using a function in query
SQL> explain plan for
  2  select * from emp where upper(name) = 'JOE';

Explained.

SQL> @xplan
Plan hash value: 2877811124

--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |  3684 |   133K|   340   (0)| 00:00:05 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP          |  3684 |   133K|   340   (0)| 00:00:05 |
|*  2 |   INDEX RANGE SCAN          | EMP_FUNC_IDX |  1474 |       |   665   (0)| 00:00:08 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access(UPPER("NAME")='JOE')

SQL> explain plan for
  2  select * from emp where name = 'Joe';

Explained.

SQL> @xplan
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    14 |   350 |   346   (2)| 00:00:05 |
|*  1 |  TABLE ACCESS FULL| EMP  |    14 |   350 |   346   (2)| 00:00:05 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("NAME"='Joe')

How To Install Oracle Enterprise Manager Grid Control version 11.1.0.1

This 25-page PDF document shows an example of a complete installation of Oracle Enterprise Manager Grid Control version 11.1.0.1 on a 64-bit Red Hat Enterprise Linux 5.4 server using an existing Oracle 11.2.0.1 repository database.

View Can’t Use Privileges Granted Through Role

Most DBAs are familiar with the quirk of Oracle’s security model that does not allow a user to utilize privileges granted via roles when executing PL/SQL stored procedures. However, I recently ran into this same quirk when trying to create a simple view. Essentially, I discovered that the user could not create a view when access to the table underlying the view was granted via a role. I could only create the view when access to the table underlying the view was granted directly to the user.

I started by creating a new user, TESTUSR and granted the user the privileges needed to create the view:

SQL> create user testusr identified by secret
  2  default tablespace users
  3  temporary tablespace temp;
User created.
SQL> grant create session to testusr;
Grant succeeded.
SQL> grant create view to testusr;
Grant succeeded.

Executing a Function in a Remote Database

An application I support accesses data in a remote database using a database link.

A developer had a need to execute a function in the remote database using the database link. This was the first time that I had run into a situation like this, so I thought I’d share an example of the SQL in case anyone else finds it useful:

SELECT REPMAN.WESTBY.TIMEZONE_ADJ@SD_LINK(REG_CREATED)
FROM SDOV.SERVICECALL@SD_LINK

Where:
- REPMAN is the schema name that owns the Westby PL/SQL package in the remote database.
- WESTBY is the package name that contains the TIMEZONE_ADJ function.
- SD_LINK is the name of the DB link to the remote database.
- REG_CREATED is the name of the column that the function is being applied to.
- SDOV is the schema that owns the SERVICECALL table in the remote database.

OraSRP: Useful Tool for Finding Slow Application SQL

Capturing response-time based SQL statistics using 10046 tracing is a well-documented and accepted best-practice for finding SQL that is contributing to slow application performance. However, reading the raw 10046 trace files can be a challenge, so a formatting tool like the open source OraSRP utility is very helpful.