Blog Aggregator - Amardeep Sidhu

The first session I chaired was Martin Widlake talking on "Avoiding Avoidable Disasters and Surviving Survivable Ones" He took as his sermon the text What could possibly go wrong? Well, for starters, there could be a problem with the USB drive so that the wireless slide controller doesn’t work. Martin disappeared to try and find a replacement, which left me with the possibility of having to vamp for an hour if he didn’t come back. Fortunately he did return, albeit without a replacement gadget, so his presentation was more static than useful usual [see Comments - Ed]. Fortunately the ideas were lively. I was particularly taken with the Formica Table. This would be a forum where “not bad” DBAs answered questions which fitted 95% of all scenarios; sort of an Oak Table For The Rest Of Us.

His main theme was that projects tend to fail because decision-makers aren’t realistic in their risk assessments. So projects are planned on the basis of everything going right. Staff are told to work extra hours and weekends without any recognition that tired people make mistakes, and fixing mistakes costs time. Or additional people are recruited which just increases the number of communication channels, to the point that the overhead of keeping everybody in the loop becomes excessive.

Martin advises us to install disaster tolerant hardware, because it gives us more interesting problems to solve. Of course we shouldn’t really switch to clustering technology just for the sake of it. But if we think we are eventually going to need RAC we should move to it now. That way we’ll have learned to live with it and smoothed all the wrinkles before the technology has become critical to the system.

There were some entertaining war stories. One concerned a failed powerpack in a cluster. A sysadmin noticed and helpfully substituted the powerpack from another machine. When he connected the first node without a hitch but the second node promptly fried that power pack too. So he called an electrician. In order to get at the problem the electrician had to climb a ladder. The ladder slipped and the flailing electrician grabbed at the nearest thing to hand, the power rail, which collapsed and took out the leccy for the entire server room. We can’t plan for such things, we can merely acknowledge that such stuff will happen.

The solutions are the obvious ones: realistic planning, smaller units of delivery, delivering something on a regular basis. One neat idea for solving the communication problem came from somebody who works for Unilever. They use Jabber to post small messages to a central message board, so everybody can see what everybody else is doing in real time. At last a business use for Twitter.

An Industrial Engineer’s Approach to DBMS

Problems with the AV set-up seem to have become a theme in the sessions I’ve chaired. Cisco’s Robyn Sands turned up with a Mac but without the requisite dongle which would allow her to plug it into the hall’s projector. So she ended up having to drive her presentation from a PDF on a loaned Windows machine. She handled the transition to an unfamiliar OS but it was an unlucky start to her session.

Industrial engineering concerns itself with “design, improvement and installation of integrated systems of people, material, equipment and energy”, which is a pretty good definition of the role of a DBA too. Industrial engineers focus on efficiency, value and methodology; they are the accountants of the engineering world. The application of IE methods to DBMS has the aim of producing a consistent application. For instance, every database in an organisation should be consistent: same installed components, same init parameters, same file locations, with transportable tablespaces and reusable code/scripts/methods. This results in safer and more flexible systems. The installation process is a flowchart; in effect the database instance is deployed as an appliance.

Another IE practice is value analysis. This says that a cost reduction adds value to a system just as much as adding a new feature. Which brings us to Statistical Process Control . Every process displays variability: there is controlled variability and uncontrolled variability. We need to use logging to track the elapsed time of our processes, and measure the degree of variability. Benchmarking is crucial because we need to define the normal system before we can spot abnormality. Abnormal variability falls into three categories:

• special case;
• trend;
• excess variation.

Once we have explained the special cases we can file and forget them. Trends and excess variation both have to be investigated and fixed. The aim is achieving a consistent level of service rather than extreme performance. If you can accurately predict the Mean Response Time then you understand your system well.

