Search

OakieTags

Who's online

There are currently 0 users and 55 guests online.

Recent comments

Affiliations

December 2010

Executing SQL statements from within the Data Guard broker (DGMGRL)

While teaching the Oracle11g release 2 Data Guard course last week my students and I found a new feature of the Data Guard broker (DGMGRL). We discovered that it is now possible to execute SQL statements directly from within DGMGRL. This new feature became available after installing the first Oracle Database 11g release 2 patchset […]

Edition-Based Redefinition (update)

Last January I wrote a post with the following content:

Upgrading critical applications can be very difficult. One of the main problems is that for reasons of availability, long downtimes cannot be periodically scheduled. Therefore, for such applications, it is desirable to implement online upgrades. This requires that the application in question, as well as any software used by the application (e.g. the database engine) all support online upgrades. Oracle has recognized this problem for years. Unfortunately, up to and including Database 11g Release 1, only a limited number of features have been implemented for that purpose. As of Oracle Database 11g Release 2, this situation has changed greatly. With edition-based redefinition, Oracle Database offers real support for implementing online upgrades. The aim of the paper Edition-Based Redefinition, that I just put online here, is to provide an overview of this new feature.

I updated the aforementioned paper to provide information about the capability, as of 11.2.0.2, to specify a default edition at the service level. At the same time I added a note about the SHOW EDITION command.

DB_BLOCK_SIZE and DB_FILE_MULTIBLOCK_READ_COUNT 3 – What is Wrong with this Quote?

December 4, 2010 (Back to the Previous Post in the Series) (Forward to the Next Post in the Series) I recently reviewed the book “Oracle Tuning the Definitive Reference Second Edition”, and did not provide an in-depth technical review of the entire book.  As I stated in a comment in the earlier blog article, I would [...]

A follow up...

A follow up to my last post. I think this guy just hit the nail on the head. I've read some of his stuff in the past. He breaks down and explains security issues pretty darn good. I think he got this idea (Washington Monument) right too.

Stuff happens. There are crazy people out there. There is a fine line between being safe and being paranoid. Maybe not even that fine. We now know that if a hijacking takes place, you don't sit in your seat - you rush the people trying to do it and pummel them. The cockpit doors being reinforced is the greatest security measure ever - and more than enough. The rest of it all - just intrusive and sometimes just embarrassing.
You have to draw a line between reasonable security and "we'll reactively respond to every new tactic as it comes along".
My big fear - they start doing this (the 'security' thing) for monuments, buses, subways, trains, state parks, restaurants, movies, etc.
I've flown over 200,000 miles this year - that is about 10 working weeks in the air - that involves a lot of airports. I know how intrusive this stuff can be. I would hate to see the "security stuff" impose itself on my other 40 some weeks (and everyone else's time).
I don't know why, but I'm reminded of one of my favorite movies. Brazil.

UltraEdit for Mac v1.3 – Beta 7.1

A new spin of Mac version of UltraEdit is out. I used the previous beta version for 2 days of demoing in Serbia and it worked great. The production release should be very soon and I for one am a happy camper.

Cheers

Tim…

ukoug2010 - doug burns movember

Media: 
See video

 Doug's moment of fame at the 2010 UKOUG in Birmingham
 

ANSI – argh

I’m not keen on ANSI standard SQL – even though it is, technically, the strategic option and even though you have to use it for full outer joins and partitioned outer joins.

One reason for disliking it is that it “separates join predicates from filter predicates” – a reason often given in praise of the syntax which, to my mind, claims a spurious distinction and introduces a mechanism that makes it harder to keep mental track of what’s going to happen as you walk  through the join order.

The other reason for disliking ANSI SQL in Oracle databases is that sometimes it really is necessary to add hints to the SQL to make the optimizer do what needs to be done – and ANSI makes it so much harder and messier to add hints to code. Here’s a wonderful example that Tony Hasler presented in our recent debate “Does Oracle Ignore Hints” at the UKOUG annual conference:

WITH q1 as (
	SELECT /*+ qb_name(q1block) */
		*
	FROM	t1
	JOIN	t2
	ON	t1_i1 = t2_i1
	AND	t1_i1 < 10
),
q2 AS (
	SELECT
		/*+ qb_name(q2block) */
		*
	FROM
		t3
	JOIN	t4
	ON	t3_i1 = t4_i1
	AND	t3_i1 < 10
)
SELECT
	/*+
		no_merge(@q1block)
		no_merge(@q2block)
		leading (@q1block t2)
		use_nl  (@q1block t1)
	*/
	*
