I was doing some SQL troubleshooting today, and had occasion to use one of my most loved 10g New Features, so I thought I'd share it with you here. Don't worry, an 11g new feature is just around the corner.
Most of you are aware of dbms_xplan I'm sure. In 9i you could use it to format the contents of the PLAN_TABLE after running an explain plan command. Also in 9i, a new view was introduced, V$SQL_PLAN. This view provides you with the actual execution plan of any SQL statement currently in the shared pool.
In 9i there was no real simple way to query this view (Tom Kyte has a marvy view and SQL statement that makes it much easier on ASK TOM).
Oracle 10g makes it very easy to query V$SQL_PLAN and find out what really happened to that SQL statement (remember, the explain plan command can be wrong!!). Let's look at an example:
-- Our query
SQL> select a.empid, a.ename, b.dept_desc
2 from emp a, dept b
3 where a.deptid=b.deptid;
EMPID ENAME DEPT_DESC
---------- ------------------------------ -------------
10 Freeman HARDWARE
20 Dutch SOFTWARE
30 Jose NOWARE
-- Get the SQL ID
SQL> select sql_id, sql_text from v$sqlarea where sql_text like '%empid%';
SQL_ID
-------------
SQL_TEXT
--------------------------------------------------------------------------------
dymt4gchudyk9
select a.empid, a.ename, b.dept_desc from emp a, dept b where a.deptid=b.deptid
dm0a3c8v9awb1
select sql_id, sql_text from v$sqlarea where sql_text like '%empid%'
-- Using the SQL_ID, get the execution plan:
SQL> select * from table(dbms_xplan.display_cursor('dymt4gchudyk9',0));
SQL_ID dymt4gchudyk9, child number 0
-------------------------------------
select a.empid, a.ename, b.dept_desc from emp a, dept b where
a.deptid=b.deptid
Plan hash value: 844388907
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 6 (100)| |
| 1 | MERGE JOIN | | 3 | 72 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 3 | 33 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | PK_DEPT | 3 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 3 | 39 | 4 (25)| 00:00:01 |
| 5 | TABLE ACCESS FULL | EMP | 3 | 39 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("A"."DEPTID"="B"."DEPTID")
filter("A"."DEPTID"="B"."DEPTID")
Other options provide more or less detail as you desire.
Tuesday, July 31, 2007
Monday, July 30, 2007
HOV Lanes - Love 'em, hate em
If you are looking for something Oracle related, this isn't the post. More will come soon though!
Here in Utah, and many other places I've lived (Chicago, Seattle) we have High Occupancy Vehicle (HOV) lanes on some of the interstates. In Utah they offer what they call "Express Passes" which allow you to travel in the HOV lane, even if you are a OOV (One Occupancy Vehicle). These are limited passes (I think about 2000 are available) and are available by subscription on a monthly basis.
Of course, when they first came out (just a few months ago) I had to have one! They cut my commute time to about 20 minutes or so one way (from maybe 40-45 minutes one way on good days, an hour on bad ones). They are worth the $50.00! Nothing is more fun than driving by at 60MPH while the other lanes of traffic are sitting there, stopped. zoom.... zoom... zoom...
So, I was driving in the HOV lane, and getting really aggravated today. Let me list my pet peeves..
1. Why do people who, with only one passenger in the vehicle and no express pass, think it's OK to just pull into the HOV lane and drive? Why is it that people can't just follow the rules?
2. The HOV lanes have restricted entry areas designated by dashed lines. Most of the HOV lane is protected by solid lines that you are not supposed to cross. So, traffic to your right is at a stand still, you are in the HOV lane moving at a good clip and WAM, some person in a truck ten foot tall with only one occupant swerves into your lane illegally and at about 10 MPH.
Again, why can't people follow the rules? Ahhhh... for the days of Mad Max and 60mm machine guns mounted on your hood.
3. Then, there is always the person in the HOV lane who wants to drive at about 1.25 percent of the speed of the people in the other lanes. This is ok, until the other lanes are running at about 15 MPH, which means we run at around 20MPH. I don't get this..... You look about 8 cars forward and it's wide open lane. DRIVE FOR GOODNESS SAKE!!
So, there is my HOV lane rant. I fully realize that the importance of these problems falls somewhere between solving the question of the source of a rancid smell and what to have for dinner tonight, but if I don't rant about it, who will? :-)
Here in Utah, and many other places I've lived (Chicago, Seattle) we have High Occupancy Vehicle (HOV) lanes on some of the interstates. In Utah they offer what they call "Express Passes" which allow you to travel in the HOV lane, even if you are a OOV (One Occupancy Vehicle). These are limited passes (I think about 2000 are available) and are available by subscription on a monthly basis.
Of course, when they first came out (just a few months ago) I had to have one! They cut my commute time to about 20 minutes or so one way (from maybe 40-45 minutes one way on good days, an hour on bad ones). They are worth the $50.00! Nothing is more fun than driving by at 60MPH while the other lanes of traffic are sitting there, stopped. zoom.... zoom... zoom...
So, I was driving in the HOV lane, and getting really aggravated today. Let me list my pet peeves..
1. Why do people who, with only one passenger in the vehicle and no express pass, think it's OK to just pull into the HOV lane and drive? Why is it that people can't just follow the rules?
2. The HOV lanes have restricted entry areas designated by dashed lines. Most of the HOV lane is protected by solid lines that you are not supposed to cross. So, traffic to your right is at a stand still, you are in the HOV lane moving at a good clip and WAM, some person in a truck ten foot tall with only one occupant swerves into your lane illegally and at about 10 MPH.
Again, why can't people follow the rules? Ahhhh... for the days of Mad Max and 60mm machine guns mounted on your hood.
3. Then, there is always the person in the HOV lane who wants to drive at about 1.25 percent of the speed of the people in the other lanes. This is ok, until the other lanes are running at about 15 MPH, which means we run at around 20MPH. I don't get this..... You look about 8 cars forward and it's wide open lane. DRIVE FOR GOODNESS SAKE!!
So, there is my HOV lane rant. I fully realize that the importance of these problems falls somewhere between solving the question of the source of a rancid smell and what to have for dinner tonight, but if I don't rant about it, who will? :-)
Wednesday, July 25, 2007
An Oracle Database 11g New Feature to share...
So, I'm just back from a quick vacation... Spent a few days on a cruise with my wife and then a couple of days at the Mouse (Disney).... had a great time!! I'll talk about the trip in a bit more detail in another blog entry. It's late now, and I wanted to get something in about Oracle Database 11g, because I promised I would.
Oracle 11g has introduced a new kind of partitioning called interval partitioning. If you have done range partitioning in the past, then you know that there is a maintenance cost. Often you find yourself having to add partitions as time goes on. Wouldn't it be nice if you could just tell Oracle you wanted to partition every month and it would create the partitions for you? That is exactly what interval partitioning does. Here is an example:
Note the interval keyword. This defines the interval that you want each partition to represent. In this case, Oracle will create the next partition for dates less than 02-01-2007 when the first record that belongs in that partition is created. Other than the interval keyword, this statement is just like a regular create table statement....
Cool stuff. Note that the partition names are system generated, which I don't personally like, but you can rename them if you like. I've not yet found a way to create some form of template that Oracle will use for partition naming (would be nice, maybe another release).
There is also an optional store in clause that provides the ability to define the tablespace to store the partition in, much like with hash partitioned tables.
So, there is a new Oracle Database 11g feature for you. More to come, and much more in my Oracle Press book Oracle Database 11g New Features, which should be out soon after Oracle Database 11g is released. It's available on Amazon for pre-orders now, and I hope you will check it out!
Oracle 11g has introduced a new kind of partitioning called interval partitioning. If you have done range partitioning in the past, then you know that there is a maintenance cost. Often you find yourself having to add partitions as time goes on. Wouldn't it be nice if you could just tell Oracle you wanted to partition every month and it would create the partitions for you? That is exactly what interval partitioning does. Here is an example:
create table selling_stuff_daily
( prod_id number not null, cust_id number not null
, sale_dt date not null, qty_sold number(3) not null
, unit_sale_pr number(10,2) not null
, total_sale_pr number(10,2) not null
, total_disc number(10,2) not null)
partition by range (sale_dt)
interval (numtoyminterval(1,'MONTH'))
( partition p_before_1_jan_2007 values
less than (to_date('01-01-2007','dd-mm-yyyy')));
Cool stuff. Note that the partition names are system generated, which I don't personally like, but you can rename them if you like. I've not yet found a way to create some form of template that Oracle will use for partition naming (would be nice, maybe another release).
There is also an optional store in clause that provides the ability to define the tablespace to store the partition in, much like with hash partitioned tables.
So, there is a new Oracle Database 11g feature for you. More to come, and much more in my Oracle Press book Oracle Database 11g New Features, which should be out soon after Oracle Database 11g is released. It's available on Amazon for pre-orders now, and I hope you will check it out!
Thursday, July 19, 2007
Frustrating Technology...
There is absolutely nothing Oracle related in this post, so if you only are interested in Oracle stuff, skip this one.
It never fails. When one thing goes wrong, there seems to be this domino effect. So last night I'm sitting at my desk trying to get some writing done. At 10pm, I have an automated backup that kicks off on my laptop to my new 1TB mybook WD drive. It has been magic for about the last two months since I bought the thing. My whole house backs up to it.
So, last night, my backup failed. For some reason, it could not get to the networked drive. So, as I'm looking over the network drive, my foot manages to catch one of my power strips (I have *way* to many of them!!) and hits the kill switch. Down go the dominoes... and enter silence. There is nothing worse than the sound of all your computers shutting down instantly .... only my one laptop kept running. My desktop, my poor wife's desktop, a laptop with a bad battery, the network modem, the router, the backup drive... all just died.
So, I sat and enjoyed the silence for a moment. No fans wuurring, no beeps, no green and orange lights... I just sat in the dark and enjoyed the total silence (the remaining laptop, a wonderful Dell XPS, is very quiet). Ah... how rare is silence these days.
Then I started it all back up. Everything came up ok, except I still can't connect to my MyBook drive which is irritating. I rebooted it, reset it, changed the network cables, and it's still not connecting to the network. BALDERDASH!!! So, if you know anything about WD MyBooks... please drop me a line. Otherwise it's a call to tech support tonight. *SIGH*
It never fails. When one thing goes wrong, there seems to be this domino effect. So last night I'm sitting at my desk trying to get some writing done. At 10pm, I have an automated backup that kicks off on my laptop to my new 1TB mybook WD drive. It has been magic for about the last two months since I bought the thing. My whole house backs up to it.
So, last night, my backup failed. For some reason, it could not get to the networked drive. So, as I'm looking over the network drive, my foot manages to catch one of my power strips (I have *way* to many of them!!) and hits the kill switch. Down go the dominoes... and enter silence. There is nothing worse than the sound of all your computers shutting down instantly .... only my one laptop kept running. My desktop, my poor wife's desktop, a laptop with a bad battery, the network modem, the router, the backup drive... all just died.
So, I sat and enjoyed the silence for a moment. No fans wuurring, no beeps, no green and orange lights... I just sat in the dark and enjoyed the total silence (the remaining laptop, a wonderful Dell XPS, is very quiet). Ah... how rare is silence these days.
Then I started it all back up. Everything came up ok, except I still can't connect to my MyBook drive which is irritating. I rebooted it, reset it, changed the network cables, and it's still not connecting to the network. BALDERDASH!!! So, if you know anything about WD MyBooks... please drop me a line. Otherwise it's a call to tech support tonight. *SIGH*
Wednesday, July 18, 2007
Everyone meet N7598U!!
So, this is one of my two airplanes... Everyone say high to N7598U. She's a Cessna 150M. We have been all over the place. From Florida to Chicago to Little Rock to Salt Lake City. She has a freshly overhauled engine in her with maybe 20 hours now. She needs a new coat of paint and some interior work, but she's just a blast to fly. Anyone have any ideas what color I should paint her? I'll post a picture of my Mooney when she gets done with her engine work.
Blasted bots and more 11g
So, I start my blog up again and what happens? Some stupid "bot" on Google determines somehow that this is a spam blog and locks it out. I had to go through a few minor hoops to get it up and running again, but it was still irritating.
Note: They had me back up and running after about 7 hours or so...
So, how about some 11g stuff. At the moment I just finished the section on virtual columns in Oracle Database 11g. Basicly a virtual column is a derived column. It derives it's value from other data stored in the table. Data in a virtual column is not stored in the database, so they consume no disk space. You can, within some restrictions, apply functions to the virtual columns, case statements and such things.
What is interesting about virtual columns is that you can use then as partition keys in partitioned tables. As Spock would say, most facinating! Here is a quick example of a table created using a virtual column:
create table emp (emp_id number, salary number, our_401k_addition as ( (salary/12)*.05) );
and the results:
SQL> insert into emp values (1,1000,default);
1 row created.
SQL> select * from emp;
EMP_ID SALARY OUR_401K_ADDITION
---------- ---------- -----------------
1 1000 4.16666667
So, now we know our 401k 5% contribution!
Oracle Database 11g New Features provides even more in-depth coverage and examples!
More to come!!
Note: They had me back up and running after about 7 hours or so...
So, how about some 11g stuff. At the moment I just finished the section on virtual columns in Oracle Database 11g. Basicly a virtual column is a derived column. It derives it's value from other data stored in the table. Data in a virtual column is not stored in the database, so they consume no disk space. You can, within some restrictions, apply functions to the virtual columns, case statements and such things.
What is interesting about virtual columns is that you can use then as partition keys in partitioned tables. As Spock would say, most facinating! Here is a quick example of a table created using a virtual column:
create table emp (emp_id number, salary number, our_401k_addition as ( (salary/12)*.05) );
and the results:
SQL> insert into emp values (1,1000,default);
1 row created.
SQL> select * from emp;
EMP_ID SALARY OUR_401K_ADDITION
---------- ---------- -----------------
1 1000 4.16666667
So, now we know our 401k 5% contribution!
Oracle Database 11g New Features provides even more in-depth coverage and examples!
More to come!!
Saturday, July 14, 2007
Idaho...
I'm in Idaho this weekend for a family reunion. It's 109 out here... 109??? Who's idea was it to have a family reunion, at a campground, in the middle of the SUMMER!?!?!?!?
Idaho is pretty, I'll give it that. From our Hotel room it actually looks a bit like Oklahoma, where I am from. It's a bit hilly (not all of Oklahoma it flat) and the grass is in it's summer hue, Yellow. It might sound ugly, but I actually find it quite pretty.
The 11g New Features book is moving along. I am about 90% done with Chapter 5 which includes Database Replay and the SQL Performance Analyzer. The good news is that Database Replay works great in the Beta. I was able to capture a workload from one database, move the related workload files to another database and replay them. Simply an amazing process. Oracle provides the ability to control things like think time and so on, so you can adjust the replay "volume" if you will. Database replay will make testing new upgrades or hardware so much easier.
SQL Performance Analyzer is another story. While it's documented in the Performance Tuning Beta documents, the Oracle supplied PL/SQL procedure is not in Beta 4, which is what I'm currently running. Beta 5 is out, but I was going to install it after I got done with Chapter 5, maybe I need to install it now though.
I'm running three different Oracle databases for this book, moving between the different ones as it suits me. I'm running on the Win platform, and also I'm running a Linux single node version on top of VMWare. Finally I've got a VMWare RAC cluster running too. All in all, a fun experience.
Well, off to the Saturday version of "How sunburned can you get?" - More later!!
Idaho is pretty, I'll give it that. From our Hotel room it actually looks a bit like Oklahoma, where I am from. It's a bit hilly (not all of Oklahoma it flat) and the grass is in it's summer hue, Yellow. It might sound ugly, but I actually find it quite pretty.
The 11g New Features book is moving along. I am about 90% done with Chapter 5 which includes Database Replay and the SQL Performance Analyzer. The good news is that Database Replay works great in the Beta. I was able to capture a workload from one database, move the related workload files to another database and replay them. Simply an amazing process. Oracle provides the ability to control things like think time and so on, so you can adjust the replay "volume" if you will. Database replay will make testing new upgrades or hardware so much easier.
SQL Performance Analyzer is another story. While it's documented in the Performance Tuning Beta documents, the Oracle supplied PL/SQL procedure is not in Beta 4, which is what I'm currently running. Beta 5 is out, but I was going to install it after I got done with Chapter 5, maybe I need to install it now though.
I'm running three different Oracle databases for this book, moving between the different ones as it suits me. I'm running on the Win platform, and also I'm running a Linux single node version on top of VMWare. Finally I've got a VMWare RAC cluster running too. All in all, a fun experience.
Well, off to the Saturday version of "How sunburned can you get?" - More later!!
Thursday, July 12, 2007
11g and other stuff...
So, did you watch the announcement yesterday about Oracle Database 11g? If not you can surf here:
and catch the announcement.
I am knee deep in writing the Oracle Database 11g New Features book for Oracle press right now:
and I have to say that there are a number of nice new features in 11g. Writing a book using Beta code is a bit of a bumpy ride though. It's made writing the book an interesting experience. Still, you get to see a great deal of what is coming, and play with it and that is quite exciting. It's also nice now that the announcement has been made, to be able to talk about 11g and some of the new features. So, in future blog entries I'll be talking about 11g. I should note that even though I'm writing the book using the Beta, I'll be going through every bit of the book after the production version of 11g is out, so you can be sure that there will be nothing in the book that is not in the production version of 11g!!
I'm working with Database Replay right now, a very interesting feature. Database replay allows you to capture a workload on a source system, and then replay it on some other test system. The setup is a bit involved but not too bad. I have noticed that replay seems to consume a bit more resources than the docs seem to indicate, but that could just be my system. Replay will allow you to manipulate certain parameters of the workload playback, such as the think time, and it deals with concurency issues too very nicely. All in all, once I can get past one minor problem I'm having, I have high expectations for this feature.
I'm working with Database Replay right now, a very interesting feature. Database replay allows you to capture a workload on a source system, and then replay it on some other test system. The setup is a bit involved but not too bad. I have noticed that replay seems to consume a bit more resources than the docs seem to indicate, but that could just be my system. Replay will allow you to manipulate certain parameters of the workload playback, such as the think time, and it deals with concurency issues too very nicely. All in all, once I can get past one minor problem I'm having, I have high expectations for this feature.
For now though, it's been raining in Salt Lake City which has been very nice. It helps to clear the air and cool it at the same time. I love rain... Give me a rainy Saturday with nothing to do but sit on my porch swing, with my wife and my wireless laptop. Some thunder is nice, but optional. I'm from Oklahoma, so you might think it weird that what I really long for is to hear those Tornado warning horns blare... but strangely it's almost a rite of spring for me and they are missed.
I am moving into a new office. For the first time since I lived in Seattle many years ago, I actually have my own window and the view is most excellent. My window faces the Salt Lake Temple of the Church of Jesus Christ of Latter-Day Saints. The mountains are in the background. I can't wait for winter and seeing snow on the mountains. I am more of a winter person myself and while summer is nice for things like swimming, I'd be more than happy if it never got above about 76F.
I am moving into a new office. For the first time since I lived in Seattle many years ago, I actually have my own window and the view is most excellent. My window faces the Salt Lake Temple of the Church of Jesus Christ of Latter-Day Saints. The mountains are in the background. I can't wait for winter and seeing snow on the mountains. I am more of a winter person myself and while summer is nice for things like swimming, I'd be more than happy if it never got above about 76F.
By the way, if you are reading this, and happen to be LDS and have some technical skills, the church is hiring. We need technical people badly. From DBA's, to Unix Admin's to project managers and more we need people. You must be LDS though, since we are a church we are allowed to hire only those of our faith, which makes sense to me.
More later!!
RF
RF
Sunday, July 08, 2007
I'm back
After a long hiatus, I'm back blogging. I was gone for a number of reasons, but now I have returned and you can expect that I will keep blogging on a regular basis.
A lot has changed in my life since my last blog. I've moved to Utah and now work for the Church of Jesus Christ of Latter-Day Saints. I love my work, saving souls is much more fun than helping someone meet their quarterly financial goals. I still am much involved with Oracle as I am a Principal Engineer and Team Manager of a great DBA team. We have a number of Oracle databases, and it's simply an amazing experience.
I've also bought a second airplane. I bought an M20A Mooney. For those of you who fly you will know that this is a bit faster than my Cessna and holds 4 people instead of two. I'll post pictures of my plane when I can (it's getting some engine work done on it at the moment).
I have opened the Blog back up to comments. I would ask that *everyone* be kind here. No personal attacks will be tolerated on anyone's part, this is not the place for that. Personal attacks just is not who I am (unless there is some clear moral issue), and I'll be no part of it, nor a part of any type of war between posters. I'm not opposed to a difference of opinion, but let's stick to facts and civility and we will all be just fine.
[Edit] One note, I've only opened the Blog to registered users. I am very much opposed to anonymous comments (I think they are gutless). So, you can leave a comment here but you will have to be a registered user and absolutely responsible for what you say. This seems fair to me.
Glad to be back!
Robert
Subscribe to:
Posts (Atom)