Robyn described a project she had worked on which focused on systems reliability. The goal was to reduce or eliminate recurring issues with a view to reducing outages - and henceout-of-hours calls - to increase the amount of uninterrupted sleep for DBAs and developers. A worthy end. The problem is simply that a DBA or developer woken at three in the morning will apply the quickest possible fix to resolve the outage but there was no budget to fix the underlying problem when they got back into the office. Usually old code is the culprit. There’s lots of kruft and multiple dependencies, which make the programs brittle. The project worked to identify the underlying causes of outages and fix them. The metric they used to monitor the project’s success was the number of out-of-hours calls: over the course of the year these fell by orders of magnitude.

Robyn finished her presentation with some maxims:

• Rules of thumbs are not heuristics.
• Discipline and consistency lead to agility.
• Reduce variation to improve performance.
• No general model applies to all systems.
• Understand what the business wants.
• Model and benchmark your system accurately.
• Understand the capabilities of you system.

The licensing round table

This event was billed as “Oracle’s Right To Reply”. Unfortunately there wasn’t an Oracle representative present and even when one was rustled up they could only take away our observations to pass them on. This confirmed an observation from Rocela’s Jason Pepper that Oracle employees are expressly forbidden from discussing licencing unless they are an account manager. This can lead to situations where advice from Support or Consulting leads to customers having exposure to increased licences.

The issues aired were the usual suspects. Why isn’t partitioning part of the Enterprise Edition licence? Why aren’t the management packs available for Standard Edition? Why isn’t there a single, easily locatable document explaining pricing policy? How can we have sensible negotiations when the account managers keep changing? There was one area which was new to me. There is a recent initiative, the Customer Optimization Team, whose task is to rationalise a customer’s licences. Somebody asked the pertinent question: what is the team’s motivation - to get the best value for customer or to sell additional licences for all the things which the customer is using without adequate licences? Perhaps we’ll get answers. I shall watch my inbox with bated breath.

Index compression

This was a bonus session I hadn’t been meaning to attend but it was worthwhile. Philip Marshall from Joraph presented his research into the effects of compression, because these are not well documented in the manual. Compression works by storing the distinct values of the compressed columns and then linking to each instance of that value, which obviously imposes a small overhead per row. So the space saved on storing the compressed column is dependent on both the length of the column and the number of instances of those values. The overhead means that compressing an index with small columns which have high variability could result in very small savings or even a larger index.

Also we need to remember that the apparent space saving could be due to the act of rebuilding the index rather than compressing it. This matters because (as we all should know) the space savings from rebuilding can be quickly lost once the index is subjected to DML. Furthermore there is a cost associated with uncompressing an index when we query its table. This is can be quite expensive. The good news is that the CPU cost of uncompressing the columns is incurred by the index read only: so it is usually only a small slice of the whole query. Still it’s a cost we should avoid paying if we aren’t actually getting a compensating saving on space. Also compression does not result in more index blocks being cached. More blocks will be read in a single sweep, but the unused blocks will be quickly discarded.

I thought this presentation was a classic example of the Formica Table approach. A focused - dare I say compressed? - look at a feature which probably most of us have contemplated using at some time without really understanding the implications. It was the kind of presentation which might just as easily have been a white paper (I will certainly be downloading the presentation to get the two matrices Philip included) but there is already so much to read on the net that a paper would have just got lost.

11g for DBAs

This was the first of a tie-less Tom Kyte’s two selections from the 11g chocolate box. I think the 11g features have been sufficiently rehearsed over the last few months that I have decided to skip the details. So here is just a list of the new features Tom thinks DBAs most need to pay the attention to.

• Encrypted tablespaces
• Active Dataguard
• Real Application Testing
• Enhancements to Data Pump (EXP and IMP are now deprecated)
• Virtual columns
• Enhancements to partitioning
• Finer grained dependency tracking
• the xml version of the alert log
• invisible indexes

Of that list encrypted tablespaces, Active Dataguard, Real Application Testing and partitioning are (or require) chargeable extras. In the earlier round table Ronan kept reminding us that we must distinguish between licensing and pricing: we have to accept that Oracle has 47% of the database market so lots of CTOs and CFOs must think it offers value for money. Which is a fair point, but it is hard to see a compelling reason why a Standard Edition DBA would choose to upgrade. Actually the enhancements for developers are very attractive, but alas we don’t carry as much sway.

One notable thing in Tom’s presentation occurred when he was demonstrating the new INTERVAL operation for partitioning. The new partitions had a year of 2020, but the dates were supposed to be from this year. It turns out Tom had been tinkering with his demo code and had removed an explicit date conversation without checking the default date format. It’s nice to know even the demi-gods fall prone to such things

Day 1 for me continued with a great session by Riyaj Shamsudeen on analytical SQL. This was exactly my sort of presentation - packed with examples, demonstrations and proof of what he was saying. In particular Riyaj did a great job on comparing the performance of old style queries that one could use to answer analytical questions and the newer syntax. Of particular benefit is the fact that in general if one needs to add another analytical metric then there is pretty much no further io cost to be incurred.

Next up was a session chairing duty for Phil Marshal’s presentation on Index Compression, This was a nice straightforward and clear introduction to the subject, again evidence based. Two particular highlights for me were, the fact that a compressed index can end up larger than an uncompressed index - especially if the cardinality of the base table is high - and that most tests that demonstrate the space benefits of index compression overstate the case because they compare an index before being compressed with the same index uncompressed - however since this sort of test builds a compressed index by a rebuild operation the correct comparitor would be with a freshly rebuilt index (no compression).

I ended my conference day with Tom Kyte again talking - and this time demoing - 11g new features. First up Tom talked about tablespace level encryption in 11g, this feature overcomes two disadvantages of column encryption in earlier versions. These were the fact that you can’t do an index range scan against an encrypted column (since adjacent data values when encrypted don’t get stored next to each other so there is no range to scan). Perhaps rather more importantly referential integrity doesn’t work because the encrypted value in a pk will not the the same as that same value encrypted in the fk column. In addition Tom neatly demonstrated the possibility of data theft - say from backup media - by the simple expedient of running strings against a datafile - complex hacking at its best!  Tom also covered virtual columns - giving you for example the ability to create a foreign key on a function, improvements to datapump import/export including two neat additions that make scripting and using a lot easier - the ability to reuse dumpfiles and the ability to compress the dump file - especially important if you are in the habit of transferring the dump either directly or indirectly - via an intermediate file - across a network link. Finally we had that rare opportunity the experience of Tom looking entirely stumped. The last feature Tom was demonstrating was the automatic creation of interval partitions on the arrival of a new row. Tom’s code looked like this

create table t(ts timestamp,audit_text varchar2(10));

insert into t values ('02-Dec-2007','xx');

Unfortunately for Tom the partition created was for the year 2020 not the expected result. Fortunately, as I make this class of mistake frequently enough to recognise it was easy enough to show that Tom was relying on a default format for the timestamp value rather than using a to_timestamp function. Failing to do this meant that Oracle was interpreting the timestamp supplied as 7am on the morning of December 2nd 2020.  I have to say that Tom handled this surprise fantastically well - both generating laughs and promising to post the full explanation - or the bug number - on his blog.

 The evening was mostly spent at the Blogger’s dinner, being introduced to Russian beer by Alex Gorbachev who impressively managed to blog about the event at 2am the morning before a presentation.

You’ll want to take some advice from Joel Spolsky of Joel on Software who recently completed his world tour. He had some great advice and lessons learned from his trip. I especially like Joel’s details about how to choose a room, music to play before starting the presentation, and how to conduct a good software […]

Oracle introduced a new feature, adaptive cursor sharing, in 11g, to improve the plans that are selected for queries containing bind variables. This feature can result in more cursors for the same query containing bind variables. We’ll explain why in this article. Before we get into the details, let’s review a little history.

Oracle introduced the bind peeking feature in Oracle 9i. With bind peeking, the Optimizer peeks at the values of user-defined bind variables on the first invocation of a cursor. This allows the optimizer to determine the selectivity of any WHERE clause condition as if literals have been used instead of bind variables, thus improving the quality of the execution plan generated for statements using bind variables.

