Question: I've heard quite a bit about using Oracle test cases, but I'm not clear on exactly what an Oracle "test case" is used for. I see the value of test cases as an illustrative tool to show how Oracle works, but I'm confused about the value of Oracle test cases when working with MOSC, and the value of test cases when testing Oracle tuning changes. When and where does it make sense to use Oracle test cases?
Answer: Yes, artificial test cases are great teaching tools for demonstrating how Oracle works, but they are also useful when you need to replicate a problem from a secure database, where you are not allowed to disclose details about the schema and table structures.
However, Oracle test cases are never needed when working with Oracle Technical Support because they are bound by non-disclosure, and the contrived nature of test cases gives them limited value in predictive analytics.
Diagram showing the
limited value of test cases
Also see these notes on the differences between contrived
test cases vs. benchmarks.
Test cases are invalid for many reasons:
- Not generalizable: The results
of a test case will vary wildly between the release of Oracle, the OS
and the hardware platform.
- Subject to manipulation: Test
cases are easily manipulated by twiddling hidden parameters to get
whatever result you desire. They have been used as "cheats" to
deceive the Oracle community on several occasions.
- Invalid data and GIGO: By
generating junk data, contrived test cases rarely account for the
real-world nuances in a SQL workload. Garbage-in, garbage-out.
As to using test cases for performance tuning, we must remember that any test must be as representative of the production database as possible. In Oracle 11g, Oracle's
SQL Performance Analyzer (SPA) has embraced a workload-based approach to test cases, whereby real-world SQL is extracted from the
dba_hist tables or the library cache, and representative system tests can be conducted with real-world SQL.
See these related notes on Oracle workload test case methods:
and include the URL for the page.
