Search

OakieTags

Who's online

There are currently 0 users and 31 guests online.

Recent comments

Affiliations

Oakies Blog Aggregator

HOWTO: Using the Oracle XMLDB Repository to Automatically Shred Windows Office Documents (Part 1)

People who have attended the UKOUG presentation this year where Mark Drake, Sr. Product Manager XML Technologies / XMLDB, Oracle HQ, and I demonstrated the first principles of the XDB Repository, might have been impressed with its (GEO/KML Spatial, Image EXIF info) capabilities combined with Google Earth. This post will zoom in on how to

Read More…

VirtualBox 4.1.8 Released…

VirtualBox 4.1.8 is now available!

Happy upgrading… :)

Cheers

Tim…




Sherlock Holmes: A Game of Shadows…

Sherlock Holmes: A Game of Shadows is a worthy sequel to the previous film. I read a couple of reviews saying the plot was too complicated. This surprises me because I would have though most people who saw the first film would understand that you are not meant to be able to fathom the clues. The whole point is that he is so amazing he can string a bunch of trivial things together and come up with some outlandish conclusion. The more ridiculous the better as far as I’m concerned.

This film is even more camp than the previous outing. There is definitely some bromance going on between Holmes and Watson. What with Holmes cross dressing and his brother (Stephen Fry) calling him Shirley, you get some totally Mr Humphries moments. The onscreen chemistry between Jude Law and Robert Downey Jr. is so good I imagine they would be a riot if you went down the pub with them.

A special note must be made of Stephen Fry’s hair in one of the scenes. It is all curled up at the ends and is truly a sight to behold. I was both cringing and laughing all at the same time. Perfect casting if you ask me.

If you loved the first one you’ll love this one. Great holiday fun.

Cheers

Tim…




Return Of The Mack…

On my final day in Bulgaria I managed to leave my coat, hat and gloves in a restaurant about 2 hours away from Sofia. I realized when we were about 30 minutes from Sofia, but thought it was no point saying anything because we didn’t have time to go back and get them. When we arrived at the hotel someone else noticed I wasn’t wearing a coat and I explained my foolishness and thought no more of it.

A few days after I got home I got an email from Milena Gerova to say the coat had been located and she was going to post it back to me. A man in a van delivered it this morning, hence the title of the post, “Return of the mack”. Thanks very much Milena. :)

If you came here hoping to see a reference to the 1996 Mark Morrison song called “Return Of The Mack“, there it is. If you were hoping for a reference to the recent Mann: The Mack song, you have that too. :)

Cheers

Tim…




Correlation oddity

This one’s so odd I nearly posted it as a “Quiz Night” – but decided that it would be friendlier simply to demonstrate it. Here’s a simple script to create a couple of identical tables. It’s using my standard environment but, apart from fiddling with optimizer settings, I doubt if there’s any reason why you need to worry too much about getting the environment exactly right.

create table t1
as
with generator as (
	select	--+ materialize
		rownum id 
	from dual 
	connect by 
		level <= 1000
)
select
	rownum			id,
	lpad(rownum,10,'0')	small_vc,
	rpad('x',100)		padding
from
	generator	v1,
	generator	v2
where
	rownum <= 10000
;

create table t2 as select * from t1;

begin
	dbms_stats.gather_table_stats(
		ownname		 => user,
		tabname		 =>'T1',
		method_opt	 => 'for all columns size 1'
	);

	dbms_stats.gather_table_stats(
		ownname		 => user,
		tabname		 =>'T2',
		method_opt	 => 'for all columns size 1'
	);

end;
/

create index t2_i1 on t2(id);

All I’ve done is create a small amount of data, collected stats, and built one index. Now run the following (fairly similar) queries.

update	t1
set	small_vc = (
		select
			max(small_vc)
		from
			t2
		where	t2.id = t1.id
	)
where
	mod(id,100) = 0
;

commit;

update	t1
set	small_vc = (
		select
			small_vc
		from
			t2
		where	t2.id = t1.id
	)
where
	mod(id,100) = 0
;

commit;

All I’m doing it an update of 100 rows through a correlated subquery. For consistency you might want to recreate the tables before running each of the updates, or perhaps run the test twice with the two updates in the opposite order the second time around. I’m not trying to do anything subtle or difficult to produce an anomaly, I’m just trying to demonstrate a strange difference between these two updates.

As you might expect, both updates (in my environment) operated through a simple subquery mechansim; Oracle didn’t attempt to transform them into a some sort of flattened update join view, so the two execution plans looked like this:

Aggregate subquery plan:
-----------------------------------------------------------------------
| Id  | Operation                     | Name  | Rows  | Bytes | Cost  |
-----------------------------------------------------------------------
|   0 | UPDATE STATEMENT              |       |       |       |    27 |
|   1 |  UPDATE                       | T1    |       |       |       |
|*  2 |   TABLE ACCESS FULL           | T1    |   100 |  1500 |    27 |
|   3 |   SORT AGGREGATE              |       |     1 |    15 |       |
|   4 |    TABLE ACCESS BY INDEX ROWID| T2    |     1 |    15 |     2 |
|*  5 |     INDEX RANGE SCAN          | T2_I1 |     1 |       |     1 |
-----------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(MOD("ID",100)=0)
   5 - access("T2"."ID"=:B1)


Simple query plan:
----------------------------------------------------------------------
| Id  | Operation                    | Name  | Rows  | Bytes | Cost  |
----------------------------------------------------------------------
|   0 | UPDATE STATEMENT             |       |       |       |    27 |
|   1 |  UPDATE                      | T1    |       |       |       |
|*  2 |   TABLE ACCESS FULL          | T1    |   100 |  1500 |    27 |
|   3 |   TABLE ACCESS BY INDEX ROWID| T2    |     1 |    15 |     2 |
|*  4 |    INDEX RANGE SCAN          | T2_I1 |     1 |       |     1 |
----------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(MOD("ID",100)=0)
   4 - access("T2"."ID"=:B1)

There really doesn’t seem to be much difference in the mechanics. For each of the 100 rows selected from table t1, we run a subquery doing a range scan against table t2, using the index t2_i1 to find corresponding rows. In every case there is only one row match: in one update I copy back the maximum value from the list that consists of of just one row, in the other update I copy back the value from every one of the matching rows I find (all one of them); luckily the second update only ever finds one matching row for each row in the driving table, so the update completes rather than crashing with error ORA-01427: single-row subquery returns more than one row.

So – would you expect the two updates to perform differently ? I wouldn’t; but they do; here are some of the session stats:

Aggregate subquery
------------------
Name                                      Value
----                                      -----
db block gets                               208
db block changes                            407
redo entries                                108
redo size                                43,996
undo change vector size                  14,944

Simple subquery
---------------
Name                                      Value
----                                      -----
db block gets                               105
db block changes                            203
redo entries                                 63
redo size                                34,624
undo change vector size                  11,248

Obviously there were many other statistics I could have reported – consistent gets, table scan rows gotten, and so on – but all the other statistics matched very closely. What you see here is just the small number of statistics that tell us that the amount of data change was different depending on whether on not you used a (redundant) aggregation to get the value you needed to do the update. In passing, if you’re running 9i or lower, or if you’re running 10g and above with RAC, or supplemental logging enabled, or if you’re running this test in the SYS schema, you’ll see the number of redo entries at (or very close to) 202 and 101 respectively – the actual number of entries in my test is the effect of in-memory undo and private redo.

To find out what’s going on, the easiest thing to do is look at a symbolic dump of the redo log for this update which, stripped to a minimum, shows the following as the redo generated for one of the later rows updated in the batch of 100.

Aggregate subquery:
-------------------
REDO RECORD - Thread:1 RBA: 0x0005c6.00000057.0030 LEN: 0x00c8 VLD: 0x01
CHANGE #1 TYP:0 CLS:30 AFN:2 DBA:0x00807017 OBJ:4294967295 SCN:0x0000.04f86220 SEQ:  2 OP:5.1
	ktubu redo: slt: 30 rci: 99 opc: 11.1 objn: 98261 objd: 98261 tsn: 5
CHANGE #2 TYP:0 CLS: 1 AFN:5 DBA:0x01800130 OBJ:98261 SCN:0x0000.04f86192 SEQ:  2 OP:11.4
	KDO Op code: LKR row dependencies Disabled
 
REDO RECORD - Thread:1 RBA: 0x0005c6.00000057.00f8 LEN: 0x0100 VLD: 0x01
CHANGE #1 TYP:0 CLS:30 AFN:2 DBA:0x00807017 OBJ:4294967295 SCN:0x0000.04f86221 SEQ:  1 OP:5.1
	ktubu redo: slt: 30 rci: 100 opc: 11.1 objn: 98261 objd: 98261 tsn: 5
CHANGE #2 TYP:0 CLS: 1 AFN:5 DBA:0x01800130 OBJ:98261 SCN:0x0000.04f86221 SEQ:  1 OP:11.5
	KDO Op code: URP row dependencies Disabled


