Oracle ORA600 News Aggregator

The Bottom Line About Oracle Deadlocks (Sequel to It Was a Dark and Stormy Night)

kurtvm — Tue, 09 Mar 2010 21:59:44 +0100

Here’s the bottom line to my last post It Was a Dark and Stormy Night (A Story About Deadlocks): Oracle Database handles deadlocks differently than other database engines such as SQL Server and DB2. The transaction of the selected victim is not rolled back; only its last statement is rolled back. All the other participants in the [...]

Hotsos Symposium 2010 — Battle Against Any Guess Is Won

kurtvm — Tue, 09 Mar 2010 21:16:51 +0100

Video fragments of my session posted at the end — read on.

I arrived at Omni Mandalay Hotel on Sunday evening with Dan Norris. I was flying through Chicago and it turned out that Dan was on the same flight and only few rows behind me. Small world.

Preparations for the conference were very chaotic on my part and, of course, I didn’t have either of my presentations ready. I was very stressed and getting sick as well — it looked like a complete disaster waiting to happen. I’d like to say that I was feeling like Doug Burns as he often managed to get sick just before a conference. Of course, I worked on my slides for the last few days as well as on the flight and presentation was slowly getting there but boy was I tired!

I quickly said hello to the crowd in the bar on the way to my room and rushed away to do some more damage to my slides. And then I had a brilliant idea — I could still see one of my best mates and do something good about my presentation! I asked Doug if he was interested in the preview (he probably wasn’t interested but he couldn’t say it to me) especially that my session wasn’t on his original agenda. Of course, that would mean that he had to leave a bunch of other good friends and spend some time tete-a-tete. Knowing Doug, this is some of the hardest thing to ask from him but it shows how good of a friend he is! (Plus, everyone thinks that he is anti-social anyway. Shhhh!)

Doug has made my day — while he provided lots of ideas and feedback on few things that I was lucking, he generally approved the idea and confirmed that it wasn’t totally crazy. I guess that was all I needed back then and Doug knew how nervous I was about it. (Thanks mate!)

So I called Sunday a day very early and went to bed before midnight. I really needed some sleep. Woken up by the alarm at 5AM (I woke up few times during the night looking at the clock — making sure I didn’t sleep through) and slides were ready just before lunch. I even managed to do a test run and it took 65 minutes — a wee bit too long for one hour session. But it was good test and I knew I had to be just a bit more concise in few parts.

Mi morning was very productive. Unfortunately, I missed the opening keynote from Tom Kyte. Such a pity! If what Doug wrote is true, Tom was talking about the mistakes we make *because* of our experience and our assumptions. This was exactly one of the points I was making in my Battle Against Any Guess — experience is danger. I wish I could see Tom’s example. Oh well, maybe another time.

I managed to attend half of the Richard Foote’s session on indexes but my mind was far away — with my own slides. Though, I did manage to focus on bitmap indexes part and the myth of bitmap indexes not working well for columns with high cardinality. Very interesting conclusions. I’m still wondering how much overhead updates will do to such bitmap index.

After lunch, it was my turn. I ordered few copies of the latest OakTable book — Expert Oracle Practices: Oracle Database Administration from the Oak Table — that I co-authored with the bunch of other Oakies. I contributed chapter 1 in the book titled just like my presentation — Battle Against Any Guess. The plan was to give a copy away during the presentation and do a draw for another one at the end of the session. I was so nervous that I forgot about it until the end of the session so I just did a draw for two copies. The lucky winners were Lynn-Georgia Tesch and Surendra Anchula. Congratulations! For the rest of you who left the contact details — please stay tuned and we’ll organize few things online.

Now the main topic of this post — my presentation. What’s unusual about this session is that it’s not some technical stuff that I usually do but a more conceptual and motivational talk. Could I pull it off? Well, I think it went fairly well in general even though I did identify few rough places and my lack of English language mastering. Might need to work a little bit more on the flow of the presentation.

We had quite a few good laughs. Later, people in the next hall were asking about it and Dan was making the jokes on the stage so it must have been loud. Anyway, I think nobody fell asleep and I managed to get people thinking about the topic. I received many “thank you” notes yesterday and compliments on a good session so by the end of the day I was more and more pleased. Thanks everyone for attending and especially big thanks to those of you who brought to my attention examples from their own battles. If you have more to discuss — contact me by email (my last name) {at} pythian.com.

Thanks to Marco Gralike for recording some fragments and sharing them. I think he has more to come.

This is the introductory couple minutes. You can definitely notice how nervous I am starting on the stage:

Solving the wrong problem example:

That’s all for now. Stay tuned — more to come.

Fun with Filters

kurtvm — Tue, 09 Mar 2010 20:15:51 +0100

This post is about the importance of filter statements in execution plans and how they can indicate problems that are not always apparent.

I had a problem recently with some code that had gone rogue – it had been running in a few seconds, being executed every 10 minutes or so. Now it ran until either someone killed it off or it got “snapshot too old” errors. I pretty much knew it was prompted by stats being gathered on the tables in question as we had just gathered stats on the tables in that schema.

The code was something like this (it is not too important what exactly the code was). Oh, and this is on 10.2.0.3, enterprise edition with partitioning.

select accounted, max(recorded_date),count(*) from W_LCG_OPENING_ where accountid||DATA_SRC_COUNTRY_ID in ( SELECT accountid||DATA_SRC_COUNTRY_ID FROM W_LCG_OPENING_ minus SELECT accountid ||DATA_SRC_COUNTRY_ID FROM W_LCG_CLIENT_ ) and 1= 1 – this is pointless, it replaces a check of “run”=”run” group by accountid,DATA_SRC_COUNTRY_ID

Those tables are quite large, several million rows in each. The code is basically scanning all of the rows in the table as there are no indexes to support the query and it is written in a way that, well, I would not write it.

Digging around in some monitoring tools I use I confirmed that the code has swapped plan

inst SQL_ID Plan Hash First Load execs ---- ------------- ----------- ------------ ------ TOT_ROWS TOT_BG TOT_DR TOT_CPU CREATED_DT ---------- ------------ ----------- -------------- ------------ 3 1t7ma4xn3rw46 3183284037 100203 09:10 125 7,854 6,457,885 1208,149 816,476,585 091020 10:27 3 1t7ma4xn3rw46 3127554972 100206 09:13 2 0 1260,936,642 980 94599,678,960 100205 09:19

Version 1 comes back in 5 or 6 seconds. Version 2 does not effectively come back, which is why it records 0 rows. You can see that the Disk Gets are pretty low in version 2 (allowing that it was never left to finish) but Buffer Gets and CPU are both massively up. So much so, it exceeds where I format the numbers with comas (as, if they are THAT big, they need fixing anyway).

I looked at the plans and, though they were different, nothing jumped out at me. So I trimmed down the code and built it up section by section until I saw something significant change. This is my general mode of tuning code if nothing initially occurs to me.
As usual, I started with any scalar or inline code, in this case that SELECT…MINUS…SELECT in the where clause.

SELECT accountid||DATA_SRC_COUNTRY_ID FROM W_LCG_OPENING_ Minus SELECT accountid ||DATA_SRC_COUNTRY_ID FROM W_LCG_CLIENT_

This came back in 3.145 seconds, with all of 62 rows. That is fine.

Now I added back the use of the MINUS code as a WHERE clause to the outer query.

select accountid from W_LCG_OPENING_ where accountid||DATA_SRC_COUNTRY_ID in ( SELECT accountid||DATA_SRC_COUNTRY_ID FROM W_LCG_OPENING_ minus SELECT accountid ||DATA_SRC_COUNTRY_ID FROM W_LCG_CLIENT_ )

This came back in 5.22 seconds with 4468 records. Again, I can live with that, we do not need sub-second response, we need sub-minute response.

So I now added back in the group by accountid…

select accountid,count(*) from W_LCG_OPENING_ where accountid||DATA_SRC_COUNTRY_ID in ( SELECT accountid||DATA_SRC_COUNTRY_ID FROM W_LCG_OPENING_ minus SELECT accountid ||DATA_SRC_COUNTRY_ID FROM W_LCG_CLIENT_ ) group by accountid

This does not come back before I have fetched and consumed a cup of tea. That is a significant change.

I was not expecting this, why would sorting under 5 thousand rows stress a database that can scan multi-million row tables in seconds? There is all that CPU being burned up and huge numbers of buffer gets, but not much in the way of disc IO, so it is probably doing something that can be done in memory a very, very large number of times.

What is the difference in plan? {Oh HELL, they look rubbish – sorry, click on the images to blow them up, I’d suggest right-click and open in a new tab, and, YES, I am working on making my blog a little more “wide and friendly”.}. Good plan first, bad plan second

The view step has disappeared, which was an expensive step so should help not hinder.
The “SORT GROUP BY” has appeared, which it would do as we introduced the “GROUP BY” clause.
The hash join has been replaced with a filter. In a more complex plan you might miss this replacement, you would maybe notice the hash join disappearing but a filter, well, it is checking some simple “WHERE” clause isn’t it?

Well, the hash join was joining the results coming from the two steps feeding into it, the PARTITION RANGE FULL and the VIEW (which is the in-memory construct of the SELECT..MINUS…SELECT statement).

Now the Filter is filtering the results from the PARTITION RANGE ALL with the results from the MINUS. At this point I’d like to highlight that the predicted cardinality and bytes coming back for the steps within the union have reduced by a factor of 100 from the good and bad plans. And I’ll also admit I hid some key detail in the screen shot. I am not showing the access and filter predicates.

The above is a screen shot showing that in the new code there are no filter predicates but an access predicate, for the access to the view (ie the select…minus…select). For easier reading, the full access predicate is below, rather than in the screen shot:

“$nso_col_1″=TO_CHAR(“ACCOUNTID”)||TO_CHAR(“DATA_SRC_COUNTRY_ID”)

However, for the slow code, there are no access predicated but are filter predicates. Again, the screen shot shows that there are predicates and I show the full text below. (Screen shots are from PL/SQL developer, btw).

