Who's online

There are currently 0 users and 21 guests online.

Recent comments


Oakies Blog Aggregator

Table rebuilds

Here’s a question from the OTN forum that I’ve seen fairly frequently in the last couple of years:

I have a table abc(c_date date, c_id varchr2(20), c_name varchar2);
The table is already range partitoned on “c_date” column and I have lot of data in that table.
Now my task is to create the hash partions under range; hash partiton needs 16 partitons.
I dont want to create another table with range and hash partitons and migrate the data. The amount of data I have is 3 terabytes.

The statement of requirements is a little ambigious in this case – does the OP want a table where new partitions should be hash partitioned, or does he want the entire data set to be range/hash partitioned. If it’s the latter then he will have to do something that rewrites (and re-indexes) the entire data set; if the former then he can run through a process that does a small amount of data dictionary manipulation to get to the required state. Here’s a quick outline that I hacked together in a few minutes between sessions (okay, I’ll admit it also needed a few minutes during a session) at the ACED briefing.

  1. Create an empty clone of the table, but introduce hash subpartitioning with a single subpartition per partition.
  2. Create a pair of ‘staging’ clones of the table – the first should be a non-partitioned table, the second should be a hash-partitioned table
  3. For each partition in the original table,
    1. exchange the partition with the first staging table
    2. exchange the first staging table with the single partition of the second staging table
    3. exchange the second staging table with the target partition of the final table
  4. Patch some statistics that can’t otherwise be created in all the exchanges
    1. Copy the hash subpartition stats up to the range partition
    2. copy the original table-level stats to the new table
  5. Change the hash partition default, drop the transfer tables and the old table and rename the new table

Here’s a code demonstration – but it doesn’t include the stats patching, and it’s not a model you should use exactly as it is on a production system.

First we create a range-based table for our demonstration:

create table pt_range (
	id1	not null,
	id2	not null,
partition by range(id1) (
	partition p200 values less than (200),
	partition p400 values less than (400)
	cast(rownum as number(8))				id1,
	cast(trunc(dbms_random.value(1,64)) as number(8))	id2,
	cast(trunc(rownum/50) as number(4))			grp,
	lpad(to_char(trunc(rownum/20)),10)			small_vc,
	rpad('x',100)						padding
	rownum <= 350

Now we can create an empty copy – but introduce the hash subpartition – and two staging tables.

create table pt_range_hash (
	id1		number(8,0)	not null,
	id2		number(8,0)	not null,
	grp		number(4),
	small_vc	varchar2(10),
	padding		varchar2(100)
partition by range(id1)
subpartition by hash (id2)
subpartitions 1
	partition p200 values less than (200),
	partition p400 values less than (400)

create table transfer_r (
	id1		number(8,0)	not null,
	id2		number(8,0)	not null,
	grp		number(4),
	small_vc	varchar2(10),
	padding		varchar2(100)

create table transfer_h (
	id1		number(8,0)	not null,
	id2		number(8,0)	not null,
	grp		number(4),
	small_vc	varchar2(10),
	padding		varchar2(100)
partition by hash (id2)
	partition p1

You’ll notice that I’ve matched the partition names of the full clone using a very simple subpartition name for the one hash subpartition, and repeated that simple name for one partition of the hash-partitioned table. This isn’t absolutely necessary, but I think it makes the next process simpler to understand.

	for r in (
		select	partition_name
		from	user_tab_partitions
		where	table_name = 'PT_RANGE'
		order by
	) loop


		execute immediate
			'alter table PT_RANGE exchange partition ' ||
			r.partition_name ||
			' with table transfer_r including indexes without validation'

		execute immediate
			'alter table transfer_h exchange partition ' ||
			'p1' ||
			' with table transfer_r including indexes without validation'

		execute immediate
			'alter table pt_range_hash exchange partition ' ||
			r.partition_name ||
			' with table transfer_h including indexes without validation'

	end loop;

When this block completes your data hasn’t moved, it hasn’t been copied, but it is now in a range/hash partitioned table. The statistics that used to exist at the partition level on the original table will now be on the new table at the subpartition level, and the new table won’t have partition-level or table-levle stats. (I may find time to write part 2 to this note with code that fixes the stats – but any reader who wants to have a shot at it instead is welcome to supply the code).

Now we can change the default behaviour of the new table to specify the number of hash partitions for future partitions:

alter table pt_range_hash
	set subpartition template 16

NOTE: I did say that this is a model that you shouldn’t follow in a production system. The main reason for this is that if something does go wrong in the loop then you could end up with partitions switching around into the wrong place in the wrong table. (And you definitely don’t want to drop any objects until you’ve checked them all!). Realistically you would probably transfer one partition at a time, and check the results after each transfer. Bear in mind, also, that this was just a little hack put together very quickly. Even though the general strategy is sound I may have overlooked some critical detail and, obviously, there are some variations on theme (IOTS, for example) where you would need to work out some code changes; there’s also an implicit assumption that the only indexes on the table are local indexes – if there are any global, or globally partitioned, indexes then the code has to be modified to allow for them, and the time for the various exchanges will be affected by the choice of index maintenance options.


Friday Philosophy – Team Ice-Cream and Telling Offs

If you manage people, it helps if they don’t dislike you. Sadly, this can be the default starting opinion for some people who have never been managers (we all know someone who “has never had a decent manager, they are all bloody idiots”). Frozen dairy products might be a route to easing this situation.

I mention this as we in the UK are having an unusually warm start to autumn, an Indian Summer as we call it. I used to work in a place that had an on-site cafe and a nice area outside to sit. If the weather was warm and I knew my team was not facing some crisis, I would occasionally pop my head around the door and announce “Team Ice-Cream!”. Anyone who wished could come down with me and I would buy them an ice-cream of their choice and we would sit out in the sun for 15 minutes and talk rubbish.

I’ve done similar in other situations. Taking the guys to the pub is the obvious one and it usually is appreciated, but in some ways it is less successful. I think this is because people will come to the pub because they want a pint and will put up with any idiot willing to provide a pint of Fosters (why is it so many of the “all managers are idiots” brigade drink some brand of nasty lager?). People will come for a tea/coffee or an ice-cream only if they are at least ambivalent to the provider. If you really dislike someone, who cares about an ice-cream? The serious malcontents will stay away and this helps identify people who really are not happy with you {so you can beat them mercilessly of course – or, if you’ve progressed beyond the school-yard, put some thought into why they are unhappy and what to do about it}.

By the way, this is very different to everyone going to the pub/restaurant in the evening and spending hours telling people what “you really think” and trying to impress Jessica the new trainee/intern. Such team building events generally need much more planning.

It’s a cheap bribe, should you resort to such shallow tactics to make people like you? Well, it’s only a cheap bribe as I said above. The trick to it is that it has to be {almost} spontaneous, such that the team are not expecting it, and not all the time. I’m not sure the teams I have done this for have always appreciated that I made special effort to do this either after a hard period of work or when there had been some malcontent within the team (people fall out, it impacts the rest of the team). The way I look at it, it also has to be a team thing and not an individual thing as the sitting around talking rubbish is a key part to the team being a team. Even if it is just over a cup of nasty coffee in the basement – that particular company’s canteen was not the best.

Oh, I should mention that I have access to a wife that makes wonderful cakes. Left-over cake is a brilliant “team ice-cream” substitute, it is both “cheap” so not a bribe but also appreciated as someone put effort in. My wife in this case. I Never claim I made the cake. well, not often.

TeddyBear Picnic Cake

That’s the carrot. What about the stick? When it comes down to it, you are there to guide the team and the individuals in it and get the best you can out of them. Not being disliked is important but you are not there to be their friend either. If someone transgresses you need to correct them.

In my opinion one of the very worst things a manager can do is dress down a member of their staff in public. That is not correcting them, that is either an attempt to humiliate them or an attempt by the boss to scape-goat the blame to a subordinate. Neither is morally correct and both are highly likely to engender considerable dislike or even hatred.

I distinctly remember one situation where I was in a team meeting and the boss’s managers came in and wanted to know why a recent change had gone so badly wrong. The manager’s response was immediate, he picked one of the team and said something like “It was him, he didn’t test the change properly”. It was so obvious that the sub-text was “it was not my fault”. In reality the sacrificed staff member was not at fault but the boss sure as heck was. A manager gets paid more as a boss and part of the reason is that you take both the credit and the blame for your team’s efforts. This action by that boss did not make us scared of failing and thus work harder, it made us distrust the man and demoralised us.

Sadly it is something I’ve seen a lot over the years and never by what I would call a good manager. I just don’t understand why these people think a public dressing down is going to inspire the target or the audience to work more effectively.

If I’m in the situation where, in a meeting or discussion, it becomes obvious one of my guys has screwed up we discuss how to sort it out as a team. Then after the meeting, the transgressor and I have a private conversation. This has several benefits:

  • I am not publicly humiliating them or scoring points in front of a crowd.
  • Neither of us is playing to the crowd and so are more likely to be honest.
  • Things can be said that stay private. I’ve had team members mess things up because they have more important issues on their mind that they are uncomfortable with the team knowing about. I’ve had to tell a guy this is chance #last and the next step is disciplinary.
  • This never happens, but there is a very small theoretical chance I could have misunderstood and, in fact, it’s my fault. You look a right idiot if you attempt to dress someone down in public and it turns out to be you.

As I said, that last point has never happened to me {yes, this is an outrageous lie :-) }. I’ve experienced that last point from the other side as well. In a large meeting I had a board member pushing me as to why we had not finished a project on the date I promised. I kept giving vague answers about “other things coming up” and it would be done by a new, given date. She would not let it go though so eventually I had to say “It is late because you told me to do other stuff as top priority, I raised this project and you told me to delay it. So it is late because you changed the priorities. That would make it your responsibility.” She was very angry but it had been her choice to do this publicly.

All this boils down to – Reward the team in public. Chastise the individual in private.

OOW2011 ACE Directors Briefing

After a happy and uneventful direct flight (what a joy it is to use those
words!) to SFO, I emerged into sunshine and arrived at the Sofitel near Oracle
HQ around about tea-time to rest before today's ACE Director Briefing. Once the
Cuddly Toys had settled on their settee ...

(... and checked the luggage allowance for
the return trip. They think they're taking it home!), I caught up with mail then
popped down to the bar, just on the off-chance I *might* find some Oak Table
types there ;-)

As the others had abandoned me to go for food, I settled
on a quick drink before adjourning to my room for some nice room service and
some more slide-polishing ;-)

Well, that was the plan but then James Morle
turned up and then Cary Millsap and then Alex Gorbachev and then .... Well, the
conversation and drinks flowed and, before I knew it, it was 1am and I'd had
nothing to eat! Sigh

Fortunately, the food at the ACE D Briefing is
pretty good and to my taste. Hooray!

I was surprised at the high turn-out of people at the Oracle Conference Centre - there must be 100+ ACE D's in attendance. I must confess I wasn't sure how
much I'd enjoy the briefing - too many buzz-words and chat about technology
areas I don't give a monkey's about! I've been very pleasantly surprised so far.
Even areas that I had less interest in such as the Dev Tools stuff were
listenable because the OTN people managed to line up decent speakers. There are
also more upcoming announcements or additions to existing announcements in the
database domain and not just Fusion Apps or middleware. I'd rather not go into
the detail because even the subjects we can blog about at this stage probably
need more thought so it might be after I've attended more presentations next
week. (Added later - I'm glad I decided not to blog about the announcements or might have had to edit this post down a little ;-))

I'd already arranged to meet some friends from Oracle later in the afternoon so skipped out after the Exadata Q & A for coffee, chat and a bit of a sneak preview of the ASH Analytics part of OEM 12 courtesy of JB in the company of a couple of other Oak Table types. But pretty soon it was time to walk back to the hotel for a couple of quick beers with Marco before going to bed early to wake up early to keep polishing slides!

All in all, I really enjoyed my first day much
more than I expected and I'm able to look forward to tomorrow (erm, today ...)

It's a shame the part about going to bed early didn't work quite as well as planned, but the chat and company here are too good. Fortunately the getting up early part did work so now I suppose it's back to those damn slides again :-(

Disclosure - I'm attending the briefing and Openworld 2011 courtesy of
the Oracle ACE Director program, which is covering my expenses. The time off
work, Cuddly Toy incidentals and sizeable bar bills are on me.

Oracle Database Appliance: Storage Performance — Part 1

Today I want to show what kind of IO performance we can get from Oracle Database Appliance (ODA). In this part, I will focus on hard disks. That’s right — those good old brown spinning disks. I often use Oracle ORION tool to stress-test an IO subsystem and find it’s limits. It’s a very simple [...]

Oracle Big Data Appliance — What’s Cooking?

Many analysts are suggesting that a big data appliance will be announced at this OOW. Based on published Oracle OpenWorld focus sessions on (PDF documents), the following technologies will most likely be the key — Hadoop, NoSQL, Hadoop data loader for Oracle, R Language. Want more details — you have to wait for them. [...]

Arrived at San Francisco (OOW & ACED)

There were no real dramas on the way from Birmingham to San Francisco, which makes a change for me. :)

I got to San Francisco at 16:00 local time and told myself I would go to bed at 21:00 at the latest. I trotted off to the gym, got cleaned up and went down to the bar to say hello to some people. Fatal mistake. I did my normal trick of talking incessantly until about 01:00. Luckily I was not drinking pints of “jetlag” like others I could mention. :)

So at about 01:00 I was in bed. I woke up at about 04:15. :( If nothing else it meant I got to catch up on all my emails and go to the gym again. I think there are going to be a few very tired people during the ACED meeting today. I apologise in advance if I fall asleep in your session.

As always, I’m a little daunted at the start of this week. Having 2 days of ACED meetings followed by the main 5 day conference is a lot more difficult than it sounds, especially when everyone knows enough to make you paranoid about your own abilities. Having said that, I know it’s going to be fun and I know I’ll be glad I came.



PS. Already missing the people I know can’t make it this year. Excited about meeting up with everyone again.

PPS. Must remember to speak less and listen more.

PPPS. Must try remain calm this year, not act like a kid in a sweet shop. :)

OOW 2011 – On Our Way

Early mornings…really hate them, but can avoid it this time due to our flight going out towards San Francisco is scheduled at 9.50PM. Oracle Open World is near and a few days away from getting started. Alex, Lucas and I meet up this morning in Schiphol, Hollands main airport for our flight to the ACE …

Continue reading »

Having Fun with ORA-00600 (qkebCreateConstantOpn1) – What is the Cause of this Error?

September 28, 2011 A friend brought an interesting Oracle Database problem to my attention. He is in the process of upgrading to a new Oracle Database server (and Oracle Database version) and a new version of an ERP package. He has experienced an ORA-00600 error with both patch 6 and patch 9 for [...]

Thirteen signs of DBA fudging

If you are a director, manager or project manager who works with DBAs, you probably have had the nagging suspicion at one time or another that a DBA’s assertions regarding his or her practices lack an empirical or scientific basis, or are simply deflections intended to pass the buck.

Manager: Mr. DBA, the application is really slow. Do you have any idea what’s wrong?

DBA: Oracle is very complex. It could be any of 100 different possible causes. I will begin checking each. Anyhow, what makes you think it is the database?

Some DBAs are professional, thoughtful scientific-minded contributors. But the sad truth is that many DBAs lack the skills to professionally manage their systems. To cover, they use deflections such as the example above, or fall back on old, long-disproved practices without the benefit of evidence. Why is this true of DBAs?  One reason is that the standard education options are poor.

To help non-DBAs realize that they are being subjected to misdirection, obsolete advice, or simply ignorance, we have compiled a list of common ways that DBAs cover for their shortcomings, and avoid legitimate empirical investigation and analysis of problems and solutions.

Take notice when a DBA:

  1. Cites “best practices,” or advice from a particular guru or website, as the sole reason for doing something.
  2. States that “this is how we have always done it” as the sole reason for doing something.
  3. Claims that reducing “head movement” by changing file or disk layout will improve performance.
  4. Blames “fragmentation” for performance problems and states the database must be taken down for “defragmentation.”
  5. Spends lots of time (and compute resources) rebuilding indexes.
  6. States he/she must do things to improve the “buffer cache hit ratio.”
  7. Discourages the use of RMAN or ASM because they are “too complex.”
  8. Insists on separating indexes from tables physically, despite a modern caching SAN.
  9. Blames performance problems on a SQL statement’s length, apparent complexity, length of execution plan or number of joins.
  10. Says that there is no conclusive way to determine the cause of slowness.
  11. Claims certain actions are “dangerous” without having a cogent technical explanation of why.
  12. States it is necessary to perform manual tasks upon database startup, or at any regular scheduled interval
  13. Accounts for time by listing the procedures/checks he/she manually runs on a daily/weekly/monthly basis

At Blue Gecko, our remote DBA services are based in proven, evidence-based standards.  When we assert that something must be done, we will always be able to provide a cogent reason, and cite evidence for our actions. We don’t do things without a good reason.  We find that pat advice from “gurus” often lacks a basis in evidence.  We deal in databases. We should always be able to cite data!

    Related posts:

    1. New whitepaper: Common Oracle Misconceptions
    2. Jeremiah Wilton published in the latest Oak Table press book!
    3. Proactive support

    NoCOUG: Performance Training with Gaja Vaidyanatha Nov 15,16

    There are only three days left (Sept 30)  to get the early bird rate for the Oracle Performance Management Seminar by Gaja Krishna Vaidyanatha covering the following topics.

    • Oracle Database Architecture
    • Multiversion Read Consistency
    • What is Performance Optimization?
    • When Should You Optimize?
    • Oracle Database Diagnostics
    • Operating System Diagnostics
    • Oracle Wait Interface
    • Low-level Trace Methods
    • Cost-Based Optimizer
    • Oracle 10g Performance Management
    • Oracle 11g Performance Management
    • Real-World Case Studies

    The seminar will be held on November 15 and 16 at the Carr America conference center in Pleasanton, CA. The price goes up on October 1. For more details on the seminar and to register, click here.