However, there was a problem with this approach, when the column used in the WHERE clause with the bind contained a data skew. If there is data skew in the column, it is likely that a histogram has been created on this column during statistics gathering. When the optimizer peeks at the value of the user-defined bind variable and chooses a plan, it is not guaranteed that this plan will be good for all possible values for the bind variable. In other words, the plan is optimized for the peeked value of the bind variable, but not for all possible values.

In 11g, the optimizer has been enhanced to allow multiple execution plans to be used for a single statement that uses bind variables. This ensures that the best execution plan will be used depending on the bind value. Let’s look at an example to see exactly how this works.

Assume I have simple table emp which has 100,000 rows and has one index called emp_i1 on deptno column.

SQL> desc emp

Name                   Null?    Type———————- ——– ———————————-ENAME                           VARCHAR2(20)EMPNO                           NUMBERPHONE                           VARCHAR2(20)DEPTNO                          NUMBER

There is a data skew in the deptno column, so when I gathered statistics on the emp table, Oracle automatically created a histogram on the deptno column.

SQL> select table_name, column_name, histogram from user_tab_cols;

TABLE_NAME         COLUMN_NAME        HISTOGRAM—————— —————— —————EMP                DEPTNO             HEIGHT BALANCEDEMP                EMPNO              NONEEMP                ENAME              NONEEMP                PHONE              NONE

Now I will execute a simple select on my emp table, which has a single WHERE
clause predicate on the deptno column. The predicate contains a bind variable. We will begin by using the value 9 for this bind variable. The value 9 occurs 10 times in the table, i.e. in 0.0001% of the rows.

SQL> exec :deptno := 9

SQL> select /*ACS_1*/ count(*), max(empno)2  from emp3  where deptno = :deptno;

COUNT(*) MAX(EMPNO)———- ———-    10         99

Given how selective the value 9 is, we should expect to get an index range scan for this query. Lets check the execution plan.

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT————————————————————————-SQL_ID  272gr4hapc9w1, child number 0————————————————————————select /*ACS_1*/ count(*), max(empno) from emp where deptno = :deptno

Plan hash value: 3184478295————————————————————————| Id | Operation                    | Name  | Rows | Bytes |Cost (%CPU)|————————————————————————|  0 | SELECT STATEMENT             |       |      |       |    2 (100)||  1 |  SORT AGGREGATE              |       |     1|    16 |           ||  2 |   TABLE ACCESS BY INDEX ROWID| EMP   |     1|    16 |    2   (0)||  3 |    INDEX RANGE SCAN          | EMP_I1|     1|       |    1   (0)|————————————————————————

So we got the index range scan that we expected. Now let’s look at the execution statistics for this statement

SQL> select child_number, executions, buffer_gets,2    is_bind_sensitive, is_bind_aware3  from v$sql4  where sql_text like ’select /*ACS_1%’;

CHILD_NUMBER EXECUTIONS BUFFER_GETS IS_BIND_SENSITIVE IS_BIND_AWARE———— ———- ———– —————– ————-          0          1          53 Y                 N

You can see we have one child cursor that has been executed once and has a small number of buffer gets. We also see that the cursor has been marked bind sensitive. A cursor is marked bind sensitive if the optimizer believes the optimal plan may depend on the value of the bind variable. When a cursor is marked bind sensitive, Oracle monitors the behavior of the cursor using different bind values, to determine if a different plan for different bind values is called for. This cursor was marked bind sensitive because the histogram on the deptno column was used to compute the selectivity of the predicate “where deptno = :deptno”. Since the presence of the histogram indicates that the column is skewed, different values of the bind variable may call for different plans.

Now let’s change the value of the bind variable to 10, which is the most popular value for the deptno column. It occurs 99900 times in the table, i.e in 99.9% of the rows.

SQL>  exec :deptno := 10

SQL> select /*ACS_1*/ count(*), max(empno)2  from emp3  where deptno = :deptno;