2- EXISTS ( (SELECT /*+ */ TO_CHAR("ACCOUNTID") ||TO_CHAR("DATA_SRC_COUNTRY_ID") FROM "W_LCG_OPENING_" "W_LCG_OPENING_" WHERE TO_CHAR("ACCOUNTID")||TO_CHAR("DATA_SRC_COUNTRY_ID")=TO_CHAR(:B1)||TO_CHAR(:B2)) MINUS (SELECT /*+ */ TO_CHAR("ACCOUNTID")||TO_CHAR("DATA_SRC_COUNTRY_ID") FROM "W_LCG_CLIENT_" "W_LCG_CLIENT_" WHERE TO_CHAR("ACCOUNTID")||TO_CHAR("DATA_SRC_COUNTRY_ID")=TO_CHAR(:B3)||TO_CHAR(:B4))) 8- TO_CHAR("ACCOUNTID")||TO_CHAR("DATA_SRC_COUNTRY_ID")=TO_CHAR(:B1)||TO_CHAR(:B2) 10- TO_CHAR("ACCOUNTID")||TO_CHAR("DATA_SRC_COUNTRY_ID")=TO_CHAR(:B1)||TO_CHAR(:B2)

Basically, the filter is to run the union query for every row from the driving query, passing in the relevant filter clause as an extra predicate to each of the individual queries of the SELECT…MINUS…SELECT

Clever, but not of much help to us as the performance is awful.

I showed this to a colleague and their immediate response was “why does that not show up as a nested loop? How am I supposed to spot that the “filter” is doing so much?” There were a couple of robust Saxon words mixed in with that statement.

So, Beware Filters replacing joins and get in the habit of checking out the filter and access predicates

If you use an old-style template for showing plans {like I do, a guilty sin of mine}, or a GUI where you have not selected that the filter and access predicates be shown, you may well not get them displayed. If you use autotrace in SQL*Plus, you will though:

db3_mw> select accountid,count(*) 2 from W_LCG_OPENING_ 3 where accountid||DATA_SRC_COUNTRY_ID in 4 ( 5 SELECT accountid||DATA_SRC_COUNTRY_ID FROM W_LCG_OPENING_ 6 minus 7 SELECT accountid ||DATA_SRC_COUNTRY_ID FROM W_LCG_CLIENT_ 8 ) 9 group by accountid 10 / Execution Plan ---------------------------------------------------------- Plan hash value: 397856216 -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cos t (%CPU)| Time | Pstart| Pstop | -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 911 | 7288 | 15M (7)| 17:25:41 | | | | 1 | SORT GROUP BY | | 911 | 7288 | 15M (7)| 17:25:41 | | | |* 2 | FILTER | | | | | | | | | 3 | PARTITION RANGE ALL | | 3153K| 24M| 83 11 (4)| 00:00:34 | 1 | 840 | | 4 | TABLE ACCESS FULL | W_LCG_OPENING_ | 3153K| 24M| 83 11 (4)| 00:00:34 | 1 | 840 | | 5 | MINUS | | | | | | | | | 6 | SORT UNIQUE NOSORT | | 31535 | 246K| 86 15 (7)| 00:00:35 | | | | 7 | PARTITION RANGE ALL | | 31535 | 246K| 86 11 (7)| 00:00:35 | 1 | 840 | |* 8 | TABLE ACCESS FULL | W_LCG_OPENING_ | 31535 | 246K| 86 11 (7)| 00:00:35 | 1 | 840 | | 9 | SORT UNIQUE NOSORT | | 132 | 1056 | 17 (18)| 00:00:01 | | | |* 10 | INDEX FAST FULL SCAN| W_LCG_CLIENT__PK | 132 | 1056 | 16 (13)| 00:00:01 | | | -------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter( EXISTS ( (SELECT /*+ */ TO_CHAR("ACCOUNTID")||TO_CHAR("DATA_SRC_C OUNTRY_ID") FROM "W_LCG_OPENING_" "W_LCG_OPENING_" WHERE TO_CHAR("ACCOUNTID")||TO_CHAR("DATA_SRC_COUNTRY_ID")=TO_CHAR(:B1)| |TO_CHAR(:B2))MINUS (SELECT /*+ */ TO_CHAR("ACCOUNTID")||TO_CHAR("DATA_SRC_COUNTRY_ID") FROM "W_LCG_CLIENT_" "W_LCG_CLIENT_DETAILS" WHERE TO_CHAR("ACCOUNTID")||TO_CHAR("DATA_S RC_COUNTRY_ID")=TO_CHAR(:B3)||TO_CHAR(:B4) ))) 8 - filter(TO_CHAR("ACCOUNTID")||TO_CHAR("DATA_SRC_COUNTRY_ID")=TO_CHAR(:B1)| |TO_CHAR(:B2)) 10 - filter(TO_CHAR("ACCOUNTID")||TO_CHAR("DATA_SRC_COUNTRY_ID")=TO_CHAR(:B1)| |TO_CHAR(:B2))

What did I do to fix the problem? Well, even though the code originally went bad due to stats being gathered, I could not force it back to a nice plan after an hour or two playing with gathering new stats so, in this case, I used an UNNEST hint.

select accountid,count(*) from W_LCG_OPENING_ where accountid||DATA_SRC_COUNTRY_ID in ( SELECT /*+ unnest */ accountid||DATA_SRC_COUNTRY_ID FROM W_LCG_OPENING_ minus SELECT accountid ||DATA_SRC_COUNTRY_ID FROM W_LCG_CLIENT_ ) group by accounted

And it forced the plan back to the version using a HASH join.

The hinted plan

I’m a little unhappy about that hint, but the code needed fixing and the hint did what I wanted it to {I think it was poor of me to only hint one of the two minus statements and I do not like resorting to hints just because I can’t work out what is “wrong” with the stats – If I work out what is wrong and still need the hint, fair enough}. However, I had to be pragmatic and get the code fixed and working in Live, so it has gone in with a hint

Hotsos 2010 - Congratulations, Marco!

kurtvm — Tue, 09 Mar 2010 18:59:24 +0100

You managed to capture a couple of minutes of my presentation when there was a picture on the screen!

What has Toyota actually proven here?

kurtvm — Tue, 09 Mar 2010 15:09:00 +0100

Interesting article in the Chicago Tribune today: "Toyota rebuts claims of SIU professor: In webcast, consulting firm re-creates electronic glitch in other vehicle brands".

First, David Gilbert demonstrates to Congress a way to cause sudden acceleration in Toyota vehicles.

Then Toyota countered his demonstration as follows: "Using similar methodology, engineers at Toyota's facility on Monday duplicated the results on camera in seven other vehicles, including a Chevrolet Malibu, Honda Accord and Ford Fusion. Officials said the results, though not maligning other manufacturers, show that the methodology was essentially flawed."

Gee, I don't know. It seems like you could just as legitimately conclude that the problems experienced by Toyota owners could well be showing up in other vehicles.

I must admit that as a programmer, it scares me to think that my safety (and the safety of my family members) when driving is so dependent on software.

A brand-new Oracle product???

kurtvm — Tue, 09 Mar 2010 15:04:15 +0100

Just came across these yesterday in the Yarra Valley Dairy (which by the way had excellent goat cheese) and still ask myself if Oracle is emerging now into new markets:Still wondering ... you'll never know ... ;-)

Thanks Melbourne!!!

kurtvm — Tue, 09 Mar 2010 14:57:25 +0100

We had a great time today with you all - and thanks to the local Oracle folks as well who'd organize everything so well. And the view from the top floor was the best we've ever had in a workshop so far :-) Hope you all start your upgrade to 11g Release 2 pretty soon - and we'll be very happy to get your feedback once you've gone live.

And - as always - please download the most recent version of the slides from here:
http://apex.oracle.com/folien
Use the keyword (Schluesselwort): upgrade112

Screw up and repair

kurtvm — Tue, 09 Mar 2010 13:58:07 +0100

I thought it worked - but of course I forgot the Oracle VM Manager on my second machine.

It still displayed the ServerPool1 that I had created previously.
In order to begin with a clean slate you might need to drop the database in the Oracle VM Server.

Login to the server as root and execute the following commands:

service stop ovs-agent
rm -rf /etc/ovs-agent/db
service start ovs-agent

Then the external disk needs to be added again:
[root@ovm utils]# ./repos.py --new /dev/sdb1
[ NEW ] 6ad759c5-d706-4b3a-9d14-bcffafc61fc3 => /dev/sdb1
[root@ovm utils]# ./repos.py --list
[ ] 6ad759c5-d706-4b3a-9d14-bcffafc61fc3 => /dev/sdb1
[root@ovm utils]# ./repos.py --root 6ad759c5-d706-4b3a-9d14-bcffafc61fc3
[ R ] 6ad759c5-d706-4b3a-9d14-bcffafc61fc3 => /dev/sdb1

The filesystems were back again and also the directories in /OVS.

Now back to importing a VM Template.

Hotsos 2010 - My Presentation

kurtvm — Tue, 09 Mar 2010 13:46:42 +0100

I really don't know how to blog about this because every time I feel I'm honestly self-critical, everyone thinks I'm close to suicide or something. I like to think I notice both the good and the bad but am probably more likely to speak openly about the bad. If you ask me it's a Scottish thing about not getting above yourself or blowing your own trumpet too loudly. Or maybe it's just that the only way you can improve is by noticing the bad stuff and fixing it? Someone once said to me "You can't be an insecure overachiever without first being insecure" and they aren't Scottish, so I shouldn't generalise. Updated later - it was Cary Millsap who came up with this line. I loved it when he mentioned it a few months ago and still do.

In this case I'll try to be even-handed and finish with the positives!

Negatives

The whole point of this presentation is that it's about 50 minutes of demonstrations and about 10 of slides. For the first 30 minutes, the demonstrations would not work. If you think that's a success, there's something the matter with you and you should probably never give any presentations!

This is the killer. In retrospect I know what the problem was and have fixed it previously with the help of others but did not do the same on the new laptop! As I moved in and out of wireless range, the lack of a network connection at the Windows end completely threw VMWare and my demos. But, believe me, when your demos have been working for a couple of weeks, you don't have long until your presentation and they stop working intermittently, it's difficult to be cool and analytical. I was so distracted by other things that might go wrong, I missed one and, having missed it, I wasn't cool enough to recognise the symptoms. Fortunately, an old Hotsos friend in the room came up with the goods in the form of a little Sprint wireless box that got me hooked up. I kissed him, but there were no tongues involved.

With only 30 minutes left, there was very little I could show and it completely ruined the whole flow of the presentation, which I'd worked so hard to get right and which I know can be terrific, because I've done similar presentations before and had been looking forward to doing the best version yet. People took the time to come and see it, I let them down and I'm sorry about that. Nobody is likely to change my view on that.

This presentation is a big deal to me, never mind anyone else. I work hard on these things, try to cover all the angles, take time off work and all because I like teaching people new stuff. When I take a week off to attend a conference, but also to present, it's a bit of a blow when your one shot fails. If you're not passionate about your presentations (and I somehow doubt anyone isn't) again, you shouldn't be presenting.

Positives

Sh*t happens when you get involved with computers. Yes, folks, I know that, but that's also why you play around with demos for a long time to minimise the possibility that it will!

This might not sound like a positive and it's dangerously close to sounding like an excuse, but the fact is that I've had 2 out of 3 presentations go very wrong recently. One of the consistent factors in this is that I switched to VMWare because I had to to run 11gR2 on Windows. It's not the same as blaming VMWare to say that I've been having to deal with stuff I haven't for a long time. When everyone was talking about VMWare as the way to go for presentations, I remember thinking 'I don't know, just seems like more moving parts that might break to me.' and I kept reading blog posts about demos being broken and then fixed just in time, all of which were on some virtualisation platform or other, but didn't have the confidence to say something. All I know is, say what you like about Windows, but I've hardly ever had a problem in multiple 2-day course teaches of performance and OEM stuff! Still, it's down to me to get on top of what are some simple issues.

Listen, I know I can present. No false modesty round here. So, to wrap this up on a positive note, I'm well aware that there are few of the people who I see present who could have managed to get through that first half hour, make people laugh, keep thinking about the problem and manage to get a short demo of Swingbench into the bargain. I think I also managed to salvage something out of the last half of the presentation without completely collapsing into a heap. Sure, I was a bit brain-addled by then, but I would have liked to have seen how others might have coped Frankly, I kept waiting for the room to empty (I've seen it happen) but the vast majority stuck around to the end. Maybe they were sadists! LOL

I am not and will never manage to be happy with that presentation but lessons have been learned, it's just a presentation and there'll be lots of others. I know that.

Postive Solution 1 - If anyone wants to try to grab me while I'm at the conference, I'll show you the screens and demos. They're very cool

Positive Solution 2 - I think Alex Gorbachev might try to arrange for me to repeat the presentation properly as a webinar. I've already done this at my current customer site once (on the 10g stuff) and it went reasonably well. I'd also ... get this ... asked Marco to video it for me yesterday with his snazzy mini-setup because I thought I might post a few bits online if they were particularly good so those that can't make it to conferences could get a taste of it. Actually, it appeals to my cold, self-deprecating sense of humour to post some of it online soon. I promise I won't make it too self-flaggelating though!

P.S. For Paul Vallee. Paul it is not all good

Finally my OVM looks like it should

kurtvm — Tue, 09 Mar 2010 10:06:58 +0100

I rebuild my OVM server for the n-th time this morning. Problem was that it was difficult to have the external disk mounted on the /OVS directory.
I tried to add a shared virtual disk - which I formatted as an ocfs2 disk to the existing disk. I found this excellent posting http://geertdepaep.wordpress.com/2008/06/09/oracle-vm-and-multiple-local-disks/ that described the process and guided me into the correct direction. Although the disk was visible in the repos.py script I was unable to see it in the Oracle VM Manager.

Now I thought that I need to build the server and format the filesystems myself.
Sorry I have no screenshots here as the installer runs on the machine itself.
During the installation the two disks (internal 50 GB and external 1 TB) are shown as /dev/sda and /dev/sdb. In the disk setup utility in the installer choose the option to define a custom layout.

The installer offers a predefined layout, which it would normally apply to the available disks.
I decided to have three filesystems on the internal disk. This will be /, /boot and a swap filesystem. On the external disk I gave away the complete disk and formatted it as an ocfs2 filesystem.
During this I received a lot of errors on the screen, stating that there was an error while setting the type of the filesystem to ocfs2. However I continued - ignoring the errors - and finally ended up with this disk layout:

[root@ovm ~]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/sda2 54G 953M 50G 2% /
/dev/sda1 99M 45M 49M 48% /boot
tmpfs 287M 0 287M 0% /dev/shm
/dev/sdb1 931G 315M 931G 1% /var/ovs/mount/6AD759C5D7064B3A9D14BCFFAFC61FC3
[root@ovm ~]#

so, now the fun can begin, as I finally got to the point where I have my 1 TB disk fully exposed for my Oracle VM server.

Chris Date in Edinburgh on 13 and 14 May 2010

kurtvm — Tue, 09 Mar 2010 08:27:41 +0100

In the noise of of all the announcements, blogs, mails, twats (and mostly plain ads), this is one that I want to Recommend.

Chris Date is doing a Seminar in Edinburgh on 13 and 14 May.

If this seems like total nonsense for you: Google Chris Date

If you work with Information Systems and notably with Databases, chances are the work of this man has had an impact on what you do.

And to better understand what you do, and how you can do it Well, it is worth to read, discuss and understand his work.

To know where this field of expertise is really going, the work of Chris is probably just as relevant, if not more, then the latest re-naming of crs or asm processes and logfiles.

I was not kidding when I had Oracle Magazine write up my nr 1 tip for people who want to get into IT: Get your Basics Knowlege Right. You could do worse then to start with the work of Chris Date.

Code Comment WTF? Part 209

kurtvm — Tue, 09 Mar 2010 07:38:00 +0100

Found this in a snippet today:-- ********************************
-- End of Package Body

END package_pkg ;
/Seriously? Was that necessary? Could I possibly be under the illusion that it is not the end of the package?

Stop it.

Now.

APEX: LDAP Authentication

kurtvm — Tue, 09 Mar 2010 07:09:00 +0100

I got called into a discussion about an existing APEX application. The custom LDAP functionality wasn't working as they expected.

I knew APEX had an LDAP authentication scheme (and don't know the full history of the project so I can't (won't) comment on why it wasn't used). So I fired up my local sandbox just to see how easy or hard it was. Admittedly, I have always avoided anything to do with LDAP...not sure why (plate is full?). I used this as a guide.

Anyway, it was remarkably easy.

Setup
APEX: 3.2.1
Web Server: Apache (OHS)
Database:BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for 32-bit Windows: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - ProductionFirst I fired up the web server:C:\oracle\http\opmn\bin>opmnctl start
opmnctl: opmn started

C:\oracle\http\opmn\bin>opmnctl startproc process-type=HTTP_Server
opmnctl: starting opmn managed processes...Opened up APEX, and created a new application. For authentication schemes I chose "No Authentication."

After I had created the application, I went into Shared Components --> Authentication Schemes --> Create

Select the default and click Next

Select "Show Login Page and Use LDAP Directory Credentials" and click Next

I've already done this so I'm selecting my current Login page, 11, click Next

Enter your LDAP Host and your DN:

Your DN String should look something like this (from article above):cn=%LDAP_USER%,l=amer,dc=oracle,dc=comMake sure you use the %LDAP_USER% after the cn= portion of the string.

Name it ldap_test, click Create Scheme:

You will then be redirected back to the list of Authentication Schemes, ldap_test should now be current

To test it just run your application and login using your LDAP (AD) credentials

Success!

V$SESSION_LONGOPS – Where’s My SQL Statement

kurtvm — Tue, 09 Mar 2010 07:00:59 +0100

March 9, 2010

A question recently appeared on the OTN forums that stated the following:

I have a query joining 4 tables and it returns 48 million. I have used /+parallel(tablename,4) */ hint with the select clause. I want to the 48 million returned by the query to another table t2.

I do get the select result in 20 minutes. when I am performing insert, I checked gv$session_longops. I usually check like select * from gv$session_longops where time_remaining>0.

It didnt return anything. I removed the where clause and rather gave the sid.(i found the sql_id from gv$session)

select * from gv$session_longops where sql_id=’XXXXXXX’;

I got 3 rows but time_remaining column is 0. Could someone explain why this is so.

OS: Linux
DB:11.1.0.6.0 – 11g
Rac- 2 node RAC

So what is happening, why does GV$SESSION_LONGOPS show 3 rows for the SQL statement, all with the TIME_REMAINING column set to 0? This is a good question, but I do not know if there is a straight-forward answer for the question. Possibly one of the first questions to ask is: What is the SQL statement, and what does its execution plan look like?

It probably makes sense to check the documentation for the GV$SESSION_LONGOPS view (or more accurately, the documentation for the single instance V$SESSION_LONGOPS view):

“V$SESSION_LONGOPS displays the status of various operations that run for longer than 6 seconds (in absolute time). These operations currently include many backup and recovery functions, statistics gathering, and query execution, and more operations are added for every Oracle release.

To monitor query execution progress, you must be using the cost-based optimizer and you must:

Set the TIMED_STATISTICS or SQL_TRACE parameters to true

Gather statistics for your objects with the ANALYZE statement or the DBMS_STATS package”

From the above, having the TIMED_STATISTICS parameter set to FALSE or failing to gather the statistics for the objects using DBMS_STATS are two possible reasons for the TIME_REMAINING column being set to 0. Any other ideas? Where is that SQL statement and execution plan when you need it? Why ask for the execution plan? From the documentation link you will notice that the view definition includes the SQL_PLAN_LINE_ID column, which refers to a specific line in the execution plan for the query. From this bit of information, we just need to define what is considered an operation (and does it have to be an uninterrupted operation) – without parallel query we might be able to perform a couple of tests. The definition of an operation and the 6 second rule might be very important.

We will use tables T3 and T4 from yesterday’s blog article since the original poster did not provide a SQL statement to reproduce the problem. Consider the following query:

SELECT T3.C1, T3.C2, T4.C3 FROM T3, T4 WHERE TRUNC(T3.C2) BETWEEN TO_DATE('08-MAR-2009','DD-MON-YYYY') AND TO_DATE('01-JUL-2009','DD-MON-YYYY') AND T3.C2=T4.C2;

If we set the STATISTICS_LEVEL parameter to ALL at the session level, run the SQL statement, and then generate the DBMS_XPLAN for the last execution, we may see an execution plan that looks something like this:

SQL_ID 2d4f5x92axqgn, child number 0 ------------------------------------- SELECT T3.C1, T3.C2, T4.C3 FROM T3, T4 WHERE TRUNC(T3.C2) BETWEEN TO_DATE('08-MAR-2009','DD-MON-YYYY') AND TO_DATE('01-JUL-2009','DD-MON-YYYY') AND T3.C2=T4.C2 Plan hash value: 1396201636 ------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem | ------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 874K|00:03:31.20 | 3771K| 3763K| | | | |* 1 | HASH JOIN | | 1 | 849K| 874K|00:03:31.20 | 3771K| 3763K| 33M| 5591K| 50M (0)| |* 2 | TABLE ACCESS FULL| T3 | 1 | 802K| 795K|00:02:32.69 | 2743K| 2743K| | | | | 3 | TABLE ACCESS FULL| T4 | 1 | 25M| 25M|00:00:35.93 | 1028K| 1020K| | | | ------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("T3"."C2"="T4"."C2") 2 - filter((TRUNC(INTERNAL_FUNCTION("C2"))>=TO_DATE(' 2009-03-08 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND TRUNC(INTERNAL_FUNCTION("C2"))<=TO_DATE(' 2009-07-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))

From the above, we are able to see that Oracle is reporting that the full table scan of table T3 required roughly 152.69 seconds and the full table scan of table T4 required roughly 35.93 seconds. Assume that in another session we executed the following SQL statement roughly every 6 seconds while the above SQL statement was in the process of being executed:

SELECT SQL_ID, SQL_PLAN_HASH_VALUE HASH_VALUE, SQL_PLAN_LINE_ID LINE_ID, OPNAME, TARGET, TARGET_DESC, TIME_REMAINING, ELAPSED_SECONDS, SID, SERIAL#, USERNAME FROM V$SESSION_LONGOPS WHERE TIME_REMAINING>0;

The output of the above SQL statement might look something like the following:

From the above output it appears that the full table scan of table T3 required between 148 and 154 seconds. At the point where the full table scan of T3 completed and the first couple of seconds (probably 6) of the full table scan of T4, the SQL statement executed in the second session returned no rows - after roughly 6 seconds of reading table T4, the query of V$SESSION_LONGOPS started reporting that table T4 was being read. From the above it appears that the read of table T4 required between 86 and 94 seconds, yet that does not agree with the DBMS_XPLAN output – it appears that in the V$SESSION_LONGOPS output the hash join operation’s time is being reported as part of this elapsed time for reading table T4.

So, we have found a third possible reason for the rows in V$SESSION_LONGOPS appearing as 0 for the OP’s SQL statement – the line in the plan had not yet consumed 6 seconds of time, or the operation had already completed.

Let’s look at one of the other SQL statements from yesterday’s blog article:

SELECT T3.C1, T3.C2, T4.C3 FROM T3, T4 WHERE T3.C2 BETWEEN TO_DATE('08-MAR-2009','DD-MON-YYYY') AND TO_DATE('01-JUL-2009','DD-MON-YYYY') + (1-1/24/60/60) AND T3.C2=T4.C2;

This statement is equivalent to the previous SQL statement, except that it is able to use the index that exists on table T3. The execution plan follows:

SQL_ID 539d93k50ruz3, child number 0 ------------------------------------- SELECT T3.C1, T3.C2, T4.C3 FROM T3, T4 WHERE T3.C2 BETWEEN TO_DATE('08-MAR-2009','DD-MON-YYYY') AND TO_DATE('01-JUL-2009','DD-MON-YYYY') + (1-1/24/60/60) AND T3.C2=T4.C2 Plan hash value: 1243183227 -------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | -------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 874K|00:00:05.19 | 85051 | | | | | 1 | MERGE JOIN | | 1 | 795K| 874K|00:00:05.19 | 85051 | | | | | 2 | TABLE ACCESS BY INDEX ROWID | T4 | 1 | 795K| 795K|00:00:01.69 | 51097 | | | | |* 3 | INDEX RANGE SCAN | IND_T4_C2 | 1 | 795K| 795K|00:00:00.42 | 10841 | | | | |* 4 | SORT JOIN | | 795K| 795K| 874K|00:00:02.11 | 33954 | 30M| 1977K| 26M (0)| | 5 | TABLE ACCESS BY INDEX ROWID| T3 | 1 | 795K| 795K|00:00:00.58 | 33954 | | | | |* 6 | INDEX RANGE SCAN | IND_T3_C2 | 1 | 795K| 795K|00:00:00.18 | 2114 | | | | -------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("T4"."C2">=TO_DATE(' 2009-03-08 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "T4"."C2"<=TO_DATE(' 2009-07-01 23:59:59', 'syyyy-mm-dd hh24:mi:ss')) 4 - access("T3"."C2"="T4"."C2") filter("T3"."C2"="T4"."C2") 6 - access("T3"."C2">=TO_DATE(' 2009-03-08 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "T3"."C2"<=TO_DATE(' 2009-07-01 23:59:59', 'syyyy-mm-dd hh24:mi:ss'))

The above SQL statement completed too quickly to report any time in the V$SESSION_LONGOPS view, so let’s set the ‘08-MAR-2009′ date to ‘08-MAR-2007′ and re-run the SQL statement. For quite a bit of time the query of V$SESSION_LONGOPS only returned the following:

no rows selected

But the SQL statement was executing, why did it not appear in V$SESSION_LONGOPS – where is that link for reporting a bug on Metalink? Or maybe, just maybe, Oracle is not performing any single operation in the execution plan continuously for more than 6 seconds (reading the index at ID 3 followed by the corresponding rows at ID 2, and then back to the index at ID 3 again).

Eventually, the following appeared after what must have been a minute or two:

Ready, aim,… hey – what is the TARGET. The LINE_ID column (the SQL_PLAN_LINE_ID column in V$SESSION_LONGOPS) reported that Oracle is executing ID 4 of the execution plan. I can’t explain why the last line remained in the V$SESSION_LONGOPS output for a minute or two (maybe someone else knows why).

So, we have found a fourth possible reason for the rows in V$SESSION_LONGOPS appearing as 0 for the OP’s SQL statement – it could be that Oracle was alternating between an index range scan and the retrieval of the matching rows from the corresponding table.

Let’s try executing the first query with a parallel hint to see what happens to the V$SESSION_LONGOPS output:

SELECT /*+ PARALLEL (T3, 4) PARALLEL (T4, 4) */ T3.C1, T3.C2, T4.C3 FROM T3, T4 WHERE TRUNC(T3.C2) BETWEEN TO_DATE('08-MAR-2009','DD-MON-YYYY') AND TO_DATE('01-JUL-2009','DD-MON-YYYY') AND T3.C2=T4.C2;

The DBMS_XPLAN output for the query follows:

SQL_ID dywt9v0xuvgyv, child number 0 ------------------------------------- SELECT /*+ PARALLEL (T3, 4) PARALLEL (T4, 4) */ T3.C1, T3.C2, T4.C3 FROM T3, T4 WHERE TRUNC(T3.C2) BETWEEN TO_DATE('08-MAR-2009','DD-MON-YYYY') AND TO_DATE('01-JUL-2009','DD-MON-YYYY') AND T3.C2=T4.C2 Plan hash value: 1800244878 ------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 874K|00:03:49.60 | 14 | | | | | 1 | PX COORDINATOR | | 1 | | 874K|00:03:49.60 | 14 | | | | | 2 | PX SEND QC (RANDOM) | :TQ10001 | 0 | 849K| 0 |00:00:00.01 | 0 | | | | |* 3 | HASH JOIN | | 0 | 849K| 0 |00:00:00.01 | 0 | 126M| 10M| 49M (0)| | 4 | PX RECEIVE | | 0 | 802K| 0 |00:00:00.01 | 0 | | | | | 5 | PX SEND BROADCAST | :TQ10000 | 0 | 802K| 0 |00:00:00.01 | 0 | | | | | 6 | PX BLOCK ITERATOR | | 0 | 802K| 0 |00:00:00.01 | 0 | | | | |* 7 | TABLE ACCESS FULL| T3 | 0 | 802K| 0 |00:00:00.01 | 0 | | | | | 8 | PX BLOCK ITERATOR | | 0 | 25M| 0 |00:00:00.01 | 0 | | | | |* 9 | TABLE ACCESS FULL | T4 | 0 | 25M| 0 |00:00:00.01 | 0 | | | | ------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("T3"."C2"="T4"."C2") 7 - access(:Z>=:Z AND :Z<=:Z) filter((TRUNC(INTERNAL_FUNCTION("C2"))>=TO_DATE(' 2009-03-08 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND TRUNC(INTERNAL_FUNCTION("C2"))<=TO_DATE(' 2009-07-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))) 9 - access(:Z>=:Z AND :Z<=:Z) filter(SYS_OP_BLOOM_FILTER(:BF0000,"T4"."C2"))

A bloom filter, just in time for Spring (it must be true, it is in the Predicate Information section of the actual execution plan).

After roughly a 6 second delay, V$SESSION_LONGOPS was queried roughly every 6 seconds. The output of the V$SESSION_LONGOPS query follows:

SQL_ID HASH_VALUE LINE_ID OPNAME TARGET TARGET_DESC TIME_REMAINING ELAPSED_SECONDS SID SERIAL# USERNAME ------------- ---------- ---------- ---------------- --------------- ----------- -------------- --------------- ---------- ---------- -------- dywt9v0xuvgyv 1800244878 7 Rowid Range Scan TESTUSER.T3 8 7 553 243 TESTUSER dywt9v0xuvgyv 1800244878 7 Rowid Range Scan TESTUSER.T3 4 7 80 544 TESTUSER dywt9v0xuvgyv 1800244878 7 Rowid Range Scan TESTUSER.T3 114 7 7 251 TESTUSER dywt9v0xuvgyv 1800244878 7 Rowid Range Scan TESTUSER.T3 14 7 159 982 TESTUSER SQL> / SQL_ID HASH_VALUE LINE_ID OPNAME TARGET TARGET_DESC TIME_REMAINING ELAPSED_SECONDS SID SERIAL# USERNAME ------------- ---------- ---------- ---------------- --------------- ----------- -------------- --------------- ---------- ---------- -------- dywt9v0xuvgyv 1800244878 7 Rowid Range Scan TESTUSER.T3 2 16 159 982 TESTUSER SQL> / SQL_ID HASH_VALUE LINE_ID OPNAME TARGET TARGET_DESC TIME_REMAINING ELAPSED_SECONDS SID SERIAL# USERNAME ------------- ---------- ---------- ---------------- --------------- ----------- -------------- --------------- ---------- ---------- -------- dywt9v0xuvgyv 1800244878 7 Rowid Range Scan TESTUSER.T3 4 9 553 243 TESTUSER dywt9v0xuvgyv 1800244878 7 Rowid Range Scan TESTUSER.T3 1 13 80 544 TESTUSER dywt9v0xuvgyv 1800244878 7 Rowid Range Scan TESTUSER.T3 7 9 7 251 TESTUSER SQL> / SQL_ID HASH_VALUE LINE_ID OPNAME TARGET TARGET_DESC TIME_REMAINING ELAPSED_SECONDS SID SERIAL# USERNAME ------------- ---------- ---------- ---------------- --------------- ----------- -------------- --------------- ---------- ---------- -------- dywt9v0xuvgyv 1800244878 7 Rowid Range Scan TESTUSER.T3 13 9 80 544 TESTUSER SQL> / SQL_ID HASH_VALUE LINE_ID OPNAME TARGET TARGET_DESC TIME_REMAINING ELAPSED_SECONDS SID SERIAL# USERNAME ------------- ---------- ---------- ---------------- --------------- ----------- -------------- --------------- ---------- ---------- -------- dywt9v0xuvgyv 1800244878 7 Rowid Range Scan TESTUSER.T3 2 12 553 243 TESTUSER dywt9v0xuvgyv 1800244878 7 Rowid Range Scan TESTUSER.T3 7 10 7 251 TESTUSER dywt9v0xuvgyv 1800244878 7 Rowid Range Scan TESTUSER.T3 6 7 159 982 TESTUSER SQL> / SQL_ID HASH_VALUE LINE_ID OPNAME TARGET TARGET_DESC TIME_REMAINING ELAPSED_SECONDS SID SERIAL# USERNAME ------------- ---------- ---------- ---------------- --------------- ----------- -------------- --------------- ---------- ---------- -------- dywt9v0xuvgyv 1800244878 7 Rowid Range Scan TESTUSER.T3 7 7 553 243 TESTUSER dywt9v0xuvgyv 1800244878 7 Rowid Range Scan TESTUSER.T3 4 10 80 544 TESTUSER SQL> / SQL_ID HASH_VALUE LINE_ID OPNAME TARGET TARGET_DESC TIME_REMAINING ELAPSED_SECONDS SID SERIAL# USERNAME ------------- ---------- ---------- ---------------- --------------- ----------- -------------- --------------- ---------- ---------- -------- dywt9v0xuvgyv 1800244878 7 Rowid Range Scan TESTUSER.T3 2 13 159 982 TESTUSER SQL> / SQL_ID HASH_VALUE LINE_ID OPNAME TARGET TARGET_DESC TIME_REMAINING ELAPSED_SECONDS SID SERIAL# USERNAME ------------- ---------- ---------- ---------------- --------------- ----------- -------------- --------------- ---------- ---------- -------- dywt9v0xuvgyv 1800244878 7 Rowid Range Scan TESTUSER.T3 4 9 553 243 TESTUSER dywt9v0xuvgyv 1800244878 7 Rowid Range Scan TESTUSER.T3 6 10 7 251 TESTUSER SQL> / SQL_ID HASH_VALUE LINE_ID OPNAME TARGET TARGET_DESC TIME_REMAINING ELAPSED_SECONDS SID SERIAL# USERNAME ------------- ---------- ---------- ---------------- --------------- ----------- -------------- --------------- ---------- ---------- -------- dywt9v0xuvgyv 1800244878 7 Rowid Range Scan TESTUSER.T3 10 8 80 544 TESTUSER SQL> / SQL_ID HASH_VALUE LINE_ID OPNAME TARGET TARGET_DESC TIME_REMAINING ELAPSED_SECONDS SID SERIAL# USERNAME ------------- ---------- ---------- ---------------- --------------- ----------- -------------- --------------- ---------- ---------- -------- dywt9v0xuvgyv 1800244878 7 Rowid Range Scan TESTUSER.T3 2 12 553 243 TESTUSER dywt9v0xuvgyv 1800244878 7 Rowid Range Scan TESTUSER.T3 1 16 80 544 TESTUSER dywt9v0xuvgyv 1800244878 7 Rowid Range Scan TESTUSER.T3 3 10 7 251 TESTUSER dywt9v0xuvgyv 1800244878 7 Rowid Range Scan TESTUSER.T3 6 10 159 982 TESTUSER SQL> / SQL_ID HASH_VALUE LINE_ID OPNAME TARGET TARGET_DESC TIME_REMAINING ELAPSED_SECONDS SID SERIAL# USERNAME ------------- ---------- ---------- ---------------- --------------- ----------- -------------- --------------- ---------- ---------- -------- dywt9v0xuvgyv 1800244878 7 Rowid Range Scan TESTUSER.T3 6 8 553 243 TESTUSER dywt9v0xuvgyv 1800244878 7 Rowid Range Scan TESTUSER.T3 8 8 80 544 TESTUSER SQL> / SQL_ID HASH_VALUE LINE_ID OPNAME TARGET TARGET_DESC TIME_REMAINING ELAPSED_SECONDS SID SERIAL# USERNAME ------------- ---------- ---------- ---------------- --------------- ----------- -------------- --------------- ---------- ---------- -------- dywt9v0xuvgyv 1800244878 7 Rowid Range Scan TESTUSER.T3 3 12 159 982 TESTUSER SQL> / SQL_ID HASH_VALUE LINE_ID OPNAME TARGET TARGET_DESC TIME_REMAINING ELAPSED_SECONDS SID SERIAL# USERNAME ------------- ---------- ---------- ---------------- --------------- ----------- -------------- --------------- ---------- ---------- -------- dywt9v0xuvgyv 1800244878 7 Rowid Range Scan TESTUSER.T3 4 10 553 243 TESTUSER dywt9v0xuvgyv 1800244878 7 Rowid Range Scan TESTUSER.T3 5 10 80 544 TESTUSER dywt9v0xuvgyv 1800244878 7 Rowid Range Scan TESTUSER.T3 5 8 7 251 TESTUSER SQL> / SQL_ID HASH_VALUE LINE_ID OPNAME TARGET TARGET_DESC TIME_REMAINING ELAPSED_SECONDS SID SERIAL# USERNAME ------------- ---------- ---------- ---------------- --------------- ----------- -------------- --------------- ---------- ---------- -------- dywt9v0xuvgyv 1800244878 7 Rowid Range Scan TESTUSER.T3 7 9 159 982 TESTUSER SQL> / SQL_ID HASH_VALUE LINE_ID OPNAME TARGET TARGET_DESC TIME_REMAINING ELAPSED_SECONDS SID SERIAL# USERNAME ------------- ---------- ---------- ---------------- --------------- ----------- -------------- --------------- ---------- ---------- -------- dywt9v0xuvgyv 1800244878 7 Rowid Range Scan TESTUSER.T3 7 9 159 982 TESTUSER SQL> / SQL_ID HASH_VALUE LINE_ID OPNAME TARGET TARGET_DESC TIME_REMAINING ELAPSED_SECONDS SID SERIAL# USERNAME ------------- ---------- ---------- ---------------- --------------- ----------- -------------- --------------- ---------- ---------- -------- dywt9v0xuvgyv 1800244878 7 Rowid Range Scan TESTUSER.T3 7 9 159 982 TESTUSER SQL> / SQL_ID HASH_VALUE LINE_ID OPNAME TARGET TARGET_DESC TIME_REMAINING ELAPSED_SECONDS SID SERIAL# USERNAME ------------- ---------- ---------- ---------------- --------------- ----------- -------------- --------------- ---------- ---------- -------- dywt9v0xuvgyv 1800244878 7 Rowid Range Scan TESTUSER.T3 7 9 159 982 TESTUSER SQL> / SQL_ID HASH_VALUE LINE_ID OPNAME TARGET TARGET_DESC TIME_REMAINING ELAPSED_SECONDS SID SERIAL# USERNAME ------------- ---------- ---------- ---------------- --------------- ----------- -------------- --------------- ---------- ---------- -------- dywt9v0xuvgyv 1800244878 7 Rowid Range Scan TESTUSER.T3 7 9 159 982 TESTUSER SQL> / SQL_ID HASH_VALUE LINE_ID OPNAME TARGET TARGET_DESC TIME_REMAINING ELAPSED_SECONDS SID SERIAL# USERNAME ------------- ---------- ---------- ---------------- --------------- ----------- -------------- --------------- ---------- ---------- -------- dywt9v0xuvgyv 1800244878 7 Rowid Range Scan TESTUSER.T3 7 9 159 982 TESTUSER SQL> / SQL_ID HASH_VALUE LINE_ID OPNAME TARGET TARGET_DESC TIME_REMAINING ELAPSED_SECONDS SID SERIAL# USERNAME ------------- ---------- ---------- ---------------- --------------- ----------- -------------- --------------- ---------- ---------- -------- dywt9v0xuvgyv 1800244878 7 Rowid Range Scan TESTUSER.T3 7 9 159 982 TESTUSER SQL> / SQL_ID HASH_VALUE LINE_ID OPNAME TARGET TARGET_DESC TIME_REMAINING ELAPSED_SECONDS SID SERIAL# USERNAME ------------- ---------- ---------- ---------------- --------------- ----------- -------------- --------------- ---------- ---------- -------- dywt9v0xuvgyv 1800244878 7 Rowid Range Scan TESTUSER.T3 2 57 80 544 TESTUSER dywt9v0xuvgyv 1800244878 7 Rowid Range Scan TESTUSER.T3 20 66 159 982 TESTUSER SQL> / no rows selected SQL> / no rows selected SQL> / no rows selected SQL> / no rows selected SQL> / no rows selected SQL> / no rows selected SQL> / no rows selected SQL> / SQL_ID HASH_VALUE LINE_ID OPNAME TARGET TARGET_DESC TIME_REMAINING ELAPSED_SECONDS SID SERIAL# USERNAME ------------- ---------- ---------- ---------------- --------------- ----------- -------------- --------------- ---------- ---------- -------- dywt9v0xuvgyv 1800244878 9 Rowid Range Scan TESTUSER.T4 9 8 81 1204 TESTUSER dywt9v0xuvgyv 1800244878 9 Rowid Range Scan TESTUSER.T4 4 7 395 631 TESTUSER SQL> / SQL_ID HASH_VALUE LINE_ID OPNAME TARGET TARGET_DESC TIME_REMAINING ELAPSED_SECONDS SID SERIAL# USERNAME ------------- ---------- ---------- ---------------- --------------- ----------- -------------- --------------- ---------- ---------- -------- dywt9v0xuvgyv 1800244878 9 Rowid Range Scan TESTUSER.T4 69 13 160 32 TESTUSER dywt9v0xuvgyv 1800244878 9 Rowid Range Scan TESTUSER.T4 12 16 81 1204 TESTUSER dywt9v0xuvgyv 1800244878 9 Rowid Range Scan TESTUSER.T4 5 15 395 631 TESTUSER dywt9v0xuvgyv 1800244878 9 Rowid Range Scan TESTUSER.T4 71 13 238 31 TESTUSER SQL> / SQL_ID HASH_VALUE LINE_ID OPNAME TARGET TARGET_DESC TIME_REMAINING ELAPSED_SECONDS SID SERIAL# USERNAME ------------- ---------- ---------- ---------------- --------------- ----------- -------------- --------------- ---------- ---------- -------- dywt9v0xuvgyv 1800244878 9 Rowid Range Scan TESTUSER.T4 11 25 81 1204 TESTUSER dywt9v0xuvgyv 1800244878 9 Rowid Range Scan TESTUSER.T4 4 24 395 631 TESTUSER dywt9v0xuvgyv 1800244878 9 Rowid Range Scan TESTUSER.T4 58 22 238 31 TESTUSER SQL> / SQL_ID HASH_VALUE LINE_ID OPNAME TARGET TARGET_DESC TIME_REMAINING ELAPSED_SECONDS SID SERIAL# USERNAME ------------- ---------- ---------- ---------------- --------------- ----------- -------------- --------------- ---------- ---------- -------- dywt9v0xuvgyv 1800244878 9 Rowid Range Scan TESTUSER.T4 7 33 81 1204 TESTUSER SQL> / no rows selected

As you can see from the above, the parallel query processes (each with a different SID) appear, and disappear from the V$SESSION_LONGOPS output as the query executes. The output shows a roughly 40 to 50 second time period during the execution of the query in the first session where nothing appeared in the V$SESSION_LONGOPS output.

So, we have found a fifth possible reason for the rows in V$SESSION_LONGOPS appearing as 0 for the OP’s SQL statement – the OP may have just queried V$SESSION_LONGOPS at the wrong time.

Any other possible reasons why the OP would see 0 for the TIME_REMAINING column when querying V$SESSION_LONGOPS during the execution of a SQL statement that is expected to require 20 minutes to complete?

Remove "old" parameters and events from your init.ora

kurtvm — Tue, 09 Mar 2010 00:31:48 +0100

It's cleanup time!!

When upgrading to a new database release, especially Oracle Database 11g Release 1 or Release 2, you should always remove old init.ora parameters from the init.ora/spfile when you upgrade. For instance we've had a lot of useful (and necessary) tuning parameters in Oracle 9i environments such as _always_anti_join=off, star_transformation=false or several events.

Make sure you remove all of them when you upgrade as keeping them will definitely slow down the database performance in the new release. You don't believe this? Then see this result of a customer case having a packaged OLTP workload for the upgrade testing. It shows the comparision of this workload with all "old" 9i parameters to a small and cleaned up init.ora:

In this case we've removed just all "old" 9i parameters from the init.ora and started with a very basic init.ora in Oracle Database 11g with only the required parameters such as diagnostic_dest and the indentical memory parameters taken from the 9i environment.

And this has speed up the processing in Oracle Database 11g by 14,5%!!!

You could always do a 'create pfile='/tmp/init.ora' from spfile;' and modify it. Use the modified init.ora when starting up your database in upgrade mode by typing 'startup upgrade pfile='/tmp/init.ora' and then don't forget to do a 'create spfile from pfile;' once the database is started up.

By the way, the pre-upgrade check script utlu112.sql downloadable from within Note:884522.1 will give you all the parameters to change in the new release. And it can be run anytime before the upgrade to check your current database release.

Give this a try and remove or comment out all the "old" stuff!!

Hotsos 2010 - Day 2 - The conference begins

kurtvm — Tue, 09 Mar 2010 00:03:43 +0100

A 3:30 start, which gave me lots of time to work on my demos and by breakfast time at 7:30, I felt in a reasonable place (but not quite done yet) and headed down to eat ... wait for it ... some fruit! Then again, I'd eaten so little the previous day that I had to eat something.

First up was Gary Goodman opening the event and introducing Tom Kyte's keynote with :-

a) A video of Tom and Hotsos regular Patty, Disco Dancing to mark tomorrow night's Disco theme. Personally, I thought it was hilarious and I hope it gets posted online somewhere. I'm not sure everyone found it as funny, but it did the trick for me.

b) A gift of a custom set of poker chips because I believe Tom's a player (of poker, that is). Tom's keynote was about (looks for title in notes and can't find it) the mistakes we make *because* of our experience and our assumptions. It was thoroughly entertaining and kicked off with a quiz for four volunteer participants who had to answer 12 apparently simple questions very quickly. They were trick questions, of course Rather than me taking voluminous notes, I'm hoping that Tom might post the questions on his blog (hint, hint) even though they were meant to be answered quickly based on verbal questioning.

Oh, and I couldn't believe it when my phone started ringing, for someone who's normally exceptionally careful about that stuff. I had it on silent last night, missed a text from Alex G and so had taken it off silent. Clown! (In retrospect, this should have been a warning sign.)

There were tons of great examples of where clever people have gone spectacularly wrong through over-confidence. We're often wrong when we answer quickly based on our prior experience because things change. I suspect I sometimes frustrate colleagues by not giving the snappy answers they expect, but I know that Oracle stuff is often not as obvious as it seems at first sight. There was even a sighting of Martin Widlake's "Making Things Better Makes Things Worse" but no name-check, Martin, so you're only *almost* famous

I particularly enjoyed the video I hadn't seen of Richard Feynmann talking about the uncomfortable state of confusion and feeling stupid. (With hindsight, why did I not see the warning signs ...)

Then I skipped two sessions I was looking forward to but, as I've said before, I need to take care of my own presenting business before I can enjoy and learn from other people's presentations. However, I bumped into a friend after Richard Foote's session and he thought it was amazing. My friends first trip to Hotsos is going well.

Sitting in my room, I was really happy with how the demos were looking, raring to go and went to iron my shirt. Damn, the iron wasn't working and I really didn't have time to wait for another to show up. No problem, though, because I always carry a special shirt that *really* doesn't need ironing - just hang it up in the bathroom with the shower on. (Another warning sign, though?)

I'm very superstitious about using any new kit for the first time, so I was still nervous about a couple of things with the demos

1) Performance, because I know I'd run them all weekend and have a new powerful laptop (more on that another time), but the demos are designed to hammer the machine. So I thought I'd disable everything I didn't need running.

2) Driving the projector because I'd never driven one with the new laptop. I arranged to check that my laptop would drive the projector ok. It seemed to, but one of the demos was behaving strangely. No time to investigate because Dan Norris needed the podium, so I decanted to Alex's Battle Against Any Guess presentation because we'd been discussing it, I know he was concerned about how it would go and wanted to show a little moral support.