Simple subquery:
----------------
REDO RECORD - Thread:1 RBA: 0x0005c7.00000044.0090 LEN: 0x0160 VLD: 0x01
CHANGE #1 TYP:0 CLS:26 AFN:2 DBA:0x00801610 OBJ:4294967295 SCN:0x0000.04f86288 SEQ:  1 OP:5.1
	ktubu redo: slt: 36 rci: 42 opc: 11.1 objn: 98261 objd: 98261 tsn: 5
CHANGE #2 TYP:2 CLS: 1 AFN:5 DBA:0x0180012c OBJ:98261 SCN:0x0000.04f86224 SEQ:  1 OP:11.19
	Array Update of 1 rows: 
	KDO Op code:  21 row dependencies Disabled


When we update using the aggregate subquery Oracle generates two redo records per row: the first locks the row (op code 11.4), the second modifies it (op code 11.5). When we do the update through a simple subquery Oracle generates a single redo record per row, which is an “array update” (op code 11.19).

This anomaly may look familiar – but this time it’s nothing to do with the audit_trail, it just seems to be the way things work. At present I can’t think of any good reason for the difference, but maybe there’s something subtle I’m overlooking.

OUI Updates Update

We’ve now received a response to the issue described in the previous entry. In short oui updates at install time are broken (apparently fixed in 11.2.0.3, but no updates are available for that release yet to check. Hi , The OUI looks for the metadata for the patches. There is an unpublished bug 11709996 on [...]

Idle Thoughts – SSD, Redo Logs, and Sector Size

December 18, 2011 It seems that I have been quite busy lately with computer related tasks that are not associated with Oracle Database, making it difficult to slow down and focus on items that have a foundation in logic.  Today I have had some spare time to dig further into the recently released “Oracle Core” [...]

Ad: The Oracle Query Optimizer 2-Day Seminar

The 31st of January and 1st of February 2012 I will present a 2-day seminar about the Oracle query optimizer in Ballerup (DK). The event is organized by Miracle A/S. The content, which is based on the chapters 2, 4, 5, 6, 9 and 10 of my book, is the following:

  • Chapter 1 describes the life cycle of SQL statements and when the database engine can share cursors.
  • Chapter 2 describes the aim and architecture of the query optimizer.
  • Chapter 3 and 4 discuss the statistics used by the query optimizer to carry out its work.
  • Chapter 5 describes the initialization parameters influencing the behavior of the query optimizer and how to set them.
  • Chapter 6 outlines different methods of obtaining execution plans, as well as how to read them and recognize inefficient ones.
  • Chapter 7 describes how to take advantage of available access structures in order to access data stored in a single table efficiently.
  • Chapter 8 goes beyond accessing a single table, by describing how to join data from several tables together.

The flyer and this page provide detailed information about the seminar.

ASH Exploding Across Versions

 

I sometimes forget which versions of ASH have which fields, so here is a bit of a graphic and textual cheat sheet. (see https://sites.google.com/site/oraclemonitor/dba_hist_active_sess_history for some queries on ASH)

 

 

10.2.0
10.2.0.3
11.1.0
11.2.0 (http://download.oracle.com/docs/cd/E11882_01/server.112/e10820/dynviews_...)
SAMPLE_IDSAMPLE_IDSAMPLE_IDSAMPLE_ID
SAMPLE_TIMESAMPLE_TIMESAMPLE_TIMESAMPLE_TIME
SESSION_IDSESSION_IDSESSION_IDSESSION_ID
SESSION_SERIAL#SESSION_SERIAL#SESSION_SERIAL#SESSION_SERIAL#
SESSION_TYPESESSION_TYPESESSION_TYPESESSION_TYPE
USER_IDUSER_IDUSER_IDUSER_ID
QC_INSTANCE_IDQC_INSTANCE_IDQC_INSTANCE_IDQC_INSTANCE_ID
QC_SESSION_IDQC_SESSION_IDQC_SESSION_IDQC_SESSION_ID
SERVICE_HASHSERVICE_HASHSERVICE_HASHSERVICE_HASH
PROGRAMPROGRAMPROGRAMPROGRAM
MODULEMODULEMODULEMODULE
ACTIONACTIONACTIONACTION
CLIENT_IDCLIENT_IDCLIENT_IDCLIENT_ID
QC_SESSION_SERIAL#QC_SESSION_SERIAL#
CONSUMER_GROUP_IDCONSUMER_GROUP_ID

MACHINE

PORT
SQL_IDSQL_IDSQL_IDSQL_ID
SQL_CHILD_NUMBERSQL_CHILD_NUMBERSQL_CHILD_NUMBERSQL_CHILD_NUMBER
SQL_OPCODESQL_OPCODESQL_OPCODESQL_OPCODE
SQL_PLAN_HASH_VALUESQL_PLAN_HASH_VALUESQL_PLAN_HASH_VALUESQL_PLAN_HASH_VALUE
XIDXIDXIDXID
FORCE_MATCHING_SIGNATUREFORCE_MATCHING_SIGNATUREFORCE_MATCHING_SIGNATUREFORCE_MATCHING_SIGNATURE

PLSQL_ENTRY_OBJECT_IDPLSQL_ENTRY_OBJECT_IDPLSQL_ENTRY_OBJECT_ID

PLSQL_ENTRY_SUBPROGRAM_IDPLSQL_ENTRY_SUBPROGRAM_IDPLSQL_ENTRY_SUBPROGRAM_ID
PLSQL_OBJECT_IDPLSQL_OBJECT_IDPLSQL_OBJECT_ID

PLSQL_SUBPROGRAM_IDPLSQL_SUBPROGRAM_IDPLSQL_SUBPROGRAM_ID

TOP_LEVEL_SQL_IDTOP_LEVEL_SQL_ID

TOP_LEVEL_SQL_OPCODETOP_LEVEL_SQL_OPCODE

SQL_PLAN_LINE_IDSQL_PLAN_LINE_ID

SQL_PLAN_OPERATIONSQL_PLAN_OPERATION

SQL_PLAN_OPTIONSSQL_PLAN_OPTIONS

SQL_EXEC_IDSQL_EXEC_ID

SQL_EXEC_STARTSQL_EXEC_START

IN_CONNECTION_MGMTIN_CONNECTION_MGMT
IN_PARSEIN_PARSE
IN_HARD_PARSEIN_HARD_PARSE
IN_SQL_EXECUTIONIN_SQL_EXECUTION

IN_PLSQL_EXECUTIONIN_PLSQL_EXECUTION

IN_PLSQL_RPCIN_PLSQL_RPC

IN_PLSQL_COMPILATIONIN_PLSQL_COMPILATION

IN_JAVA_EXECUTIONIN_JAVA_EXECUTION

IN_BINDIN_BIND

IN_CURSOR_CLOSEIN_CURSOR_CLOSE

IN_SEQUENCE_LOAD

TOP_LEVEL_CALL#

TOP_LEVEL_CALL_NAME
IS_SQLID_CURRENT
SQL_OPNAME
EVENTEVENTEVENTEVENT
EVENT_IDEVENT_IDEVENT_IDEVENT_ID
EVENT#EVENT#EVENT#EVENT#
SEQ#SEQ#SEQ#SEQ#
P1P1P1P1
P2P2P2P2
P3P3P3P3
WAIT_TIMEWAIT_TIMEWAIT_TIMEWAIT_TIME
SESSION_STATESESSION_STATESESSION_STATESESSION_STATE
TIME_WAITEDTIME_WAITEDTIME_WAITEDTIME_WAITED
CURRENT_OBJ#CURRENT_OBJ#CURRENT_OBJ#CURRENT_OBJ#
CURRENT_FILE#CURRENT_FILE#CURRENT_FILE#CURRENT_FILE#
CURRENT_BLOCK#CURRENT_BLOCK#CURRENT_BLOCK#CURRENT_BLOCK#
P1TEXTP1TEXTP1TEXTP1TEXT
P2TEXTP2TEXTP2TEXTP2TEXT
P3TEXTP3TEXTP3TEXTP3TEXT
WAIT_CLASSWAIT_CLASSWAIT_CLASSWAIT_CLASS
WAIT_CLASS_IDWAIT_CLASS_IDWAIT_CLASS_IDWAIT_CLASS_ID

CURRENT_ROW#CURRENT_ROW#
TM_DELTA_TIME
TM_DELTA_CPU_TIME
TM_DELTA_DB_TIME
DELTA_TIME
DELTA_READ_IO_REQUESTS

DELTA_WRITE_IO_REQUESTS
DELTA_READ_IO_BYTES
DELTA_WRITE_IO_BYTES
DELTA_INTERCONNECT_IO_BYTES
PGA_ALLOCATED
TEMP_SPACE_ALLOCATE
BLOCKING_SESSIONBLOCKING_SESSIONBLOCKING_SESSION_STATUSBLOCKING_SESSION_STATUS
BLOCKING_SESSION_STATUSBLOCKING_SESSION_STATUSBLOCKING_SESSIONBLOCKING_SESSION
BLOCKING_SESSION_SERIAL#BLOCKING_SESSION_SERIAL#BLOCKING_SESSION_SERIAL#BLOCKING_SESSION_SERIAL#

BLOCKING_INST_ID

BLOCKING_HANGCHAIN_INFO
FLAGSFLAGS
REMOTE_INSTANCE#REMOTE_INSTANCE#

TIME_MODEL
IS_AWR_SAMPLE

ECID
CAPTURE_OVERHEAD
REPLAY_OVERHEAD
IS_CAPTURED
IS_REPLAYED





10.1
SAMPLE_ID SAMPLE_TIME SESSION_ID SESSION_SERIAL# SESSION_TYPE USER_ID QC_SESSION_ID QC_INSTANCE_ID SERVICE_HASH PROGRAM MODULE ACTION CLIENT_ID SQL_ID SQL_CHILD_NUMBER SQL_OPCODE SQL_PLAN_HASH_VALUE EVENT EVENT_ID EVENT# SEQ# P1 P2 P3 WAIT_TIME SESSION_STATE TIME_WAITED CURRENT_OBJ# CURRENT_FILE# CURRENT_BLOCK#

I Wish

A couple of days ago I wrote about some things I’d like to see in index statistics, which means changing dbms_stats.gather_index_stats(). Here’s an idea for dbms_stats.gather_table_stats(). I’d like to see the number of chained and migrated rows counted separately in dba_tables when we run the packaged procedure. The optimizer will use information about chained or migrated rows, but the information is only collected if you use the analyze command (and even then the two figures are summed into the single value chain_cnt) .

I have to say that it does look as if the information that Oracle would need to count (and distinguish between) chained rows and migrated rows is already available in the blocks. If we check a bit of a block dump we can see that various flags seem to tell us everything that the code would need to know. Here’s an extract from a table block dump, showing the start of three rows (technically row pieces) in the block.

tab 0, row 0, @0x1c79
tl: 9 fb: --H----- lb: 0x2  cc: 0
nrid:  0x0180000b.0

tab 0, row 1, @0x1952
tl: 807 fb: --H-FL-- lb: 0x0  cc: 4
col  0: [200]
 78 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
 ...

tab 0, row 2, @0x162b
tl: 807 fb: --H-FL-- lb: 0x0  cc: 4
col  0: [200]
 78 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
 ...

Notice particularly the “flag byte (fb)”, and the presence of one row reporting a “next rowid (nrid)”.
Amongst other things, the flag byte tells us what the row pieces represent.

H – this is a row header
F – this is the first row piece of a row
L – this is the last row piece of a row

These three flag values start us in the right direction. If we have a simple migrated row then we will have a head piece (in the original row position), pointing to the complete row. The head piece will have an H flag byte, and an nrid pointing to the actual row; when we get to the actual row its flag byte will show only the FL bits, and there will be an hrid (head rowid) associated with it, pointing back to the original location.

If we have a simple chained row, the first section will have the H and F bits set, and an nrid entry; any “middle” rowpieces will have an nrid, but no flag bits set, and the last row piece will have the L bit set, and no nrid or hrid.

If a row has migrated, and then chained after migrating, the first piece will have the H bit set, and an nrid entry; the next piece will have the F bit set and both an hrid and nrid; any middle pieces will have an nrid but no flag bits set; and the last piece will have the L bit set, but no nrid or hrid.

There may be other alternatives, of course, e.g. a row that first chains on an update, leaving a few columns behind, and then migrates those columns out of the initial location; then there may be other variations when rows shrink and start to “unchain” or “migrate back (partially)”; and I haven’t even started to think about rows with more than 254 columns which have “intra-block” chaining – i.e. several row pieces in the same block. But looking at the flags and information about head/next row pieces, it looks as if a full scan of a table could identify the number of chained rows and the number of migrated rows separately. If it has an H only it has migrated (but may also have chained), if it’s got an HF it has chained (only). Any rows with no flag setting or L don’t add much value unless we want to work out an average number of pieces per chained row. Any rows with FL are the migrated rows.  Any rows with F only are rows which have migrated and then chained.

Inevitably there could be accounting errors on sampling, though. We might find 10 rows with H only, but only 7 rows with FL; what assumption should we make about the three outstanding rows ? Might they be just migrated, or could they be migrated and chained ? In view of the room for error, it might be sensible to implement the counts only as part of the approximate NDV mechanism that only runs if you choose the auto sample size option, and always seems to do a full segment scan.

Related articles.

Another difference between analyze and dbms_stats
Anomalies with counting continued row activity