COUNT(*) MAX(EMPNO)———- ———- 99900     100000

We expect to get the same plan as before for this execution because Oracle initially assumes it can be shared. Let’s check:

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT————————————————————————SQL_ID  272gr4hapc9w1, child number 0————————————————————————select /*ACS_1*/ count(*), max(empno) from emp where deptno = :deptno

Plan hash value: 3184478295————————————————————————| Id | Operation                    | Name  | Rows | Bytes |Cost (%CPU)|————————————————————————|  0 | SELECT STATEMENT             |       |      |       |    2 (100)||  1 |  SORT AGGREGATE              |       |     1|    16 |           ||  2 |   TABLE ACCESS BY INDEX ROWID| EMP   |     1|    16 |    2   (0)||  3 |    INDEX RANGE SCAN          | EMP_I1|     1|       |    1   (0)|————————————————————————

The plan is still an index range scan as before, but if we look at the execution statistics, we should see two executions and a big jump in the number of buffer gets from what we saw before.

SQL> select child_number, executions, buffer_gets, 2    is_bind_sensitive, is_bind_aware 3  from v$sql 4  where sql_text like ’select /*ACS_1%’;

CHILD_NUMBER EXECUTIONS BUFFER_GETS IS_BIND_SENSITIVE IS_BIND_AWARE———— ———- ———– —————– ————-          0          2        1007 Y                 N

You should also note that the cursor is still only marked bind sensitive and not bind aware at this point. So let’s re-execute the statement using the same popular value, 10.

SQL> exec :deptno := 10

SQL> select /*ACS_1*/ count(*), max(empno)2  from emp3  where deptno = :deptno;

COUNT(*)  MAX(EMPNO) ———- ———–99900      100000

Behind the scenes during the first two executions, Oracle was monitoring the behavior of the queries, and determined that the different bind values caused the data volumes manipulated by the query to be significantly different. Based on this difference, Oracle “adapts” its behavior so that the same plan is not always shared for this query. Hence a new plan is generated based on the current bind value, 10.

Let’s check what the new plan is.

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT——————————————————————–SQL_ID  272gr4hapc9w1, child number 1——————————————————————–select /*ACS_1*/ count(*), max(empno) from emp where deptno = :deptno

Plan hash value: 2083865914——————————————————————–                                                                       | Id  | Operation            | Name  | Rows   | Bytes | Cost (%CPU)|——————————————————————–|   0 | SELECT STATEMENT     |       |        |       |   240 (100)||   1 |  SORT AGGREGATE      |       |     1  |   16  |            ||*  2 |   TABLE ACCESS FULL  | EMP   | 95000  | 1484K |   240   (1)|——————————————————————–                                                                        