In the event, he needn't have worried. Personally, I find his Russian accent all but impenetrable, particularly after we've had a few beers and are both shouting at each other, but I'm sure the feeling's mutual! But the fact that he had the room rolling about with laughter at regular intervals meant it had gone very well! I hope he still does plenty of technical presentations with lots of demos, but he proved he can do something a little more conceptual and message-driven. Better still, I'd been frantically trying to get my demos working at the back of the hall by re-enabling services I'd disabled and they started to work I turned round to Cary Millsap who was hiding their too and dramatically mimed wiping the sweat away from my brow. Time to head back into the other room and watch the tail-end of Dan Norris' Database Machine presentation. I didn't see nearly enough and was completely pre-occupied by what was to come, so will need to catch up on the slides later.

Some interesting Q & A at the end which were slightly less interesting for me because my demos started playing up again! So, a room full of people, I'm rebooting my laptop, nothing is working and then, bang, it's time!

Whilst my presentation was originally part of this post, my review is so long-winded and personal, I've moved it to another post that people can choose to skip over! That way it doesn't detract from the good presentations I saw yesterday, such as Kevin Closson's "Ten Years After Y2K And We Still "Party Like It's 1999"".

Excellent, as always, and packed full of information, it also sort of tied up the way the day opened, albeit in a very different tone and style that things change. Kevin has the perspective of having been in the industry a long time, so he's seen the past, but also being able to see where we are now and where we're going, so he concentrated on a few of his pet subjects that I'm pretty sure you'll see cropping up on his blog in the near future but, to give you a taster, he talked about the merits of SMT on Nehalem EP chips for different workloads, which is something we'd been talking about at the opening reception. I'm pretty sure I can't disable it on my laptop to give it a try, but I might have a word with him about it anyway. He talked about Flash, Direct-attached storage, NFS, virtualisation and all that good stuff as always but because I missed the first 10 min for a much-needed break and couldn't read the slides because I forgot my glasses, I'm going to have to try to hit on him for a copy of his slides later! Oh, and I was wondering why the presentation was well attended but not packed. I'd forgotten Tom Kyte was speaking next door. I was happy with my choice.

