| Undo advice. [message #559802] |
Fri, 06 July 2012 06:12  |
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
|
Senior Member |
|
|
11:53:51 SQL>select * from v$version
11:53:55 2 /
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Solaris: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
All, I have an infuriating problem and I'm hoping for some advice. Well actually it is a number of problems but I'll use one example as a microcosm.
Allow me to try and explain, below is an example of the explain plan for the query I'm going to use as the example:
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 912K(100)| |
| 1 | HASH JOIN | | 3412K| 940M| 826M| 912K (1)| 03:02:28 |
| 2 | HASH JOIN | | 3412K| 787M| 699M| 748K (1)| 02:29:39 |
| 3 | HASH JOIN | | 3610K| 657M| 557M| 560K (1)| 01:52:07 |
| 4 | HASH JOIN | | 3610K| 516M| 492M| 419K (1)| 01:23:51 |
| 5 | HASH JOIN | | 3610K| 451M| 351M| 346K (1)| 01:09:19 |
| 6 | HASH JOIN | | 3610K| 309M| 220M| 225K (1)| 00:45:09 |
| 7 | TABLE ACCESS FULL | T1 | 3611K| 179M| | 63605 (2)| 00:12:44 |
| 8 | TABLE ACCESS FULL | T2 | 20M| 742M| | 128K (1)| 00:25:40 |
| 9 | TABLE ACCESS FULL | T3 | 20M| 796M| | 70784 (1)| 00:14:10 |
| 10 | INDEX FAST FULL SCAN| I1 | 20M| 369M| | 28717 (1)| 00:05:45 |
| 11 | TABLE ACCESS FULL | T4 | 20M| 801M| | 75766 (2)| 00:15:10 |
| 12 | TABLE ACCESS FULL | T5 | 19M| 942M| | 107K (1)| 00:21:35 |
| 13 | TABLE ACCESS FULL | T6 | 21M| 954M| | 75314 (1)| 00:15:04 |
------------------------------------------------------------------------------------------------------
Now then, this query runs perfectly and as expected (direct reads/DB scattered reads) until it hit step 13 in the plan (FTS of T6). At step 13 it basically enters into a (rather fatal) process of reading almost nothing but undo exclusively.
Now then, I wonder why this might be. Of course the natural reaction is to say "they data is changing, its read consistency (optional rookie insult)". Now, I found this hard to believe that the volume of data could be so extensively changed, keeping in mind its a good sized table and it's doing a full access.
I checked the DBA_HIST_SEG_STAT(DB_BLOCK_CHANGES_DELTA) view for the period and in the run up, i.e. the explain plan steps 2-12 took approx 20 minutes. In this window, less than 1% of the blocks in the table T6 were changed (approx 3-4k out of a 500k block table).
So that's odd. Nearly every access of this table is reading undo, yet since this query started, 99% of the table is unchanged. But the query is behaving as if everything it reads has altered since it began.
To be clear, I understand the read consistency etc and expect it, what I DONT expect is the virtually the ENTIRE table scan operation routing via undo when I'm accessing the full table, yet hardly any blocks are altered after I start running.
Note this query/application seems to fetch the rows in blocks of 1k, its almost as if its re-executing for each fetch, but I wouldnt have expected that.
In summary:
>Query starts
>hits final table
>almost exclusive undo reads
>under 1% of block changes in read table since query began
Quite frankly none of this makes any sense to me and flies in the face of what I know (or as it feels right now, thought I knew). So it is clear to me that I'm missing something. Is there any other way anyone knows of that might cause a session to read into undo beyond a session changing data after the query executed?
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Undo advice. [message #559823 is a reply to message #559820] |
Fri, 06 July 2012 10:13  |
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
|
Senior Member |
|
|
Michel Cadot wrote on Fri, 06 July 2012 16:06So it is not my explaination.
Regards
Michel
It may be, there's nothing to say (yet) that a large update isnt running nightly I'm as yet unaware of.
|
|
|
|
| Re: Undo advice. [message #559831 is a reply to message #559823] |
Fri, 06 July 2012 11:36 |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Quote:there's nothing to say (yet) that a large update isnt running nightly I'm as yet unaware of. This you can check: query dba_tab_modifications, it will show how many rows have been I/U/Dd since the the last time the the table was analyzed. Remember to
exec dbms_stats.flush_database_monitoring_info
first, to be sure the view is up to date.
|
|
|
|
| Re: Undo advice. [message #559833 is a reply to message #559831] |
Fri, 06 July 2012 11:49 |
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
|
Senior Member |
|
|
John Watson wrote on Fri, 06 July 2012 17:36Quote:there's nothing to say (yet) that a large update isnt running nightly I'm as yet unaware of. This you can check: query dba_tab_modifications, it will show how many rows have been I/U/Dd since the the last time the the table was analyzed. Remember to
exec dbms_stats.flush_database_monitoring_info
first, to be sure the view is up to date.
I thought about this angle too, however the tables can go some time without stat work being done 
A daily run is rare except on certain tables, these aren't on the 'special' list.
Current process is gather at x% change and it takes a few days to hit this point, this was why I was checking through block change in history views.
[Updated on: Fri, 06 July 2012 11:50] Report message to a moderator |
|
|
|
|
|
| Re: Undo advice. [message #559852 is a reply to message #559850] |
Fri, 06 July 2012 13:47 |
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
|
Senior Member |
|
|
|
I'll give that a try the next time I get into the office, off site for a week or so now. I'll keep an eye on this thread though and feed back if we get a resolution.
|
|
|
|
| Re: Undo advice. [message #560243 is a reply to message #559852] |
Wed, 11 July 2012 08:41 |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Here's an idea: could it be that T6 is large enough to be read by a serial direct read? Uncle Oracle might decide to do that if you have _serial_direct_read=auto which is the default for 11.2.0.3. In that case, I think that any past images of blocks would have to be constructed dynamically as required, whereas if it were read by a buffered read then past images already existing in the cache could be used. Easy to test, set _serial_direct_read=never for your session.
A very slim chance, but worth a try?
|
|
|
|
|
|
| Re: Undo advice. [message #561552 is a reply to message #561451] |
Mon, 23 July 2012 16:41 |
 |
alan.kendall@nfl.com
Messages: 163
Registered: June 2012
Location: Culver City, California
|
Senior Member |
|
|
If stats gets run then dba_tab_modifications gets purged so I use dba_hist_seg_stat to see modifications.
ENWEBP1P > @dba_hist_seg_stat_INPUT_OBJECT_to_see_if_PHYSICAL_WRITES_LAST_7_DAYS.sql
Enter value for like_object_name: REGISTRATIONS_PK
DATE OBJECT_NAME PHYSICAL_WRITES_DELTA
-------------------- ------------------------------- ---------------------
2012-07-15 Sunday REGISTRATIONS_PK 1781
2012-07-16 Monday REGISTRATIONS_PK 3356
2012-07-17 Tuesday REGISTRATIONS_PK 3369
2012-07-18 Wednesday REGISTRATIONS_PK 3465
2012-07-19 Thursday REGISTRATIONS_PK 3179
2012-07-20 Friday REGISTRATIONS_PK 3087
2012-07-21 Saturday REGISTRATIONS_PK 2903
2012-07-22 Sunday REGISTRATIONS_PK 3401
2012-07-23 Monday REGISTRATIONS_PK 1650
9 rows selected.
ENWEBP1P > list
1 select to_char(c.BEGIN_INTERVAL_TIME,'YYYY-MM-DD Day') "DATE",a.object_name,
2 sum(b.PHYSICAL_WRITES_DELTA) physical_writes_delta
3 from dba_objects a,dba_hist_seg_stat b,sys.wRM$_SNAPSHOT c
4 where b.SNAP_ID >
5 (select max(SNAP_ID)-24*8 from sys.wRM$_SNAPSHOT) and a.object_id=b.OBJ#
6 and upper(a.object_name) like upper('%&like_object_name%') and b.PHYSICAL_WRITES_DELTA>0
7 and c.instance_number=(select instance_number from v$instance)
8 and c.snap_id=b.snap_id
9 group by to_char(c.BEGIN_INTERVAL_TIME,'YYYY-MM-DD Day'),a.object_name
10* order by 1,3
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
