Query Tunning

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.

Code:

---

SELECT ...
  FROM ( SELECT ...
          FROM mytable
          WHERE column  = constant AND
                column2 In ()etc) table_name,

---

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
  LEFT OUTER JOIN tablec ON
      join clause

---

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. :p

Define

Quote:

---

This query is NOT comin out AT ALL...

---

Is this PeopleSoft? NVision perhaps?

Quote:

---

Originally posted by stmontgo
Define

Is this PeopleSoft? NVision perhaps?

---

I should have guessed this was some sort of sappy application. :rolleyes:

Quote:

---

Originally posted by gandolf989
I should have guessed this was some sort of sappy application. :rolleyes:

---

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

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

---

I'm not qualified to make that kind of guess with any accuracy. According to www.dba-oracle.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.

---

And according to www.orafaq.com:

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.

---

But why does this query suck? :rolleyes:

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

Quote:

---

And according to www.orafaq.com:

---

oh boo hoo! a little alter session never hurt nobody

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

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...

---

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.

Its a Business Objects Query

Quote:

---

Originally posted by balajiyes
Its a Business Objects Query

---

You chould call them and tell them to fix their query.
But there is probably another hundred queries like like that one. :rolleyes: