Home » RDBMS Server » Performance Tuning » Query not running in oracle 10.2.0.3 (Oracle, 10.2.0.3, windows)
Query not running in oracle 10.2.0.3 [message #297577] Fri, 01 February 2008 07:52 Go to next message
shankar muthusamy
Messages: 21
Registered: November 2006
Location: singapore
Junior Member
Hi,

1. Previously procedure and queries inside are successfully running in oracle 10.2.0.0 we apply the patch 10.2.0.3 after that query is not running it takes huge memory and takes more time also.

Please let know if there is any difference in two versions for executing query.

2. One complex query is running perfectly in 9i, we upgrade to 11G after that query is not running, query have more inline views and more tables.

Please let me know, if there is any special tuning needed in higher versions.

Thanks and Regards
Shankar.M
Re: Query not running in oracle 10.2.0.3 [message #297578 is a reply to message #297577] Fri, 01 February 2008 07:58 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
My crystal ball is broken. Sad

Regards

Raj

P.S : Please read the forums guidelines before you post.
Re: Query not running in oracle 10.2.0.3 [message #297579 is a reply to message #297577] Fri, 01 February 2008 08:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ 10.2.0.0 never existed, so there may be many differences or not, is this a logic puzzle?
There are differences between patchsets, of course.

2/ Is this the same query or not?

Query tuning process is the same in all versions.
Read How to Identify Performance Problem and Bottleneck .

Regards
Michel
Re: Query not running in oracle 10.2.0.3 [message #297582 is a reply to message #297579] Fri, 01 February 2008 08:18 Go to previous messageGo to next message
shankar muthusamy
Messages: 21
Registered: November 2006
Location: singapore
Junior Member
Hi

1. Sorry its 10.2.0.1.0

2. It same query

select count(1)
FROM invhd sinv,
invhd pinv,
shipdt ship,
dealdt pdealdt,
dealhd pdealhd,
dealdt sdealdt,
dealhd sdealhd,
process proc
WHERE ship.ship_flg = 'C'
AND sinv.ord_no = ship.linkord_no
AND ship.ord_no = SUBSTR (pinv.ord_no, 1, 12)
AND ship.ship_no = SUBSTR (pinv.ord_no, 14, 2)
AND SUBSTR (ship.ord_no, 1, 10) = pdealdt.deal_no
AND SUBSTR (ship.ord_no, 12, 1) = pdealdt.line
AND pdealdt.deal_no = pdealhd.deal_no
AND SUBSTR (ship.linkord_no, 1, 10) = sdealdt.deal_no
AND SUBSTR (ship.linkord_no, 12, 1) = sdealdt.line
AND sdealdt.deal_no = sdealhd.deal_no
AND NVL (sinv.accounted, 'N') = 'N'
AND NVL (sinv.posted_flg, 'N') = 'Y'
AND sdealhd.pr_code = proc.pr_code
AND sdealhd.prcat_code = proc.prcat_code
AND sdealhd.unit_code = proc.unit_code
AND TO_CHAR (NVL (sinv.value_dt, sinv.inv_dt), 'RRRRMMDD') <=
TO_CHAR (proc.process_date, 'RRRRMMDD')



wether substr will create any problem

Thanks
Shankar.M

Re: Query not running in oracle 10.2.0.3 [message #297595 is a reply to message #297582] Fri, 01 February 2008 11:34 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Quote:
AND TO_CHAR (NVL (sinv.value_dt, sinv.inv_dt), 'RRRRMMDD') <=
TO_CHAR (proc.process_date, 'RRRRMMDD')

Is there any particular reason why you convert the date to string and comparing it ?

a) No. of records in the table
b) Are the stats updated
c) Any indexes on any of those tables
d) Explain plan for your query

Without these information it is really difficult to advise how to fine tune or find the problem.

Regards

Raj
Re: Query not running in oracle 10.2.0.3 [message #297696 is a reply to message #297577] Sat, 02 February 2008 10:50 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
There are several questions that need asking about this query that you should think about.

1) many of us have seen often the use of counts to do existential queries. So it is prudent to ask, do you really need to know how many rows there are that meet your criteria, or do you only need to know if there is at least one. If the answer is you need only know there is at least one then you can recast your count as an existential query and it will go real fast.

2) along the same lines, there are mean "dead ends" in your query where clause. you do a lot of joining but in the end never actually look at any of the real data that any of these joins lead you to. This is of course a reflection of your count, but it suggests, is it necessary to actually do all these joins. The likelyhood is yes it is but you should know this to be true if you wish to get the correct answer.

3) out of curiosity, is this code you wrote, or are you inheriting this from developers that no longer work on your project?

4) as a comment, your database design leaves much to be desired as can be noted by the fact that there are many fields for which you are forced to do substring lookups on. You should be able to describe what it is you are substriging. if this is a new system you are building the you should redo your data model so that these substr operations are not required.

5) one last thing. When the oracle cost based optimizer gets it wrong, it is almost always because it has not computed a correct number of rows being returned for one or more steps on a query plan. this is most often because statistics have not been collected on the data. I suggest you collect stats on your schema (compile, not estimate, and cascade=>true to make sure you get indexes). then try your query again.

OK, as has been indicated, you have posted little to go on. But as was also noted, the tuning process is generally the same most of the time so here is one idea.

If this query is very important to you, you can consider constructing indexes specifically to make it go fast. You would have two ideas in mind here:

1) you want your query plan to not visit any tables, only indexes
2) you want to try and elimintate as much data as possible up front in your first two or three rowsource accesses

Here are a suggested set of indexes. Examine them and try to understand how these indexes might achieve these two goals.

create index sinv_i1 on invhd (nvl(accounted,'N'),nvl(posted_flg,'N'),ord_no,value_dt,inv_dt)
/

create index ship_i1 on shipdt (ship_flg,linkord_no,ord_no,ship_no)
/

create index pinv_i1 on invhd (Substr(Ord_No,1,12),Substr(Ord_No,14,2))
/

create index pDealdt_i1 on dealdt (Deal_No,Line)
/

create index pDealhd_i1 on ealhd (deal_no)
/

create index sDealdt_i1 on Dealdt (deal_no,line)
/

create index sDealhd_i1 on Dealhd (deal_no,pr_code,prcat_code,unit_code)
/

create index Proc_i1 on Process (pr_code,prcat_code,unit_code,To_char(Process_Date,'RRRRMMDD'))
/


Cowardly disclaimer: if any of the ideas suggested here hose you, don't come looking for me or anyone else here. You should be doing this work with your DBA at your side. You DBA is an asset you should be exploiting and learning from.

Good luck, I leave it to you to find any syntax errors in this stuff. Kevin
Re: Query not running in oracle 10.2.0.3 [message #297810 is a reply to message #297595] Sun, 03 February 2008 21:35 Go to previous messageGo to next message
shankar muthusamy
Messages: 21
Registered: November 2006
Location: singapore
Junior Member
Hi,

1. Thanks you all, i got the solution for 10g (10.2.0.3).

2. Still i have 2nd problem, query which working fine 9i not running in 11g database.

Thanks and Regards
Shankar.M

Re: Query not running in oracle 10.2.0.3 [message #298021 is a reply to message #297577] Mon, 04 February 2008 11:12 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
so, you want to clue us as to what your solution for 10 was?

Kevin
Previous Topic: select query taking long time
Next Topic: db job
Goto Forum:
  


Current Time: Fri Dec 09 09:47:26 CST 2016

Total time taken to generate the page: 0.10127 seconds