Oracle Tips and Tricks — David Fitzjarrell

January 28, 2008

Lies, Damned Lies, and Statistics

Filed under: General,Performance,stats — dfitzjarrell @ 15:03

Apparently Oracle 10.2.0.3 and later releases up to 12.1.0.x have a problem with fixed object statistics (well, it’s not a problem, really, they simply don’t exist by default) causing queries against views using some of these fixed objects to take an abnormally long period of time for the data volume processed. As an example take the following query:

select distinct owner from v$access;

Executing this query in a plain-vanilla 10.2.0.3 database provides the following output:

SQL> select distinct owner from v$access;
select distinct owner from v$access
*
ERROR at line 1: ORA-01013: user requested cancel of current operation

The operation was cancelled after 5 minutes as the query would not return results in a ‘reasonable’ time frame. A subsequent execution, left to its devices, returned its two-row result set in approximately 12 minutes.

The query plan:

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  dmfdcmvwkgfqh, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ distinct owner from v$access

Plan hash value: 3850776806

---------------------------------------------------------------------------------------------------------------------------------------------------------
 Id   Operation                   Name             Starts  E-Rows E-Bytes Cost (%CPU) E-Time    A-Rows    A-Time     OMem   1Mem   O/1/M
---------------------------------------------------------------------------------------------------------------------------------------------------------
   1   HASH UNIQUE                                      2     105  14175      1 (100) 00:00:01       3 00:01:44.35                   
   2    NESTED LOOPS                                    2     105  14175      0   (0)              348 00:01:29.55                   
   3     NESTED LOOPS                                   2      10    820      0   (0)              348 00:01:29.53                   
   4      MERGE JOIN CARTESIAN                          2     100   5700      0   (0)              125K00:00:00.61                   
*  5       FIXED TABLE FULL       X$KSUSE               2       1     19      0   (0)               81 00:00:00.01                   
   6       BUFFER SORT                                 81     100   3800      0   (0)              125K00:00:00.49    118K   118K     2/0/0
   7        FIXED TABLE FULL      X$KGLDP               2     100   3800      0   (0)             3128 00:00:00.05                   
*  8      FIXED TABLE FIXED INDEX X$KGLLK (ind:1)     125K      1     25      0   (0)              348 00:02:19.55                   
*  9     FIXED TABLE FIXED INDEX  X$KGLOB (ind:1)     348      10    530      0   (0)              348 00:00:00.02                   
---------------------------------------------------------------------------------------------------------------------------------------------------------

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

   5 - filter("S"."INST_ID"=USERENV('INSTANCE'))
   8 - filter(("L"."KGLLKUSE"="S"."ADDR" AND "L"."KGLLKHDL"="D"."KGLHDADR" AND "L"."KGLNAHSH"="D"."KGLNAHSH"))
   9 - filter(("O"."KGLNAHSH"="D"."KGLRFHSH" AND "O"."KGLHDADR"="D"."KGLRFHDL"))

Notice the MERGE JOIN CARTESIAN and the disparity between the estimated rows (100) and the actual rows (125,000). Since this is accessing fixed tables the error is probably in the fixed table statistics. DBMS_STATS provides a procedure to compute fixed table statistics:

SQL> connect / as sysdba
SQL> exec dbms_stats.gather_fixed_objects_stats(NULL);

PL/SQL procedure successfully completed.

SQL>

Running the original query again:

SQL> select distinct owner
2 from v$access;

OWNER
----------------------------------------------------------------
PUBLIC
SYS

Elapsed: 00:00:00.14

Execution Plan
----------------------------------------------------------
Plan hash value: 174934893

----------------------------------------------------------------------------------------------
 Id   Operation                   Name             Rows   Bytes  Cost (%CPU) Time
