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  |
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 #297582 is a reply to message #297579] |
Fri, 01 February 2008 08:18  |
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 |
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 |
 |
Kevin Meade
Messages: 2103
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
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Tue Nov 26 20:54:19 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
