One of the themes I'll keep coming back to is how things are often not how you have assumed they are, and how there are always opportunities to learn something new about Oracle. I am quite familiar with histograms in Oracle from reading the manuals and white papers, but have always left it to Oracle to decide what columns histograms were needed. This is mainly due to the databases I have worked on not being large or complicated enough to need further analysis so far. But knowing the day would come when I would have to do something I thought I would review what I knew, and in doing so learnt a few new things - or rather realised that some of my assumptions had been incorrect.
Rather than re-read the manual I decided to go to a better source of knowledge rather than just raw information - Jonathan Lewis's Cost Based Oracle Fundamentals. I already knew that Oracle had 2 types of histogram - Frequency and Height Balanced. And I knew that Frequency stored a row count per individual data value, while a Height Balanced stores a row count per range of values (strictly the row count is the same per range, and it is the data range size that varies). And I knew that the Optimizer in Oracle used this extra row count data in the histogram to calculate better estimates for row counts that would match constraints in queries. In reading the chapter on Histograms and working through the examples and doing my own tests I realised that some of my assumptions had been wrong about how Oracle uses these histograms.
Assumption 1 - Oracle uses all the entries in a histogram to calculate its row count estimate
I knew this to be true for a Frequency Histogram, and had assumed something similar for a Height Balanced one. But I was wrong. In a height balanced histogram although Oracle may have up to 254 buckets, it only uses the ones that contain the same end point values to identify "popular values" - those that occur more frequently than the width of a single bucket, and so span more than one bucket in size. For all other values it ignores the histogram completely and uses the column level Density statistic in the same manner as if there were no histogram.
In other words there is a threshold, being the number of rows covered by a single bucket. For data values that occur more often than this and are also recorded in two or more buckets in a height balanced histogram, the Optimizer uses the number of buckets to calculate the estimated row count, along with the number of rows per bucket. For all other values Oracle assumes a uniform distribution and uses the column level Density statistic. This will not be the same as one over the Number of Distinct Values (NDV), but is calculated differently to remove the effect of the rows for those popular values i.e. the Density is lowered to the average of the "unpopular" values.
Assumption 2 - A Frequency Histogram has an entry for all data values occurring in the table
This is true if either the table is relatively small, or you force Oracle to read all the data rows when gathering statistics. If you leave Oracle to use its default sampling method and you have a large table then some values may not be sampled, and they will be missing from the Frequency Histogram produced.
What Oracle does is to calculate and store a Density value for the column that is half that of the least frequent occurring value in the histogram. Values that appear in query constraints that do not appear in the Frequency Histogram are therefore assumed to occur at half the row count of the value with the smallest row count in the histogram. So again, the Optimizer may end up not using a histogram that exists and instead use the Density statistic of the column when executing a particular query.
None of this is new, and I was able to double check this via various other sources. It was just another thing to add to the list of assumptions I've made in the past that turn out not to be true. Even with histograms in place there is a reasonable chance that the Optimizer will actually not be using the histogram itself but instead the Density statistic value of the column. And also changing the value of the Density for the column can have an impact on queries, even when there is a histogram on that column.
Tuesday, 28 June 2011
Thursday, 16 June 2011
Hinting - Lessons Learnt
Following on from my previous post about some SQL that needed to be tuned, I thought I'd summarise some additional important lessons I've learnt about using hints. I already knew quite a lot about hints in Oracle from reading different sources, and to avoid them as much as possible. In spite of this I have still learnt a few new things about using hints in practise. I know that others have said most of this before, but it bears repeating because it really does affect whether any hints do work or not.
- Name all your query blocks using the qb_name hint. This clarifies things both in terms of how Oracle itself reports back your execution plan, and in terms of how you specify your hints.
- Qualify all object references with query block names e.g. t1@mainq. Again, this is how Oracle reports object references, and it is precise in terms of where the hint should be applied.
- Check your hints are being used with the 'outline' option to dbms_xplan.display_cursor. If the hints being listed are the same ones that you used, then all is well. If not, then it is likely that some of your hints are actually being ignored.
- Test the hints individually. This follows on from the previous point about proving the hint is recognised and honoured by Oracle. It is possible that a similar hint is being automatically produced by the Optimizer as a result of another hint you are using. In my case it looks like a USE_NL hint was being ignored, but a Nested Loop was produced anyway because of an INDEX hint.
- Include all other relevant hints, such as LEADING and UNNEST. Previously I would have assumed that these would be produced by the Optimzer automatically but Jonathan Lewis includes them in his hint sets so they must be relevant.
Thursday, 9 June 2011
Customers who won't wait to be helped
[I'm back. I haven't posted for a long while due to a combination of being busy and somewhat losing focus on the blogging side, but I hope to post more frequently in the future. I was going to start with a post on histograms in Oracle but ...]
I work for a software house, whose heavyweight application runs on top of Oracle. Earlier this week I was dealing with a customer who had poor performance on just one SQL query after having updated their statistics. I spent a day analysing the SQL and the data - what was it trying to do and why - and another day trying different solutions to make it use a different execution plan and run faster for them. I then sent the results to the support people dealing with this customer so that the solution could be implemented. Imagine my surprise then when I see today that Jonathan Lewis has a blog post about the same SQL statement, and makes reference to a post on one of the Oracle Technical Forums.
I'm surprised that a customer would post SQL from a third party application on the web like that, and that they would somehow expect a better answer to come back than from the software vendor themselves. I was even more surprised because they were asking for a set of hints to change the execution plan, and I had just come up with such a set of hints just 2 days earlier. Okay, maybe this had not been forwarded to the customer yet, because our Support department was doing further testing on it first. So it is highly likely that the customer had not seen my solution yet. But I'm still amazed that a customer would be willing to put into production some recommendation they had got off a web forum, and on the basis that they could not work out a better solution themselves - the poster did not know how to do hints properly.
In terms of the problem itself, the cause of the poor performance is actually the extreme skew within the data in this table. On average most of the date values occur thousands of times, but the particular value used in the query only occurs about ten times in the table. Hence another execution plan is faster for this particular value.
Which brings me back to histograms, which I was planning on doing a completely different post on anyway. There is a histogram on the constrained column, but it is a height balanced histogram because there are over 254 distinct values, and so there are no statistics on row counts of individual values other than the most popular values. The value used in the query is unpopular, and the average number of rows per value across all the unpopular values is in the tens of thousands. Hence the choice of an execution plan using a HASH JOIN, as that scales in a different way to higher row counts than a NESTED LOOP join does.
Maybe Oracle should introduce a new type of histogram? One that records both the most popular values, and the least popular values, and then stores a single average for all the values in between (the density). That would have helped here. It certainly seems the case that Oracle does not handle heavily skewed data sets well, though of course you should never expect a "one size fits all" solution to be perfect for all possible scenarios. What the Optimizer does is try and produce an execution plan that is the best for most of the possible input values. Which is what it did here, based on the statistics available for the average number of rows per date value via the density statistic.
Another viable solution is to make the Optimizer believe that the date constraint will match far fewer rows and so choose the NESTED LOOP join method itself. This is the approach put forward by Wolfgang Breitling in his paper on Tuning by Cardinality Feedback, in which he suggests changing the Density statistic stored for the column. And indeed reducing the density value by a factor of 100 has this effect. The upside of this approach is that it avoids the need to change the SQL or use hints, which do not always work as intended.
I work for a software house, whose heavyweight application runs on top of Oracle. Earlier this week I was dealing with a customer who had poor performance on just one SQL query after having updated their statistics. I spent a day analysing the SQL and the data - what was it trying to do and why - and another day trying different solutions to make it use a different execution plan and run faster for them. I then sent the results to the support people dealing with this customer so that the solution could be implemented. Imagine my surprise then when I see today that Jonathan Lewis has a blog post about the same SQL statement, and makes reference to a post on one of the Oracle Technical Forums.
I'm surprised that a customer would post SQL from a third party application on the web like that, and that they would somehow expect a better answer to come back than from the software vendor themselves. I was even more surprised because they were asking for a set of hints to change the execution plan, and I had just come up with such a set of hints just 2 days earlier. Okay, maybe this had not been forwarded to the customer yet, because our Support department was doing further testing on it first. So it is highly likely that the customer had not seen my solution yet. But I'm still amazed that a customer would be willing to put into production some recommendation they had got off a web forum, and on the basis that they could not work out a better solution themselves - the poster did not know how to do hints properly.
In terms of the problem itself, the cause of the poor performance is actually the extreme skew within the data in this table. On average most of the date values occur thousands of times, but the particular value used in the query only occurs about ten times in the table. Hence another execution plan is faster for this particular value.
Which brings me back to histograms, which I was planning on doing a completely different post on anyway. There is a histogram on the constrained column, but it is a height balanced histogram because there are over 254 distinct values, and so there are no statistics on row counts of individual values other than the most popular values. The value used in the query is unpopular, and the average number of rows per value across all the unpopular values is in the tens of thousands. Hence the choice of an execution plan using a HASH JOIN, as that scales in a different way to higher row counts than a NESTED LOOP join does.
Maybe Oracle should introduce a new type of histogram? One that records both the most popular values, and the least popular values, and then stores a single average for all the values in between (the density). That would have helped here. It certainly seems the case that Oracle does not handle heavily skewed data sets well, though of course you should never expect a "one size fits all" solution to be perfect for all possible scenarios. What the Optimizer does is try and produce an execution plan that is the best for most of the possible input values. Which is what it did here, based on the statistics available for the average number of rows per date value via the density statistic.
Another viable solution is to make the Optimizer believe that the date constraint will match far fewer rows and so choose the NESTED LOOP join method itself. This is the approach put forward by Wolfgang Breitling in his paper on Tuning by Cardinality Feedback, in which he suggests changing the Density statistic stored for the column. And indeed reducing the density value by a factor of 100 has this effect. The upside of this approach is that it avoids the need to change the SQL or use hints, which do not always work as intended.
Monday, 7 March 2011
I hate Agile Development
I really hate, loath and detest "Agile Development". At least I hate how they use it where I work. For the record I actually agree with most of the principles and points of Agile Development. I'm pretty sure I have said so before. But what they do in the name of Agile Development where I work is really terrible.
The list of "crimes" conducted by the development teams against databases and development in general by their "use" of Agile methods continues to get longer and longer. Lately I have had to deal with a number of such crimes and their consequences.
I now term Agile the "ignore it today, it will be someone else's problem tomorrow" development methodology. I really do think that many of the developer's believe in a world of magic fairies or pixies that will sprinkle magic Agile dust everywhere in the middle of the whole development, and all of their ignored issues will just disappear and go away. Unfortunately, everyone else is living and dealing with the real world. Have you ever tried living in a house built and delivered one room at a time, all with separately laid foundations, and installed plumbing, electrics, windows and doors? It is a mess, and it gets worse over time as more and more rooms are added on, because there was never an overall design, and no one ever bothered to think about future requirements until they had to deliver them.
The list of "crimes" conducted by the development teams against databases and development in general by their "use" of Agile methods continues to get longer and longer. Lately I have had to deal with a number of such crimes and their consequences.
- One group went ahead with development against nothing more than a strawman design, that in turn was for only verbally stated requirements. No one could agree in a meeting I attended what the actual requirements were. The number of times someone said "I assumed he meant ..." was incredible.
- Another group designed and implemented their own tables, but now have no time to have their database design reviewed before the release deadline. It "must" be delivered as it is, because it is too late to change anything.
- Another group simply did not bother to document anything. When asked at how they arrived at their final solution and the analysis they went through, they could not explain or justify anything.
- All of the groups have used the phrase "we left that for another sprint" for some major, critical piece of functionality. They are simply ignoring anything that is too difficult.
- Iteration speed is more important than getting it right or meeting all the requirements. The fact that the speed of iteration is introducing more errors that need to be corrected by future sprints never gets flagged by anyone. I am sure the quality of what is being delivered is going down, but everyone else is happy that we now have some more buttons to click on the user facing screens and forms.
- Design and code reviews are simply ignored. The only measurement seems to be the volume of exposed and visible functionality delivered. Whether it works or not is never really considered. By that time the developers have moved onto the next sprint, and the next piece of functionality to be delivered. The push is for additional functionality added to the application software at all costs.
- Many application level requirements are simply ignored in early development sprints, just to deliver some usable functionality as early as possible. The fact that the initial design and development cannot be extended in the future to meet the ignored requirements is itself simply ignored.
I now term Agile the "ignore it today, it will be someone else's problem tomorrow" development methodology. I really do think that many of the developer's believe in a world of magic fairies or pixies that will sprinkle magic Agile dust everywhere in the middle of the whole development, and all of their ignored issues will just disappear and go away. Unfortunately, everyone else is living and dealing with the real world. Have you ever tried living in a house built and delivered one room at a time, all with separately laid foundations, and installed plumbing, electrics, windows and doors? It is a mess, and it gets worse over time as more and more rooms are added on, because there was never an overall design, and no one ever bothered to think about future requirements until they had to deliver them.
Tuesday, 25 May 2010
Date Columns at the end of Indexes
A generally good rule of thumb is that date columns should appear at the end of a multi-column index and not at the start or the middle. Why? Because generally date columns are used in range comparisons in queries a lot more often than equality comparisons. The effect of putting such a date column in the middle of an index is to distribute values of the following columns across the different dates within the index.
What this means is that if you have an index on 3 columns - A, D, Z say - where D is a date and A and Z are not (either numeric or a character string) and you execute a query constraining on all 3 columns but with a range constraint on D, the database server will have to read and check a great many index entries
An index is a tree like structure organized by the columns in it. So at the highest levels we branch out for different values of A. Then lower down, for each value of A it branches out for each value of D within that particular value of A. And underneath that we have each value of Z that occurs for each value of D. The value 456 of Z may only occur a few times in the whole table, but it could occur on any value date of D, and so could be in different parts of the index.
This means that when executing the query, the database server will traverse the index, find the branch for A with the value 123 and then all the values for D underneath it. Due to the date range constraint, it will have to check many different values of D to see which ones also have Z with a value of 456.
If the index was on A, Z, D instead, then fewer index entries would need to be checked when executing the query. First it would traverse the index to 123 for A. Then it would traverse down the next level to 456 for Z. Then it would traverse down the next level to D and all values greater than the specified date (1/1/2009). At this point all such entries in the index match the 3 constraints, and so can be retrieved.
This alternate index leads to far fewer index entries being read and checked when executing this particular query.
I am not saying that dates should always go at the end of indexes. If Z was not constrained in the query, only A and D, then the second index would not be much use, and the first index would be much better. There may also be cases where dates are used with equality constraints rather than ranges. But the general rule is still a good one. Put date columns at the end of an index, unless you know the queries being executed would benefit from that date column appearing earlier in the index. As ever the best indexes are those that are most useful to the queries you execute and cover the referenced columns.
An example of this I have just come across resulted in a query execution time coming down from 10,000 ms (10 seconds) by an order of magnitude to 20 ms - a major improvement I think you'll agree. To me this just shows the greater "efficiency" of the index - far fewer index blocks need to be visited to satisfy the query, and those fewer blocks are more likely to be cached in memory too. Hence the orders of magnitude reduction in the elapsed time of the query.
Furthermore this query is executed in a loop some 200+ times within the application I was looking at. So the net effect is not just 10 seconds once down to less than 1 second once, but really 2,000 seconds or 33 minutes down to less than 33 seconds across the overall job each day it is run. A worthwhile improvement from having an alternate index with the date column at the end of it.
What this means is that if you have an index on 3 columns - A, D, Z say - where D is a date and A and Z are not (either numeric or a character string) and you execute a query constraining on all 3 columns but with a range constraint on D, the database server will have to read and check a great many index entries
select ... from T where A = 123 and D > to_date ('20090101', 'YYYYMMDD') and Z = 456 ;
An index is a tree like structure organized by the columns in it. So at the highest levels we branch out for different values of A. Then lower down, for each value of A it branches out for each value of D within that particular value of A. And underneath that we have each value of Z that occurs for each value of D. The value 456 of Z may only occur a few times in the whole table, but it could occur on any value date of D, and so could be in different parts of the index.
This means that when executing the query, the database server will traverse the index, find the branch for A with the value 123 and then all the values for D underneath it. Due to the date range constraint, it will have to check many different values of D to see which ones also have Z with a value of 456.
If the index was on A, Z, D instead, then fewer index entries would need to be checked when executing the query. First it would traverse the index to 123 for A. Then it would traverse down the next level to 456 for Z. Then it would traverse down the next level to D and all values greater than the specified date (1/1/2009). At this point all such entries in the index match the 3 constraints, and so can be retrieved.
This alternate index leads to far fewer index entries being read and checked when executing this particular query.
I am not saying that dates should always go at the end of indexes. If Z was not constrained in the query, only A and D, then the second index would not be much use, and the first index would be much better. There may also be cases where dates are used with equality constraints rather than ranges. But the general rule is still a good one. Put date columns at the end of an index, unless you know the queries being executed would benefit from that date column appearing earlier in the index. As ever the best indexes are those that are most useful to the queries you execute and cover the referenced columns.
An example of this I have just come across resulted in a query execution time coming down from 10,000 ms (10 seconds) by an order of magnitude to 20 ms - a major improvement I think you'll agree. To me this just shows the greater "efficiency" of the index - far fewer index blocks need to be visited to satisfy the query, and those fewer blocks are more likely to be cached in memory too. Hence the orders of magnitude reduction in the elapsed time of the query.
Furthermore this query is executed in a loop some 200+ times within the application I was looking at. So the net effect is not just 10 seconds once down to less than 1 second once, but really 2,000 seconds or 33 minutes down to less than 33 seconds across the overall job each day it is run. A worthwhile improvement from having an alternate index with the date column at the end of it.
Monday, 29 March 2010
DTrace - Great if you can get it to work at all
On the face of it I think DTrace is a phenomenal piece of technology from Sun. That on the fly you can turn on instrumentation on all kernel events and system calls, and write your own scripts to count calls, sum elapsed time, or look at which files had the most I/Os to them, and much more. All implemented with no external change to Solaris itself - all existing commands and utilities run the same - and minimal impact on performance when not enabled. Truly amazing.
For the record I should confess that I was a Sun employee when DTrace was initially announced. However I was an Oracle Consultant and had nothing to do with Solaris kernel development or DTrace. My viewpoint was and remains that of a user of Solaris, and I did not gain any inside knowledge about DTrace one way or another.
I did see the demonstrations that are commonly repeated, where they start off a rogue process and then use DTrace scripts to see what is going on, and which process is causing all the extra work on the system. It looks so simple and so direct - a few iterations and you have identified the process in question and got some of the details of what it is doing.
I have been waiting since then to use DTrace in anger on some real life investigations. It has taken some time for Solaris 10 to be fully rolled out by most organizations. And that opportunity just occurred the other day. A chance to use DTrace to see what an application was doing, and in which functions it was spending most of its time.
A quick peruse of the Web finds the DTrace Toolkit, in which I find something called dappprof.d which does something similar to what I want - elapsed time per function call. I write my own D script for what I want, using similar probes and timestamp calculations, and am ready to look inside this application. Problem solved? No!
Unfortunately, DTrace does not work! Or at least that is what seems to be happening when I try and use it. Which is why I am writing this blog post. Sorry to be so negative, but either it does what it is supposed to do or it does not. And unfortunately it simply does not do what it is supposed to. And I've just spent 2 days banging my head against a brick wall trying to get DTrace to do what it should be doing. The good news is that there is an "answer" to the problem I had, which I'll get to later on. But this could all have been avoided if some of the "details" of DTrace had been done properly by Sun - like useful and helpful error messages, and configuration options that made sense.
First, unlike other software products which tell you WHY something went wrong so that you can fix it, DTrace simply says "I'm not going to do that" and stops. Shades of HAL from 2001: A Space Odyssey spring to mind. Generally whenever something goes wrong with Oracle or a programming language compiler I get a specific message - "syntax error at line xxx", "missing keyword expected", "out of memory". With DTrace you get a message that amounts to "I did not like that so I am stopping" one way or another.
My first error message was:
I managed to find a blog entry by Jon Haslam of Sun, that says that there is nothing you can do about this, other than disabling certain protections within DTrace. And generally speaking those protections are there for a reason, so it is not recommended to disable them.
Okay, maybe I was sampling too often, or matched too many probes, or something. As I say, DTrace never tells you WHAT caused the failure. So I cut down my script and instead of 20,000 probes I now match less than 100. Just 33 in fact. Does it run properly? No! Still the same problem:
The script only has 4 probes as far as I am concerned, even though DTrace ends up matching more than this:
So the entry and exit points of functions in the program file itself (a.out), every 5 seconds, and finally at the end of the script when it stops. How can these be causing "systemic unresponsiveness?".
I try different combinations - a different process, take out the tick-5sec probe, name the functions to match so only "11 probes" matched - and always the same result:
At this point the only conclusion I can come to is that DTrace is useless. What else can I conclude? Every time I run my minimal D script it sits there with no output messages and then aborts. Why do I not see the output from the 5 second ticks? Why does it never work?
As you can imagine I am just about ready to give up on DTrace - forever. I have been through the trouble of designing and writing an initial D script to help investigate my application behaviour, and have requested the appropriate privileges from the system administrators - dtrace_user and dtrace_proc - but have achieved nothing from 2 days of continuous trying. After 2 days I have got zero information out of DTrace, useful or not.
I'm ready to throw in the towel and give up on DTrace as another piece of great but fundamentally flawed Sun technology when I have one more thought. Why are the 5 second tick probes never firing? Why do I never see any output from them? What if the tick probes never fired? Wouldn't my running totals in the aggregations get very large? Could this be causing some kind of overflow and DTrace to simply give up?
A quick Google on the terms "dtrace tick probe privilege" turns up an entry at http://www.mail-archive.com/dtrace-discuss@opensolaris.org/msg02621.html with the same symptoms - the tick probe never fires. And the solution is confirmed in the reply http://www.mail-archive.com/dtrace-discuss@opensolaris.org/msg02622.html - you need the "dtrace_kernel" privilege for the tick probes to fire and output anything. So I make another request to the system administrators for this privilege, and voila! My D script now works as it should do.
So it seems that to do anything useful with DTrace you will need the maximum privilege level of dtrace_kernel, regardless of what the manual tells you differently. Yes, to "use" the tick probe of the profile provider in a D script you need the "dtrace_user" privilege. However, such a defined tick probe will never fire unless you have the "dtrace_kernel" privilege. The "dtrace_user" privilege gives you the "right" to use a tick probe, but not the "access" to that probe at run time. You need the "dtrace_kernel" privilege too for the run time "access" to the tick probe.
I've a feeling that this is extremely poorly designed and documented by Sun. It is not documented in the standard DTrace manuals - I know I looked very closely at them on how to make the tick probe work. So it must be a common problem that people run into time and time again on production systems when trying to work with the least necessary privileges. Yet my Google search only found one explicit reference to this, on a forum posting by someone with the same problem. All the other hits on "dtrace tick probe privilege" were standard descriptions of using probes in DTrace. Nothing from Sun themselves on their own web sites.
For the record I should confess that I was a Sun employee when DTrace was initially announced. However I was an Oracle Consultant and had nothing to do with Solaris kernel development or DTrace. My viewpoint was and remains that of a user of Solaris, and I did not gain any inside knowledge about DTrace one way or another.
I did see the demonstrations that are commonly repeated, where they start off a rogue process and then use DTrace scripts to see what is going on, and which process is causing all the extra work on the system. It looks so simple and so direct - a few iterations and you have identified the process in question and got some of the details of what it is doing.
I have been waiting since then to use DTrace in anger on some real life investigations. It has taken some time for Solaris 10 to be fully rolled out by most organizations. And that opportunity just occurred the other day. A chance to use DTrace to see what an application was doing, and in which functions it was spending most of its time.
A quick peruse of the Web finds the DTrace Toolkit, in which I find something called dappprof.d which does something similar to what I want - elapsed time per function call. I write my own D script for what I want, using similar probes and timestamp calculations, and am ready to look inside this application. Problem solved? No!
Unfortunately, DTrace does not work! Or at least that is what seems to be happening when I try and use it. Which is why I am writing this blog post. Sorry to be so negative, but either it does what it is supposed to do or it does not. And unfortunately it simply does not do what it is supposed to. And I've just spent 2 days banging my head against a brick wall trying to get DTrace to do what it should be doing. The good news is that there is an "answer" to the problem I had, which I'll get to later on. But this could all have been avoided if some of the "details" of DTrace had been done properly by Sun - like useful and helpful error messages, and configuration options that made sense.
First, unlike other software products which tell you WHY something went wrong so that you can fix it, DTrace simply says "I'm not going to do that" and stops. Shades of HAL from 2001: A Space Odyssey spring to mind. Generally whenever something goes wrong with Oracle or a programming language compiler I get a specific message - "syntax error at line xxx", "missing keyword expected", "out of memory". With DTrace you get a message that amounts to "I did not like that so I am stopping" one way or another.
My first error message was:
dtrace: processing aborted: Abort due to systemic unresponsiveness
I managed to find a blog entry by Jon Haslam of Sun, that says that there is nothing you can do about this, other than disabling certain protections within DTrace. And generally speaking those protections are there for a reason, so it is not recommended to disable them.
Okay, maybe I was sampling too often, or matched too many probes, or something. As I say, DTrace never tells you WHAT caused the failure. So I cut down my script and instead of 20,000 probes I now match less than 100. Just 33 in fact. Does it run properly? No! Still the same problem:
dtrace: script './gpsmintrace.d' matched 33 probes
dtrace: processing aborted: Abort due to systemic unresponsivenessThe script only has 4 probes as far as I am concerned, even though DTrace ends up matching more than this:
pid$target:a.out::entry
pid$target:a.out::return
profile:::tick-5sec
dtrace:::ENDSo the entry and exit points of functions in the program file itself (a.out), every 5 seconds, and finally at the end of the script when it stops. How can these be causing "systemic unresponsiveness?".
I try different combinations - a different process, take out the tick-5sec probe, name the functions to match so only "11 probes" matched - and always the same result:
dtrace: processing aborted: Abort due to systemic unresponsivenessAt this point the only conclusion I can come to is that DTrace is useless. What else can I conclude? Every time I run my minimal D script it sits there with no output messages and then aborts. Why do I not see the output from the 5 second ticks? Why does it never work?
As you can imagine I am just about ready to give up on DTrace - forever. I have been through the trouble of designing and writing an initial D script to help investigate my application behaviour, and have requested the appropriate privileges from the system administrators - dtrace_user and dtrace_proc - but have achieved nothing from 2 days of continuous trying. After 2 days I have got zero information out of DTrace, useful or not.
I'm ready to throw in the towel and give up on DTrace as another piece of great but fundamentally flawed Sun technology when I have one more thought. Why are the 5 second tick probes never firing? Why do I never see any output from them? What if the tick probes never fired? Wouldn't my running totals in the aggregations get very large? Could this be causing some kind of overflow and DTrace to simply give up?
A quick Google on the terms "dtrace tick probe privilege" turns up an entry at http://www.mail-archive.com/dtrace-discuss@opensolaris.org/msg02621.html with the same symptoms - the tick probe never fires. And the solution is confirmed in the reply http://www.mail-archive.com/dtrace-discuss@opensolaris.org/msg02622.html - you need the "dtrace_kernel" privilege for the tick probes to fire and output anything. So I make another request to the system administrators for this privilege, and voila! My D script now works as it should do.
So it seems that to do anything useful with DTrace you will need the maximum privilege level of dtrace_kernel, regardless of what the manual tells you differently. Yes, to "use" the tick probe of the profile provider in a D script you need the "dtrace_user" privilege. However, such a defined tick probe will never fire unless you have the "dtrace_kernel" privilege. The "dtrace_user" privilege gives you the "right" to use a tick probe, but not the "access" to that probe at run time. You need the "dtrace_kernel" privilege too for the run time "access" to the tick probe.
I've a feeling that this is extremely poorly designed and documented by Sun. It is not documented in the standard DTrace manuals - I know I looked very closely at them on how to make the tick probe work. So it must be a common problem that people run into time and time again on production systems when trying to work with the least necessary privileges. Yet my Google search only found one explicit reference to this, on a forum posting by someone with the same problem. All the other hits on "dtrace tick probe privilege" were standard descriptions of using probes in DTrace. Nothing from Sun themselves on their own web sites.
Wednesday, 10 March 2010
OpenSolaris is not Solaris
I've been playing with an installation of OpenSolaris on my Intel x86 PC within a VirtualBox virtual machine. (VirtualBox is good, but I'll say more about it separately). The good news is that I managed to install OpenSolaris (2009.06) easily enough and get logged into it for normal use. The bad news is that OpenSolaris is not Solaris, which introduces a whole bunch of incompatability issues. And given that I have done this in order to install and test Oracle, it creates a whole series of problems that would not occur if it was really Solaris.
What is OpenSolaris?
The simplistic answer is that OpenSolaris is an open sourced subset of Solaris i.e. the subset that Sun owned the rights to and could open source. But that is not the real truth. The real truth is that OpenSolaris is the open sourced next generation version of Solaris i.e. Solaris 11. And indeed the version number of OpenSolaris is 11 (or 5.11 technically speaking). And Sun have changed a lot of administration side things between Solaris 10 and 11. And they are still changing things, as OpenSolaris is a work in progress by Sun. A lot can change even between releases of OpenSolaris. So remember that OpenSolaris is not Solaris. There is no mention yet of any level of compatability between Solaris 10 and OpenSolaris.
Clearly OpenSolaris shares the same kernel core as Solaris 10. But outside of that, in terms of the various applications that can be installed, things change. A lot. Solaris has always had its own "packages" installed via the "pkgadd" command. However it lacked a GUI (at least I was not aware of any before now), and did not handle dependencies between packages (A uses things from B, so B should be installed first). You also had to obtain the package distribution file yourself somehow - download or CD media - before it could be installed.
The major Linux distributions have all addressed these issues, so that installing most application software is just a case of firing up the GUI utility, clicking on the application name and then the "Install" button. OpenSolaris has now tried to do the same thing, via its Image Packaging System (IPS), which seems to share the same concepts as Synaptic in Ubuntu. There are repositories of packages, and the packages include lists of dependencies on other packages. Now you can install a package via a few simple clicks, and the package distribution is downloaded from the repository.
Nice. Except ... That there is only one repository, being Sun's own repository of its packages included as part of Solaris. So there is no third party software to choose from. And Sun continues to name all its packages with a very short name and a prefix of "SUNW". Would you know that "SUNWarc" were the "Lint Libraries"? Or that the "Solaris Bundled tools" were in package "SUNWsprot", but that "SUNWbtool" was in fact the "CCS tools bundled with SunOS". Not clear or straightforward at least.
Which leads to the second problem. Even if you know that there is a package containing a program or utility you need, finding out what the name of the package is can be quite hard work. There is not a lot of useful information on OpenSolaris yet that you can find easily via Google. And Sun has provided very little documentation.
Okay, I'll just use the existing Solaris 10 packages? No! OpenSolaris is not Solaris, and even though you could try to install a Solaris 10 package, and it might work, it is not supported and the results are not guaranteed. This is because OpenSolaris is really Solaris 11, and things can change in the kernel and interface libraries. So you should only use OpenSolaris packages, and not Solaris 10 ones.
Are there any other repositories? Of any shape or form? Yes. There is one at Blastwave. Can I use its packages? Yes, but they are very old. Clearly someone did port various Open Source to earlier releases of OpenSolaris, and these have been gathered together at Blastwave. But when I went looking for some things, I could only find 3 year old versions from 2007. In the end I downloaded the source code to the current version of the utility I needed and compiled it all myself.
Oracle 10gR2 Issues
First there is the issue of required packages, and I've already discussed the new IPS on OpenSolaris. For Oracle 10gR2, as per the Oracle 10gR2 on Solaris installation notes, you will need to install:
You also need to install SUNWmfrun, which is the Motif Runtime libraries. Yes, the Oracle 10gR2 graphical utilities use Motif behind the scenes. And Motif is no longer installed as standard on OpenSolaris.
Then can we install Oracle 10gR2? No, because the Oracle installer sees the operating system version as 11 (or rather 5.11 as given by "uname -r") and stops because it is not certified against it. Which is not surprising, as you can only certify against officially released operating systems, and OpenSolaris is still a work in progress. But we will assume that OpenSolaris (11) is fully compatible with Solaris 10, and use the "-IgnoreSysPrereqs" to make Oracle ignore this mismatch in the operating system version.
Then the installer will fail with missing Java libraries e.g.
This is because the Oracle installer is using Java 1.4.2 which is shipped as part of the Oracle 10gR2 distribution itself, but OpenSolaris does not have the right bits for backwards compatability with this, hence the missing library message. Again we can use an installer command line option to tell it to use the current Java Runtime Environment on OpenSolaris (1.6), which will not be missing any bits it needs:
Well, again no. There were various errors about certain files not existing. It seems that the installer cannot create symbolic links between files. Using an Oracle installation on another system I was able to work out what these files should be linked to, and manually did this at the end of the installation.
And finally, Yes, I was able to start Oracle and create a database.
What is OpenSolaris?
The simplistic answer is that OpenSolaris is an open sourced subset of Solaris i.e. the subset that Sun owned the rights to and could open source. But that is not the real truth. The real truth is that OpenSolaris is the open sourced next generation version of Solaris i.e. Solaris 11. And indeed the version number of OpenSolaris is 11 (or 5.11 technically speaking). And Sun have changed a lot of administration side things between Solaris 10 and 11. And they are still changing things, as OpenSolaris is a work in progress by Sun. A lot can change even between releases of OpenSolaris. So remember that OpenSolaris is not Solaris. There is no mention yet of any level of compatability between Solaris 10 and OpenSolaris.
Clearly OpenSolaris shares the same kernel core as Solaris 10. But outside of that, in terms of the various applications that can be installed, things change. A lot. Solaris has always had its own "packages" installed via the "pkgadd" command. However it lacked a GUI (at least I was not aware of any before now), and did not handle dependencies between packages (A uses things from B, so B should be installed first). You also had to obtain the package distribution file yourself somehow - download or CD media - before it could be installed.
The major Linux distributions have all addressed these issues, so that installing most application software is just a case of firing up the GUI utility, clicking on the application name and then the "Install" button. OpenSolaris has now tried to do the same thing, via its Image Packaging System (IPS), which seems to share the same concepts as Synaptic in Ubuntu. There are repositories of packages, and the packages include lists of dependencies on other packages. Now you can install a package via a few simple clicks, and the package distribution is downloaded from the repository.
Nice. Except ... That there is only one repository, being Sun's own repository of its packages included as part of Solaris. So there is no third party software to choose from. And Sun continues to name all its packages with a very short name and a prefix of "SUNW". Would you know that "SUNWarc" were the "Lint Libraries"? Or that the "Solaris Bundled tools" were in package "SUNWsprot", but that "SUNWbtool" was in fact the "CCS tools bundled with SunOS". Not clear or straightforward at least.
Which leads to the second problem. Even if you know that there is a package containing a program or utility you need, finding out what the name of the package is can be quite hard work. There is not a lot of useful information on OpenSolaris yet that you can find easily via Google. And Sun has provided very little documentation.
Okay, I'll just use the existing Solaris 10 packages? No! OpenSolaris is not Solaris, and even though you could try to install a Solaris 10 package, and it might work, it is not supported and the results are not guaranteed. This is because OpenSolaris is really Solaris 11, and things can change in the kernel and interface libraries. So you should only use OpenSolaris packages, and not Solaris 10 ones.
Are there any other repositories? Of any shape or form? Yes. There is one at Blastwave. Can I use its packages? Yes, but they are very old. Clearly someone did port various Open Source to earlier releases of OpenSolaris, and these have been gathered together at Blastwave. But when I went looking for some things, I could only find 3 year old versions from 2007. In the end I downloaded the source code to the current version of the utility I needed and compiled it all myself.
Oracle 10gR2 Issues
First there is the issue of required packages, and I've already discussed the new IPS on OpenSolaris. For Oracle 10gR2, as per the Oracle 10gR2 on Solaris installation notes, you will need to install:
- SUNWarc SUNWbtool SUNWhea SUNWlibm SUNWlibms SUNWsprot SUNWtoo
- SUNWi1of SUNWi1cs SUNWi15cs SUNWxwfnt
You also need to install SUNWmfrun, which is the Motif Runtime libraries. Yes, the Oracle 10gR2 graphical utilities use Motif behind the scenes. And Motif is no longer installed as standard on OpenSolaris.
Then can we install Oracle 10gR2? No, because the Oracle installer sees the operating system version as 11 (or rather 5.11 as given by "uname -r") and stops because it is not certified against it. Which is not surprising, as you can only certify against officially released operating systems, and OpenSolaris is still a work in progress. But we will assume that OpenSolaris (11) is fully compatible with Solaris 10, and use the "-IgnoreSysPrereqs" to make Oracle ignore this mismatch in the operating system version.
Then the installer will fail with missing Java libraries e.g.
Exception java.lang.UnsatisfiedLinkError:
/tmp/OraInstall<...>/jre/1.4.2/lib/i386/motif21/libmawt.so:
ld.so.1: java: fatal: libdps.so.5: open failed: No such file or directory occurred..This is because the Oracle installer is using Java 1.4.2 which is shipped as part of the Oracle 10gR2 distribution itself, but OpenSolaris does not have the right bits for backwards compatability with this, hence the missing library message. Again we can use an installer command line option to tell it to use the current Java Runtime Environment on OpenSolaris (1.6), which will not be missing any bits it needs:
- ./runInstaller -IgnoreSysPrereqs -jreLoc /usr/java/jre
Well, again no. There were various errors about certain files not existing. It seems that the installer cannot create symbolic links between files. Using an Oracle installation on another system I was able to work out what these files should be linked to, and manually did this at the end of the installation.
And finally, Yes, I was able to start Oracle and create a database.
Subscribe to:
Posts (Atom)
