What version of Oracle and operating system/type are you using. Is this
the Clarify help desk application?
If it is Clarify, you can contact their support and open a case with them.
They have some additional indexes, etc. that they recommend that helped us
quite a bit with our Clarify application. I also created quite a few
histograms as our data was very skewed. I made quite a few changes to the
TABLE_CASE table adding both additional indexes and histograms.
I'd recommend starting first with Clarify to see what they recommend.
Make sure you have analyzed all your tables and indexes if you are supposed
to be using cost-based optimizer. Then I would look at every table in this
query and all of the columns in the where clause. Do a row count of
these tables and look at the data distribution on the columns shown in the
where clause. If the non-bind-variable data in the column shown in the
where clause is highly skewed, and especially if you are querying for
values that are a very small percentage of all values, I'd try adding a
histogram.
I am having a very difficult day today with our QA box so I can't offer a
lot more details but hopefully this can get you started. If you are still
having problems Monday, I can possibly provide more assitance at that time.
I've successfully done extensive performance tuning on our Clarify
database.
Cherie Machler
Oracle DBA
Gelco Information Network
Inder.Singh@gec
its.ge.com To: Multiple recipients of list ORACLE-L
<[EMAIL PROTECTED]>
Sent by: cc:
[EMAIL PROTECTED] Subject: Urgent - Query Optimization
m
06/21/02 10:53
AM
Please respond
to ORACLE-L
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)
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
