Home » SQL & PL/SQL » SQL & PL/SQL » Query Tuning
Query Tuning [message #235117] Fri, 04 May 2007 02:17 Go to next message
atulrsingh
Messages: 55
Registered: November 2006
Location: Mumbai
Member
Hi All,

I am using RBO in my query and the query is taking 1 sec but if i remove the hint it takes 5 sec.

Now i want to use CBO. pls suggest some solution for using CBO


SELECT DISTINCT pe_location_code,
ENTY.enty_name pe_location_name,
pe_coverage_type,
pe_pl_slno,
cov_type.ans_order_no
FROM POLICY_EXPOSURE,
(
SELECT *
FROM ANSWER_TABLE
WHERE gtablecode1 ='COVERAGE_TYPE'
AND gtablecode2 != 'ZZZZZZZZZZ') cov_type ,
(
SELECT /*+ INDEX(ENTITY_ADDRESS_MASTER ENTY_TABLE_IDX) */
enty_code, enty_name
FROM ENTITY_ADDRESS_MASTER
WHERE enty_status = 'Y'
AND enty_group_code = 'LOCATION' ) ENTY
WHERE pe_session_no = '31078'
AND pe_coverage_type ='INVCOL'
AND pe_location_code = ENTY.enty_code
ORDER BY pe_pl_slno ,
cov_type.ans_order_no ;
Re: Query Tuning [message #235118 is a reply to message #235117] Fri, 04 May 2007 02:18 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
analyze your tables.
Re: Query Tuning [message #235121 is a reply to message #235117] Fri, 04 May 2007 02:24 Go to previous messageGo to next message
atulrsingh
Messages: 55
Registered: November 2006
Location: Mumbai
Member
I am migrating from 9i to 10G

And we are analysing the query which are having the RBO used in the queries.

so we need to remove those RBO hints.

so only analysing the tables will sort the performance issue or there some more alternate solutions for 10G

Thnx
Re: Query Tuning [message #235176 is a reply to message #235121] Fri, 04 May 2007 04:16 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Only after you analyzed your tables & indexes you can say anything about what the CBO does.
Previous Topic: How to find Oracle server or Oracle Client
Next Topic: Problem with the UTL_FILE
Goto Forum:
  


Current Time: Sat Dec 10 08:56:34 CST 2016

Total time taken to generate the page: 0.07132 seconds