DBA Blogs

I ran into a case today where I had tuned a query by putting a cardinality hint into a view, but the optimizer changed the plan anyway by overriding the cardinality hint with cardinality feedback.  So, I found out how to turn cardinality feedback off in case you want the cardinality hint to stick.  I built a simple testcase for this post.

Here is the test query:

select /*+ cardinality(test,1) */ count(*) from test;

The first time it runs the plan shows that the optimizer thinks there is one row in the test table:

-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |   292 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| TEST |     1 |   292   (1)| 00:00:04 |
-------------------------------------------------------------------

But the second time cardinality feedback tells the optimizer the truth:

-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |   292 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| TEST | 31467 |   292   (1)| 00:00:04 |
-------------------------------------------------------------------

Note
-----
   - cardinality feedback used for this statement

How annoying!  It ignored my cardinality hint. But you can add this hint to turn off cardinality feedback:

opt_param('_optimizer_use_feedback' 'false')

and then you are back to the original plan with rows = 1.  This doesn’t prove that this will help improve performance just that the plan will show the cardinality I’m trying to make it use.

- Bobby

 

 

With real possibilities and opportunities, blogging is getting mature day by day, and so is the technology and its innovations. The combination of both becomes a dazzling medley, which is called as Log Buffer. Enjoy this week’s stunning Log Buffer #311.

Oracle:

Sudip Datta is writing about database as a service.

Premature optimization, (probably) because of Donald Knuth’s famous line “premature optimization is the root of all evil,” (see Structured Programming with go to Statements) is, at the very least, a controversial topic.

Are you using the application server that best serves your changing business needs? Maybe it’s time to consider an upgrade? Suggested by R A Sanyal.

Not all Deadlocks are created the same, according to Doug.

Bitten by a Virtual Column, _OPTIMIZER_IGNORE_HINTS Doesn’t Ignore Hints? Charles Hooper blogs.

SQL Server:

Ryan Adams is renaming server with SQL.

Adding a Linked Server with ‘Oracle Provider for OLE DB’ as the provider – Proceed with caution !

Need to check when was the SQL Server instance was rebooted?

Bradley Schacht had an interesting situation where an SSIS package was scheduled to run at 1 AM but failed with a deadlock message.

Continued investment in your professional development for career growth is hard. It’s also essential to being a successful Data Professional.

MySQL:

Chris Calender is troubleshooting “Waiting for table metadata lock” Errors for both MyISAM and InnoDB Tables.

Baron is currently finishing some features to make a program highly resilient to occasional crashing bugs.

The MySQL Connect 2013 Call for Papers is Open!

Want to loose your foreign key? Here is how.

So what about the diffstat of MariaDB compared to MySQL? Stewart Smith opines.

This will be a periodically updated list of useful Oracle Youtube videos.

17-Mar-13   Jonathan Lewis -- Pessimist Exadata

17-Mar-13   Connor McDonald -- RAC Upgrade Nightmare

19-Mar-13  Cary Millsap (with Kerry Osborne)  --  My Perspective on Exadata

24-Mar-13  Tim Gorman  --  Data Warehousing

13-Apr-13  Tom Kyte  --  What are we still doing wrong ?

17-May-13  Gwen (Chen) Shapira  --  Big Disasters

.
.
.

Just a quick announcement that my second book is available from Packt Publishing.  OCA Oracle Database 11g: Database Administration I: A Real-World Certification Guide (again with the long title) is designed to be a different kind of certification guide.  Generally, it seems to me that publishers of Oracle certification guides assume that the only people who want to become certified are those with a certain level of experience, like a working DBA with several years on the job.  So, these guides make a lot of assumptions about the reader.  They end up being more about a lot of facts for the test rather than a cohesive learning experience.  My book attempts to target to a different kind of reader.  I've observed in the last several years that many people from non-database backgrounds are setting out to get their OCA or OCP certifications.  These folks don't necessarily bring a lot of knowledge or experience to this attempt, just a strong drive to learn.  My two books are designed to start from the beginning, then take the reader through all of the subjects needed for the certification test.  They're designed to be read straight through, completing the examples along the way.  In a sense, I'm attempting to recreate the experience of one of my Oracle classes in book form. 




You'll find the book at these fine sellers of books.

Packt Publishing
Amazon
Barnes and Noble

Whenever I need to test a query that has bind variables I usually go to the development team to ask them what typical values are or what the values were the last time the query ran.  I’m pretty sure that in the past when I went looking for a DBA_HIST view that held bind variables that I came up empty.  Today I’m working on tuning a query with a bind variable and I tried to find the value of the bind variable last Sunday using the DBA_HIST_SQLBIND view.  Strangely enough it had exactly what I wanted so I’m not sure if there are cases where this doesn’t capture the variables and cases where it does, but it worked for me today so it may be useful to others in certain cases.

I had the sql_id of the query from an AWR report spanning the period of high CPU usage on Sunday: 40wpuup08vws6.  I ran this query to get the bind variable for all executions of this sql_id.

select 
sn.END_INTERVAL_TIME,
sb.NAME,
sb.VALUE_STRING 
from 
DBA_HIST_SQLBIND sb,
DBA_HIST_SNAPSHOT sn
where 
sb.sql_id='40wpuup08vws6' and
sb.WAS_CAPTURED='YES' and
sn.snap_id=sb.snap_id
order by 
sb.snap_id,
sb.NAME;

It produced this output for the bind variable B1.

END_INTERVAL_TIME          NAM VALUE_STRING
-------------------------- --- -----------------
03-FEB-13 02.00.32.733 AM  :B1 02/02/13 00:00:00
03-FEB-13 03.00.36.316 AM  :B1 02/02/13 00:00:00
10-FEB-13 02.00.29.975 AM  :B1 02/09/13 00:00:00
10-FEB-13 03.00.23.292 AM  :B1 02/09/13 00:00:00
17-FEB-13 02.00.36.688 AM  :B1 02/16/13 00:00:00
17-FEB-13 03.00.06.374 AM  :B1 02/16/13 00:00:00
24-FEB-13 01.00.33.691 AM  :B1 02/23/13 00:00:00
24-FEB-13 02.00.20.269 AM  :B1 02/23/13 00:00:00
24-FEB-13 03.00.16.811 AM  :B1 02/23/13 00:00:00
03-MAR-13 02.00.17.974 AM  :B1 03/02/13 00:00:00
03-MAR-13 03.00.33.340 AM  :B1 03/02/13 00:00:00
10-MAR-13 10.00.10.356 PM  :B1 03/09/13 00:00:00
10-MAR-13 11.00.43.467 PM  :B1 03/09/13 00:00:00
11-MAR-13 12.00.12.898 AM  :B1 03/09/13 00:00:00

