Optimizing a query selecting from 10 tables

Yes, Oracle supplies trace tools as part of the database server. The first is the ability to trace a database session, and there are several ways to do so. The simplest is to issue the SQL statement ALTER SESSION SET SQL_TRACE=TRUE. Oracle8i and Oracle9i offer the DBMS_SUPPORT package, which enables the capture of wait event information. In Oracle10g, DBMS_MONITOR is the preferred means of tracing. The TKPROF utility formats the raw trace files produced by any of these methods. You should consult the documentation, particularly the Performance Tuning Guide.

By the way, a query that joins 10 tables may be a tough one to optimize.