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 [...]

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.

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

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

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.

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.

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

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.

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.

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.

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!

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:

SQL_ID        HASH_VALUE    LINE_ID OPNAME          TARGET          TARGET_DESC  TIME_REMAINING ELAPSED_SECONDS        SID    SERIAL#  USERNAME ------------- ---------- ---------- --------------- --------------- ------------ -------------- --------------- ---------- ----------  -------- 2d4f5x92axqgn 1396201636          2 Table Scan      TESTUSER.T3                             104              53        159        909  TESTUSER 2d4f5x92axqgn 1396201636          2 Table Scan      TESTUSER.T3                              92              62        159        909  TESTUSER 2d4f5x92axqgn 1396201636          2 Table Scan      TESTUSER.T3                              82              73        159        909  TESTUSER 2d4f5x92axqgn 1396201636          2 Table Scan      TESTUSER.T3                              74              81        159        909  TESTUSER 2d4f5x92axqgn 1396201636          2 Table Scan      TESTUSER.T3                              59              95        159        909  TESTUSER 2d4f5x92axqgn 1396201636          2 Table Scan      TESTUSER.T3                              52             101        159        909  TESTUSER 2d4f5x92axqgn 1396201636          2 Table Scan      TESTUSER.T3                              41             112        159        909  TESTUSER 2d4f5x92axqgn 1396201636          2 Table Scan      TESTUSER.T3                              33             120        159        909  TESTUSER 2d4f5x92axqgn 1396201636          2 Table Scan      TESTUSER.T3                              23             131        159        909  TESTUSER 2d4f5x92axqgn 1396201636          2 Table Scan      TESTUSER.T3                              14             140        159        909  TESTUSER 2d4f5x92axqgn 1396201636          2 Table Scan      TESTUSER.T3                               5             148        159        909  TESTUSER no rows selected 2d4f5x92axqgn 1396201636          3 Table Scan      TESTUSER.T4                              57              10        159        909  TESTUSER 2d4f5x92axqgn 1396201636          3 Table Scan      TESTUSER.T4                              42              18        159        909  TESTUSER 2d4f5x92axqgn 1396201636          3 Table Scan      TESTUSER.T4                              27              31        159        909  TESTUSER 2d4f5x92axqgn 1396201636          3 Table Scan      TESTUSER.T4                              18              41        159        909  TESTUSER 2d4f5x92axqgn 1396201636          3 Table Scan      TESTUSER.T4                               6              53        159        909  TESTUSER 2d4f5x92axqgn 1396201636          3 Table Scan      TESTUSER.T4                               6              57        159        909  TESTUSER 2d4f5x92axqgn 1396201636          3 Table Scan      TESTUSER.T4                               6              63        159        909  TESTUSER 2d4f5x92axqgn 1396201636          3 Table Scan      TESTUSER.T4                               6              70        159        909  TESTUSER 2d4f5x92axqgn 1396201636          3 Table Scan      TESTUSER.T4                               6              75        159        909  TESTUSER 2d4f5x92axqgn 1396201636          3 Table Scan      TESTUSER.T4                               6              81        159        909  TESTUSER 2d4f5x92axqgn 1396201636          3 Table Scan      TESTUSER.T4                               6              86        159        909  TESTUSER no rows selected

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:

SQL_ID        HASH_VALUE    LINE_ID OPNAME          TARGET          TARGET_DESC  TIME_REMAINING ELAPSED_SECONDS        SID    SERIAL# USERNAME ------------- ---------- ---------- --------------- --------------- ------------ -------------- --------------- ---------- ---------- -------- f3scvt222bu0t 1243183227          4 Sort Output                                            1194              12        159        909 TESTUSER f3scvt222bu0t 1243183227          4 Sort Output                                            2115              22        159        909 TESTUSER f3scvt222bu0t 1243183227          4 Sort Output                                            2115              22        159        909 TESTUSER ... (in the above the last row continued to repeat with ELAPSED_SECONDS displayed as 22 for 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?

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!!

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!

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.

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

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 [...]

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) {}