Search

Top 60 Oracle Blogs

Recent comments

Oracle optimizer statistics

Understanding Oracle Optimizer Statistics Webinar Q&A

There were quite a few questions asked during the webinar yesterday that I didn't have time to answer online. So, here's the list of questions from the webinar transcripts with my answers. In some cases, I combined several similar questions and gave a single answer. Hope it's helpful!

1. Can you give your thoughts on bind peeking and how this relates to statistics?

Understanding Oracle Optimizer Statistics Webinar Follow-up

Thanks to everyone for attending today's webinar on Understanding Oracle Optimizer Statistics sponsored by Embarcadero Technologies. I appreciate everyone who took time to join me today and hope you found it informative.

The webinar recording will be posted within in the next couple of days, but you can download the presentation file now.

Understanding Oracle Optimizer Statistics - presentation

I'll provide the link to the recording as soon as it's available and will update this post with Q&A from the webinar sessions shortly.

Thanks and again and see you in March when I'll be presenting "Back to the Future: Oracle SQL Performance Firefighting using AWR."

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.


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], [], [],
[], [], [], [], [], [], [], []