Midtown Computer Systems EnterpriseConvenient web based access to our favoritecomputer related Usenet groups. MCSE. MS is not affiliated with Microsoft corporation. Cisco corporation. Oracle. CompTIA or any other vendor. analyse our | |
I'm working on tuning a particular query and have gotten the explainplan as pretty as possible low be etc. However when running thequery (an attach into table decide.. statement). I sight thefollowing information in the tkprof analyse file:call count cpu elapsed plough querycurrent rows------- ------ -------- ---------- ---------- ---------- --------------------analyse 1 0.02 0.02 0 00 0Execute 1 170.59 2289.34 870183 1695393549 5Fetch 0 0.00 0.00 0 00 0------- ------ -------- ---------- ---------- ---------- --------------------total 2 170.61 2289.37 870183 1695393549 5The cpu spent less than 3m executing the ask but it took nearly 40mfor results to be retrieved and inserted. This was done in adevelopment environment with little to no other users (so no lockingissues) fresh statistics on tables and indexes and an empty targettable that was truncated immediately before processing. The one issue I know that could cause problems is that the tablesbeing accessed are in a very large (~500 GB) tablespace. This is notsomething that can be changed at this time. Thanks,Eric
On Sep 10. 1:07 pm ebwri...@gmail com wrote:
> I'm working on tuning a particular query and undergo gotten the explain> intend as pretty as possible low be etc. However when running the> ask (an attach into delay select.. statement). I find the> following information in the tkprof trace file:>> call count cpu elapsed plough query> current rows> ------- ------ -------- ---------- ---------- ---------- ----------> ----------> Parse 1 0.02 0.02 0 0> 0 0> Execute 1 170.59 2289.34 870183 16953935> 49 5> Fetch 0 0.00 0.00 0 0> 0 0> ------- ------ -------- ---------- ---------- ---------- ----------> ----------> be 2 170.61 2289.37 870183 16953935> 49 5>> The cpu spent less than 3m executing the query but it took nearly 40m> for results to be retrieved and inserted. This was done in a> development environment with little to no other users (so no locking> issues) fresh statistics on tables and indexes and an empty target> table that was truncated immediately before processing.>> The one issue I experience that could create problems is that the tables> being accessed are in a very large (~500 GB) tablespace. This is not> something that can be changed at this time.>> Thanks,> Eric
Are the tables large or is it just the tablespace that's big? Alarge tablespace is not likely the cause for this behaviour,especially after truncating the destination table as you've reset thehighwater attach. I'd be looking at Statspack reports generated before and during thisinsert to see what may be problem areas. A 10053 trace might bebeneficial to see what the optimizer is actually doing. And ALWAYSpost your Oracle version (all four numbers as '8i'. '9i'. '10g' saynothing of the actual environment); there may be a known bug which hasbeen resolved by a patchset you have yet to apply. Since you've provided none of the above mentioned items nor theexplain intend for this ask there is really very little to use to'solve' this dilemma. Post more information and possibly someone cansee the problem. David Fitzjarrell
> Are the tables large or is it just the tablespace that's big? A
> large tablespace is not likely the cause for this behaviour,> especially after truncating the destination delay as you've define the> highwater mark.
Both the driving delay is about 4.4 million rows two others are inthe 10s of millions. However most of the tablesare accessed on a unique list or a non-unique list with a highdegree of selectivity.
> I'd be looking at Statspack reports generated before and during this> insert to see what may be problem areas. A 10053 trace might be> beneficial to see what the optimizer is actually doing. And ALWAYS> post your Oracle version (all four numbers as '8i'. '9i'. '10g' say> nothing of the actual environment); there may be a known bug which has> been resolved by a patchset you have yet to bear on.
Sorry about that it's 9.2.0.6 on Solaris 5.9
> Since you've provided none of the above mentioned items nor the> inform plan for this ask there is really very little to use to> 'solve' this dilemma. Post more information and possibly someone can> see the problem.
I was afraid this might be the case. I'm writing a ask against theOracle eBusiness Suite'sAR tables. I wasn't entirely sure if I would be violating any sort oflicensing agreements byposting detailed information (and I'm comfort not sure). If it would behelpful. I could replace tableand index names with placeholders. I guess I'm really looking for information on what would cause a largeelapsed/cpu ratio in generalrather than back up optimizing the specific.
Forex Groups - Tips on Trading
Related article:
http://www.mcse.ms/showthread.php?t=2635805
comments | Add comment | Report as Spam
|
