Oracle Misdirection: Lies, Filthy Dirty Lies!

So I learned today that Oracle with it's EXPLAIN PLAN and AUTOTRACE features sometimes will "lie" about how a particular query is executing.

For a problematic query against a view in one of our databases, I pulled the query into Aqua Data Studio (cross platform database development/admin tool that I use)  and ran an EXPLAIN PLAN on it. What truly puzzled me was that the query appeared to be using the correct index but never seemed to return in a reasonable amount of time.

After speaking with our Senior Oracle DBA, he informed me that the RDBMS will often decide at execution to use a different plan than what EXPLAIN PLAN or AUTOTRACE says it is going to use.  Some of this decision depends on the whether or not you use bind variables or at times the data itself that you are binding in your predicates.

To troubleshoot, I needed to look for the SQL_ID for the query in question.  To make sure I pulled the EXACT query that was causing the problem, I actually looked at a join between the v$sql and v$sql_bind_capture views focusing in on a CustomerID for a query that I knew I had just run:

SELECT /*+ PARALLEL */
       v.executions,
       c.VALUE_STRING as CustomerID,
       v.sql_fulltext,
       v.sql_id,
       v.rows_processed, v.parsing_schema_name,
       v.last_active_time, v.last_load_time, v.first_load_time,
       v.disk_reads, v.buffer_gets, v.cpu_time, ROUND(v.physical_read_bytes/(1024*1024)) as read_meg
FROM  v$sql v, v$sql_bind_capture c
WHERE
v.sql_fulltext LIKE '%CustomerSummary%' AND
v.sql_id = c.SQL_ID
AND c.name like '%CustomerId%'
AND c.VALUE_STRING like '%2005681%'
AND v.PARSING_SCHEMA_NAME = 'MO_COUNTIES'
and v.last_active_time >= sysdate - 1
ORDER BY physical_read_bytes DESC;

This then gave me a SQL_ID that I could plug into the following that provided a table pinpointing the EXACT plan used at execution:

select * from table(dbms_xplan.display_cursor('3qub1pyswv17b','',''));  

Digging through this plan I discovered that an index I thought should have been used was not.  In addition to asking our DBA to gather statistics, I also for the time being modified the view such that it now has a hint driving it to the correct index.

A TNS-Nameless Connection!

This week we have been rolling out our new web application and late one evening long after our server administrator had signed off, I realized that while the Oracle client had been successfully installed on a server, the tnsnames.ora file had not been configured.

To add to my frustration, I didn't have rights to edit the file and needed to test database connectivity and verify that a view I had created to use the USERENV/HOST variable via the SYS_CONTEXT function was able to successfully differentiate inbound requests and return the appropriate data based on originating server - which of course meant I needed to test from that server in particular.

I discovered that SQL*PLUS does allow for a tnsnames-less connection by directly specifying the entry when connecting.  Below is an example of what I used to connect from the command line on the server:

sqlplus user/pw@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=myhostname.domain.com)(PORT=1521)))(CONNECT_DATA=(SID=proddb)))

This allowed me to get around not having admin rights for the Oracle client installation.  Too cool.

Searching Oracle View Source w/ Wildcard Queries

Last week I actually had the opportunity to get my hands dirty and in doing so ran into an annoyance with searching through source our custom application views within Oracle.  Because it took me longer than my customary several minute all-powerful Google search to come up with an answer that satisfied me, I decided to write a blog entry to keep track of what I did (for future reference) as well as to help others that might come across the same frustration I did.

As any respectable PL/SQL developer knows, packages and stored procedure source can be searched using a VARCHAR typed column that is provided in the ALL_SOURCE and USER_SOURCE views that are owned by SYS.  Source for views however is only exposed via a LONG column in the SYS owned DBA_VIEWS view.  The LONG data type of course is unfortunately not searchable using wildcard predicates.  For example, even the simplest query like the one depicted below that is searching for TO_CHAR function references across all views in the database returns a data type inconsistency error:

QUERY: SELECT * FROM DBA_VIEWS DBA_VIEWS WHERE TEXT '%TO_CHAR%'

ERROR: ORA-00932-inconsistent datatypes expected number got long.

I found many solutions out there that describe creating a copy of DBA_VIEWS but essentially converting the LONG column using the TO_LOB function when the new table is built.  For example, as described here you could build the copy and then query as follows:

        insert into dba_vw
      select owner, view_name, text_length,
      to_lob(text),type_text_length, type_text, 
      oid_text_length,oid_text, view_type_owner, 
      view_type, superview_name 
    from dba_views;
     
    select *from dba_vwwhere text like '%TO_CHAR%';

I didn't like this solution though as I hate creating tables as that requires extra work to get to the solution that I really want and also have to remember to drop the table after I'm done with it.  After much searching I was able to find another solution referenced here that the DBMS_METADATA.GET_DDL function to convert the data in a sub-select thereby making it available to be queried using a wildcard predicate.

    select view_name
    from ( select view_name, 
                  dbms_metadata.get_ddl ('VIEW', view_name) text
           from user_views where rownum <= 100000)
    where upper (text) like '%TO_CHAR%';

While I've found a few other references to the solution above, by in large part most of the proposed solutions go with the create a new table approach which again I didn't like and thus why I'm posting it here so that I can find it again easily and so it might benefit others as well.