Query execution plan acquisition (Oracle)

To obtain the query execution plan for a query as executed by Content Platform Engine for Oracle, use the technique that is described here. This technique comes from Oracle support.

Immediately after a search is run from the FileNet P8 application through Content Platform Engine, login as the sys user. Run the following commands from a SQL query tool such as SQL*Plus. For "query portion", substitute enough of the exact SQL text to uniquely identify the query which was previously found to be slow:

COLUMN id format 999 
COLUMN pid format 999 
COLUMN operation format a25 
COLUMN options format a20 
SET linesize 150 
SET pagesize 999 

SELECT distinct hash_value, sql_text 
FROM v$sql 
WHERE lower(sql_text) 
LIKE lower('%query portion%'); 

SELECT distinct id, parent_id AS pid, lpad (' ', depth) || operation operation, options, object_name, cost, bytes 
FROM v$sql_plan 
WHERE hash_value = <hash value from previous query> order by id;