Home » RDBMS Server » Performance Tuning » Undo advice. (11.0.2.3 Sol 10 64bit)
Undo advice. [message #559802] Fri, 06 July 2012 06:12 Go to next message
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 #559810 is a reply to message #559802] Fri, 06 July 2012 09:16 Go to previous messageGo to next message
Michel Cadot
Messages: 67236
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maybe there was a bulk change on the table someday (maybe long ago if the table was never accessed since) and the blocks were not cleaned out.

Regards
Michel
Re: Undo advice. [message #559811 is a reply to message #559810] Fri, 06 July 2012 09:23 Go to previous messageGo to next message
BlackSwan
Messages: 26722
Registered: January 2009
Location: SoCal
Senior Member
Is the observation regarding UNDO repeatable?
Re: Undo advice. [message #559818 is a reply to message #559811] Fri, 06 July 2012 09:48 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
BlackSwan wrote on Fri, 06 July 2012 15:23
Is the observation regarding UNDO repeatable?


Yes, it's recurring.
Re: Undo advice. [message #559820 is a reply to message #559818] Fri, 06 July 2012 10:06 Go to previous messageGo to next message
Michel Cadot
Messages: 67236
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So it is not my explaination.

Regards
Michel
Re: Undo advice. [message #559823 is a reply to message #559820] Fri, 06 July 2012 10:13 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
Michel Cadot wrote on Fri, 06 July 2012 16:06
So 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 Go to previous messageGo to next message
John Watson
Messages: 8295
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 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
John Watson wrote on Fri, 06 July 2012 17:36
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.


I thought about this angle too, however the tables can go some time without stat work being done Sad

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 #559850 is a reply to message #559833] Fri, 06 July 2012 13:16 Go to previous messageGo to next message
BlackSwan
Messages: 26722
Registered: January 2009
Location: SoCal
Senior Member
I wonder what, if anything, would change if you did as below prior to issuing complex join
SELECT * FROM T6;
Re: Undo advice. [message #559852 is a reply to message #559850] Fri, 06 July 2012 13:47 Go to previous messageGo to next message
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 Go to previous messageGo to next message
John Watson
Messages: 8295
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 #561451 is a reply to message #560243] Mon, 23 July 2012 01:22 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
Ok. Selecting * from the table didnt help notably.

@John: I'll check soon as I can Smile
Re: Undo advice. [message #561552 is a reply to message #561451] Mon, 23 July 2012 16:41 Go to previous message
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
Previous Topic: SQL optimisation
Next Topic: understanding a SQL plan
Goto Forum:
  


Current Time: Thu Jul 09 13:58:58 CDT 2020