EXPERT RESPONSE
This is a loaded question. There are training classes designed to go over how to interpret tkprof and explain plan. I strongly recommend you get hold of a tuning book and go over them.
Tkprof actually lets you analyze trace file that dumps all I/O information regarding a particular SQL statement. On the other hand, explain plan tells you how the Oracle cost-based optimizer is planning to run your query. The result of the explain plan will vary if the schema that you are using the SQL statement against is analyzed or not.
Therefore, please ensure that you analyze the schema using DBMS_STATS.GATHER_SCHEMA_STATS procedure.
You can run the following query against the plan table to see exactly what your query does. Please ensure you set your query for the plan table using the following command.
EXPLAIN PLAN SET STATEMENT_ID = 'ASIF_HALIYAL_QUERY_1'
FOR <YOUR SQL STATEMENT>
Now querying the plan table using the following SQL statement will let you know exactly what the CBO is planning to do with your SQL. Many things to look at for instance if the index you want the query to use is being used, is there a full table scan (note sometimes a full table scan may be desirable) and if so why, etc.
SELECT ID, PARENT_ID, cardinality "Rows",
LPAD(' ', 1*(LEVEL-1)) ||
LTRIM(' ', 1*(LEVEL-1)) ||
LTRIM(RTRIM(OPERATION)) || ' ' ||
LTRIM(RTRIM(OPTIONS)) || ' ' ||
LTRIM(RTRIM(OBJECT_NAME)) || ' ' ||
LTRIM(RTRIM(REMARKS)) EXECUTION_PLAN
FROM PLAN_TABLE
WHERE STATEMENT_ID = 'ASIF_HALIYAL_QUERY_1'
CONNECT BY PRIOR ID = PARENT_ID
AND PRIOR STATEMENT_ID = STATEMENT_ID
AND STATEMENT_ID = 'ASIF_HALIYAL_QUERY_1'
START WITH ID = 0 AND STATEMENT_ID = 'ASIF_HALIYAL_QUERY_1'
ORDER BY POSITION
|
