I am way too much of a geek.

I came across this graph on a humor site I frequent.



My first response is that "This graph is incorrect. You cannot measure the chances of Shrodinger's Cat being alive or dead because that state is not observable, thus not measurable."

Ouch...what a geek!

For those of you who don't get it...
http://en.wikipedia.org/wiki/Schrodingers_cat
http://en.wikipedia.org/wiki/Heisenberg_uncertainty

Incorrect query results using a function based index

I recently encountered a situation where a Function-Based Index would be disabled and there was not a known reason. This particular FBI performs a query against another table (which is actually a violation of the DETERMINISTIC requirement but was still allowed). So I set up a small test trying to find the reason an FBI would be disabled.

Good news - in the test I performed I did not disable an index
Bad news - since it did not, I don't know what disabled the index
Really, REALLY BAD NEWS - using a query in a function-based index opens the door to incorrect results.

I show my work at the bottom of the blog so you can see exactly what is happening. There are a few more conditions I need to test (index rebuild, disable/reenable the index) and if you have other ideas, suggestions, please include them in your comments.

I created two tables, t_emp & t_dept, that were copies of emp & dept, respectively. There are not any integrity constraints (other than not null) nor indexes. I then created a function that accepted the department number (deptno) and returned the department name (dname). This mirrors the function that DAFY is using for the disabled FBI.

When I created the index, the index value is the t_dept.dname. Here is a list of some of the tests and the impact on the index.
1) Insert a record into t_emp with a deptno value not in t_dept - no error and FBI is enabled.
2) Delete a record from t_dept that has records with the same deptno value in t_emp - no error and FBI is enabled.
3) Truncate t_dept - no error and FBI is enabled.
4) Recreate t_dept as in the original process - no error and FBI is enabled.

I did not find a triggering event to disable the index. However, I found something I consider more of a problem.

When updating the dname in t_dept, the FBI is not updated. This means that incorrect data is returned. If a new record is inserted, then the FBI value reflects the new dname. If the deptno value in t_emp is updated...the FBI is *NOT* updated! Querying the t_emp table using the function created, which is the way FBIs are intended to be used, uses the FBI and returns what are now incorrect results.

SQL> create table t_emp as select * from emp;
SQL> create table t_dept as select * from dept;

SQL> CREATE OR REPLACE FUNCTION get_dept_name
2 ( p_deptno IN t_emp.deptno%TYPE)
3 RETURN t_dept.dname%TYPE
4 DETERMINISTIC
5 IS
6 l_dname t_dept.dname%TYPE;
7 CURSOR c_dname IS
8 SELECT dname
9 FROM t_dept
10 WHERE deptno = p_deptno;
11 BEGIN
12 OPEN c_dname;
13 FETCH c_dname INTO l_dname;
14 CLOSE c_dname;
15 RETURN l_dname;
16 END get_dept_name;
17 /
SQL> show error
No errors.
SQL> CREATE INDEX ix_dname ON t_emp (get_dept_name(deptno))
2 /
SQL> SQL> SELECT index_name, index_type, funcidx_status
2 FROM user_indexes
3 WHERE table_name = 'T_EMP';

INDEX_NAME INDEX_TYPE FUNCIDX_
------------------------------ --------------------------- --------
IX_DNAME FUNCTION-BASED NORMAL ENABLED


SQL> select * from t_dept;

DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

SQL> insert into t_emp (empno, deptno) values (1000, 10);
SQL> commit;
SQL> SELECT index_name, index_type, funcidx_status
2 FROM user_indexes
3 WHERE table_name = 'T_EMP';

INDEX_NAME INDEX_TYPE FUNCIDX_
------------------------------ --------------------------- --------
IX_DNAME FUNCTION-BASED NORMAL ENABLED

SQL> insert into t_emp (empno, deptno) values (1001, 50)
2 /
SQL> commit;
SQL> SELECT index_name, index_type, funcidx_status
2 FROM user_indexes
3 WHERE table_name = 'T_EMP';

INDEX_NAME INDEX_TYPE FUNCIDX_
------------------------------ --------------------------- --------
IX_DNAME FUNCTION-BASED NORMAL ENABLED
SQL> delete from t_dept where deptno = 10;
SQL> commit;
SQL> SELECT index_name, index_type, funcidx_status
2 FROM user_indexes
3 WHERE table_name = 'T_EMP';

INDEX_NAME INDEX_TYPE FUNCIDX_
------------------------------ --------------------------- --------
IX_DNAME FUNCTION-BASED NORMAL ENABLED
SQL> select * from t_emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 09-DEC-82 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 12-JAN-83 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
1000 10
1001 50
SQL> SELECT index_name, index_type, funcidx_status
2 FROM user_indexes
3 WHERE table_name = 'T_EMP';

INDEX_NAME INDEX_TYPE FUNCIDX_
------------------------------ --------------------------- --------
IX_DNAME FUNCTION-BASED NORMAL ENABLED

SQL> truncate table t_dept;
SQL> SELECT index_name, index_type, funcidx_status
2 FROM user_indexes
3 WHERE table_name = 'T_EMP';

INDEX_NAME INDEX_TYPE FUNCIDX_
------------------------------ --------------------------- --------
IX_DNAME FUNCTION-BASED NORMAL ENABLED