----------------------------------------------------------------------------------------------
   0  SELECT STATEMENT                                15   1305      5 (100) 00:00:01
   1   HASH UNIQUE                                    15   1305      5 (100) 00:00:01
   2    NESTED LOOPS                                 433  37671      4 (100) 00:00:01
   3     NESTED LOOPS                                433  29877      3 (100) 00:00:01
   4      HASH JOIN                                  433  16021      1 (100) 00:00:01
   5       FIXED TABLE FULL       X$KSUSE            170   2040      0   (0) 00:00:01
   6       FIXED TABLE FULL       X$KGLLK            433  10825      1 (100) 00:00:01
   7      FIXED TABLE FIXED INDEX X$KGLDP (ind:1)      1     32      0   (0) 00:00:01
   8     FIXED TABLE FIXED INDEX  X$KGLOB (ind:1)      1     18      0   (0) 00:00:01
----------------------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
        269  bytes sent via SQL*Net to client
        246  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          4  rows processed

SQL>

Notice the results are returned in less than 1 second, and the MERGE JOIN CARTESIAN is missing from the execution plan. More detailed information of the above plan is posted below:

SQL> select * from table(dbms_xplan.display_cursor('dmfdcmvwkgfqh',0,'ALL ALLSTATS'));


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  dmfdcmvwkgfqh, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ distinct owner from v$access

Plan hash value: 174934893

---------------------------------------------------------------------------------------------------------------------------------------------------------
 Id   Operation                   Name             Starts  E-Rows E-Bytes Cost (%CPU) E-Time    A-Rows    A-Time     OMem   1Mem   O/1/M
---------------------------------------------------------------------------------------------------------------------------------------------------------
   1   HASH UNIQUE                                      1      15   1305      5 (100) 00:00:01       4 00:00:00.10                   
   2    NESTED LOOPS                                    1     433  37671      4 (100) 00:00:01     394 00:00:00.09                   
   3     NESTED LOOPS                                   1     433  29877      3 (100) 00:00:01     394 00:00:00.06                   
*  4      HASH JOIN                                     1     433  16021      1 (100) 00:00:01     335 00:00:00.03   1236K  1236K     1/0/0
*  5       FIXED TABLE FULL       X$KSUSE               1     170   2040      0   (0)              170 00:00:00.01                   
   6       FIXED TABLE FULL       X$KGLLK               1     433  10825      1 (100) 00:00:01     335 00:00:00.01                   
*  7      FIXED TABLE FIXED INDEX X$KGLDP (ind:1)     335       1     32      0   (0)              394 00:00:00.03                   
*  8     FIXED TABLE FIXED INDEX  X$KGLOB (ind:1)     394       1     18      0   (0)              394 00:00:00.03                   
---------------------------------------------------------------------------------------------------------------------------------------------------------

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

   4 - access("L"."KGLLKUSE"="S"."ADDR")
   5 - filter("S"."INST_ID"=USERENV('INSTANCE'))
   7 - filter(("L"."KGLLKHDL"="D"."KGLHDADR" AND "L"."KGLNAHSH"="D"."KGLNAHSH"))
   8 - filter(("O"."KGLNAHSH"="D"."KGLRFHSH" AND "O"."KGLHDADR"="D"."KGLRFHDL"))

Notice that the actual rows (A-Rows) is much closer in agreement with the estimated rows (E-Rows), thus improving the optimizer’s efforts in determining a viable execution plan. [It has also come to my attention that the exp utility will hang in some releases when exporting cluster definitions. This ‘hang’ may also be a result of missing fixed object statistics if the database in question is a 10g or later release.] I would include this in the script used to update schema statistics (in many cases scheduled for a weekly run) and let these be generated ‘automatically’.

A workaround is to set optimizer_mode at the session level. Setting this parameter to

RULE

also ‘solves’ the problem by using antiquated optimizer code that eliminates the cost model altogether. Oracle recommends the first ‘option’ (computing statistics on the fixed objects) and also recommends these statistics be kept current. Adding a call to the dbms_stats.gather_fixed_objects_stats(NULL) procedure to the weekly statistics run should be a fairly easy modification to make, and would be my choice to ensure queries against fixed objects return in a reasonable period of time with respect to the data volume.

