Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

performance Tuning 10g reference poster
 
Oracle training in Linux 

commands
 
Oracle training 

Excel
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Reply to this topicStart new topic
> comparison of 2 execution plans, 1 execution plan is missing table access by rowid but it works
eric30mu
post Mar 24 2012, 09:08 AM
Post #1


Member
**

Group: Members
Posts: 16
Joined: 9-March 05
Member No.: 1,590



We created a new table from a copy of an existing table and experienced performance issues we believe because of the way the table was loaded (more blocks in the new table means more CG's and higher clustering factor for indexes). While troubleshooting, we compared the execution plans from the previous table and the new table from 1 of the sql statements that was slower and found this:

Rows Execution Plan
------- ---------------------------------------------------
0 UPDATE STATEMENT MODE: ALL_ROWS
0 UPDATE OF 'WIN_STORE'
0 TABLE ACCESS MODE: ANALYZED (BY LOCAL INDEX ROWID) OF
'WIN_STORE' (TABLE)
0 NESTED LOOPS
0 SORT (UNIQUE)
0 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'IF_TRAN_DATA' (TABLE)
0 INDEX MODE: ANALYZED (RANGE SCAN) OF
'IF_TRAN_DATA_I2' (INDEX)
0 PARTITION RANGE (ALL) PARTITION: START=1 STOP=36
0 INDEX MODE: ANALYZED (RANGE SCAN) OF 'PK_WIN_STORE'
(INDEX (UNIQUE)) PARTITION: START=1 STOP=36


Rows Execution Plan
------- ---------------------------------------------------
0 UPDATE STATEMENT MODE: ALL_ROWS
0 UPDATE OF 'WIN_STORE_OLD'
0 NESTED LOOPS
0 SORT (UNIQUE)
0 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'IF_TRAN_DATA' (TABLE)
0 INDEX MODE: ANALYZED (RANGE SCAN) OF 'IF_TRAN_DATA_I2'
(INDEX)
0 PARTITION RANGE (ALL) PARTITION: START=1 STOP=30
0 INDEX MODE: ANALYZED (RANGE SCAN) OF 'PK_WIN_STORE_OLD'
(INDEX (UNIQUE)) PARTITION: START=1 STOP=30

how can the good plan leave out this step? Don't you have to go the table to update it?

TABLE ACCESS MODE: ANALYZED (BY LOCAL INDEX ROWID) OF
'WIN_STORE' (TABLE)
Go to the top of the page
 
+Quote Post
burleson
post Mar 24 2012, 09:24 AM
Post #2


Advanced Member
***

Group: Members
Posts: 11,086
Joined: 26-January 04
Member No.: 13



Hi Eric,

Interesting question . . . .

>> because of the way the table was loaded

Most DBA's use CTAS, as does the dbms_refefinition package:

http://dba-oracle.com/googlesearchsite_pro...=reorganization

In CTAS, you quickly copy-over the rows into a pristine state.

******************************************

>> more blocks in the new table means more CG's

Huh?

Usually, a reorg re-claims half-empty blocks, not add more blocks to the table!

Did you adjust PCTFREE for the reorg?

*************************************************
>> higher clustering factor for indexes).

Er, did you re-sequence the rows into index order when you copied the table?

I don't understand your execution plan question, sorry . . . .

As a general rule, a reorganized table returns it into the "pristine" state it existed prior to getting fragmented. It's very rare for a newly reorged table to perform worse unless somebody forgot to migrate a constraint or index into the new table.

1 - Did you re-analyze the "new" table and his indexes with dbms_stats?

2 - Is there a chance that you forgot to move some indexes/constraints into the new table?


Please advise . . . Thanks!


--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
eric30mu
post Mar 24 2012, 06:16 PM
Post #3


Member
**

Group: Members
Posts: 16
Joined: 9-March 05
Member No.: 1,590



QUOTE (burleson @ Mar 24 2012, 02:24 PM) *
Hi Eric,

Interesting question . . . .

>> because of the way the table was loaded

Most DBA's use CTAS, as does the dbms_refefinition package:

http://dba-oracle.com/googlesearchsite_pro...=reorganization

In CTAS, you quickly copy-over the rows into a pristine state.

******************************************

>> more blocks in the new table means more CG's

Huh?

Usually, a reorg re-claims half-empty blocks, not add more blocks to the table!

Did you adjust PCTFREE for the reorg?

*************************************************
>> higher clustering factor for indexes).

Er, did you re-sequence the rows into index order when you copied the table?

I don't understand your execution plan question, sorry . . . .

As a general rule, a reorganized table returns it into the "pristine" state it existed prior to getting fragmented. It's very rare for a newly reorged table to perform worse unless somebody forgot to migrate a constraint or index into the new table.

1 - Did you re-analyze the "new" table and his indexes with dbms_stats?