So, you can see what value this date type bind variable B1 contained each weekend that the query ran.  The query runs for multiple hours so that is why it spanned multiple AWR snapshot intervals.

Here is a zip of my test script and its log: zip.

- Bobby

I was asked by a colleague recently to look at why some memory parameters they had set were not being adhered to. Having done so I thought a blog post was worthwhile on the subject, this is my first one (ever) so be gentle :)

On the database in question (version 11.2.0.2) both JAVA_POOL_SIZE and LARGE_POOL_SIZE had been set to 256MB and 128MB respectively, this was clear from the spfile. However when starting up the database the values were seemingly ignored and we ended up with the below values, taken from the alert log start-up sequence:

large_pool_size          = 768M
 java_pool_size           = 768M

Why?

The reason is due to two factors:

• The SGA granule size, for this system it is set to: 134217728 (128MB), this is calculated at start-up based on the total SGA size, the value is visible through v$sgainfo or the hidden parameter: “_ksmg_granule_size“.

• The number of sub-pools in the SGA, for this we can look at the hidden parameter: “_kghdsidx_count”, which is set to 6 on our system. This value is again calculated at instance start-up and is based on several factors, the most notable being CPU_COUNT, the more CPUs the more sub-pools you get (to a maximum of 7). In my example here we have a CPU_COUNT of 24, 1 sub-pool for every 4 CPUs. There are other factors involved in calculating the number sub-pools and I have seen situations where this hidden parameter (by default) doesn’t seem to accurately reflect the number of pools in use; I am still experimenting with this. If you do set “_kghdsidx_count” manually this value will be used (but I would not recommend doing this without Oracle Support involvement).

Now using the above information we can see the calculation involved:

GranuleSize*#SubPools: 134217728*6 = 805306368 = 768M

We therefore end up with a memory overhead of 1155M, on large systems like this probably not too big a deal I hear you say. Well this time it may have contributed to an issue, we couldn’t fit the SGA into the available huge pages, from the alert log:

***********************************************************
 Huge Pages allocation failed (free: 4288 required: 4737)
 Allocation will continue with default/smaller page size
 **********************************************************

This could obviously have a big knock on effect on performance with a large SGA (much documented I won’t go into that here).

Also worth noting, if you explicitly set either JAVA_POOL_SIZE or LARGE_POOL_SIZE to zero when using manual memory management this does seem to be honoured in the tests I have done.

The story doesn’t stop there though, STREAMS_POOL_SIZE was set explicitly to 67108864 (64M), but on start-up this only ended up being 128M i.e. one SGA granule. So the sub-pools do not seem to affect the STREAMS_POOL_SIZE in the same manner, even with manual memory. It is documented that rounding to 1 granule will occur (but that is the case for all SGA areas).

Automatic Memory Management

This database was using manual memory management. If you switch to using ASMM with SGA_TARGET (I haven’t tested behaviour with MEMORY_TARGET) then the defaults seem to change again.

Some experiments on my own 11.2.0.3 database show that with SGA_TARGET set, JAVA_POOL_SIZE, LARGE_POOL_SIZE all default to the size of one SGA granule and do not seem to be impacted by the number of sub-pools when ASMM is in use, even if you explicitly set them to zero. Again STREAMS_POOL_SIZE has to be different and will default to zero if you do not set any value, if you make use of streams components then memory would be transferred dynamically. If you set a minimum value for any of these pools it will be rounded to the nearest granule automatically.

Worst Case…

In summary then, when using manual memory management remember that some of your memory pools could end up being much larger than you expect, this is particularly the case on systems with a very large SGA and CPU_COUNT. At 11gR2 you can end up with a granule size of 512M and a maximum of 7 sub-pools. All of which means the minimum size for the java and large pools are a whopping 3584M, combined total of 7168M. Worth being aware of I think, particularly since most people I speak with tend to use manual memory management for systems with a very large SGA. I have deliberately left out SHARED_POOL_SIZE because the calculations here get even messier, another time maybe …

I would be very interested in other people’s findings on this, for instance, have you tried setting any of the hidden parameters manually to reduce the minimum sizes and why did you need to do this?

Oracle Support References:

How To Determine Granule Size [ID 947152.1]
How To Determine The Default Number Of Subpools Allocated During Startup [ID 455179.1]

Mother, mother Oracle. I have heard you call…
Wanted to sail upon your cloud (control) since 7.3.4…
You’ve seen it all, you’ve seen it all…
(Paraphrased from Jimmy Buffett’s, “A Pirate Looks at 40”)

For a while now, Oracle Enterprise Manager (OEM) has had an application management component for E-Business Suite (EBS), but it wasn’t until this latest release that Oracle decided to bundle the previously stand-alone Application Management Pack and the Application Change Management Pack and deliver them as a plug-in for Oracle Enterprise Manager Cloud Control. It’s touted as a complete and integrated application management solution to help achieve high levels of performance, availability, and control while helping reduce the costs of managing Oracle E-Business Suite applications, in both version 11i and R12.

Today, we’ll look at a high level overview of deploying the EBS Application Management Pack (AMP) and some of the basic features, such as proactive monitoring of the EBS components and concurrent processing management. We’ll also discuss some of the new integrated change management features such as Patch Manager, Setup Manager, and the Customization Manager. And then finally, we’ll talk about some of the functionality delivered with AMP release 12.1.0.1. Each of these items mentioned above could be a blog post in themselves (and probably will be in the future), so for now, we’re just going to arm you with enough information to be dangerous and to get you started.

First, some of the certification information we all so desperately crave:

• Oracle Enterprise Manager Cloud Control 12c Release 1 (12.1.0.1) or 12c Release 2 (12.1.0.2)
• Oracle E-Business Suite:
  • 11i: Release 11.5.10 CU2 with ATG_PF.H RUP6 or higher
  • 12.0: Release 12.0.4 with R12.ATG_PF.A.delta.6
  • 12.1: Release 12.1 with R12.ATG_PF.B.delta.3