SQL> drop table t_dept;
SQL> SELECT index_name, index_type, funcidx_status
2 FROM user_indexes
3 WHERE table_name = 'T_EMP';

INDEX_NAME INDEX_TYPE FUNCIDX_
------------------------------ --------------------------- --------
IX_DNAME FUNCTION-BASED NORMAL ENABLED

SQL> create table t_dept as select * from dept;
SQL> SELECT index_name, index_type, funcidx_status
2 FROM user_indexes
3 WHERE table_name = 'T_EMP';

INDEX_NAME INDEX_TYPE FUNCIDX_
------------------------------ --------------------------- --------
IX_DNAME FUNCTION-BASED NORMAL ENABLED

SQL> select * from t_emp where get_dept_name(deptno) = 'ACCOUNTING'
2 /

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7839 KING PRESIDENT 17-NOV-81 5000 10
7934 MILLER CLERK 7782 23-JAN-82 1300 10
1000 10

SQL> update t_dept set dname = 'TEST' where deptno = 10;
SQL> commit;
SQL> select * from t_emp where get_dept_name(deptno) = 'ACCOUNTING'
2 /

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7839 KING PRESIDENT 17-NOV-81 5000 10
7934 MILLER CLERK 7782 23-JAN-82 1300 10
1000 10

SQL> insert into t_emp (empno, deptno) values (1002, 10);
SQL> commit
2 /
SQL> select * from t_emp where get_dept_name(deptno) = 'ACCOUNTING'
2 /

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7839 KING PRESIDENT 17-NOV-81 5000 10
7934 MILLER CLERK 7782 23-JAN-82 1300 10
1000 10
SQL> select * from t_emp where get_dept_name(deptno) = 'TEST'
2 /

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
1002 10
SQL> update t_emp set deptno = deptno;
SQL> select * from t_emp where get_dept_name(deptno) = 'ACCOUNTING'
2 /

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7839 KING PRESIDENT 17-NOV-81 5000 10
7934 MILLER CLERK 7782 23-JAN-82 1300 10
1000 10
SQL> update t_emp set deptno = 10 where deptno = 10;
SQL> select * from t_emp where get_dept_name(deptno) = 'ACCOUNTING'
2 /

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7839 KING PRESIDENT 17-NOV-81 5000 10
7934 MILLER CLERK 7782 23-JAN-82 1300 10
1000 10

SQL> set autotrace on explain
SQL> select * from t_emp where get_dept_name(deptno) = 'ACCOUNTING'
2 /

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7839 KING PRESIDENT 17-NOV-81 5000 10
7934 MILLER CLERK 7782 23-JAN-82 1300 10
1000 10

Execution Plan
----------------------------------------------------------
Plan hash value: 4230458767

----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 87 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T_EMP | 1 | 87 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IX_DNAME | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("SCOTT"."GET_DEPT_NAME"("DEPTNO")='ACCOUNTING')
SQL> select * from t_emp where get_dept_name(deptno) = 'TEST'
2 /

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
1002 10

Execution Plan
----------------------------------------------------------
Plan hash value: 4230458767

----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 87 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T_EMP | 1 | 87 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IX_DNAME | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("SCOTT"."GET_DEPT_NAME"("DEPTNO")='TEST')

Note
-----
- dynamic sampling used for this statement

On the Importance of Bind Variables

This is not a discussion of parsing and peeking with bind variables. Those topics are well covered by others, especially Tom Kyte and Jonathan Lewis.

A frequent diagnostic task is to determine why a sql statement has started performing poorly or that the performance is very erratic. The first question to answer is "What is different now than when it was running well?" Perhaps it is processing more data, reading more blocks from disk or memory, or using a different execution plan.

The key to querying the historical activity of a sql statement is being able to locate that statement in your performance tables (Statspack, AWR or home grown). When a statement is parsed, a sql id is assigned. The rules about formatting and literals apply, so two statements that are 'nearly identical' will get different sql_ids. If the statements are identical, then you will get the same sql_id.

As an aside, sql_id is the best way to locate a sql statement as the value of hash_value may not be "unique enough". I always thought there was only unique and nonunique, apparently there are shades of unique gray.

Two recent diagnosis tasks started with the same description "This process used to run well, now it is running slowly". One statement (Query1) used bind variables and had the same sql_id over time. The other statement (Query2) was being dynamically generated, used literals and had a different sql_id for previous runs.

For Query1, I queried the Statspack tables and found that the elapsed time for a statement had suddenly increased (though the cpu time, rows processed, buffer gets, disk reads had remained basically the same). Further diagnosis located the root cause (SAN configuration issue).

For Query2, an extended sql_trace revealed a shockingly bad execution plan. Unfortunately, as the sql_id was changing, I could not look at the historical performance (without manually reviewing all the query code in the performance tables...a task that would have taken weeks/months). We were left without any real clues as to 'what changed' and we could not duplicate better performance in any of the non-production environments.

Had Query2 used bind variables instead of literals, we could have gone back through history. This may or may not have revealed the root cause of the performance problem, but we would have had a reasonable opportunity.

Quite often diagnosis and optimization is being able to answer that question "What is different now than when it was running well?". Proper use of bind variables can be very beneficial in gathering the data to answer that question.