FROM
	q1
JOIN
	q2
ON	t1_i1 + t2_i1 = t3_i1 + t4_i1
;

Just to make life really hard, he’s included a couple of “factored subqueries” – and there are a few outstanding optimizer defects with handling subquery factoring – so when he claimed that this was an example of Oracle ignoring hints I had two different directions of investigation to worry about.

Here’s the execution plan (from my 10.2.0.3 system with the data generation, constraints and indexing that Tony supplied):

------------------------------------------------------------------------
| Id  | Operation                      | Name  | Rows  | Bytes | Cost  |
------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |       |   250K|    33M|    54 |
|*  1 |  HASH JOIN                     |       |   250K|    33M|    54 |
|   2 |   VIEW                         |       |  5000 |   327K|    11 |
|*  3 |    HASH JOIN                   |       |  5000 |   581K|    11 |
|   4 |     TABLE ACCESS BY INDEX ROWID| T4    |  5000 |   253K|     3 |
|*  5 |      INDEX RANGE SCAN          | T4_I1 |   900 |       |     2 |
|   6 |     TABLE ACCESS BY INDEX ROWID| T3    |  5000 |   327K|     3 |
|*  7 |      INDEX RANGE SCAN          | T3_I1 |   900 |       |     2 |
|   8 |   VIEW                         |       |  5000 |   361K|    12 |
|*  9 |    HASH JOIN                   |       |  5000 |   615K|    12 |
|  10 |     TABLE ACCESS BY INDEX ROWID| T1    |  5000 |   297K|     3 |
|* 11 |      INDEX RANGE SCAN          | T1_I1 |   900 |       |     2 |
|  12 |     TABLE ACCESS BY INDEX ROWID| T2    |  5000 |   317K|     3 |
|* 13 |      INDEX RANGE SCAN          | T2_I1 |   900 |       |     2 |
------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$16C51A37
   2 - SEL$4C69CCA2 / Q2@SEL$1
   3 - SEL$4C69CCA2
   4 - SEL$4C69CCA2 / T4@SEL$2
   5 - SEL$4C69CCA2 / T4@SEL$2
   6 - SEL$4C69CCA2 / T3@SEL$2
   7 - SEL$4C69CCA2 / T3@SEL$2
   8 - SEL$7939585E / Q1@SEL$1
   9 - SEL$7939585E
  10 - SEL$7939585E / T1@SEL$3
  11 - SEL$7939585E / T1@SEL$3
  12 - SEL$7939585E / T2@SEL$3
  13 - SEL$7939585E / T2@SEL$3

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T1_I1"+"T2_I1"="T3_I1"+"T4_I1")

As you can see, Oracle has copied the two factored subqueries inline (they appear just once each in the body of the query so this is – probably – inevitable). Then Oracle has obeyed the no_merge() hints – which I could check by deleting the hints and watching the plan change. So why, in lines 10 through 13, has Oracle not obeyed the leading() and use_nl() hints ?

By changing the ANSI syntax to traditional Oracle syntax, I got a different plan:

-------------------------------------------------------------------------
| Id  | Operation                       | Name  | Rows  | Bytes | Cost  |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |       |   250K|    33M| 10045 |
|*  1 |  HASH JOIN                      |       |   250K|    33M| 10045 |
|   2 |   VIEW                          |       |  5000 |   327K|    11 |
|*  3 |    HASH JOIN                    |       |  5000 |   581K|    11 |
|   4 |     TABLE ACCESS BY INDEX ROWID | T4    |  5000 |   253K|     3 |
|*  5 |      INDEX RANGE SCAN           | T4_I1 |   900 |       |     2 |
|   6 |     TABLE ACCESS BY INDEX ROWID | T3    |  5000 |   327K|     3 |
|*  7 |      INDEX RANGE SCAN           | T3_I1 |   900 |       |     2 |
|   8 |   VIEW                          |       |  5000 |   361K| 10003 |
|   9 |    TABLE ACCESS BY INDEX ROWID  | T1    |     1 |    61 |     2 |
|  10 |     NESTED LOOPS                |       |  5000 |   615K| 10003 |
|  11 |      TABLE ACCESS BY INDEX ROWID| T2    |  5000 |   317K|     3 |
|* 12 |       INDEX RANGE SCAN          | T2_I1 |   900 |       |     2 |
|* 13 |      INDEX RANGE SCAN           | T1_I1 |     1 |       |     1 |
-------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   2 - Q2BLOCK / Q2@SEL$1
   3 - Q2BLOCK
   4 - Q2BLOCK / T4@Q2BLOCK
   5 - Q2BLOCK / T4@Q2BLOCK
   6 - Q2BLOCK / T3@Q2BLOCK
   7 - Q2BLOCK / T3@Q2BLOCK
   8 - Q1BLOCK / Q1@SEL$1
   9 - Q1BLOCK / T1@Q1BLOCK
  11 - Q1BLOCK / T2@Q1BLOCK
  12 - Q1BLOCK / T2@Q1BLOCK
  13 - Q1BLOCK / T1@Q1BLOCK

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T1_I1"+"T2_I1"="T3_I1"+"T4_I1")
   3 - access("T3_I1"="T4_I1")
   5 - access("T4_I1"<10)
   7 - access("T3_I1"<10)
  12 - access("T2_I1"<10)
  13 - access("T1_I1"="T2_I1")
       filter("T1_I1"<10)

