1

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.

  • With performance problems with system tables rule optimizer helps sometimes. alter session set optimizer_mode=RULE. And by "gathering statistics" you mean DBMS_STATS.GATHER_FIXED_OBJECTS_STATS right? – Mindaugas Riauba Oct 20 '15 at 5:48
  • @MindaugasRiauba Unfortunately neither changing optimizer mode or DBMS_STATS.GATHER_FIXED_OBJECT_STATS changed the plan. – Jon Heller Oct 20 '15 at 14:23
  • 1
    You cannot manually create those views, those are dynamically created for you when instance comes up. You can see the definition in v$fixed_view_definition but I'd strongly caution against trying to re-create it. If you think you have a bad plan, see if it goes away after instance bounce, i.e. performance improves after instance bounce. You should also try to compare NLS parameters between the instances where you have good vs bad performance. If you wish, you can try to unpin the cursor using dbms_shared_pool package (see metalink note 726780.1). – Raj Oct 20 '15 at 18:40
  • @Raj I think you're on to something. nls_comp and nls_sort are set to BINARY right now but the instance was started with them set to BINARY_CI and LINGUISTIC. I can't find the relevant cursors in the cache, and alter system flush shared_pool; doesn't help. Maybe a restart with the (now) correct parameters is my only hope. It may take a while before I have permission to restart. I'll let you know what happens. Thanks for your help. – Jon Heller Oct 20 '15 at 22:49
3

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.

  • 1
    I created a Service Request, Oracle support also suggested updating sys.pros$, and it worked. I wouldn't say "do not ever do this in a real database", but maybe "do not ever do this in a real database without contacting Oracle support." – Jon Heller May 23 '16 at 21:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.