Verifying you have fixed object statistics involves a quick query of the sys.tab_stats$ table. There should be in the neighborhood of 580 rows returned (at least on the 10.2.0.3.0 databases I’ve queried).

The time required for dbms_stats.gather_fixed_objects_stats to complete can vary from 1 minute to possibly 5 minutes or more (depending upon the server configuration and user load) so this won’t report ‘PL/SQL procedure successfully completed.’ immediately. You can monitor the progress of the collection using the V$SESSION_LONGOPS view:

SQL> select sid, serial#, units, sofar, totalwork, start_time, last_update_time, time_remaining
  2  from v$session_longops
  3  where (sid, serial#) in (select sid, serial# from v$session where username = 'SYS')
  4  and units = 'Objects'
  5  order by target, last_update_time;

       SID    SERIAL# UNITS                                 SOFAR  TOTALWORK START_TIM LAST_UPDA TIME_REMAINING
---------- ---------- -------------------------------- ---------- ---------- --------- --------- --------------
       145       3796 Objects                                  34         34 08-OCT-08 08-OCT-08           0
       145       3796 Objects                                   3          3 08-OCT-08 08-OCT-08           0
       145       3796 Objects                                 548        548 08-OCT-08 08-OCT-08           0

SQL>

The TOTALWORK column indicates the total quantity of UNITS the task entails. The SOFAR column reports, in UNITS, the work completed.

Oracle, in another Metalink Note ( 549895.1) suggests setting the hidden init parameter _optimizer_cartesian_enabled to false, either at the session level or at the instance level [this prevents the CBO from choosing to use a cartesian join when other access paths are available. In Oracle 10.2.0.3 and later releases an additional hidden parameter, _optimizer_mjc_enabled, also needs to be set to FALSE to force the optimizer to completely eliminate any merge join cartesian paths. Thanks, Nuno, for that bit of information.]. I’d rather compute statistics on the fixed objects rather than set a hidden init.ora parameter, as setting that parameter may adversely affect other queries which aren’t experiencing problems.

If, for some reason, you decide you don’t want fixed objects statistics that’s a simple operation to undertake. Simply do this:

SQL> exec dbms_stats.delete_fixed_objects_stats

PL/SQL procedure successfully completed.

SQL>

and the fixed objects statistics are gone.

As stated earlier including these in the regular statistics run is probably a good idea, so that any changes to data in those fixed objects will be captured.

In Oracle 12.1.0.x and later releases these statistics are included in the pre-configured automatic statistics job (read here) so these shouldn’t be an issue.

There’s nothing better than nice, fairly fresh statistics. And I ain’t lyin’.

Advertisements

2 Comments »

  1. After reading your post (Thanks to Jonathan Lewis)I realized that This is still a problem on 11G as well. Thank you for pointing out the workaround, gathering fixed object stats it is not working on dba_extends but solves v$access problem.

    Comment by Coskan Gundogar — June 2, 2009 @ 22:22 | Reply

  2. Just noticed this entry via Jonathan's blog.A few comments regarding the "_optimizer_cartesian_enabled" parameter to eliminate merge_join_cartesian:in 10.2.0.3, that is not enough.You need to set also:_optimizer_mjc_enabled = FALSEto get the optimizer to COMPLETELY ignore MJC!I found this out with a problem we had with our Peoplesoft HR system: after a lot of trouble with a particular statement, support finally gave me the above parameter to get rid of MJC. I was already using the other one you noted.Why is it even enabled by default is mystifying: I definitely struggle trying to find ONE example where MJC would result in a better execution plan.Other than with completely empty tables – in which case who cares?I do recall the enormous trouble instructors went to in basic relational technology courses back in the 80s, explaining why one should NEVER have a cartesian join…

    Comment by Noons — June 3, 2009 @ 02:02 | Reply


RSS feed for comments on this post. TrackBack URI

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

Create a free website or blog at WordPress.com.

%d bloggers like this: