| TOAD / Oracle qeps [message #297004] |
Tue, 29 January 2008 22:13  |
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 #297059 is a reply to message #297018] |
Wed, 30 January 2008 00:45  |
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 #297084 is a reply to message #297072] |
Wed, 30 January 2008 01:25 |
rleishman
Messages: 3728
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 |
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 #297093 is a reply to message #297090] |
Wed, 30 January 2008 02:19 |
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 |
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 #297218 is a reply to message #297093] |
Wed, 30 January 2008 13:01 |
 |
Littlefoot
Messages: 21823
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 |
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 |
rleishman
Messages: 3728
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 |
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 |
rleishman
Messages: 3728
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 |
rleishman
Messages: 3728
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 |
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 |
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 |
rleishman
Messages: 3728
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 |
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;
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
