Re: Urgent - Query Optimization

Inder.Singh_at_gecits.ge.com wrote:
>
> Hi,
> I have a report which was taking 48 minutes. So I added rule hint in that
> now it is taking 14-15 min but, still it's not acceptable.. Is there any way
> I can elimintae these nested loops or Can I replace them with hash joins. or
> is there any other way to optimize this query. I don't have always_semi_join
> to hash_joins in the init.ora file and I don't want to set that in init.ora.
> Is there any session level setting like any rule which enforces the hash
> join.
>
> Regards,
> SELECT STATEMENT Optimizer=HINT: RULE
> SORT (UNIQUE)
> NESTED LOOPS
> NESTED LOOPS
> NESTED LOOPS
> NESTED LOOPS
> NESTED LOOPS
> NESTED LOOPS
> NESTED LOOPS
> NESTED LOOPS
> NESTED LOOPS
> NESTED LOOPS
> NESTED LOOPS
> NESTED LOOPS
> NESTED LOOPS
> NESTED LOOPS
> NESTED LOOPS
> NESTED LOOPS
> NESTED LOOPS
> NESTED LOOPS
> TABLE ACCESS (FULL) OF
> TABLE_EMP_CERT
> TABLE ACCESS (BY INDEX ROWID) OF
> TABLE_CERTIFICATION
> INDEX (UNIQUE SCAN) OF
> CERTIFICATION_OBJINDEX (UNIQUE)
> TABLE ACCESS (BY INDEX ROWID) OF
> TABLE_PART_CLASS
> INDEX (UNIQUE SCAN) OF
> PART_CLASS_NAME_INDEX (UNIQUE)
> TABLE ACCESS (BY INDEX ROWID) OF
> TABLE_EMPLOYEE
> INDEX (UNIQUE SCAN) OF
> EMPLOYEE_OBJINDEX (UNIQUE)
> TABLE ACCESS (BY INDEX ROWID) OF
> TABLE_PART_NUM
> INDEX (RANGE SCAN) OF
> IND_PART_NUM2PART_CLASS (NON-UNIQUE)
> TABLE ACCESS (BY INDEX ROWID) OF
> TABLE_MOD_LEVEL
> INDEX (RANGE SCAN) OF
> IND_PART_INFO2PART_NUM (NON-UNIQUE)
> TABLE ACCESS (BY INDEX ROWID) OF
> TABLE_SITE_PART
> INDEX (RANGE SCAN) OF
> IND_SITE_PART2PART_INFO (NON-UNIQUE)
> TABLE ACCESS (BY INDEX ROWID) OF TABLE_CASE
> INDEX (RANGE SCAN) OF IND_CASE_PROD2SITE_PART
> (NON-UNIQUE)
> TABLE ACCESS (BY INDEX ROWID) OF TABLE_SITE
> INDEX (UNIQUE SCAN) OF SITE_OBJINDEX (UNIQUE)
> TABLE ACCESS (BY INDEX ROWID) OF TABLE_ADDRESS
> INDEX (UNIQUE SCAN) OF ADDRESS_OBJINDEX (UNIQUE)
> TABLE ACCESS (BY INDEX ROWID) OF
> TABLE_X_CASE_INFORMATION
> INDEX (RANGE SCAN) OF CASE_OBJIDINDEX (NON-UNIQUE)
> TABLE ACCESS (BY INDEX ROWID) OF TABLE_DEMAND_HDR
> INDEX (RANGE SCAN) OF IND_DEMANDHDR_CASEINFO2CASE
> (NON-UNIQUE)
> TABLE ACCESS (BY INDEX ROWID) OF TABLE_DEMAND_DTL
> INDEX (RANGE SCAN) OF IND_DEMAND_DTL2DEMAND_HDR
> (NON-UNIQUE)
> TABLE ACCESS (BY INDEX ROWID) OF TABLE_CONDITION
> INDEX (UNIQUE SCAN) OF CONDITION_OBJINDEX (UNIQUE)
> TABLE ACCESS (BY INDEX ROWID) OF TABLE_GBST_ELM
> INDEX (UNIQUE SCAN) OF GBST_ELM_OBJINDEX (UNIQUE)
> TABLE ACCESS (BY INDEX ROWID) OF TABLE_MOD_LEVEL
> INDEX (UNIQUE SCAN) OF MOD_LEVEL_OBJINDEX (UNIQUE)
> TABLE ACCESS (BY INDEX ROWID) OF TABLE_PART_NUM
> INDEX (UNIQUE SCAN) OF PART_NUM_OBJINDEX (UNIQUE)
> TABLE ACCESS (BY INDEX ROWID) OF TABLE_SITE
> INDEX (UNIQUE SCAN) OF SITE_OBJINDEX (UNIQUE)
> TABLE ACCESS (BY INDEX ROWID) OF TABLE_SITE
> INDEX (UNIQUE SCAN) OF SITE_OBJINDEX (UNIQUE)
>
> --

What you are posting here is hardly helpful (in terms of uselessness, it comes second to 'hallo, here are the contents of V$WAITSTAT and events plus my init.ora, please rewrite the query'). When you want to optimize a query, it's better to start with the query, vague hints (not in the Oracle acceptance) about volumes, indexes, number of rows expected and selectivity of criteria.

-- 
HTH,

Stephane Faroult
Oriole Software
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Stephane Faroult
  INET: sfaroult_at_oriole.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).