We received notice of a patch to correct the bug I discussed with gathering stats on tables with virtual columns/function-based indexes in parallel in my previous post. The patch is number 10013177 and can be found at https://updates.oracle.com/download/10013177.html.
After applying the patch, all stats collections worked without error. Whew!
Pages
▼
Wednesday, July 13, 2011
Thursday, June 30, 2011
Bug alert!
While testing Oracle 11.2.0.2 (on Red Hat Enterprise Linux), I recently bumped up against a problem with stats collections using DEGREE > 1 that results in an ORA-600.
If I execute with an estimate_percent of anything other than auto_sample_size, the ORA-20011 goes away, but the ORA-600 remains. Interestingly enough, if I execute with Degree=>1, everything is fine.
After doing more research and finding that I could execute a stats collection on other objects without any problems, I narrowed down the problem to the existence of certain types of function-based indexes. In particular, if the table has an index that uses either a DECODE or a CASE expression or a user-defined function, the problem occurs. As long as the table doesn't have one of these, everything works fine.
The contact to Oracle Support in regard to this issue replied as follows:
It possible you're getting the problem described in Bug:11774077 which is closed as a duplicate of Bug:10013177. For more information please read Note:10013177.8. Functional index causes max decode group by sql to truncate values / can cause dump.
Please check if one of the workarounds will solve your problem:
- Set _replace_virtual_columns=false
OR
- Set _disable_function_based_index=true
OR
- Gathering table stats using degree 1.
Ouch! Turning off the use of virtual columns and function-based indexes is *not* an option. We rely quite heavily on them in our application. And, for the tables that have this problem, gathering stats without doing it in parallel (degree 1) is doable, but not desirable as we have a limited window of time to collect stats and have relied on being able to collect in parallel to reduce the total time it takes to complete the collections.
We're still researching other options or workarounds and pushing on Oracle Support for a fix for this problem, but are not making much progress on either front at this point.
I'll update again soon with more information as I have it.
20:56:41 SQL> BEGIN DBMS_STATS.GATHER_TABLE_STATS('TR_OWNER','TR_POL', Estimate_Percent=>dbms_stats.AUTO_SAMPLE_SIZE, method_opt=>'FOR ALL INDEXED COLUMNS SIZE AUTO',cascade=>FALSE, Degree=>dbms_stats.AUTO_DEGREE); END;
20:56:41 2 /
BEGIN DBMS_STATS.GATHER_TABLE_STATS('TR_OWNER','TR_POL', Estimate_Percent=>dbms_stats.AUTO_SAMPLE_SIZE, method_opt=>'FOR ALL INDEXED COLUMNS SIZE AUTO',cascade=>FALSE, Degree=>dbms_stats.AUTO_DEGREE); END;
*
ERROR at line 1:
ORA-20011: Approximate NDV failed: ORA-12801: error signaled in parallel query
server P024, instance db100.srv.com:TRXTPLT2 (2)
ORA-00039: error during periodic action
ORA-00600: internal error code, arguments: [17114], [0x2AE3C33489B8], [], [],
[], [], [], [], [], [], [], []
ORA-06512: at "SYS.DBMS_STATS", line 23112
ORA-06512: at "SYS.DBMS_STATS", line 23205
ORA-06512: at line 1
If I execute with an estimate_percent of anything other than auto_sample_size, the ORA-20011 goes away, but the ORA-600 remains. Interestingly enough, if I execute with Degree=>1, everything is fine.
After doing more research and finding that I could execute a stats collection on other objects without any problems, I narrowed down the problem to the existence of certain types of function-based indexes. In particular, if the table has an index that uses either a DECODE or a CASE expression or a user-defined function, the problem occurs. As long as the table doesn't have one of these, everything works fine.
The contact to Oracle Support in regard to this issue replied as follows:
It possible you're getting the problem described in Bug:11774077 which is closed as a duplicate of Bug:10013177. For more information please read Note:10013177.8. Functional index causes max decode group by sql to truncate values / can cause dump.
Please check if one of the workarounds will solve your problem:
- Set _replace_virtual_columns=false
OR
- Set _disable_function_based_index=true
OR
- Gathering table stats using degree 1.
Ouch! Turning off the use of virtual columns and function-based indexes is *not* an option. We rely quite heavily on them in our application. And, for the tables that have this problem, gathering stats without doing it in parallel (degree 1) is doable, but not desirable as we have a limited window of time to collect stats and have relied on being able to collect in parallel to reduce the total time it takes to complete the collections.
We're still researching other options or workarounds and pushing on Oracle Support for a fix for this problem, but are not making much progress on either front at this point.
I'll update again soon with more information as I have it.
Wednesday, January 26, 2011
Hotsos Symposium 2011
The 2011 Hotsos Symposium begins in just a little over a month (March 6) and I can't wait! I've been fortunate to have attended every Symposium except one (last year) since these events began. When I was asked to conduct the Training Day following this year's main Symposium (the Training Day is on March 10) I was honored to accept and thrilled that I would be back in attendance this year.
I believe the Symposium is unique in many ways. It is the one and only conference that I know of focused specifically on Oracle performance. Over the span of just a few days, attendees are privileged to hear the best speakers from all over the globe. This year's event is overflowing once again with a stellar line-up of presenters on topics such as "Database I/O Performance" (Alex Gorbachev), "Contemporary Latch Internals" (Andrey Nikolaev), and "Five Things Every Programmer (and DBA) Should Know about Oracle" (Andrew Zitelli). Cary Millsap will be delivering his award-winning presentation "Thinking Clearly about Performance" and Kerry Osborne will be delivering the Keynote address as well as presenting on "Tuning Exadata". The week will end up with Tom Kyte's birds-eye view of the week's sessions as he provides his representation of those topics in the insightful and engaging way only Tom can.
I have the privilege of following this great line-up of speakers by presenting the Training Day. My topic (albeit a very wordy title) is "Managing SQL Performance - Practical Information and Tools for Writing and Maintaining Optimally Performing SQL". I'm looking forward to sharing many of the things I do every day that help me to take poorly performing SQL and making it hum! Don't you just love it when you can get a win like reducing a query's response time from nearly an hour down to a few seconds?! I know I do. The thing is, it isn't magic. Admittedly, it's fun to have people shake their heads in wonderment when I'm able to accomplish such "miracles", but I'll share my secrets for how I go about doing it. Well, OK...they're not really secrets. What I do is simply the application of a systematic, repeatable approach that anyone can learn and utilize. Like anything, the more you practice, the better (and faster) you get at the process.
I think one of the most fun things about becoming more and more proficient at optimizing SQL is that you appear to have abilities that seem almost magical. I thought I'd share this video where Penn & Teller demonstrate how what seems like magic can be uncovered and shown to be just well-timed execution of known and practiced actions.
I hope the Training Day will allow me to show you what lurks under the covers and make the magic seem practical for you! See you there!
I believe the Symposium is unique in many ways. It is the one and only conference that I know of focused specifically on Oracle performance. Over the span of just a few days, attendees are privileged to hear the best speakers from all over the globe. This year's event is overflowing once again with a stellar line-up of presenters on topics such as "Database I/O Performance" (Alex Gorbachev), "Contemporary Latch Internals" (Andrey Nikolaev), and "Five Things Every Programmer (and DBA) Should Know about Oracle" (Andrew Zitelli). Cary Millsap will be delivering his award-winning presentation "Thinking Clearly about Performance" and Kerry Osborne will be delivering the Keynote address as well as presenting on "Tuning Exadata". The week will end up with Tom Kyte's birds-eye view of the week's sessions as he provides his representation of those topics in the insightful and engaging way only Tom can.
I have the privilege of following this great line-up of speakers by presenting the Training Day. My topic (albeit a very wordy title) is "Managing SQL Performance - Practical Information and Tools for Writing and Maintaining Optimally Performing SQL". I'm looking forward to sharing many of the things I do every day that help me to take poorly performing SQL and making it hum! Don't you just love it when you can get a win like reducing a query's response time from nearly an hour down to a few seconds?! I know I do. The thing is, it isn't magic. Admittedly, it's fun to have people shake their heads in wonderment when I'm able to accomplish such "miracles", but I'll share my secrets for how I go about doing it. Well, OK...they're not really secrets. What I do is simply the application of a systematic, repeatable approach that anyone can learn and utilize. Like anything, the more you practice, the better (and faster) you get at the process.
I think one of the most fun things about becoming more and more proficient at optimizing SQL is that you appear to have abilities that seem almost magical. I thought I'd share this video where Penn & Teller demonstrate how what seems like magic can be uncovered and shown to be just well-timed execution of known and practiced actions.
I hope the Training Day will allow me to show you what lurks under the covers and make the magic seem practical for you! See you there!