Notice how the optimizer is now obeying the leading() and use_nl() hints.

The problem is this: Oracle doesn’t optimise ANSI SQL, it transforms it then optimises it. Transformation can change query blocks, and Tony’s hints apply to specific query blocks. After a little testing and checking I worked out what the SQL looked like AFTER transformation and BEFORE optimisation; and it’s this:

select
	/*+ qb_name(sel$4) */
	*
from
	(
	SELECT
		/*+ qb_name(sel$1) */
		Q1.T1_I1 T1_I1, Q1.T1_I2 T1_I2, Q1.T1_D1 T1_D1, Q1.T2_I1 T2_I1, Q1.T2_I2 T2_I2, Q1.T2_TS T2_TS,
		Q2.T3_I1 T3_I1, Q2.T3_I2 T3_I2, Q2.T3_TSTZ T3_TSTZ, Q2.T4_I1 T4_I1, Q2.T4_I2 T4_I2
	FROM
		(
		SELECT
			/*+ NO_MERGE QB_NAME (Q1BLOCK) */
			from$_subquery$_003.T1_I1_0 T1_I1, from$_subquery$_003.T1_I2_1 T1_I2,
			from$_subquery$_003.T1_D1_2 T1_D1, from$_subquery$_003.T2_I1_3 T2_I1,
			from$_subquery$_003.T2_I2_4 T2_I2, from$_subquery$_003.T2_TS_5 T2_TS
		FROM	(
			SELECT
				/*+ qb_name(sel$3) */
				T1.T1_I1 T1_I1_0, T1.T1_I2 T1_I2_1, T1.T1_D1 T1_D1_2,
				T2.T2_I1 T2_I1_3, T2.T2_I2 T2_I2_4, T2.T2_TS T2_TS_5
			FROM
				TEST_USER.T1 T1,
				TEST_USER.T2 T2
			WHERE
				T1.T1_I1 = T2.T2_I1
			AND	T1.T1_I1 < 10
			)	from$_subquery$_003
		)	Q1,
		(
		SELECT
			/*+ NO_MERGE QB_NAME (Q2BLOCK) */
			from$_subquery$_006.T3_I1_0 T3_I1, from$_subquery$_006.T3_I2_1 T3_I2,
			from$_subquery$_006.T3_TSTZ_2 T3_TSTZ, from$_subquery$_006.T4_I1_3 T4_I1,
			from$_subquery$_006.T4_I2_4 T4_I2
		FROM
			(
			SELECT
				/*+ qb_name(sel$2) */
				T3.T3_I1 T3_I1_0, T3.T3_I2 T3_I2_1, T3.T3_TSTZ T3_TSTZ_2,
				T4.T4_I1 T4_I1_3, T4.T4_I2 T4_I2_4 FROM TEST_USER.T3 T3, TEST_USER.T4 T4
			WHERE
				T3.T3_I1 = T4.T4_I1
			AND	T3.T3_I1 < 10
			)	from$_subquery$_006
		)	Q2
	WHERE
		Q1.T1_I1 + Q1.T2_I1 = Q2.T3_I1 + Q2.T4_I1
	)
;

I got most of this from the “Query Block Name / Object Alias” section of the ANSI execution plan (there are some important clues there, like ‘T1@SEL$3′) and the “unparsed” SQL from the 10053 trace.