OEM AMP Deployment Diagram

Let’s get started:

1.     Deploying Application Management Pack 12.1.0.1 for Oracle E-Business Suite

Step by Step to Install New AMP Plug-in – Oracle E-Business Suite Plug-in 12.1.0.1.0 on Top of Grid Control 12c [ID 1463040.1] (Note: you’ll need a MOS login to access this document)

Since the Oracle Application Management Pack for E-Business Suite is a plug-in, a fully operational Oracle Enterprise Manager 12c Cloud Control environment needs to be up and running already and management agents deployed to your EBS servers. (In fact, the note mentioned above will actually give you directions on installing OEM 12c and deploying the management agents).

The next step is to deploy the Fusion Middleware plug-in in order to prepare it to receive the AMP plug-in. After that, you can download the E-Business Suite AMP plug-in from the Enterprise Manager Store and add it to your Software Library. Once that’s ready, you can deploy the AMP plug-in much like you did for the Middleware plug-in. Please note, that during the installation, the Oracle Management Service will be bounced, so if you are deploying this in a production environment, be aware that there may be short outages in your monitoring.

2.     Overview of Application Change Management Pack for Oracle E-Business Suite

Oracle has integrated some great new products to make your life a whole lot easier in the arena of Change Management. Change Management for Oracle E-Business Suite used to be a stand-alone product, but is now integrated into the Oracle AMP for EBS. It provides a centralized view to monitor and orchestrate changes (both functional and technical) across multiple Oracle E-Business Suite systems. Along with an overview of each product, I’ll include a link where you can watch a short demonstration from Oracle to further explain some of the functionality and actually see it in action. (Note: The videos are from an older version, but you’ll get the point)

• Patch Manager: Allows you to deploy patches across Oracle E-Business Suite instances from a single console. Patch Manager integrates with My Oracle Support and can automatically download and deploy patches across multiple (single or multi-node) Oracle E-Business Suite instances. Both Oracle E-Business Suite patches and patchsets, as well as patches created with Customization Manager, are supported.
  Patch Manager Demonstration Video 

• Customization Manager: Automates the process of packaging, releasing, deploying, and migrating customizations for a single Oracle E-Business Suite instance or multiple Oracle E-Business Suite instances. It provides capabilities to integrate with third-party source control repositories to access customizations that need to be packaged. Before packaging the updates, Customization Manager validates the customization against software coding best practices. These custom packages can then be patched to Oracle E-Business Suite instances like any other Oracle Patch either using the Oracle Applications DBA (AD) utilities or using Patch Manager.
  Customization Manager Demonstration Video 

• Setup Manager: Helps define implementation projects in terms of functional setups that need to be migrated across Oracle E-Business Suite instances. Setup Manager extends the capabilities of Oracle iSetup by providing functional configuration change management capabilities across multiple Oracle E-Business Suite instances.
  Setup Manager Demonstration Video 

3.    Overview of Application Management Pack for Oracle E-Business Suite Functionality

The Oracle Application Management Pack for Oracle E-Business Suite provides an Administration Dashboard for remotely managing Oracle E-Business Suite targets from an Enterprise Manager Cloud Control console. Jobs can be submitted to start/stop various Oracle E-Business Suite components from the Administration Dashboard. The details and statuses of these jobs can be tracked through standard Enterprise Manager Job screens.

In addition, AMP provides an Application System Performance capability that allows you to monitor the performance of specific targets, such as Concurrent Requests, User Sessions, and Workflow Items. You can also monitor HTTP server requests, JVM usage, and throughput. Also, there are a whole slew of additional APIs to monitor EBS specific items such as concurrent processing and users.

Probably one of the best features is the ability to schedule an E-Business Suite system automatically using the Smart Clone procedure: Whether it’s a single-node to single-node or multi-node to single-node environment (as of now, only scaling down is supported). There’s also support for configuring the database, even RAC environments (although the actual database cloning is done via the Oracle Enterprise Manager Database Plug-in – more to come on that subject soon). The Smart Clone procedure can also fill specific needs like data masking, data compression, as well as customized pre and post-scripts needed to finish your clone.

Finally, AMP can provide seamless integration with all the administrative activities you’re used to within Oracle Application Manager (OAM). It allows you to compare configuration information from earlier in time or from multiple other EBS systems being monitored by OEM. You can also view diagnostic and patch information for each environment.

Below is a list of E-Business Suite R12 targets that AMP can manage (If you’re still on 11.5.10.2, please, please, please get yourself upgraded before extended support runs out – Nov. 2013. A full list of available 11.5.10.2 targets can be found in the installation guide – Part No. E28715-03):

List of Available R12 Targets for Oracle Application Management Pack for E-Business Suite

Oracle E-Business Suite Oracle E-Business Suite system Oracle Applications Service Aggregate service for monitoring Oracle
E-Business Suite. Concurrent Processing Service Service for monitoring concurrent processing.
The key components for this service are:
Database Instance, Applications Listener, and
Oracle Concurrent Manager. Forms-Based Applications Service Service for monitoring Oracle Forms-based
applications. The key components for this
service are: Database Instance, Forms, OC4J,
and Oracle HTTP Server. Self-Service Applications Service Service for monitoring Self-Service
applications. The key components for this
service are: Database Instance, OC4J, and
Oracle HTTP Server. Workflow Service Service for monitoring Oracle Workflow. The
key components for this service are: Database
Instance, Applications Listener, Oracle
Concurrent Manager, Oracle Workflow Agent
Listener, Oracle Workflow Background
Engine, and Oracle Workflow Notification
Mailer. Oracle E-Business Suite Node System A system target that contains all the Oracle
E-Business Suite Infrastructure targets for a
given Oracle E-Business Suite system. Oracle E-Business Suite Infrastructure System for monitoring an Oracle E-Business
Suite node. It contains targets for components
that always run from that particular node. Oracle Concurrent Manager Target for monitoring concurrent managers. Internal Concurrent Manager Target for monitoring the Internal Concurrent
Manager. Oracle E-Business Suite Workflow System for monitoring Oracle Workflow. It
contains the Oracle Workflow Agent Listener,
Oracle Workflow Background Engine, and
Oracle Workflow Notification Mailer targets. Oracle Workflow Agent Listener Target for monitoring the Oracle Workflow
Agent listeners. Oracle Workflow Background Engine Target for monitoring the Oracle Workflow
Background Engine. Oracle Workflow Notification Mailer Target for monitoring the Oracle Workflow
Notification Mailer. Oracle Applications JVM Usage Target for monitoring Oracle Applications
JVMs. Oracle E-Business Suite Custom Objects Target for monitoring the custom objects
configuration. Oracle E-Business Suite Patch Information Target for monitoring the patch information
configuration.