I did have one more presentation scheduled, to see the first of Kerry Osborne's but I was pretty low at this stage and sleep deprivation was kicking in, so I headed back to my room to catch up with some blogging and tweeting (thanks for the support) and then it was time for dinner with the Oak Table crew. Very nice it was, too, hats off to Marco and Carol for choosing that place, the company of all and for Carol's consistently top-notch organisation and geek-ferrying. Nice big lump of beautiful steak, some chips and a beer. That's more like it! A few more drinks back at the bar but I made my excuses and left at 11:30 despite some vain attempts at hypnotising me into staying!

Oh, but the weather was still rubbish all day

Regardless of everything that happened yesterday, I now have 3 days of no responsibility and can just learn, socialise, eat and perhaps sleep a bit more. I woke up at 3:30 again this morning, but turned over and managed another 2 hours. Bliss!

Hotsos 2010 - Day 1.79 - Friends show up

kurtvm — Tue, 09 Mar 2010 00:03:23 +0100

Hard at work preparing for my presentation yesterday until it got nearer to 5pm and people started coming in on their flights. First up was Kevin Closson, who looked a somewhat sad sight (by his usual standards), clutching a bag from a PC store containing what he hoped would be the crucial bit of software that would help him solve his over-written MBR. I waited for him in the bar and discovered Mark Bobac had Ubuntu Live on a USB stick. I suppose I could have gone and told Kevin, but I knew he'd be down imminently

It was also a brilliant experience to bump into Paul Matuszyk again, who I worked with briefly 4 years ago at Sky. This is his first conference, largely inspired by a set of Symposium 2006 notes I gave him and my encouragement so it was a) great to see him again and b) pleasing when I saw him coming out of Richard Foote's presentation, blown away by how good it was. I think he's already decided it was a good decision