Given how unselective the value 10 is in the table, it’s not surprising that the new plan is a full table scan. Now if we display the execution statistics we should see an additional child cursor (#1) has been created. Cursor #1 should show a number of buffers gets lower than cursor #0 and it is marked both bind sensitive and bind aware. A bind aware cursor may use different plans for different bind values, depending on how selective the predicates containing the bind variable are.

Looking at the execution statistics:

SQL> select child_number, executions, buffer_gets, 2    is_bind_sensitive, is_bind_aware 3  from v$sql 4  where sql_text like ’select /*ACS_1%’;

CHILD_NUMBER EXECUTIONS BUFFER_GETS IS_BIND_SENSITIVE IS_BIND_AWARE———— ———- ———– —————– ————-          0          2        1007 Y                 N          1          1         821 Y                 Y  

we see that there is a new cursor, which represents the plan which uses a table scan. But if we execute the query again with a more selective bind value, we should use the index plan:

SQL> exec :deptno := 9

SQL> select /*ACS_1*/ count(*), max(empno)2  from emp3  where deptno = :deptno;

COUNT(*) MAX(EMPNO)———- ———-    10         99

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT————————————————————————SQL_ID  272gr4hapc9w1, child number 2————————————————————————select /*ACS_1*/ count(*), max(empno) from emp where deptno = :deptno

Plan hash value: 3184478295————————————————————————| Id | Operation                    | Name  | Rows | Bytes |Cost (%CPU)|————————————————————————|  0 | SELECT STATEMENT             |       |      |       |    2 (100)||  1 |  SORT AGGREGATE              |       |     1|    16 |           ||  2 |   TABLE ACCESS BY INDEX ROWID| EMP   |     1|    16 |    2   (0)||  3 |    INDEX RANGE SCAN          | EMP_I1|     1|       |    1   (0)|————————————————————————

The proper plan was chosen, based on the selectivity produced by the current bind value.

There is one last interesting thing to note about this. If we look at the execution statistics again, there are three cursors now:

SQL> select child_number, executions, buffer_gets, 2    is_bind_sensitive, is_bind_aware, is_shareable 3  from v$sql 4  where sql_text like ’select /*ACS_1%’;

CHILD_NUMBER EXECUTIONS BUFFER_GETS IS_B_SENS IS_B_AWAR IS_SHAR———— ———- ———– ——— ——— ———-          0          2         957 Y         N         N          1          1         765 Y         Y         Y          2          2           6 Y         Y         Y

The original cursor was discarded when the cursor switched to bind aware mode. This is a one-time overhead. Note that the cursor is marked as not shareable (is_shareable is “N”), which means that this cursor will be among the first to be aged out of the cursor cache, and that it will no longer be used. In other words, it is just waiting to be garbage collected.

There is one other reason that you may notice additional cursors for such a query in 11g. When a new bind value is used, the optimizer tries to find a cursor that it thinks will be a good fit, based on similarity in the bind value’s selectivity. If it cannot find such a cursor, it will create a new one (like above, when one (#1) was created for unselective “10″ and one (#2) was created for highly-selective “9″). If the plan for the new cursor is the same as one of the existing cursors, the two cursors will be merged, to save space in the cursor cache. This will result in one being left behind that is in a not shareable state. This cursor will be aged out first if there is crowding in the cursor cache, and will not be used for future executions.

Q & A
Instead of answering the questions in your comments one by one, I am going to summarize the questions and provide my answers here.

Q: Is this behavior managed by 11g optimizer automatically and we don’t need cursor_sharing anymore?
A: We have not changed the behavior of the cursor_sharing parameter yet, for backwards compatibility purposes. So if you set it to similar, adaptive cursor sharing will only kick in for queries where the literals are replace with binds. We hope that in the future, this feature will persuade people to set cursor_sharing to force.

Q: Would it have any impact like holding library cache latches for longer time to search for appropriate child cursor.
A: Any additional overhead in matching a cursor is always a concern, and we strive to minimize the impact. There is of course some increase in the code path to match a bind-aware cursor, since it requires more intelligent checks. This feature should not, however, impact cursors which are not yet marked bind-aware.

Q: What triggers a cursor to be marked “bind sensitive”?
A: Our goal is to consider many types of predicates where the selectivity can change when the bind value changes. In this first version of the feature, we only handle equality predicates where a histogram exists on the column and range predicates (with or without histogram). We do not currently consider LIKE predicates, but it is on the top of our list for future work.

Q: Also it sounds like the optimizer is using the number of rows returned to decided that it’s time for a new plan…
A: I am not going to go into the details of the “special sauce” for how we decide to mark a cursor bind-aware. The number of rows processed is one input.

Q: Are you planning a hint to mark statements as bind-aware ?
A: Yes, we plan to add this in the future. This will allow users to bypass the startup cost of automatically determining that a query is a good candidate for bind-aware cursor sharing.

Many performance assessments start with the unpleasantness of having to guess a number of configuration items for lack of available information and/or knowledge. Whilst the server which hosts the database usually quickly delivers its little secrets, the storage configuration information is frequently more difficult to obtain from a remote administrator who has to manage […]