| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Urgent - Query Optimization
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_at_gec
its.ge.com To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
Sent by: cc:
root_at_fatcity.co 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: Inder.Singh_at_gecits.ge.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-LReceived on Fri Jun 21 2002 - 12:48:30 CDT
(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: Cherie_Machler_at_gelco.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).
 |
 |