Home » RDBMS Server » Performance Tuning » TOAD / Oracle qeps
TOAD / Oracle qeps [message #297004] Tue, 29 January 2008 22:13 Go to next message
rgoldwrx
Messages: 11
Registered: April 2006
Junior Member
NVision / Trees and Oracle qeps

Hi, I have a query that I see in TOAD in the session browser. I copy the query into the sql session and the plan
is different between the 2. The one in session browser runs really badly. Its hard to tune a query when in sql
when it uses a different plan when it runs in Peoplesoft. Anyone know why this occurs:
The query runs for an hour and times out in Peoplesoft (as per the session browser plan), and takes about 10 seconds when
run in the sql editor. One factor is that data is probably inserted into the treetables each time the query is run. The query in sql editor doesn't tend to pick up data, so it must be uncommitted at the time.

Query:
SELECT  a.accounting_period, a.ACCOUNT, SUM (a.posted_total_amt)
    FROM  pstreeselect10 l2,  pstreeselect10 l1,  ps_ledger a, pstreeselect05 l
   WHERE a.ledger = 'ACTUALS'
     AND a.fiscal_year = 2008
     AND a.accounting_period BETWEEN 0 AND 998
     AND l2.selector_num = 307028
     AND a.ACCOUNT = l2.range_from_10
     AND l.selector_num = 308606
     AND a.business_unit = l.range_from_05
     AND l1.selector_num = 309876
     AND a.deptid = l1.range_from_10
     AND a.currency_cd = 'AUD'
     AND a.statistics_code = ' '
GROUP BY a.accounting_period, a.ACCOUNT;

Plan in Session browser:
Plan
SELECT STATEMENT  ALL_ROWSCost: 8  							
	11 SORT GROUP BY  Cost: 8  Bytes: 73  Cardinality: 1  						
		10 TABLE ACCESS BY INDEX ROWID TABLE SYSADM.PS_LEDGER Cost: 3  Bytes: 42  Cardinality: 1  					
			9 NESTED LOOPS  Cost: 7  Bytes: 73  Cardinality: 1  				
				7 MERGE JOIN CARTESIAN  Cost: 4  Bytes: 31  Cardinality: 1  			
					4 MERGE JOIN CARTESIAN  Cost: 3  Bytes: 20  Cardinality: 1  		
						1 INDEX RANGE SCAN INDEX SYSADM.PSAPSTREESELECT10 Cost: 2  Bytes: 10  Cardinality: 1  	
						3 BUFFER SORT  Cost: 1  Bytes: 10  Cardinality: 1  	
							2 INDEX RANGE SCAN INDEX SYSADM.PSAPSTREESELECT10 Cost: 1  Bytes: 10  Cardinality: 1  
					6 BUFFER SORT  Cost: 3  Bytes: 11  Cardinality: 1  		
						5 INDEX RANGE SCAN INDEX (UNIQUE) SYSADM.PS_PSTREESELECT05 Cost: 1  Bytes: 11  Cardinality: 1  	
				8 INDEX RANGE SCAN INDEX SYSADM.PSDLEDGER Cost: 2  Cardinality: 1  			

Plan in sql editor:
Plan
SELECT STATEMENT  ALL_ROWSCost: 8  Bytes: 73  Cardinality: 1  						
	9 SORT GROUP BY  Cost: 8  Bytes: 73  Cardinality: 1  					
		8 NESTED LOOPS  Cost: 7  Bytes: 73  Cardinality: 1  				
			6 NESTED LOOPS  Cost: 6  Bytes: 63  Cardinality: 1  			
				4 NESTED LOOPS  Cost: 5  Bytes: 52  Cardinality: 1  		
					2 TABLE ACCESS BY INDEX ROWID TABLE SYSADM.PS_LEDGER Cost: 4  Bytes: 42  Cardinality: 1  	
						1 INDEX RANGE SCAN INDEX SYSADM.PSCLEDGER Cost: 3  Cardinality: 1  
					3 INDEX RANGE SCAN INDEX (UNIQUE) SYSADM.PS_PSTREESELECT10 Cost: 1  Bytes: 10  Cardinality: 1  	
				5 INDEX RANGE SCAN INDEX (UNIQUE) SYSADM.PS_PSTREESELECT05 Cost: 1  Bytes: 11  Cardinality: 1  		
			7 INDEX RANGE SCAN INDEX SYSADM.PSAPSTREESELECT10 Cost: 1  Bytes: 10  Cardinality: 1  			


[EDITED by LF: added [code] and [pre] tags to preserve formatting]

[Updated on: Wed, 30 January 2008 00:56] by Moderator

Report message to a moderator

Re: TOAD / Oracle qeps [message #297018 is a reply to message #297004] Tue, 29 January 2008 23:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code.
Use the "Preview Message" button to verify.
Always post your Oracle version (4 decimals).

Also provide the requested information regarding query tuning.
Also read How to Identify Performance Problem and Bottleneck .

Regards
Michel
Re: TOAD / Oracle qeps [message #297059 is a reply to message #297018] Wed, 30 January 2008 00:45 Go to previous messageGo to next message
rgoldwrx
Messages: 11
Registered: April 2006
Junior Member
we use oracle 10.2.0.3

I can read a lot of performance doco, but essentially I can tune the query, thats not the problem. The main issue seems to be that the plans differ in Toad between Sql Editor and Session Monitor.

It would appear to be related to the statistics not including uncommitted data. There are some settings I've tried to overcome this but to no avail. The uncommited data in itself does not add much to the size of the tables, almost more like the optimiser is doing some sort of range scan of the index to determine the best plan, and not considereing uncommitted data from the current session.

[Updated on: Wed, 30 January 2008 00:48]

Report message to a moderator

Re: TOAD / Oracle qeps [message #297062 is a reply to message #297059] Wed, 30 January 2008 00:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maybe you can read but I can't read your query and plans as they are not formatted.

There can be many reasons for different plans like bind variables type.

Regards
Michel
Re: TOAD / Oracle qeps [message #297064 is a reply to message #297062] Wed, 30 January 2008 00:51 Go to previous messageGo to next message
rgoldwrx
Messages: 11
Registered: April 2006
Junior Member
no bind variables

seems odd, the original post looks well formatted to me, does it depend on the monitor setting or something
Re: TOAD / Oracle qeps [message #297072 is a reply to message #297064] Wed, 30 January 2008 01:00 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
rgoldwrx wrote on Wed, 30 January 2008 07:51
seems odd, the original post looks well formatted to me, does it depend on the monitor setting or something

No, it doesn't depend on "monitor settings" but on reading the OraFAQ Forum Guide. It contains the "How to format your post" section which will help you properly format your posts. Read it, please. This time, I've added required tags to improve readability.
Re: TOAD / Oracle qeps [message #297084 is a reply to message #297072] Wed, 30 January 2008 01:25 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Plans are dynamic depending on the data. If the state of the data is different at runtime to what you are able to achieve in a controlled environment, you will never be sure to get the same plan.

Sure there are parameters and settings that may be different - and they may even be causing the problem - but so long as the data is different you might still not solve the problem.

So let's look at it logically: where could the problems be: I see two:

1. Cartesian Join
2. Index Range Scan

You've got two cartesian joins. When you cartesian join N rows to M rows you get N*M rows back. This can blow out a query if N and M are both large.

Oracle chooses cartesian joins in (typically) 3 cases:
- You provide no join criteria - not the case here.
- Oracle thinks it is a good candidate for a STAR JOIN - possible
- Oracle thinks one of the row sources has 0 or 1 row - very likely (all those CARDINALITY 1 entries).

CBO thinks INDEX SYSADM.PSAPSTREESELECT10 range scan will return 1 row. What column is this index on? selector_num is my guess. Will it return 1 row for either of the values supplied in L1 or L2?

CBO also thinks INDEX SYSADM.PS_PSTREESELECT05 will return 1 row. This looks to also be an index on selector_num. Will it return 1 row for the value supplied? Or more?

The other possibility is the Index Range Scan on INDEX SYSADM.PSDLEDGER. Which columns are in this index? A range scan can return 1 row, or it can return 1 Million. This range scan is the second step of a NESTED LOOPS join, so it is being called over and over again.

The easiest way to find out exactly where the problem is, is to trace the session and obtain row counts for each step of the plan from TKProf.

Ross Leishman
Re: TOAD / Oracle qeps [message #297085 is a reply to message #297072] Wed, 30 January 2008 01:38 Go to previous messageGo to next message
rgoldwrx
Messages: 11
Registered: April 2006
Junior Member
NVision / Trees and Oracle qeps

Hi, I have a query that I see in TOAD in the session browser. I copy the query into the sql session and the plan
is different between the 2. The one in session browser runs really badly. Its hard to tune a query when in sql
when it uses a different plan when it runs in Peoplesoft. Anyone know why this occurs:
The query runs for an hour and times out in Peoplesoft (as per the session browser plan), and takes about 10 seconds when
run in the sql editor. One factor is that data is probably inserted into the treetables each time the query is run. Not sure how
to control that, but I've read that its better to have dynamic tree tables anyway.

Query:

SELECT a.accounting_period, a.ACCOUNT, SUM (a.posted_total_amt)
FROM pstreeselect10 l2, pstreeselect10 l1, ps_ledger a, pstreeselect05 l
WHERE a.ledger = 'ACTUALS'
AND a.fiscal_year = 2008
AND a.accounting_period BETWEEN 0 AND 998
AND l2.selector_num = 307028
AND a.ACCOUNT = l2.range_from_10
AND l.selector_num = 308606
AND a.business_unit = l.range_from_05
AND l1.selector_num = 309876
AND a.deptid = l1.range_from_10
AND a.currency_cd = 'AUD'
AND a.statistics_code = ' '
GROUP BY a.accounting_period, a.ACCOUNT;

Plan in Session browser:
Plan
SELECT STATEMENT ALL_ROWSCost: 8
11 SORT GROUP BY Cost: 8 Bytes: 73 Cardinality: 1
10 TABLE ACCESS BY INDEX ROWID TABLE SYSADM.PS_LEDGER Cost: 3 Bytes: 42 Cardinality: 1
9 NESTED LOOPS Cost: 7 Bytes: 73 Cardinality: 1
7 MERGE JOIN CARTESIAN Cost: 4 Bytes: 31 Cardinality: 1
4 MERGE JOIN CARTESIAN Cost: 3 Bytes: 20 Cardinality: 1
1 INDEX RANGE SCAN INDEX SYSADM.PSAPSTREESELECT10 Cost: 2 Bytes: 10 Cardinality: 1
3 BUFFER SORT Cost: 1 Bytes: 10 Cardinality: 1
2 INDEX RANGE SCAN INDEX SYSADM.PSAPSTREESELECT10 Cost: 1 Bytes: 10 Cardinality: 1
6 BUFFER SORT Cost: 3 Bytes: 11 Cardinality: 1
5 INDEX RANGE SCAN INDEX (UNIQUE) SYSADM.PS_PSTREESELECT05 Cost: 1 Bytes: 11 Cardinality: 1
8 INDEX RANGE SCAN INDEX SYSADM.PSDLEDGER Cost: 2 Cardinality: 1

Plan in sql editor:
Plan
SELECT STATEMENT ALL_ROWSCost: 8 Bytes: 73 Cardinality: 1
9 SORT GROUP BY Cost: 8 Bytes: 73 Cardinality: 1
8 NESTED LOOPS Cost: 7 Bytes: 73 Cardinality: 1
6 NESTED LOOPS Cost: 6 Bytes: 63 Cardinality: 1
4 NESTED LOOPS Cost: 5 Bytes: 52 Cardinality: 1
2 TABLE ACCESS BY INDEX ROWID TABLE SYSADM.PS_LEDGER Cost: 4 Bytes: 42 Cardinality: 1
1 INDEX RANGE SCAN INDEX SYSADM.PSCLEDGER Cost: 3 Cardinality: 1
3 INDEX RANGE SCAN INDEX (UNIQUE) SYSADM.PS_PSTREESELECT10 Cost: 1 Bytes: 10 Cardinality: 1
5 INDEX RANGE SCAN INDEX (UNIQUE) SYSADM.PS_PSTREESELECT05 Cost: 1 Bytes: 11 Cardinality: 1
7 INDEX RANGE SCAN INDEX SYSADM.PSAPSTREESELECT10 Cost: 1 Bytes: 10 Cardinality: 1

Re: TOAD / Oracle qeps [message #297090 is a reply to message #297085] Wed, 30 January 2008 02:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you are unable to understand how to format your post (and use "Preview Message" button) I don't think you are able to understand anything about our explaination of what happened.

Regards
Michel
Re: TOAD / Oracle qeps [message #297093 is a reply to message #297090] Wed, 30 January 2008 02:19 Go to previous messageGo to next message
rgoldwrx
Messages: 11
Registered: April 2006
Junior Member
Michel, I don't think you understand the problem, and you're spending a lot of energy on something quite trivial. If you don't have any ideas then please stop harassing me and move on. I understand it may not be a simple problem.
Re: TOAD / Oracle qeps [message #297094 is a reply to message #297085] Wed, 30 January 2008 02:33 Go to previous messageGo to next message
rgoldwrx
Messages: 11
Registered: April 2006
Junior Member
thanks rleishman, more info:

select count(*) from pstreeselect10 --> 17997

select count(*) from pstreeselect10 where selector_num = 307028 --> 213

select count(*) from pstreeselect10 where selector_num = 309876 --> 213

select count(*) from pstreeselect05 --> 642

select count(*) from pstreeselect05 where selector_num = 308606 --> 23

select count(*) from ps_ledger --> 2463210;

select count(*) from ps_ledger a
where a.ledger = 'ACTUALS'
AND a.fiscal_year = 2008
AND a.accounting_period BETWEEN 0 AND 998
and a.currency_cd = 'AUD'
AND a.statistics_code = ' ' --> 21270

indexes:
CREATE UNIQUE INDEX SYSADM.PS_PSTREESELECT10 ON SYSADM.PSTREESELECT10
(SELECTOR_NUM, TREE_NODE_NUM, RANGE_FROM_10, RANGE_TO_10)

CREATE INDEX SYSADM.PSAPSTREESELECT10 ON SYSADM.PSTREESELECT10
(SELECTOR_NUM, RANGE_FROM_10)

CREATE UNIQUE INDEX SYSADM.PS_PSTREESELECT05 ON SYSADM.PSTREESELECT05
(SELECTOR_NUM, TREE_NODE_NUM, RANGE_FROM_05, RANGE_TO_05)
Re: TOAD / Oracle qeps [message #297108 is a reply to message #297094] Wed, 30 January 2008 03:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I even don't try to understand your problem till you don't follow the guidelines.
If you want me to stop the "harass" you, just follow the rules.

Regards
Michel
Re: TOAD / Oracle qeps [message #297218 is a reply to message #297093] Wed, 30 January 2008 13:01 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
rgoldwrx wrote on Wed, 30 January 2008 09:19
Michel, I don't think you understand the problem, and you're spending a lot of energy on something quite trivial. If you don't have any ideas then please stop harassing me and move on. I understand it may not be a simple problem.


Read message #297072 once again, please.

Then compare the very first message in this topic, with a proper use of [code] (and [pre]) tags and the same you posted recently. You'll see the difference - explain plain is much more readable when the formatting is preserved - you can even see a tree there! In your post, without [code] tags, reading requires additional efforts.

Do you need help? Yes, you do.
Does a reader need to see a properly formatted information? Yes, he/she does.

Please, stop harassing us with unformatted messages.
Re: TOAD / Oracle qeps [message #297251 is a reply to message #297004] Wed, 30 January 2008 17:11 Go to previous messageGo to next message
rgoldwrx
Messages: 11
Registered: April 2006
Junior Member
I've gone into a wrap program and changed it to wrap at 80 characters and reposted. For me the first looks post looks perfectly formatted. If I need to go to each line and carriage return at 80 characters then I don't think that is at all practical.

Who can't see it formatted correctly at over 80 characters, doesn't make any sense to me...
Re: TOAD / Oracle qeps [message #297255 is a reply to message #297251] Wed, 30 January 2008 20:25 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
rgoldwrx wrote on Thu, 31 January 2008 10:11
For me the first looks post looks perfectly formatted.

That's because Littlefoot formatted it for you. Take a look at your second post that included the SQL and plan. That's what it looked like before LF formatted it.

All LF did was to place [code] and [/code] tags around your code. You could agree to do the same and we could get on with the job of helping you rather than spending (now) 11 posts to discuss formatting.

OK, so we have a cartesian join of 213x213x23 =~ 1 million thereabouts. This 1M rows is step 1 in a nested loop operation, so we are performing 1M range scans on INDEX SYSADM.PSDLEDGER, but you've described every index but that one so I can't tell how good/bad it will be.

Interestingly, both plans you posted have the same cost, so it is reasonably predictable that Oracle might could choose either with equal likelihood.

The core problem we have here is that access predicates that return 213 and 23 rows are being evaluated by the optimiser as most likely to return 1 row. That's what we've got to fix.

Are your statistics up to date (gathered with DBMS_STATS package)?

Does the data in these tables change dramatically whilst the end-to-end job is underway. ie. Is this SQL submitted against tables that have been rebuilt or substantially changed since statistics were last gathered?

Ross Leishman
Re: TOAD / Oracle qeps [message #297257 is a reply to message #297255] Wed, 30 January 2008 21:41 Go to previous messageGo to next message
rgoldwrx
Messages: 11
Registered: April 2006
Junior Member
Are your statistics up to date (gathered with DBMS_STATS package)?
yes, they are gathered every 60 mins for these tree tables

Does the data in these tables change dramatically whilst the end-to-end job is underway. ie. Is this SQL submitted against tables that have been rebuilt or substantially changed since statistics were last gathered?
data will change little to be effected by the stats, ie
pstreeselect10 will add 213 rows per run
pstreeselect05 will add 23 rows per run

ps..I'm still puzzled about the formatting, I'll have a better crack at it next time though.
Re: TOAD / Oracle qeps [message #297303 is a reply to message #297257] Thu, 31 January 2008 01:22 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
You have shown us the SQL above with hard coded values like 307028.

Are the values hard-coded in the real program - either directly into the program or concatenated into a SQL string with EXECUTE IMMEDIATE - or do you use bind variables?

Ross Leishman
Re: TOAD / Oracle qeps [message #297305 is a reply to message #297303] Thu, 31 January 2008 01:28 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Ahhh! I noticed you're using 10.2. If you are using bind variables it is probably bind-variable peeking anyway.

Forget my last question - it doesn't help. Are the values of these selector_nums always increasing? ie. Are the 213 new rows the ones with the HIGHEST value if selector_num?

If so, your 1-hour old statistics show that the highest value is X. You insert rows with value X+1. Then you query values X+1. Oracle thinks there are none and performs a Cartesian Join.

If I'm right, the best solution is to gather stats AFTER the INSERT and BEFORE the SELECT. Or you could try a DYNAMIC_SAMPLING hint.

Ross Leishman
Re: TOAD / Oracle qeps [message #297459 is a reply to message #297305] Thu, 31 January 2008 17:04 Go to previous messageGo to next message
rgoldwrx
Messages: 11
Registered: April 2006
Junior Member
I think you're on the issue there. The added rows is using a higher count! I have tried making DYNAMIC_SAMPLING mods at the database level but this hasn't helped. As this is a function within a 'black box' part of the system I don't have the opportunity to recalc stats after the data is inserted but before selected.

This is psrtly why we have a script that recalc stats every hour on these tables, for subsequent queries that use this data.

Re: TOAD / Oracle qeps [message #297462 is a reply to message #297459] Thu, 31 January 2008 17:31 Go to previous messageGo to next message
rgoldwrx
Messages: 11
Registered: April 2006
Junior Member
we are looking at the upgrade to 11g in the not so distant future for another system, not sure if this situation can be / is addressed in the new version.

Getting slightly off track, but we also have some temp tables, which originally have stats of 0 rows, and in a similar situation the plan executed is a poor one. I have had to remove stats and lock stats to improve this situation. Seems s bit of a problem with the optimiser to me, I haven't seen this in previous versions.
Re: TOAD / Oracle qeps [message #297467 is a reply to message #297462] Thu, 31 January 2008 20:05 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
If you truely cannot access the production code, you will need to investigate PLAN STABILITY. Search the manuals, and involve your DBA - it's not straightforward.

Ross Leishman
Re: TOAD / Oracle qeps [message #300348 is a reply to message #297004] Fri, 15 February 2008 02:37 Go to previous message
jayassun
Messages: 1
Registered: February 2008
Location: Slovenia
Junior Member
Hi

I had a similar problem where TOAD gave me a different execution plan in session browser and in sql browser. I resolved this case of bind peeking with a hidden parameter:

alter session set "_optim_peek_user_binds" = FALSE;

Previous Topic: need help in tuning this query
Next Topic: A query with one additional condition slows down
Goto Forum:
  


Current Time: Sun Dec 04 20:51:34 CST 2016

Total time taken to generate the page: 0.06395 seconds