So, in essence, Oracle Application Management Pack for Oracle E-Business Suite can help manage and streamline your application processes; saving you time, money, manpower, and resources. You can keep an eye on your entire environment and complete life-cycle processes all from the convenience of a single dashboard. With the integration of the Oracle Application Change Management Pack, keeping track of setups and customizations are a snap, and you can now even deploy these in a manner familiar to us all: adpatch. It does require an additional license (nothing this nice is ever free, eh!), but I’m hoping I’ve given you enough information to get you started down the path of seeing if this tool can enhance your Oracle E-Business Suite experience.

Please stay tuned for upcoming posts where I get more in-depth into installing and using Oracle Application Management Pack for E-Business Suite.

If you are interested in understanding HDFS permissions model you would google something like “hdfs adming guide” which will probably bring you to this page. If you start reading it very soon you will see this:

In this release of Hadoop the identity of a client process is just whatever the host operating system says it is. For Unix-like systems,

• The user name is the equivalent of `whoami`;
• The group list is the equivalent of `bash -c groups`.

In the future there will be other ways of establishing user identity (think Kerberos, LDAP, and others). There is no expectation that this first method is secure in protecting one user from impersonating another. This user identity mechanism combined with the permissions model allows a cooperative community to share file system resources in an organized fashion.

This is as scary as it looks: if somebody will substitute `whoami` command he will be able to fake any user when connecting to HDFS.
But wait, the documentation is for version 0.19.1, which is an old one. Indeed, if look at the code for org.apache.hadoop.security in this version you will see this:

static String getUnixUserName() throws IOException {
String[] result = executeShellCommand(
new String[]{Shell.USER_NAME_COMMAND}); <-- this is not good!
if (result.length!=1) {
throw new IOException("Expect one token as the result of " +
Shell.USER_NAME_COMMAND + ": " + toString(result));
}
return result[0];
}


Ok, let now take a look at the same code in a more recent 0.20 version:

static Subject getCurrentUser() {
return Subject.getSubject(AccessController.getContext());
}


The whole user authentication module was rewritten to use Java’s SecurityManager API which is not that easy to trick into thinking that you are someone else. So, code was changed. Now let’s check documentation for latest versions. This is latest documentation version on Apache Hadoop page. I hope you will be as surprised and amazed as I was when I found it out, but docs were not updated and are still referring to the old implementation of security module, which is very confusing and can lead many people to think Hadoop is using even more primitive authentication model than it does.

Here is a link to my updated Exadata PowerPoint presentation.

I’ve been trying to improve my Exadata talk for the Collaborate 13 conference.  I’ve done this talk at work and at the ECO conference in October but I’m not completely happy with it.  The criticism I’ve received boils down to these things:

1. The slides don’t stand on their own
2. I jump into the middle of the detail without enough introduction

So, I’ve updated the slides to have comments on many of the slides.  This will be good as a reminder of what I want to say and to make the slides more meaningful to someone who just has the PowerPoint.

I’ve also added several slides to show the execution plan of a sample query to do a better job of setting up the slides I already have that discuss how data flows through an Exadata server as compared with a normal server.  The point is that when the table is accessed blocks are read and certain rows are selected and certain columns are projected.

Lastly I added a slide on direct path read which shows how the buffer cache can be bypassed even on a normal Oracle database server in some cases.  This is also part of the introduction in that it provides background needed to understand how the Exadata Smart Scan bypasses the buffer cache.

If anyone has time to read through the slides and give me their feedback I’d be happy to hear it.  Hopefully the net result will be a presentation that is both useful to me and my company and to those attending the conference.

- Bobby

P.S.  Edited again 3/22/2013

Spring is a very active conference season for me. I might be going to half a dozen conferences in 3-4 months. That’s a lot of travel but I look forward to all them. The conference I’m probably looking forward the most this year is IOUG COLLABORATE. Maybe because it’s the largest user group conference or because I have been missing some of the previous conferences for various reasons (like volcano activity in Denmark or delayed passport at the US consulate) or because I’m more and more connected with IOUG every year (this year I’m the IOUG Director of Communities) or because COLLABORATE is the conference I’m meeting some of my very good friends that I rarely see otherwise or because it’s in Denver this year – the place I’m going to every year for RMOUG but missed this February due to unfortunate circumstances.

Now that I think about it, I think the reason I’m looking forward to it so much this year is because this year is special for IOUG – it’s our 20th anniversary and COLLABORATE 13 is where the main celebration is going to happen.

I wish I can see many of the blog readers there so if you are at COLLABORATE 13 — do find me to say hello please. If you are not yet committed to come to Denver in April, I would love to do something to swing the balance of your decision scale towards COLLABORATE. The best gift I could think of is to contribute an hours of my personal time to you as thank you for coming to Denver for this special for IOUG COLLABORATE conference. Phone or video call would be the best communication media and you can choose any topic you want to talk about that you think I can contribute to – be it around community involvement, blogging, certifications or a technology topic such us discuss your approach to database consolidation, your company’s big data strategy, Hadoop adoption plans or focus on specific issue you are facing such as a specific performance issue you need help with or disaster recovery strategy. Anything goes, we can even talk about woodworking if you’d like. Though, I have much more shallow knowledge and limited experience in woodworking compare to database technologies.

So what do you need to to claim an hour of my time? Register for IOUG COLLABORATE 13 before the end of March and use the code BOD13. I will reach our to you and arrange some time between us. You can also reach out to me by leaving a comment on this blog post below or just filling the Contact Us form referring to my attention as long as you register between now and the end of March 2013, count on that offer.

Why BOD13 priority code? Well, we have a small friendly competition between the board of directors and conference committee on who can attract the most attendees. It’s more fun than anything else and this proposal is my way to get your registrations on my side. ;)

Don’t forget to leverage group discount if you can and discount for hotel booking through IOUG — this will save you quite a bit off registration fees. The early bird discount is over but those those are still valid.

