The death of the demo script

On AskTom, one of the things we always strive to do is provide demo scripts in our answers where appropriate.  That way, people can see our line of thought, and hopefully get an understanding as to how we arrived at a solution.  And there’s two very important concepts in that last sentence:

  • our line of thought
  • how we arrived

The demo script itself, is a representation of that, not necessarily a cast iron guarantee that you should be cut/pasting it into your Production system!  If you are honestly expecting that a demo script that we run, will be exactly how it will run on your system, you’re perhaps being a little naive.

Why ? Because for a long time in the speaker community, there’s been a humorous theme about how the concept of demo scripts has required more and more “terms and conditions” as each version of Oracle got more sophisticated.  Namely, the evolution of  what a demo script might be impacted by :

(Now before anyone takes me to task here on versions and features etc, just like demo scripts themselves, I’m using this as an example of what you need be aware of, not a definitive or exhaustive list)

Oracle 7:

“Here’s my script…and chances are, it will just run like this on your Oracle 7 installation.”

Oracle 8: (multiple blocksizes)

“Here’s my script…it was built an 8k blocksize, your results may differ if you are not.”

Oracle 8i: (locally managed tablespace, and auto segment space management introduced)

“Here’s my script… it was built an 8k blocksize, on a locally managed tablespace, with uniform extent size 1m, in segment space auto-managed, your results may differ if you are not.”

Oracle 9i: (system stats, cpu costing introduced)

“Here’s my script… it was built an 8k blocksize, on a locally managed tablespace, with uniform extent size 1m, in segment space auto-managed, with no system stats calculated or stored, your results may differ if you are not.”

Oracle 10g: (NOWORKLOAD system stats always on, dynamic sampling defaults)

“Here’s my script… it was built an 8k blocksize, on a locally managed tablespace, with uniform extent size 1m, in segment space auto-managed, with NOWORKLOAD system stats gathered, dynamic sampling set to default, your results may differ if you are not.”

Oracle 10.2: (auto histogram, ‘default’ system stats)

“Here’s my script… it was built an 8k blocksize, on a locally managed tablespace, with uniform extent size 1m, in segment space auto-managed, with installation default system stats gathered, dynamic sampling set to default, stats gathered with default parameters, and no previous queries run against your table other than the one in the demo, your results may differ if you are not.”

Oracle 11: (auto multiblock read count)

“Here’s my script… it was built an 8k blocksize, on a locally managed tablespace, with uniform extent size 1m, in segment space auto-managed, with installation default system stats gathered, dynamic sampling set to default, stats gathered with default parameters, and no previous queries run against your table other than the one in the demo, with multiblock read count explicitly set to 128, and a file system that supports 1meg physical I/O, your results may differ if you are not”

Oracle 11.2: (serial direct read, exadata-specific system stats)

“Here’s my script… it was built an 8k blocksize, on a locally managed tablespace, with uniform extent size 1m, in segment space auto-managed, with installation default system stats gathered, dynamic sampling set to default, stats gathered with default parameters, and no previous queries run against your table other than the one in the demo, with multiblock read count explicitly set to 128, and a file system that supports 1meg physical I/O, with serial direct enabled, and a buffer cache size of exactly 2G, and on a non-exadata system, your results may differ if you are not”

Oracle 12: (auto-stats, clustering factor optimization, automatic extended stats)

“Here’s my script… it was built an 8k blocksize, on a locally managed tablespace, with uniform extent size 1m, in segment space auto-managed, with installation default system stats gathered, dynamic sampling set to default, stats gathered with default parameters, and no previous queries run against your table other than the one in the demo, with multiblock read count explicitly set to 128, or a file system that supports 1meg physical I/O, with serial direct enabled, and a buffer cache size of exactly 2G, and on a non-exadata system, with clustering factor TABLE_CACHED_BLOCKS left unchanged, and no hidden columns automatically added to your table by extended stats, your results may differ if you are not”

Oracle 12.1.0.2: (in-memory, large table caching, full database caching, on-chip functionality)

“Here’s my script… it was built an 8k blocksize, on a locally managed tablespace, with uniform extent size 1m, in segment space auto-managed, with installation default system stats gathered, dynamic sampling set to default, stats gathered with default parameters, and no previous queries run against your table other than the one in the demo, with multiblock read count explicitly set to 128, or a file system that supports 1meg physical I/O, with serial direct enabled, and a buffer cache size of exactly 2G, and on a non-exadata system, with clustering factor TABLE_CACHED_BLOCKS left unchanged, and no hidden columns automatically added to your table by extended stats, on an Intel chipset, without large table caching activated, and no auto capture of sql plans, your results may differ if you are not”

And I’ve not even mentioned the myriad of platform differences (things might run slightly different on Windows versus Linux versus AIX ), and of course, the question may have come from someone running Oracle Apps, which depending on the version being used, these parameters such as those below may be set to non-default values:

  • _b_tree_bitmap_plans
  • _fast_full_scan_enabled
  • _like_with_bind_as_equality
  • _sort_elimination_cost_ratio
  • optimizer_secure_view_merging

which changes how queries are optimized and even how they may be transformed before optimization.

But what if we eliminate the version discrepancy ? … Rest assured, even if you have the exact same version of software as us, the same potential for difference applies.  We’re typically on our VM inside our laptop on a private network, and you are on a server with different CPU, different RAM, different parameters, different storage, different platform.  What patches have you applied ? What parameters have you set ? There are just so many variables in play here.

So are demo scripts are waste of time?… Of course not.  Just remember what they are – an addenda to a line of thought, an application of logic, a glimpse into a potential useful process.  Your job is then to take advantage of that thought and logic and discover for yourself the possible benefits on your system.

Happy scripting ! Smile

Advertisements

3 thoughts on “The death of the demo script

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s