Then it was the usual suspects, the usual drinking and some chat with KC, who always has smart stuff to say. In amongst that, I liked him pointing out that my new iCore 7 laptop is probably what they used to euphemistically call a 'Portable' computer. He's not wrong there

Not too late to bed and really looking forward to the first day of the conference and my presentation.

Weird exception handling

kurtvm — Mon, 08 Mar 2010 22:42:00 +0100

PL/SQL got me fooled today. My assignment was to build a new procedure that gets invoked together with an existing procedure. After I had build and unit tested my new procedure, the tester wanted to conduct a system integration test. He had trouble coming up with a situation where the old and new procedure were called. So I helped him by having a look at some of the surrounding code and my

Inserts Experiencing an Increasing CPU Consumption

kurtvm — Mon, 08 Mar 2010 22:23:42 +0100

Last week I had to analyze a strange performance problem. Since the cause/solution was somehow surprising, at least for me, I thought to share it with you. Let me start by quickly describing the setup and what was done to reproduce the problem: Database version: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 (64-bit) Operating system: Solaris 10 (SPARC) To [...]

Oracle Database 11g helps BMW ORACLE Racing win America's Cup

kurtvm — Mon, 08 Mar 2010 21:31:16 +0100

I recently sat down with Ian "Fresh" Burns, design coordinator for the BMW ORACLE Racing trimaran, and talked about how technology helped win back the America's Cup. An amazing sight to see on the water, the BMW ORACLE Racing trimaran is able to travel at 4 times the wind speed, is as large as a baseball diamond, and has a wing sail that's 23 stories tall. Some of you will be interested to learn from Fresh how Oracle Database 11g played a major part in helping BMW ORACLE Racing win the oldest active trophy in International sport. Plus, Fresh shares a couple of other interesting bits about the race.

To learn more, tune into our podcast conversation with Fresh.

var gaJsHost = (("https:" == document.location.protocol) ? "https://ssl." : "http://www.");
document.write(unescape("%3Cscript src='" + gaJsHost + "google-analytics.com/ga.js' type='text/javascript'%3E%3C/script%3E"));

try {
var pageTracker = _gat._getTracker("UA-13185312-1");
pageTracker._trackPageview();
} catch(err) {}

International Women’s Day

kurtvm — Mon, 08 Mar 2010 20:52:16 +0100

If you do not know what International Women’s Day is: http://www.internationalwomensday.com/

