Hi All,
I have to tune this query. I have no clue how\where to start this.
This query is NOT comin out AT ALL...
Please give ur valuable outputs
Attached my the query and plan
Many Thanks in advance
Printable View
Hi All,
I have to tune this query. I have no clue how\where to start this.
This query is NOT comin out AT ALL...
Please give ur valuable outputs
Attached my the query and plan
Many Thanks in advance
Try using subqueries.
Also try joining together tables that have an inner join and then join to that the outer join tables.Code:SELECT ...
FROM ( SELECT ...
FROM mytable
WHERE column = constant AND
column2 In ()etc) table_name,
You can also do a subquery in the outer join. My strategy is to use subqueries to eliminate rows and can be eliminated before doing a join of any kind, and to put the outer joins at the end. Doing so should cut down on the nested loops which tend to be costly, and hopefully replace them with hash joins. IMHO Hash is best. :pCode:SELECT ...
FROM ( SELECT ...
FROM mytable
WHERE column = constant AND
column2 In ()etc) table_name
LEFT OUTER JOIN tablec ON
join clause
Define
Is this PeopleSoft? NVision perhaps?Quote:
This query is NOT comin out AT ALL...
I should have guessed this was some sort of sappy application. :rolleyes:Quote:
Originally posted by stmontgo
Define
Is this PeopleSoft? NVision perhaps?
Yes follow along closely! Let's see what happens next. My money is on PS Financials with NVISION Oracle 8i with optimizer_max_permutations=80000 but that's just a wild guessQuote:
Originally posted by gandolf989
I should have guessed this was some sort of sappy application. :rolleyes:
I'm not qualified to make that kind of guess with any accuracy. According to www.dba-oracle.com:Quote:
Originally posted by stmontgo
Yes follow along closely! Let's see what happens next. My money is on PS Financials with NVISION Oracle 8i with optimizer_max_permutations=80000 but that's just a wild guess
And according to www.orafaq.com:Quote:
The _optimizer_search_limit and _optimizer_max_permutations parameters work together, and the optimizer will generate possible table joins permutations until the value specified by _optimizer_search_limit or _optimizer_max_permutations is exceeded. When the optimizer stops evaluating table join combinations, it will choose the combination with the lowest cost. For example, queries joining nine tables together will exceed the optimizer_search_limit but still may spend expensive time attempting to evaluate all 362,880 possible table join orders (nine factorial) until the optimizer_max_permutations parameter has exceeded its default limit of 80,000 table join orders.
But why does this query suck? :rolleyes:Quote:
NOTE: This is an internal Oracle parameter. Do NOT use it unless instructed to do so by Oracle Support. Playing with this parameter may be harmful.
Hi Guys thanks for ur effort
My boss gave this query and wanted me to tune. I don't understand any thing...
Its
optimizer_max_permutations = 2000
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
PL/SQL Release 9.2.0.4.0 - Production
CORE 9.2.0.3.0 Production
TNS for HPUX: Version 9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0 - Production
oh boo hoo! a little alter session never hurt nobodyQuote:
SQL> alter session set optimizer_max_permutations=1999;
Session altered.
SQL>
alas he has not answered my questions on the configuration
> Is this PeopleSoft
> What do you mean by not coming out at all? Do you wait 2 minutes and ctrl+c, 20 minutes
Then it would benefit you to rewrite it using the advice I gave you above, and then test that query and see if the explain plan changed. By the way you are using manual undo and archivelog is turned off. You might want to do some research and change to automatic undo and turn on archivelog.Quote:
Originally posted by balajiyes
Hi Guys thanks for ur effort
My boss gave this query and wanted me to tune. I don't understand any thing...