Home » RDBMS Server » Performance Tuning » Query Optimization
Query Optimization [message #64781] Wed, 28 January 2004 20:39 Go to next message
PagInit
Messages: 9
Registered: January 2004
Junior Member
OPERATION OPTIONS OBJECT_NAME
------------------------------ --------------- --------------------
SELECT STATEMENT
SORT GROUP BY
CONCATENATION
NESTED LOOPS
NESTED LOOPS
HASH JOIN
TABLE ACCESS FULL DB_MASTER
HASH JOIN
TABLE ACCESS BY INDEX ROWID FISCAL_WEEK
INDEX RANGE SCAN I_FISCAL_MONTH
TABLE ACCESS BY GLOBAL INDEX INVOICE_LINE

OPERATION OPTIONS OBJECT_NAME
------------------------------ --------------- --------------------
ROWID

NESTED LOOPS
INLIST ITERATOR
TABLE ACCESS BY INDEX ROWID CUSTOMER_MASTER
INDEX RANGE SCAN IU_CUST_CODE
INDEX RANGE SCAN I_INVOICE_BILL_TO
TABLE ACCESS BY INDEX ROWID ITEM_MASTER
INDEX UNIQUE SCAN PK_ITEM_MASTER
TABLE ACCESS BY INDEX ROWID CUSTOMER_MASTER
INDEX UNIQUE SCAN PK_CUSTOMER_MASTER

OPERATION OPTIONS OBJECT_NAME
------------------------------ --------------- --------------------
NESTED LOOPS
NESTED LOOPS
NESTED LOOPS
NESTED LOOPS
NESTED LOOPS
TABLE ACCESS FULL CUSTOMER_MASTER
TABLE ACCESS BY GLOBAL INDEX INVOICE_LINE
ROWID

INDEX RANGE SCAN I_INVOICE_BILL_TO
TABLE ACCESS BY INDEX ROWID CUSTOMER_MASTER

OPERATION OPTIONS OBJECT_NAME
------------------------------ --------------- --------------------
INDEX UNIQUE SCAN PK_CUSTOMER_MASTER
TABLE ACCESS BY INDEX ROWID FISCAL_WEEK
INDEX UNIQUE SCAN PK_FISCAL_WEEK
TABLE ACCESS BY INDEX ROWID ITEM_MASTER
INDEX UNIQUE SCAN PK_ITEM_MASTER
TABLE ACCESS BY INDEX ROWID DB_MASTER
INDEX UNIQUE SCAN PK_DB_MASTER
Re: Query Optimization [message #64786 is a reply to message #64781] Fri, 30 January 2004 13:13 Go to previous messageGo to next message
ilver
Messages: 50
Registered: January 2004
Member
Hi,

Even you don't make your question absolutely clear, I dare to give you two(tree) hints:
Use explain plan to get a better overview of the execution plan.
(Set up extended SQL trace to get timed statistics)
And be aware of "things in the query that won't show up in the execution plan"

/ilver
Re: Query Optimization [message #64800 is a reply to message #64786] Thu, 05 February 2004 09:29 Go to previous message
Michel Bartov
Messages: 35
Registered: February 2003
Member
I posted a GUI tool to manage and tune the Oracle database. One of the many functions is to analyze an SQL statement, generating the explain plan and allows you to try various scenarios (including hints) to find the best execution plan.
The link is http://www.barsoft.net It is a free trial, and I will appreciate any kind of feedback/suggestions.
M.
Previous Topic: Analyze table and Index usage by CBO
Next Topic: Criteria for Initial Extents and Next Extents of Blank Table and Having Rows in above 8 Lakhs
Goto Forum:
  


Current Time: Fri Mar 29 09:39:25 CDT 2024