Start planning your blog posts for Ada Lovelace day now (March 24th, http://findingada.com/ Ada Lovelace Day is an international day of blogging (videologging, podcasting, comic drawing etc.!) to draw attention to the achievements of women in technology and science.)

To that end, I would like to point out all the women currently in science and tech fields that I admire and think are doing great things. I think it would be great if everyone, male or female, made a list like this:

The women that have taught me science/tech along the way:

High School:
Mary Lou Ciavarra (Physics)
Maria Petretti (Pre-Algebra, and Academic Decathlon)
Reneé Fishman (Biology)
Lisa Acquaire (Economics during Academic Decathlon)

College:
Professor Kalpana White (Biology), and in whose fruit fly lab I worked for 2 semesters.
Professor Eve Marder (Introductory Neuroscience)

Though Brandeis does have female faculty in the Computer Science department, I did not manage to have any classes with female Computer Science faculty members.

My current female DBA co-workers at Pythian: Isabel Pinarci (Oracle), Michelle Gutzait (SQL Server), Catherine Chow (Oracle) and Jasmine Wen (Oracle).

And to folks in the greater MySQL/tech community and tech co-workers past and present, especially those I have been inspired and helped by: Tracy Gangwer, Leslie Hawthorn, Selena Deckelmann (Postgres), Amy Rich, Anne Cross, and more (If I have forgotten you, I apologize!).

Pete Finnigan Webinar on Oracle Security

kurtvm — Mon, 08 Mar 2010 20:30:39 +0100

It has been quite a while since my last blog post; i keep promising to post more often and even worse I have a long list of things to blog about but I don't seem to get enough time recently....[Read More]

Posted by Pete On 08/03/10 At 04:04 PM

February Top 10

kurtvm — Mon, 08 Mar 2010 19:35:00 +0100

As the name implies, it's the Top 10 for this past month. Probably the biggest reason I am doing this is the EBS Install series has become a runaway success. I've never had something become so popular so quickly...which of course tells me I'm no good and John Piwowar is the best ever. :)

John deserves quite a bit of recognition so the more I can provide, the better.

Title	Pageviews	Unique Pageviews
EBS Install Guide - Part I	541	394
Learn Oracle Business Intelligency (OBIEE)	444	337
EBS Install Guide - Part 2	347	262
DBMS_CRYPTO: Example	339	295
APEX: Install 3.2.1 on 11gR2	263	225
EBS Install Guide - Part 3	253	202
OBIEE Posts	243	215
OBIEE: How to Migrate Your rpd	234	183
BULK COLLECT and FORALL	187	166

Oracle OpenWorld 2010 Call For Papers

kurtvm — Mon, 08 Mar 2010 19:14:20 +0100

Oracle OpenWorld 2010 Call For Papers is now open.

Oracle OpenWorld is the place to present your database ideas, experiences, and accomplishments to tens of thousands of the world's most demanding Oracle users.

Submit a database paper and you could present your ideas to the highest-profile technology thought leaders, C-level executives from the Fortune 100, peers from around the world, and the most-influential members of the media.

We are eager to hear your database ideas, implementations and experiences, and we invite you to share them with the entire Oracle community. Now is your chance to be heard. This CFPs closes 11:59 pm PDT on Sunday, March 21

Get started at http://ow.ly/1eJYk

We'll see you at the show!

ORA-15477: cannot communicate with the volume driver

kurtvm — Mon, 08 Mar 2010 18:27:00 +0100

ORA-15477: cannot communicate with the volume driver
when add volume...

SQL> alter diskgroup data add volume 'asm_vol1' size 5G;
alter diskgroup data add volume 'asm_vol1' size 5G
*
ERROR at line 1:
ORA-15032: not all alterations performed
ORA-15477: cannot communicate with the volume driver

SQL> alter diskgroup data set attribute 'compatible.advm'='11.2';
alter diskgroup data set attribute 'compatible.advm'='11.2'
*
ERROR at line 1:
ORA-15032: not all alterations performed
ORA-15242: could not set attribute compatible.advm
ORA-15238: 11.2 is not a valid value for attribute compatible.advm
ORA-15477: cannot communicate with the volume driver

# acfsutil registry -l
acfsutil registry: CLSU-00100: Operating System function: open64 (/dev/ofsctl) failed with error data: 2
acfsutil registry: CLSU-00101: Operating System error message: No such file or directory
acfsutil registry: CLSU-00103: error location: OOF_1
acfsutil registry: ACFS-00502: Failed to communicate with the ACFS driver. Verify the ACFS driver has been loaded.

then load ACFS.

# /u01/app/grid/bin/acfsload start -s
ACFS-9327: Verifying ADVM/ACFS devices.
ACFS-9322: done.

Try again.

SQL> alter diskgroup data set attribute 'compatible.advm'='11.2';

Diskgroup altered.

SQL> alter diskgroup data add volume 'asm_vol1' size 5G;

Diskgroup altered.

SQL> !ls -l /dev/asm/asm_v*
brwxrwx--- 1 root oinstall 252, 86529 Mar 9 00:11 /dev/asm/asm_vol1-169

Written By: Surachart Opun http://surachartopun.com

Be [proh-ak-tiv]

kurtvm — Mon, 08 Mar 2010 18:00:05 +0100

I keep telling my team that they we need to be more proactive all the time. On our website (www.remotedbaexperts.com) we talk a lot about proactive monitoring, proactive problem prevention, etc.

Proactivity has several meanings:

Having an orientation to the future, anticipating problems and taking affirmative steps to deal positively with them rather than reacting after a situation has already occurred.
Acting in advance to deal with an expected difficulty.
Psychiatrist Dr. Viktor Frankl, describes a “proactive person” as one that takes responsibility for their life, instead of blaming outside circumstances or other people for their situation.

As you may infer from the above definitions, there are several aspects to proactivity. Proactivity is an attitude or mindset. My first exposure to the term came from Stephen Covey’s book, Seven Habits of Highly Effective People, a few years ago. He treats the subject better than anyone else. According to him, being proactive is the first of seven habits that highly effective people develop and live by. His model of proactivity claims that humans, unlike any other living forms, possess four abilities or capacities:

Self-awareness
Imagination
Conscience
Independent will

These capacities enable the “freedom to choose” how we respond to a stimulus—the things we see, hear, think, etc. or the conditions and circumstances we find ourselves in. Covey points out that, “highly proactive people do not blame circumstances, conditions, or past conditioning for their behavior.” Their behaviors (responses) are based on values, not on feelings. Reactive people tend to be affected more by their environment and by things outside their control such as weather. If the weather is “bad” they feel “bad” — they suffer and get down because of it. They worry about the war, the economy, and about many other things that they have nothing or very little to do about or control over. They worry about others and what they did, do, will do, or did not, don’t, or will not do. They are also highly affected by how others treat them, react to them, etc. They live at the mercy of others.

Covey poses that until a “reactive” or “non-proactive” person realizes, acknowledges, and accepts they are what and where they are today because of the choices and decisions they have made, as opposed to external conditions, circumstances and conditioning, they cannot become proactive.

Reactive people use a different language than proactive ones. Reactive people tend to say things like:

Nothing I can do
I can’t help it
I have to…
I cannot…
I wish…
There is no way…
It’s impossible…
If it wasn’t for…
I have no control…
Why are they doing this to me?

Worst, they believe these things and allow them to control them. They put their time and energy on trying to change things and people they do not have any control over. On the other hand proactive people focus on things they control and can do something about. According to Covey, when you do that you actually increase your “circle of influence.” By focusing and working on things you can control, you become better able to affect things that you otherwise did not before. That is the key point and most powerful lesson regarding this concept.

Covey describes three areas where the problems we face in life fall:

Direct control (problems involving our own behavior)
Indirect control (problems involving others’ behavior)
No control (problems we can do nothing about)

He goes on to recommend that in order to solve “direct control” problems we need to work on our habits. To solve “indirect control” problems we need to work on our methods of influence. And in order to deal with problems we do not control we need to learn to accept there is nothing we can do and then move on.

I mentioned earlier that the first step to change our stance from reactive to proactive is recognizing and accepting it. The “language” list above can help you monitor your own language and assess your stance. Once you do make a list of all the things you worry most about, then determine which area of control they lay in: Direct, Indirect, or No control. With that inventory complete, move on to identifying the change strategies that will best deal with them including habits to change, new or different methods of influence, etc. If you do so, you will see your “circle of influence” grow bigger and bigger — Imagine what that would be like — And what it would mean to you and others important to you?

If you have not read Covey’s book Seven Habits of Highly Effective People yet, get a hold of it ASAP — It is GREAT — it may change your life!!! And NO, I did not get a free copy of the book to “promote” itJ! In fact, I think it is one of the most sold business books of all times.

The BEST is Yet to Come!

Epi Torres, CEO

Be [proh-ak-tiv] is a post from: Remote DBA Experts

April Seattle Apps Tech Group Networking Event

kurtvm — Mon, 08 Mar 2010 17:16:35 +0100

We’re going to change it up a little bit and have a networking / social event. No presentation this time – just a chance to meet up with colleagues in the area for some food, drink, and conversation. The location is TBD, but will be somewhere downtown – we’re working on securing a spot.

Please send this out to anyone you think might be interested; we’d like to get as many of the people signed up on this list to attend as possible!

The Meetup RSVP can be found at: http://www.meetup.com/seattle-oracle-apps-tech/calendar/12816702/

April Seattle Apps Tech Group Networking Event

kurtvm — Mon, 08 Mar 2010 17:11:23 +0100

Hi All,

Please send this out to anyone you think might be interested; we’d like to get as many of the people signed up on this list to attend as possible!

Any vendors who would like to sponsor, please let me know.

Chuck

ORA-15041 - ASM Disk Group Unbalanced

kurtvm — Mon, 08 Mar 2010 17:07:00 +0100

Use ASM Disk Group NORMAL redundancy (11gR2) and found error while create tablespace.

SQL> create tablespace test datafile '+DATA' size 5G;
create tablespace test datafile '+DATA' size 5G
*
ERROR at line 1:
ORA-01119: error in creating database file '+DATA'
ORA-17502: ksfdcre:4 Failed to create file +DATA
ORA-15041: diskgroup "DATA" space exhausted

What Wrong??? - I have free size on "DATA", Check ASM Disk Group.

SQL> select name, total_mb, free_mb, required_mirror_free_mb, usable_file_mb ,type from v$asm_diskgroup where name='DATA';

NAME TOTAL_MB FREE_MB REQUIRED_MIRROR_FREE_MB USABLE_FILE_MB TYPE
------------------------------ ---------- ---------- ----------------------- -------------- ------
DATA 257807 112233 23437 44398 NORMAL

Check (ASM) alert log file.

kfdpDumpBg()
kfdpDumpBg() - Done

Check on ASM Instance and Investigate the problem.

SQL> select path, free_mb,total_mb,state from v$asm_disk where group_number in (select group_number from v$asm_diskgroup where name='DATA');

PATH FREE_MB TOTAL_MB STATE
------------------- ---------- ---------- --------
/dev/sdl 1017 23437 NORMAL
/dev/sdb 7867 23437 NORMAL
/dev/sdk 7988 23437 NORMAL
/dev/sdq 23211 23437 NORMAL
/dev/sdf 7864 23437 NORMAL
/dev/sdg 14819 23437 NORMAL
/dev/sdc 13827 23437 NORMAL
/dev/sdi 6971 23437 NORMAL
/dev/sde 14829 23437 NORMAL
/dev/sdj 1 23437 NORMAL
/dev/sdd 13839 23437 NORMAL

Disk Group UNBALANCE, then

SQL> alter diskgroup data rebalance power 11;

Diskgroup altered.

Check.

SQL> select path, free_mb,total_mb,state from v$asm_disk where group_number in (select group_number from v$asm_diskgroup where name='DATA');

PATH FREE_MB TOTAL_MB STATE
------------------- ---------- ---------- --------
/dev/sdl 1018 23437 NORMAL
/dev/sdb 7867 23437 NORMAL
/dev/sdk 7988 23437 NORMAL
/dev/sdq 23209 23437 NORMAL
/dev/sdf 7864 23437 NORMAL
/dev/sdg 14820 23437 NORMAL
/dev/sdc 13827 23437 NORMAL
/dev/sdi 6971 23437 NORMAL
/dev/sde 14830 23437 NORMAL
/dev/sdj 0 23437 NORMAL
/dev/sdd 13839 23437 NORMAL

Nothing to resolve... Check on v$operation.

SQL> select group_number, operation, state, error_code from v$asm_operation;

GROUP_NUMBER OPERA STAT ERROR_CODE
------------ ----- ---- --------------------------------------------
1 REBAL ERRS ORA-15041

Try to check & repair ASM Disk Group.

SQL> alter diskgroup data check all repair;

Diskgroup altered.

SQL> select group_number, operation, state, error_code from v$asm_operation;

GROUP_NUMBER OPERA STAT ERROR_CODE
------------ ----- ---- --------------------------------------------
1 REBAL ERRS ORA-15041

SQL> select path, free_mb,total_mb,state from v$asm_disk where group_number in (select group_number from v$asm_diskgroup where name='DATA');

PATH FREE_MB TOTAL_MB STATE
------------------- ---------- ---------- --------
/dev/sdl 1018 23437 NORMAL
/dev/sdb 7867 23437 NORMAL
/dev/sdk 7988 23437 NORMAL
/dev/sdq 23209 23437 NORMAL
/dev/sdf 7864 23437 NORMAL
/dev/sdg 14820 23437 NORMAL
/dev/sdc 13827 23437 NORMAL
/dev/sdi 6971 23437 NORMAL
/dev/sde 14830 23437 NORMAL
/dev/sdj 0 23437 NORMAL
/dev/sdd 13839 23437 NORMAL

Nothing to resolve... - "DATA" ASM Disk Group still unbalance, and v$asm_operation show error.
think & make Idea: Move some files or resize some files on "DATA" Disk Group, so login database and make something.
Example:

SQL> alter database tempfile '+DATA/orcl/tempfile/temp.263.712603677' resize 1G;

Database altered.

Check on ASM again.

SQL> select path, free_mb,total_mb,state from v$asm_disk where group_number in (select group_number from v$asm_diskgroup where name='DATA');

PATH FREE_MB TOTAL_MB STATE
------------------- ---------- ---------- --------
/dev/sdl 1685 23437 NORMAL
/dev/sdb 8322 23437 NORMAL
/dev/sdk 8357 23437 NORMAL
/dev/sdq 22855 23437 NORMAL
/dev/sdf 8324 23437 NORMAL
/dev/sdg 15013 23437 NORMAL
/dev/sdc 14043 23437 NORMAL
/dev/sdi 7386 23437 NORMAL
/dev/sde 15011 23437 NORMAL
/dev/sdj 711 23437 NORMAL
/dev/sdd 14054 23437 NORMAL

Check v$asm_operation.

SQL> select group_number, operation, state, power, error_code from v$asm_operation;

GROUP_NUMBER OPERA STAT ERROR_CODE
------------ ----- ---- --------------------------------------------
1 REBAL RUN

ASM instance... "DATA" Disk Group rebalancing, So Wait... and check

SQL> select group_number, operation, state, power, error_code from v$asm_operation;

no rows selected

SQL> select path, free_mb,total_mb,state from v$asm_disk where group_number in (select group_number from v$asm_diskgroup where name='DATA');

PATH FREE_MB TOTAL_MB STATE
------------------- ---------- ---------- --------
/dev/sdl 10524 23437 NORMAL
/dev/sdb 10524 23437 NORMAL
/dev/sdk 10528 23437 NORMAL
/dev/sdq 10532 23437 NORMAL
/dev/sdf 10521 23437 NORMAL
/dev/sdg 10523 23437 NORMAL
/dev/sdc 10522 23437 NORMAL
/dev/sdi 10526 23437 NORMAL
/dev/sde 10522 23437 NORMAL
/dev/sdj 10522 23437 NORMAL
/dev/sdd 10523 23437 NORMAL

SQL> alter diskgroup data rebalance power 1;

Diskgroup altered

Disks rebalanced, Try to create tablespace again.

SQL> select name, total_mb, free_mb, required_mirror_free_mb, usable_file_mb ,type from v$asm_diskgroup where name='DATA';

NAME TOTAL_MB FREE_MB REQUIRED_MIRROR_FREE_MB USABLE_FILE_MB TYPE
------------------------------ ---------- ---------- ----------------------- -------------- ------
DATA 257807 115767 12916 51425 NORMAL

SQL> create tablespace test datafile '+DATA' size 30G;

Tablespace created.

SQL> select path, free_mb,total_mb,state from v$asm_disk where group_number in (select group_number from v$asm_diskgroup where name='DATA');

PATH FREE_MB TOTAL_MB STATE
------------------- ---------- ---------- --------
/dev/sdl 4936 23437 NORMAL
/dev/sdb 4931 23437 NORMAL
/dev/sdk 4931 23437 NORMAL
/dev/sdq 4932 23437 NORMAL
/dev/sdf 4922 23437 NORMAL
/dev/sdg 4933 23437 NORMAL
/dev/sdc 4936 23437 NORMAL
/dev/sdi 4940 23437 NORMAL
/dev/sde 4933 23437 NORMAL
/dev/sdj 4928 23437 NORMAL
/dev/sdd 4938 23437 NORMAL

What this told me? Make Sure ASM Disk Group no problem (REBALANCE).Written By: Surachart Opun http://surachartopun.com

SQL Developer: Install Unit Testing Repository

kurtvm — Mon, 08 Mar 2010 14:41:00 +0100

Get the latest SQL Developer release here.

I'm not a big tools guy, I prefer SQL*Plus to anything else. I especially don't like paying for tools (yes, the database is a tool and costs a lot of money...I do realize the hypocrisy).

After Syme Kutz's presentation at SOUG, I've been looking more closely at SQL Developer. I've been using it (and JDeveloper) since they were both made freely available a few years ago. Mostly for the schema browsing, looking around, importing and exporting data. I do use it (SQL Developer) to write reports that I can share with the Business folks as well.

Syme's presentation was primarily on Unit Testing (which I begged for). First step to using Unit Testing is to install the repository, a set of tables the application uses to build and store tests and their results.

You need to have version 2.1 or greater.

First up, go to Tools --> Unit Test --> Select Current Repository

You'll be prompted to select a connection (i.e. database) to use

Would you like to create one now? Select Yes.

You're then told the the required roles do not exist, select OK.

Confirm running SQL

Running...will take just a few seconds

Success!

That's it. Easy right? Future posts will detail managing users and creating tests.