Recreate fixed views

Question

Is it possible to recreate Oracle fixed views? I have a performance problem with GV$ACTIVE_SESSION_HISTORY that might require rebuilding the view.

A simple select * from gv$active_session_history; runs forever because of a bad execution plan. This only happens on a small number of our databases and probably has something to do with previous NLS settings. The below execution plan uses 2 FIXED TABLE FULL operations, because the NLSSORT predicate prevents a fixed index from being used:

explain plan for select * from gv$active_session_history;

select * from table(dbms_xplan.display);

Plan hash value: 2432277601

------------------------------------------------------------------------------------------------
| Id  | Operation          | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                           |   136K|   169M|   215 (100)| 00:00:04 |
|   1 |  VIEW              | GV$ACTIVE_SESSION_HISTORY |   136K|   169M|   215 (100)| 00:00:04 |
|   2 |   NESTED LOOPS     |                           |   136K|    62M|   215 (100)| 00:00:04 |
|   3 |    FIXED TABLE FULL| X$KEWASH                  |   136K|  3196K|    72 (100)| 00:00:02 |
|*  4 |    FIXED TABLE FULL| X$ASH                     |     1 |   454 |     0   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

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

   4 - filter("S"."SAMPLE_ADDR"="A"."SAMPLE_ADDR" AND "S"."SAMPLE_ID"="A"."SAMPLE_ID" 
              AND "S"."SAMPLE_TIME"="A"."SAMPLE_TIME" AND 
              NLSSORT("S"."NEED_AWR_SAMPLE",'nls_sort=''BINARY_CI''')=NLSSORT("A"."NEED_AWR_SAMPLE",'n
              ls_sort=''BINARY_CI'''))

For comparison, here is a good execution plan on 99% of our databases:

Plan hash value: 436940376

-------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |                           |   100 |   127K|     0   (0)| 00:00:01 |
|   1 |  VIEW                     | GV$ACTIVE_SESSION_HISTORY |   100 |   127K|     0   (0)| 00:00:01 |
|   2 |   NESTED LOOPS            |                           |   100 |   131K|     0   (0)| 00:00:01 |
|   3 |    FIXED TABLE FULL       | X$KEWASH                  |   100 |  5200 |     0   (0)| 00:00:01 |
|*  4 |    FIXED TABLE FIXED INDEX| X$ASH (ind:1)             |     1 |  1299 |     0   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------

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

   4 - filter("S"."SAMPLE_ADDR"="A"."SAMPLE_ADDR" AND "S"."SAMPLE_ID"="A"."SAMPLE_ID" AND 
              "S"."SAMPLE_TIME"="A"."SAMPLE_TIME" AND "S"."NEED_AWR_SAMPLE"="A"."NEED_AWR_SAMPLE")

---

Here is what I've tried, and workarounds that don't work:

1. Hints and plan management features. Fixing queries one-at-a-time isn't good enough. This fixed view is used in too many system queries, I don't want to modify them all. For example, I can fix my example with a hint like this: select /*+ use_hash(@"SEL$3" "A"@"SEL$3") */ * from gv$active_session_history order by sample_time desc;. But I can't change the system queries that use GV$*, and I don't want to have to manage each individual query.
2. Gathering statistics. "Rows = 1" implies bad statistics, but I've already tried gathering statistics and it doesn't help.
3. Faking statistics. I couldn't get the plan to use a hash join even after setting the table rows ridiculously high and setting the column distinct low. Even when the optimizer thinks the join returns quadrillions of rows it still uses a nested loop with two full table scans.

4. Changing NLS settings. At first this looks like the typical linguistic-sorting-ignoring-index issue. But nls_sort and nls_comp are both set to BINARY. When I change nls_comp and nls_sort at the session level, the predicate has 2 NLSSORT functions:

   alter session set nls_comp='LINGUISTIC';
   alter session set nls_sort='BINARY_CI';
   explain plan for select * from gv$active_session_history where sql_id = '7f7bap53hb12w';
   select * from table(dbms_xplan.display);
   
   ...
   
   Predicate Information (identified by operation id):
   ---------------------------------------------------
   
      4 - filter(NLSSORT("A"."SQL_ID",'nls_sort=''BINARY_CI''')=HEXTORAW('37663762617035336
                 86231327700')  AND "S"."SAMPLE_ADDR"="A"."SAMPLE_ADDR" AND 
                 "S"."SAMPLE_ID"="A"."SAMPLE_ID" AND "S"."SAMPLE_TIME"="A"."SAMPLE_TIME" AND 
                 NLSSORT("S"."NEED_AWR_SAMPLE",'nls_sort=''BINARY_CI''')=NLSSORT("A"."NEED_AWR_SAMPLE",'n
                 ls_sort=''BINARY_CI'''))
   

   When I set them back to BINARY one of the NLSSORT goes away, but one remains, preventing the index:

   alter session set nls_comp='BINARY';
   alter session set nls_sort='BINARY';
   explain plan for select * from gv$active_session_history where sql_id = '7f7bap53hb12w';
   select * from table(dbms_xplan.display);
   
   ...
   
   Predicate Information (identified by operation id):
   ---------------------------------------------------
   
      4 - filter("A"."SQL_ID"='7f7bap53hb12w' AND "S"."SAMPLE_ADDR"="A"."SAMPLE_ADDR" AND 
                 "S"."SAMPLE_ID"="A"."SAMPLE_ID" AND "S"."SAMPLE_TIME"="A"."SAMPLE_TIME" AND 
                 NLSSORT("S"."NEED_AWR_SAMPLE",'nls_sort=''BINARY_CI''')=NLSSORT("A"."NEED_AWR_SAMPLE",'n
                 ls_sort=''BINARY_CI'''))
   

5. Find the view source. I can't find the full view source. It's not in DBA_VIEWS and only the first 4000 characters is in $FIXED_VIEW_DEFINITION. I tried grep -i v.*active_session_history * in $ORACLE_HOME/rdbms/admin/ but didn't see anything.

6. Recompile. alter view gv$active_session_history compile; throws an ORA-600.

I'm running 11.2.0.4 on Solaris. I created an Oracle support service request but have not received an answer yet.

Answer 1

Let's say you created the database with the below parameters:

NLS_COMP=LINGUISTIC
NLS_SORT=BINARY_CI

And by create, I really mean create, from scratch. A DBCA custom database, or running CREATE DATABASE and dictionary scripts manually.

If this happened, these will be your database level NLS properties:

SQL> select * from nls_database_parameters 
     where parameter in ('NLS_COMP', 'NLS_SORT') 
     order by parameter;

PARAMETER  VALUE
---------- ----------
NLS_COMP   LINGUISTIC
NLS_SORT   BINARY_CI

(By default here you should see BINARY and BINARY, and to be honest, I can not remember a single case where the database had different values - except the one I have just created in my sandbox.)

Given the above, you will get the same execution plan as in your question. You can restart the instance, or set NLS_COMP and NLS_SORT at session or system (instance) level to the same values, it will not 'fix' the execution plan.

To modify the above setting, it is technically possible (but do not ever do this in a real database) to update these values manually (re-running the dictionary scripts will not update this):

SQL> update props$ set value$ = 'BINARY' where name in ('NLS_COMP', 'NLS_SORT');

2 rows updated.

SQL> commit;

Commit complete.

After this (and a shutdown + startup), the same query used the fixed index without any implicit NLSSORT calls in the filter.

Revert the changes:

SQL> update props$ set value$ = 'BINARY_CI' where name in ('NLS_SORT');

1 row updated.

SQL> update props$ set value$ = 'LINGUISTIC' where name in ('NLS_COMP');

1 row updated.

SQL> commit;

Shutdown, startup, explain, dbms_xplan.display, and it is wrong again.

Another (troublesome, but at least supported) option would be recreating the database with the default (BINARY, BINARY) values.