See you in Denver!

Every employee at Pythian can use one workday per year to spend on activities to help the community. We call it a Pythian Volunteer Day. It’s up to the employee to decide what to use if for and when. This year I used my volunteer day to donate blood and this post is a short story about my experience.

I started donating blood when I arrived in Australia in 2005. The office where I was working at the time was close to an Australian Red Cross blood donor center and one day, when I was coming back from lunch, I decided to go in and inquire about blood donation. I booked my first donation there, on the spot, and started donating, not as regularly as I wanted, but still every so often.

In 2009, due to changes in life and routine, I stopped donating. Quite a few times since then I thought about starting again but never did. Last month, when I saw a Facebook update by Austin Arrowsmith saying that he’d just donated blood, it was like someone shouting in my ear: “Slacker! You should do that too!” I hadn’t decided yet how to use my Pythian Volunteer Day, and this seemed like a great opportunity and a great cause. So I did it.

Human blood is a product always in high demand and the lives of many people depend on donors. Currently in Australia, only 1 in 30 people donate blood and 1 in 3 will need blood in their lifetime; about 27000 donations are needed every week in Australia alone. The donated blood can help in a large number of cases, from accidents to cancer and other serious illnesses.

You can choose to donate whole blood (plasma + red cells), plasma, or platelets. Each one of these blood sub-products has a specific use and can help many people. When I started donating blood, I learned that in Australia my red cells cannot be used since I come from an area of incidence of Chagas disease (Brazil). If I donate whole blood, the blood is later centrifuged and only the plasma and platelets are used. All the red cells are discarded.

Since my red cells are dear to me, I decided to donate plasma instead. The process of donating plasma is a bit more involved and lengthier because the red cells need to be separated from the plasma and returned to the donor. However, on the plus side, plasma can be donated much more often than whole blood because the body needs less time to recover since it doesn’t lose the red cells. Whole blood donations can be done every 12 weeks, while plasma and platelets can be donated every 2-3 weeks.

And when you finish donating you get free treats!! Milkshakes, hotdogs, muffins, biscuits, etc. All to make you feel well and come back often to donate more.

Roll up your sleeves and help. Every drop of blood counts!

Note about the picture: Since I haven’t donated blood for a long time, I needed to donate whole blood once before being allowed to donate plasma. That’s why the picture shows the whole blood unit.

Your Pythian Volunteer Day can be used towards any initiatives. Do you have any ideas? Please leave a comment, I’d love to hear about your experience.

As with any product, when a new feature is introduced, there is always increased chance of bugs. Oracle E-Business Suite is no different. In release Release 12, Oracle introduced a usability feature called Configurable Homepage. This is a new homepage layout which is drastically different from 11i. This feature brings in a new gamut of personalization features to the Oracle E-Business Suite home page. This homepage feature got a new bug with 12.1.3. This bug only affects EBS 12.1.2 and 12.1.3 customers who have OAMainMenuRESTHandler.class version 120.0.12010000.18

