I’m really looking forward to the Rocky Mountain Oracle User Groups Training days next week, at the Colorado Convention Center, as it will bring several new challenges for me. I am going to be involved in three very different sessions at this year’s conference:
Locking Table Statistics Provides Plan Stability True or False?
I was recently asked if I would ever recommend locking table statistics as a way of preventing execution plans from changing. As, with a lot questions’ regarding the Optimizer and statistics the answer was “it depends”.
There are definitely some edge cases where locking the statistics is the best approach but I wouldn’t generally recommend it to achieve plan stability. Before I share the edge cases where I believe locking statistics would help, lets first look at exactly what locking table statistics does and why it’s not a good approach to plan stability.
From Oracle Database 10g onwards you have had the ability to lock the optimizer statistics for a table or schema, via the DBMS_STATS package.
BEGIN
DBMS_STATS.LOCK_TABLE_STATS(‘SH’,’SALES’);
END;
/
The LOCK_STATS procedure locks all of the statistics related to a table, including the table statistics (number of rows, number of blocks etc.), column statistics (min and max values, number of distinct values etc.), histograms and the statistics on all dependent indexes. Once statistics are locked, no modifications can be made to those statistics until the statistics have been unlocked or if the FORCE option is used in the GATHER_*_STATS procedures.
I was recently asked if I would ever recommend locking table statistics as a way of preventing execution plans from changing. As, with a lot questions’ regarding the Optimizer and statistics the answer was “it depends”.
There are definitely some edge cases where locking the statistics is the best approach but I wouldn’t generally recommend it to achieve plan stability. Before I share the edge cases where I believe locking statistics would help, lets first look at exactly what locking table statistics does and why it’s not a good approach to plan stability.
Continue reading “Locking Table Statistics Provides Plan Stability True or False?”
Building a simple Node.js application with Oracle
Anyone who knows me will tell you, my expertise is the Oracle Database and not web applications or JSON. But my new role is all about pushing myself out of my comfort zone, so I wanted to get some hands on experience with both application development and JSON in the Oracle Database.
My colleague, Mark Drake, the PM for JSON in the Oracle Database, suggested I get my feet wet by trying the new Movie Ticketing Application tutorial available on the JSON page on Oracle.com.
The Movie Ticketing Application is written in Node.js and connects to the Oracle Database via a REST Data Service. Since I recently signed up for the Oracle Database Exadata Express cloud service, I had the perfect Database setup for a web application.
Anyone who knows me will tell you, my expertise is the Oracle Database and not web applications or JSON. But my new role is all about pushing myself out of my comfort zone, so I wanted to get some hands on experience with both application development and JSON in the Oracle Database.
My colleague, Mark Drake, the PM for JSON in the Oracle Database, suggested I get my feet wet by trying the new Movie Ticketing Application tutorial available on the JSON page on Oracle.com.
The Movie Ticketing Application is written in Node.js and connects to the Oracle Database via a REST Data Service. Since I recently signed up for the Oracle Database Exadata Express cloud service, I had the perfect Database setup for a web application.
Continue reading “Building a simple Node.js application with Oracle”
Excited to be the newest member of the AskTom Team
A little over a year ago Connor McDonald and Chris Saxon took over answering questions posted on AskTom, which covers many topics related to building applications on top of the Oracle Database. Connor and Chris have done an amazing job of keeping up with the constant influx of questions, with nearly 16,000 answered to date.
Last week, I was lucky enough to be invited to join Connor and Chris in answering questions submitted AskTom. So, if you have any burning questions regarding the Oracle Database or building applications on top of the Oracle Database, you can submit them at AskTom and Connor, Chris or I will get back to you!
Online Statistics Gathering
Although there have been a number of significant changes to the Oracle Database in 12c, some of my favorite new features are actually the small enhancements, designed to make your life a little easier. So, I decided to spend some time this year sharing the details on these small but extremely useful features.
One such enhancement is Online Statistics Gathering.
Whenever an index is created, Oracle automatically gathers optimizer statistics for that index. The database piggybacks the statistics gather on the full data scan and sort operation necessary for the index creation. This approach has worked so well since it was introduced in 9i, few people even realize it’s happening.
Although there have been a number of significant changes to the Oracle Database in 12c, some of my favorite new features are actually the small enhancements, designed to make your life a little easier. So, I decided to spend some time this year sharing the details on these small but extremely useful features.
One such enhancement is Online Statistics Gathering.
Coming Soon!
I recently switched my role in the Oracle Database Development team from the product management team for Database In-Memory and Advance Compression to be a database evangelist.
With this new role comes a new blog and an opportunity for me to broaden the topics I blog about. I’ll also have an opportunity to share my own opinion and approach to optimizing your Oracle investment.
I hope you will join me on this new journey and will learn a little something along the way!