Re: Full table scan despite appropriate indexes

On Feb 8, 5:53 pm, "aravind.ka..._at_gmail.com" <aravind.ka..._at_gmail.com> wrote:

> From what I learnt here, the key is the trace file.
> That will help me understand in what order Oracle is using the tables
> and joins.

Aravind,

FYI, there are ways to get at the trace file contents without having direct access to the Oracle server's filesystem (where the trace files are written out). See:

    http://www.dbazine.com/oracle/or-articles/still1     "A Novel Use for Oracle External Tables"     Jared Still, April 13, 2005

    http://www.adp-gmbh.ch/blog/2006/02/05.php     "On reading trace files with PL/SQL"     Rene Nyffenegger, February 5, 2006

Jared's approach uses external tables, while Rene's uses direct access via the UTL_FILE package. They both seem to work, but Rene's is more complete in that it has code to automatically identify the trace file for the session (usul. the cumbersome part). If you can get your DBA to set up the TRACER user using Nyffenegger's code and grant you access to it, you'll all be all set as far as being able to set up traces and retrieve the contents of the trace files, all w/out needing direct access to a production server's filesystem (which the DBA's like to protect as you can imagine).

HTH,
JH Received on Fri Feb 09 2007 - 12:27:21 CST