Notice how the query blocks q1block and q2block still exist – that’s why the no_merge() hints can survive the transformation. Notice, though, that the transformation engines has introduced a layer of inline views inside q1block and q2block - which is why the leading(@q1block t2) and use_nl(@q1block t1) hints are no longer valid: they reference objects which are not in q1block. To get his hints to work at the global level, Tony would have to change the last two hints to reference sel$3 rather than q1block.

So, next time you write a complicated piece of ANSI, make sure you think carefully about what you’re going to have to do if you subsequently have to add hints to force a particular execution plan.  (And bear in mind that one day the transformation engine might be modified to transform the query differently.)

[Further reading on "ignoring hints"]

Exadata on Cloud

I just saw this from Oracle Newsflash. Oracle Launches Exadata On Demand for High-Performance Computing. http://www.oracle.com/newsletters/information-indepth/on-demand/dec-10/exadataod.html.

This is very exciting. Of course the details are little at this time. But when the model matures, it allows us to play with the technology without investing a whole lot in in. A pet peeve of mine about Exadata was that it was a hardware; not a software. Unlike other Oracle products, I couldn't just download it and test the features. The limitation is huge. My role in my organization is to develop roadmaps and champion technological innovations. But without getting hands dirty, it comes to a pure paper excercise, which is not something I ever do.

With this, I am sure a lot of folks will now be able to validate their current systems against the Exadata platforms, making it easier and more prudent for them to champion it within their sphere of influence.

DB_BLOCK_SIZE and DB_FILE_MULTIBLOCK_READ_COUNT 2 – What is Wrong with this Quote?

December 3, 2010 (Back to the Previous Post in the Series) (Forward to the Next Post in the Series) I recently reviewed the book “Oracle Tuning the Definitive Reference Second Edition”, and did not provide an in-depth technical review of the entire book.  As I stated in a comment in the earlier blog article, I would [...]

Friday Philosophy – Run Over by a Bus

I chaired a session at the UKOUG this week by Daniel Fink, titled “Stop Chasing your tail: Using a Disciplined Approach to Problem Diagnosis”. It was a very good talk, about having a process, an approach to solving your IT problems and that it should be a process that suits you and your system. All good stuff and I utterly agree with what he said.

But it was a passing comment Daniel made that really set me thinking. It was something like:

You should be considering how people will look after the system after you have gone, the classic ‘what will we do if you are hit by a bus’….. No, I don’t like thinking like that, that phrase… I prefer ‘after you win the lottery and retire to a great life’.

It just struck a chord with me. Mr Fink’s {and I do go all formal when I intend respect} take on this is a far more positive way of looking at the situation of leaving the system in a state that others can look after once you are no longer able to help. The “Bus” phrase is very, very common, at least in the UK and I suspect in the US, and it is a very negative connotation. “Make sure it all works as something nasty is going to happen to you, something sudden, like being smeared across the tarmac by 25 tons of Greyhound doing 50mph, something basically fatal so you can’t prepare and you can’t help any more”. So, not just moved on, but dead.

Daniel made me realise that we should be looking at this from totally the other perspective and that doing so is much, much, much better. “Make it work so that they love you, even when you have gone away to a happier situation – one involving no road-based unpleasantness at all”.

Everyone leaves their job eventually and I like to think it is often for more positive reasons. Like retiring, or a better job {better for you, but a real shame for your old company as they like you so much}, moving to a new area, attempting a dream. Yes, sometimes (depressingly often at present) it is because you get made redundant or things go bad with your managers, or HR take over the organisation. But even so, better to leave knowing you did so with your professional duty intact I think. It’s one way of winning in a losing situation.

If turning the “bus” metaphor into a “lottery” metaphor results in the response in your brain of “well, when I do leave rich and happy, I still want to leave a painful mess behind me” – then it may indicate that you better leave where you are working as soon as possible in any case? As it is not a good situation and you are deeply very unhappy about it.

Up until now I have sometimes used a far more gruesome but less fatal phrase for the concept of making sure things continue after you leave and can no longer help, which is “involved in a freak lawnmower accident”. As in, can’t type but not dead. I’m going to stop using it, I’ve decided that even with my macabre sense of humour, it really is not a good way to think about doing your job properly. Daniel, your attitude is better. Thank you.

Oh, if you went along to the conference you can get the latest version of Daniel’s talk slides from the UKOUG web site (try this link), otherwise, he has a copy here – pick “papers and presentations”. It has lots of notes on it explaining what the slides mean (ie, what he actually says), which I think is a very nice thing for him to have spent the time doing.