2 - Is there a chance that you forgot to move some indexes/constraints into the new table?


Please advise . . . Thanks!


I intentionally left out details about the reorg because my question is about the difference between the execution plans.
... it was more than a reorg. It was rebuilt with a new column, but the problem appears to be that it was loaded parallel insert append into a tablespace with uniform sized extents, instead of autoallocate. The result is that the # of blocks went up because of the way parallel insert appends work (without autoallocate), but we think we have that issue under control. We're going to rebuild the table and yes we will order by the indexed columns in order to get a superior clustering factor and index access.

What I don't understand is the difference between the 2 execution plans provided. It looks like the one with win_store_old skips table access by rowid (3rd step from the top on the first plan provided, but missing from the second plan). I thought that unless oracle performs a FFS that it has to go to the table after an index range scan. Can oracle actually combine 2 steps in some cases as the plan on the bottom sends to indicate( I would argue that update and table access by rowid are implied in the 2nd line from the top in the second plan)? What gives?

Thanks,
Eric
Go to the top of the page
 
+Quote Post
SteveC
post Mar 25 2012, 03:15 PM
Post #4


Advanced Member
***

Group: Members
Posts: 2,807
Joined: 11-March 08
Member No.: 18,933



Comparing execution plans between tables with different structures is like taking a book written in Greek and wondering why there are fewer (or more) pages when it is translated into French. The **only** valid comparison of execution plans is against the same base object. Change the statement, get different stats, use a hint, add an index - those are all valid comparisons. Table with structure X versus table with structure Y - there are better things to do with your time.

For one, you know the structure is different because of the partition iterator (1-36 versus 1-30). You mention nothing about how the indexes compare, difference in stats, even the number of rows being evaluated/pruned.

If you really want to know why there is a difference, then do a 10053 trace and merrily follow along with the choices the optimizer made along the way. No guess work involved then.
Go to the top of the page
 
+Quote Post
eric30mu
post Mar 25 2012, 07:57 PM
Post #5


Member
**

Group: Members
Posts: 16
Joined: 9-March 05
Member No.: 1,590



QUOTE (SteveC @ Mar 25 2012, 08:15 PM) *
Comparing execution plans between tables with different structures is like taking a book written in Greek and wondering why there are fewer (or more) pages when it is translated into French. The **only** valid comparison of execution plans is against the same base object. Change the statement, get different stats, use a hint, add an index - those are all valid comparisons. Table with structure X versus table with structure Y - there are better things to do with your time.

For one, you know the structure is different because of the partition iterator (1-36 versus 1-30). You mention nothing about how the indexes compare, difference in stats, even the number of rows being evaluated/pruned.

If you really want to know why there is a difference, then do a 10053 trace and merrily follow along with the choices the optimizer made along the way. No guess work involved then.


Steve C:
Thanks for the post. I am not sure that I agree with your assertion that a change in table structure allows the optimizer to essentially skip the table access by rowid step after an index range scan that is visible in the plan or combine it with the nested loops step. Notwithstanding the difference in the number of partitions, the plans except for the step match.
But your point about the 10053 trace is very valid. I will look into that, but doesn't the range scan with no table access by rowid represent very unusual if not undocumented behavior? I don't recall ever observing it and thought that I would post to see if anyone else had seen it before.

Thanks,
Eric
Go to the top of the page
 
+Quote Post
rastl
post Mar 26 2012, 05:03 AM
Post #6


Advanced Member
***

Group: Members
Posts: 54
Joined: 12-October 06
Member No.: 5,309



Is there perhaps an IOT involved in the original data model/execution plan ?
Go to the top of the page
 
+Quote Post
eric30mu
post Mar 26 2012, 09:24 AM
Post #7


Member
**

Group: Members
Posts: 16
Joined: 9-March 05
Member No.: 1,590



QUOTE (rastl @ Mar 26 2012, 10:03 AM) *
Is there perhaps an IOT involved in the original data model/execution plan ?


No. The only difference between the 2 objects is that one column (1 of the 2 pk columns) was changed from number(4) to number(5) we broke out the max partitions into 6 additional ones. But again, my question doesn't deal so much with the difference between the execution plans as much as how can oracle perform a range scan and then not perform a table access by rowid. We got the same plans from autotrace and library cache (v$sql_plan_table I think it is called). My understanding is that index range scans and table access by rowid go together like love and marriage, except for this one execution plan.

OBJECT_NAME OBJECT_TYPE OWNER CREATED
------------------------------ ------------------- ------------------------------ ---------
WIN_STORE_OLD TABLE PARTITION 19-JUL-03
WIN_STORE TABLE PARTITION 10-MAR-12
...
WIN_STORE_OLD TABLE 19-JUL-03
WIN_STORE TABLE 10-MAR-12
Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 16th April 2014 - 05:40 AM