$ strings -a $JAVA_TOP/oracle/apps/fnd/framework/webui/rest/OAMainMenuRESTHandler.class|grep Header
[$Header: OAMainMenuRESTHandler.java 120.0.12010000.18 2010/06/11 14:37:27 akbansal noship $

This bug almost looks like a JDBC connection leak, but its not exactly a leak. When users navigate to any self service page like "timecard" entry, away from the home page, the home page locks a JDBC connection to the database. This DB connection remains locked and not shared with other JDBC sessions until the user navigates back to the home page. This issue for example increases the number of connections required on the database on a weekend where all users start logging into EBS to fill their time sheets.

The fix for this bug is outlined in the below metalink note

Connections Locked In OANAVIGATEPORTLETAM After Login To The Configurable Homepage [ID 1454792.1]

In short, the fix is to disable configurable home page or apply patch 13822452. I seriously believe this patch needs to be included in a CPU bundle, as a low lurking bug like this can drive a DBA crazy during peak hours.

One thing that has always amazed me is the way that all of our decisions, big or small, have an impact in our life. Sometimes you can’t notice the fork in the roads that those decisions have caused, but sometimes they are as clear as rain.

For me, a simple Oracle DBA, one of those life decisions was to have a talk with a great friend about your own empowerment in social media and why I should open a Twitter account. This led me to follow Yury Velikanov, who then tweeted a conversation about how to share your blog with Oracle to be able to attend 2012 Oracle Open World (OOW) as a blogger.

So I did exactly that and my proposal got accepted. This this gave me the opportunity to make my way to San Francisco. During those days at OOW, I had the opportunity to hear Pythian’s founder, Paul Vallée’s Ted Talk. After those 15 minutes and an evening talk with Greg Leger, I had the inquietude to join this company that I had just heard of on this trip.

Now this wasn’t going to be a smooth ride, it was actually going to be an arduous interview and hiring process. This is due to the fact that Pythian is made up of the top talent from around the world, and it strives for that.

Fast forward 6 months later: March 4th, 2013 was my first day at Pythian and what a week it has been. Filled since the first minutes with training in the way of life of a Pythianite, and all I can say is that it has lived up to and exceeded everything that I had imagined it was going to be.

Here are a few insights into why:

• Let me know if the first day that you got to your new job, you sat down with the CEO of your company and had a one hour talk with him, regarding its mission and the vision and route the organization is taking.
• You were already encouraged by the owner of the company to start blogging and start raising your game.
• You get to be around, talk, sit around the dinner table and trade ideas with the people considered to be the top 5% worldwide of the field you are in.
• You see that they strive to avoid the pitfall in all of their employees, the famous phrase in Stephen King’s The Shinning  “All work and no play makes Jack a dull boy”.
• You start  believing Pythian’s point of view “that the quality of our work is essential to our clients’ success and the quality of our service is essential to our success“, and with this, it is critical to know that it is a team effort , not a stand alone hero. Hence they make every effort from the beginning, to make you feel like a part of the this growing family.

So by Friday, I was already wearing the “Love Your Data” shirt, sharing doughnuts around the Ottawa office and really excited to be here to give it my best to succeed and be an intricate part of this organization.

And now all I can say after week one has come and gone, and looking back at all the sacrifices and choices I made to be here at Pythian (which I won’t go into in this post, that is for a one on one coffee if you want to hear about it), is that I’m looking forward to week two, three, four and the rest to come …

I spent a lot of time yesterday digging into performance issues on a new database only to find a simple issue where two tables were joined on a column that was a number on one table and a varchar2 on the other.  That column was a varchar2(4) on six or eight tables but one table – and it had to be the one with the most rows – had the same column defined as number(4) and as a result the queries were running with inefficient plans.  All I had to do to find this was get a plan of the sample query I was working on and look for TO_NUMBER in the predicates section but of course I spent hours looking at other things first.  So, I thought I would document how to make a quick check for this kind of type conversion.  It is “implicit” type conversion because there is no TO_NUMBER in the sql itself.  The optimizer has to add the TO_NUMBER so it can compare the character column to the number column.

Here is how I setup the example tables to mimic the situation I saw yesterday:

-- table with number column

create table ntest (div_nbr number(4));

-- table with character column

create table ctest (div_nbr varchar2(4));

Here is a sample query that joins these two tables on the columns of the same name but different types:

-- join on the column

select count(*)
from ntest,ctest
where ntest.div_nbr=ctest.div_nbr;

Here is how I get the plan which will includes the predicates section with the TO_NUMBER conversion function:

-- get plan

select * from table(dbms_xplan.display_cursor(null,null,'ALL'));

Finally, here is the plan with the predicates section with the TO_NUMBER function:

------------------------------------------------------------------
| Id  | Operation           | Name  | Rows  | Bytes | Cost (%CPU)|
------------------------------------------------------------------
|   0 | SELECT STATEMENT    |       |       |       |     5 (100)|
|   1 |  SORT AGGREGATE     |       |     1 |    17 |            |
|*  2 |   HASH JOIN         |       |     1 |    17 |     5  (20)|
|   3 |    TABLE ACCESS FULL| NTEST |     1 |    13 |     2   (0)|
|   4 |    TABLE ACCESS FULL| CTEST |     1 |     4 |     2   (0)|
------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("NTEST"."DIV_NBR"=TO_NUMBER("CTEST"."DIV_NBR"))

Step 2 of the plan is the hash join and the predicate information section shows how the two columns are joined for this step.  CTEST.DIV_NBR has to be converted to a number before it can be compared to NTEST.DIV_NBR.

So, I recommend putting a check for TO_NUMBER in the predicate information section of the plan into your toolkit of things to check when tuning a query.  This kind of thing shows up more often than you would think and it is hard to find because if you look at the query itself you just see a normal join on the DIV_NBR column.

Our resolution of this issue was to change the type of the one table to varchar2(4).  This combined with tuning optimizer_index_cost_adj resulted in dramatic improvements in performance on our test system.

Here is a zip of the script I used to show how to find TO_NUMBER in the predicate section.

- Bobby

In 11.2.0.2, Oracle introduced a change to the default size of Partition Segment.
According to Support Note "Initial Extent Size of a Partition Changed To 8MB From 64KB After Upgrade to 11.2.0.2 [ID 1295484.1]",  the INITIAL Extent of a Partition is now 8MB.  Furthermore, Deferred Segment Creation also applies to Partitions.

Thus, when you initially create a Partitioned Table, (and DEFERRED_SEGMENT_CREATION is left at the default value of TRUE), the Partitions are segmentless.  As soon as you populate a Partition, it is created as a Segment with an INITIAL of 8MB and NEXT of 1MB.

Here is a simple demo :

SQL*Plus: Release 11.2.0.2.0 Production on Sat Mar 9 16:45:49 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Enter user-name: hemant/hemant
ERROR:
ORA-28002: the password will expire within 7 days



Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> 
SQL> create tablespace PARTITION_TEST ;      

Tablespace created.

SQL> 
SQL> select extent_management, allocation_type, segment_space_management,
  2  initial_extent, next_extent, pct_increase
  3  from dba_tablespaces
  4  where tablespace_name = 'PARTITION_TEST'
  5  /

EXTENT_MAN ALLOCATIO SEGMEN INITIAL_EXTENT NEXT_EXTENT PCT_INCREASE
---------- --------- ------ -------------- ----------- ------------
LOCAL      SYSTEM    AUTO            65536

SQL> 
SQL> l
  1  create table a_partitioned_table
  2  (id   number not null,
  3  data_column varchar2(20)
  4  )
  5  partition by range (id)
  6  (
  7  partition P_ID_100 values less than ('101') tablespace PARTITION_TEST,
  8  partition P_ID_200 values less than ('201') tablespace PARTITION_TEST,
  9  partition P_MAX values less than (MAXVALUE) tablespace PARTITION_TEST
 10* )
SQL> /

Table created.

SQL> 
SQL> set pages600
SQL> set long 32767
SQL> select dbms_metadata.get_ddl('TABLE','A_PARTITIONED_TABLE','HEMANT') from dual;

DBMS_METADATA.GET_DDL('TABLE','A_PARTITIONED_TABLE','HEMANT')
--------------------------------------------------------------------------------

  CREATE TABLE "HEMANT"."A_PARTITIONED_TABLE"
   (    "ID" NUMBER NOT NULL ENABLE,
        "DATA_COLUMN" VARCHAR2(20)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  STORAGE(
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"
  PARTITION BY RANGE ("ID")
 (PARTITION "P_ID_100"  VALUES LESS THAN ('101') SEGMENT CREATION DEFERRED
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "PARTITION_TEST" ,
 PARTITION "P_ID_200"  VALUES LESS THAN ('201') SEGMENT CREATION DEFERRED
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "PARTITION_TEST" ,
 PARTITION "P_MAX"  VALUES LESS THAN (MAXVALUE) SEGMENT CREATION DEFERRED
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "PARTITION_TEST" )


SQL> 

Note how the two Partitions are created with "SEGMENT CREATION DEFERRED" and there is no Storage allocated to them yet. Let's look for the Segments and Extents.

SQL> select segment_name, partition_name, blocks, bytes/1024
  2  from dba_segments
  3  where tablespace_name = 'PARTITION_TEST';

no rows selected

SQL> select segment_name, partition_name, extent_id, file_id, block_id, blocks
  2  from dba_extents
  3  where tablespace_name = 'PARTITION_TEST';

no rows selected

SQL> select table_name, partition_name from user_tab_partitions;

TABLE_NAME                     PARTITION_NAME
------------------------------ ------------------------------
A_PARTITIONED_TABLE            P_MAX
A_PARTITIONED_TABLE            P_ID_200
A_PARTITIONED_TABLE            P_ID_100

SQL> 

So, although the three Partitions exist, no Segments and Extents yet exist. Now, let's populate one Partition -- just inserting a single row will suffice
.

SQL> insert into a_partitioned_table values (150,'HundredFifty');

1 row created.

SQL> commit;

Commit complete.

SQL> 

Now, let's look for the Segment and Extent. Note that Partition P_ID_200 would have the row with ID=150.

SQL> select segment_name, partition_name, blocks, bytes/1024
  2  from dba_segments
  3  where tablespace_name = 'PARTITION_TEST';

SEGMENT_NAME
--------------------------------------------------------------------------------
PARTITION_NAME                     BLOCKS BYTES/1024
------------------------------ ---------- ----------
A_PARTITIONED_TABLE
P_ID_200                             1024       8192


SQL> select segment_name, partition_name, extent_id, file_id, block_id, blocks
  2  from dba_extents
  3  where tablespace_name = 'PARTITION_TEST';

SEGMENT_NAME
--------------------------------------------------------------------------------
PARTITION_NAME                  EXTENT_ID    FILE_ID   BLOCK_ID     BLOCKS
------------------------------ ---------- ---------- ---------- ----------
A_PARTITIONED_TABLE
P_ID_200                                0          6        128       1024


SQL> 

So, Partition P_ID_200 now has an 8MB Segment with an Initial Extent of 8MB. Let's view the DDL for the Table.

SQL> select dbms_metadata.get_ddl('TABLE','A_PARTITIONED_TABLE','HEMANT') from dual;

DBMS_METADATA.GET_DDL('TABLE','A_PARTITIONED_TABLE','HEMANT')
--------------------------------------------------------------------------------

  CREATE TABLE "HEMANT"."A_PARTITIONED_TABLE"
   (    "ID" NUMBER NOT NULL ENABLE,
        "DATA_COLUMN" VARCHAR2(20)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  STORAGE(
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"
  PARTITION BY RANGE ("ID")
 (PARTITION "P_ID_100"  VALUES LESS THAN ('101') SEGMENT CREATION DEFERRED
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "PARTITION_TEST" ,
 PARTITION "P_ID_200"  VALUES LESS THAN ('201') SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "PARTITION_TEST" ,
 PARTITION "P_MAX"  VALUES LESS THAN (MAXVALUE) SEGMENT CREATION DEFERRED
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "PARTITION_TEST" )


SQL> 

Note how Partitions P_ID_100 and P_MAX are unchanged but Partition P_ID_200 now has SEGMENT CREATION IMMEDIATE and STORAGE (INITIAL 8M NEXT 1M) !

What if I were to split the Partitions to create new Partitions ?

SQL> alter table A_PARTITIONED_TABLE split partition P_ID_100 at ('51')
  2  into (partition P_ID_50, partition P_ID_100);

Table altered.

SQL> alter table A_PARTITIONED_TABLE split partition P_ID_200 at ('151')
  2  into (partition P_ID_150, partition P_ID_200);

Table altered.

SQL> 
SQL> select dbms_metadata.get_ddl('TABLE','A_PARTITIONED_TABLE','HEMANT') from dual;

DBMS_METADATA.GET_DDL('TABLE','A_PARTITIONED_TABLE','HEMANT')
--------------------------------------------------------------------------------

  CREATE TABLE "HEMANT"."A_PARTITIONED_TABLE"
   (    "ID" NUMBER NOT NULL ENABLE,
        "DATA_COLUMN" VARCHAR2(20)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  STORAGE(
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"
  PARTITION BY RANGE ("ID")
 (PARTITION "P_ID_50"  VALUES LESS THAN ('51') SEGMENT CREATION DEFERRED
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "PARTITION_TEST" ,
 PARTITION "P_ID_100"  VALUES LESS THAN ('101') SEGMENT CREATION DEFERRED
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "PARTITION_TEST" ,
 PARTITION "P_ID_150"  VALUES LESS THAN ('151') SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "PARTITION_TEST" ,
 PARTITION "P_ID_200"  VALUES LESS THAN ('201') SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "PARTITION_TEST" ,
 PARTITION "P_MAX"  VALUES LESS THAN (MAXVALUE) SEGMENT CREATION DEFERRED
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "PARTITION_TEST" )


SQL> 
SQL> select segment_name, partition_name, blocks, bytes/1024
  2  from dba_segments
  3  where tablespace_name = 'PARTITION_TEST';

SEGMENT_NAME
--------------------------------------------------------------------------------
PARTITION_NAME                     BLOCKS BYTES/1024
------------------------------ ---------- ----------
A_PARTITIONED_TABLE
P_ID_200                             1024       8192

A_PARTITIONED_TABLE
P_ID_150                             1024       8192


SQL> select segment_name, partition_name, extent_id, file_id, block_id, blocks
  2  from dba_extents
  3  where tablespace_name = 'PARTITION_TEST';

SEGMENT_NAME
--------------------------------------------------------------------------------
PARTITION_NAME                  EXTENT_ID    FILE_ID   BLOCK_ID     BLOCKS
------------------------------ ---------- ---------- ---------- ----------
A_PARTITIONED_TABLE
P_ID_150                                0          6        128       1024

A_PARTITIONED_TABLE
P_ID_200                                0          6       1152       1024


SQL> 

So, only the two Partitions that have been created out of P_ID_200 have been materialized as Segments and Extents while the two Partitions split out of P_ID_100 are still Segment-less (without Storage allocated).

So remember : the default behaviour of Segment creation for Partitions has been changed since 11.2.0.2
Having said that, the note that I mentinoed at the beginning does document how this default behaviour can be overridden.
You need this information when you are creating a table with, say, 125 partitions but have only 1 to a few thousand rows in each partition and have to explain why the table is taking up 1GB of space.
Also note that once a Partition has had an 8MB extent allocated to it, any new Partitions that you create out of splitting it will also hae 8MB extents allocated --even if they are empty !

.
.
.

There are so many great blogs out there regarding Oracle, SQL Server, MySQL and various other database technologies. Keeping track of all of them is no less than a Herculean task. This Log Buffer Edition is an effort to pick few of those great blog posts to appreciate the oceans of database blogs out there.

Oracle:

Nial Litchfield recently reviewed a table with no fewer than 23 indexes on it.

Toon Koppelaars asks. And what about table constraints?

Can Oracle Database Release 2 (11.2.0.3) Properly Count Cores? No. Does It Matter All That Much? Not Really..Kevin Closson writes.

Jonathan Lewis has an interesting blog post about duplicate indexes.

How can we make Oracle Database 12cR2 the best release ever? Tim opines.

SQL Server:

Bob Horkay on pivot job history acros multiple SQL Server instances.

John Sansom shares as how to restart the SQL Server Agent Service using PowerShell.

A delightful read from Paul White on execution plan analysis.

Brent Ozar says that you don’t have a Big Data problem.

Data Explorer–Where Does The Real Work Get Done? Chris answers.

MySQL:

A good news to all those MariaDB users that have been waiting to get Galera synchronous multi-master replication to their favorite MySQL flavor.

MySQL modularity, are we there yet? Stewart Smith wonders.

Kolbe blogs that MySQL 5.6 includes a couple new options that allow you to store replication master and relay information in tables.

Valeriy Kravchuk lists down 17 famous MySQL bug reporters.

Baron is discussing how to avoid surprising users and someone pointed out that what seems intuitive and rational to one person is often complete insanity for others.

OK.  This is really cool.  I found an archive of my old geocities blog: archive

I thought geocities was gone forever but it isn’t.  Quite cool.

- Bobby

Let’s face it, upgrading your database to the latest and greatest release is not the top item on your VP of IT’s agenda. Even with all of the interesting features, improved stability, and enhanced security a new release can bring, a database upgrade is often perceived as a “have to do” project that can be put on hold and dealt with when necessary.

Notwithstanding all the new shiny features the latest release can provide, today I am going to put in dollars and cents the reasons why upgrading makes economical sense.

Take for example a typical modern production Oracle database:

• Database size 1TB
• Intel x86 server with 2 hexacore processors

Also assume you license the server as follows (list prices quoted):

• Oracle Database Enterprise Edition - $47,000
• Oracle Partitioning - $11,500
• Oracle Diagnostics Pack - $5,000
• Oracle Tuning Pack - $5,000

Standard annual fees for maintenance and support = $69,000 (sum of the above) * 12 (number of cores) * 0.5 (licensing multiplier for x86 CPUs) * 0.22 (22% of list price) = $91,080. That’s what you pay to have Oracle for product support and updates.

Now let’s assume that regular support has ended for the version you are currently on and you now need extended support. Extended support requires additional fees on top standard maintenance and support.

In year 1, you will have to pay a  10% premium, while in year 2 and 3, a 20% premium on top of the standard annual support and maintenance fees. Total added cost for extended support for 3 years = $91,080 * 0.1 + $91,080 * 0.2 + $91,080 * 0.2 = $45,540.

Compare that with the cost of upgrading, which depending on the complexity of the project, will range from $10,000 – $15000. With IT budgets under pressure more than ever, it quickly becomes clear that there is no economical reason to stay on the current version of the database.

We have analyzed a large sample of databases under Pythian management and discovered some interesting numbers we want to share with you.

Where do you fit in this snapshot? Are you on the latest version of your database? Is your database about to require added support?  Are you overpaying for extended support? If so, maybe it’s time to seriously consider that upgrade project you’ve been delaying.

Consider you have been asked to implement an Oracle security checklist on a 11g Release 2 production database and, as part of this task, you follow the principle of least privilege.

You revoke all non-required privileges from PUBLIC , revoke any extra ANY privileges from non-required users, and, in brief, you allow all users to perform only what they really require and not more, so you revoke all extra privileges.

One of the system privileges that is against the least privilege principal is the UNLIMITED TABLESPACE privilege. You can manage each individual user so they have unlimited quota on the few tablespaces they require, but having UNLIMITED TABLESPACE causes user to have unlimited quota on ALL tablespaces of the database.

Consider you have a user KAMRAN who needs access to USERS tablespace only, but UNLIMITED TABLESPACE has already been granted to him; no quota on USERS has been granted to him individually, only UNLIMITED TABLESPACE :

SQL> select * from dba_sys_privs where grantee='KAMRAN';

GRANTEE                        PRIVILEGE                        ADM
------------------------------ -------------------------------- ---
KAMRAN                         UNLIMITED TABLESPACE             NO

SQL> select * from dba_ts_quotas where username='KAMRAN';

no rows selected

You plan to revoke UNLIMITED TABLESPACE from this user and instead grant him UNLIMITED quota on USERS tablespace , so you plan to grant required quota first and then revoke UNLIMITED TABLESPACE as follows:

SQL> alter user KAMRAN quota unlimited on USERS;

User altered.

SQL> select * from dba_ts_quotas where username='KAMRAN';

TABLESPACE_NAME      USERNAME    BYTES  MAX_BYTES   BLOCKS MAX_BLOCKS DRO
-------------------- ---------- ------ ---------- -------- ---------- ---
USERS                KAMRAN          0         -1        0         -1 NO

Now that KAMRAN has UNLIMITED quota on USERS tablespace and you have confirmed it with the above query, you THINK you can safely revoke UNLIMITED TABLESPACE and you issue the following command:

SQL> revoke UNLIMITED TABLESPACE from KAMRAN;

Revoke succeeded.

And you think everything is fine

BUT …
This won’t work and causes the following error as soon as the user tries to create a new segment or extend an existing one:

ORA-01536: space quota exceeded for tablespace

You have granted required quota first and then revoked UNLIMITED TABLESPACE so why doesn’t it work ?

This is the reason:

When the UNLIMITED TABLESPACE privilege is revoked from a user, it ALSO revokes all granted quotas on any individual tablespace from the user. In other words, after revoking this privilege from a user, the user won’t have any quota on any tablespace at all:

SQL> revoke UNLIMITED TABLESPACE from KAMRAN;

Revoke succeeded.

SQL> select * from dba_ts_quotas where username='KAMRAN';

no rows selected

This is an unexpected behavior for this privilege, and if you are not aware of it, it can cause you trouble if you revoke it from an application user in the above order in your production database.
You will need to revoke UNLIMITED TABLESPACE first and then grant required quota in each tablespace, even though the user will experience a lack of quota for a short period of time between the two commands.