Friday, August 1, 2014

What’s your Preference?

Gathering stats in Oracle has come a long way from the simple Analyze command we used back in V7 days.  Today the DBMS_STATS package has a lot of procedures and functions in it.  And on top of that many of these procedures and functions have many parameters.  Setting these correctly can make a big difference for the stats collected, which in turn can have a huge impact on performance.  There is a set of procedures in DBMS_STATS that can help you bring some sanity to the chaos. 

These procedures (listed below) allow you to set many of the parameters used by the different statistic collection procedures to a value you want.  And even let you set up defaults, called global settings.  Here are the procedures.

SET_TABLE_PREFS – Set a parameter for a particular table
SET_SCHEMA_PREFS – Set a parameter all current tables in a schema*
SET_DATABASE_PREFS – Set a parameter all current tables in the database*
SET_GLOBAL_PREFS – Set a parameter to a "default" at the global level

* Note: The schema and database calls actually call SET_TABLE_PREFS for all tables currently defined at that level (schema or database).  These setting do not affect new tables.  But SET_GLOBAL_PREFS does!

Another note worth mentioning is that the SET_DATABASE_PREFS procedure doesn’t change a setting for Oracle owned tables by default.  There is a parameter ADD_SYS for this procedure which is set to false by default. Set this to true if you wish to have the setting applied to Oracle owned tables as well.

Common to all these procedures are 2 parameters, PNAME (parameter name) and PVALUE (parameter value).  The list of possible PNAME values is here:
AUTOSTATS_TARGET *
CASCADE
CONCURRENT *
DEGREE
ESTIMATE_PERCENT
GLOBAL_TEMP_TABLE_STATS
GRANULARITY
INCREMENTAL
INCREMENTAL_LEVEL
INCREMENTAL_STALENESS
METHOD_OPT
NO_INVALIDATE
PUBLISH
STALE_PERCENT
TABLE_CACHED_BLOCKS
OPTIONS

* Note: Only valid at the global level

As you can see there are plenty of things you can set.  As a simple example if you wanted to set the STALE_PERCENT for all current tables to 30% in you database you could do something like this:

SQL> EXEC DBMS_STATS.SET_DATABASE_PREFS('STALE_PERCENT','30');

The key to note here is that this explicitly sets STALE_PERCENT at the table level to 30.  After running this you could do something like this to see it’s setting on a table (here the good old EMP table).

SQL> DECLARE
  2   V_PVALUE VARCHAR2(100);
  3  BEGIN
  4   V_PVALUE := DBMS_STATS.GET_PREFS('STALE_PERCENT','OP','EMP');
  5   DBMS_OUTPUT.PUT_LINE('STALE PERCENT IS '||V_PVALUE);
  6  END;
  7  /
STALE PERCENT IS 30

You can also set the global preferences if you like.  Setting these will only take affect when there isn’t a setting at the table level, at create time.  Let’s take a look at how this works.  So if you were to do this:

EXEC DBMS_STATS.SET_GLOBAL_PREFS('STALE_PERCENT','42');

And run the little block from above again you’d see that the values stays at 30 for the EMP table.  Now if you did this to the EMP table:

EXEC DBMS_STATS.SET_TABLE_PREFS('OP','EMP','STALE_PERCENT',NULL);

And checking the STATLE_PERCENT on EMP you’ll see its set to:

STALE PERCENT IS 10

Which is the Oracle system default.  OK, so what did setting the global preference really do?  Now create a new table as a copy of EMP.

SQL> create table emp2 as select * from emp;

Now check the STALE_PERCENT on EMP2 and you’ll see:

SQL> DECLARE
  2   V_PVALUE VARCHAR2(100);
  3  BEGIN
  4   V_PVALUE := DBMS_STATS.GET_PREFS('STALE_PERCENT','OP','EMP2');
  5   DBMS_OUTPUT.PUT_LINE('STALE PERCENT IS '||V_PVALUE);
  6  END;
  7  /
STALE PERCENT IS 42

So what’s the take-away here?  One setting the preferences is easy to do for a single table, all tables in a schema and the entire database.  And two you can have them set for new tables as well (global).  The thing to keep in mind is that if you set a preference to NULL, it will take on the Oracle default value not the setting at the global level.  

Tuesday, July 8, 2014

Just where is my trace file?

This is pretty cool.

There is a view you can use to see some settings from a diagnostic point of view: V$DIAG_INFO

SQL> DESC V$DIAG_INFO
 NAME                 NULL?    TYPE
 -------------------- -------- --------------
 INST_ID                       NUMBER
 NAME                          VARCHAR2(64)
 VALUE                         VARCHAR2(512)
 CON_ID                        NUMBER


It's a fairly small view with 11 rows in it (in  my 12.1.0.1 database on my laptop at least).  The name column shows the kind of information stored:

SQL> select name from v$diag_info;

NAME
----------------------------------
Diag Enabled
ADR Base
ADR Home
Diag Trace
Diag Alert
Diag Incident
Diag Cdump
Health Monitor
Default Trace File
Active Problem Count
Active Incident Count


Of interest to this post is Default Trace File.  When selecting this it shows not just the trace file name but also the entire path.  On my little laptop this is what I have:

SQL> SELECT VALUE FROM V$DIAG_INFO WHERE NAME='Default Trace File';

VALUE
--------------------------------------------------------------------
C:\APP\ORACLE\diag\rdbms\hotsos\hotsos\trace\hotsos_ora_9616.trc

And if I set TRACEFILE_IDENTIFIER that will get reflected in the name:

SQL> ALTER SESSION SET TRACEFILE_IDENTIFIER = 'RVD_TEST';
Session altered.
SQL> SELECT VALUE FROM V$DIAG_INFO WHERE NAME='Default Trace File';

VALUE
--------------------------------------------------------------------------
C:\APP\ORACLE\diag\rdbms\hotsos\hotsos\trace\hotsos_ora_9616_RVD_TEST.trc

Nice. 

Monday, June 30, 2014

It's a bug.

Just wanted to let ya all know that the delayed block cleanout issue I logged with Oracle last December is an official bug:

Bug 17912562 : DELAYED BLOCK CLEANOUT NOT WORKING IN12C




Friday, June 6, 2014

Memory Used in a Sort

Found a little issue with the data displayed by DBMS_XPLAN.DISPLAY_CURSOR when it comes to the Used-Tmp column. This is certainly something to be aware of when you look at that column in the output. 


Take a look at this simple statement:


SQL> SELECT PLAN_TABLE_OUTPUT
  2    FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR
  3    ('g4tr51k11z5a0',0,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  g4tr51k11z5a0, child number 0
-------------------------------------
select * from big_tab order by owner

Plan hash value: 3765827574

-----------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | Writes |  OMem |  1Mem | Used-Mem | Used-Tmp|
-----------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |      1 |        |   2868K|00:00:46.50 |   48224 |  97395 |  49190 |       |       |          |         |
|   1 |  SORT ORDER BY     |         |      1 |   2868K|   2868K|00:00:46.50 |   48224 |  97395 |  49190 |   432M|  6863K|  100M (1)|     385K|
|   2 |   TABLE ACCESS FULL| BIG_TAB |      1 |   2868K|   2868K|00:00:04.26 |   48215 |  48205 |      0 |       |       |          |         |
-----------------------------------------------------------------------------------------------------------------------------------------------

Looking at the Used-Tmp column it appears this didn’t use much temp space, only 385K for a sort that has a Used-Mem of 100M, hey that’s really great!  But how can that be?  Well it can’t is the short answer.  There appears to be a bug here. 

The data used by this function is coming from V$SQL_PLAN_STATISTICS_ALL.  If we look at that view for this query we can see that value is in the LAST_TEMPSEG_SIZE column (394240/1024 = 385K).  The documentation says that all these column are in bytes.  But It certainly appears that LAST_TEMPSEG_SIZE isn’t.

SQL> SELECT SQL_ID, ESTIMATED_OPTIMAL_SIZE, ESTIMATED_ONEPASS_SIZE, LAST_MEMORY_USED, LAST_TEMPSEG_SIZE
  2  FROM v$sql_plan_statistics_all
  3  WHERE SQL_ID = 'g4tr51k11z5a0';

SQL_ID        ESTIMATED_OPTIMAL_SIZE ESTIMATED_ONEPASS_SIZE LAST_MEMORY_USED LAST_TEMPSEG_SIZE
------------- ---------------------- ---------------------- ---------------- -----------------
g4tr51k11z5a0
g4tr51k11z5a0              453454848                7027712        104879104            394240
g4tr51k11z5a0

How is it that I can say with any level of certainty that this isn’t in bytes?  One thing is it certainly seems odd that a sort that used 100M would only use a couple hundred bytes of temp space.  But also if we look at a different view V$SQL_WORKAREA we see:

SQL> SELECT SQL_ID, ESTIMATED_OPTIMAL_SIZE, ESTIMATED_ONEPASS_SIZE, LAST_MEMORY_USED, LAST_TEMPSEG_SIZE
  2  FROM V$SQL_WORKAREA
  3  WHERE SQL_ID = 'g4tr51k11z5a0';

SQL_ID        ESTIMATED_OPTIMAL_SIZE ESTIMATED_ONEPASS_SIZE LAST_MEMORY_USED LAST_TEMPSEG_SIZE
------------- ---------------------- ---------------------- ---------------- -----------------
g4tr51k11z5a0              453454848                7027712        104879104         403701760

This appears to be in bytes.  Note that 40370176/1024 = 394240 the number we saw in V$SQL_PLAN_STATISTICS_ALL.  

So it sure looks like the view V$SQL_PLAN_STATISTICS_ALL is already converting the temp segment size into Kilobytes, but the function DBMS_XPLAN.DISPLAY_CURSOR thinks it’s in bytes and covert it to Kilobytes.  This actually makes it Megabytes, so it wasn’t using 385K it was using 385M.

Wednesday, June 4, 2014

ANSI or Classic?



I’m an old dog and I have yet to embrace ANSI syntax for writing SQL.  There is coming a day that I will likely have to move to ANSI and it appears to be coming sooner rather than later.  So I’ve started on this path.  While look around on the web the other day I stumbled on to a post saying that the old syntax is “bad” and the ANSI syntax is better (http://www.orafaq.com/node/2618).  So just for fun I pulled the queries used to see what the difference was.  

There isn’t any.

The author had stated that the classic syntax “is generating a cartesian product, and then filtering the result set with a predicate.”

The reality is that both the classic and the ANSI syntax create exactly the same plan.  Running both I then looked at V$SQL to see the plan created:

SQL> SELECT *
  2  FROM   emp,
  3         dept
  4  WHERE  emp.deptno = dept.deptno
  5         AND dept.dname = 'SALES';

SQL> SELECT *
  2  FROM   emp
  3         join dept USING(deptno)
  4  WHERE  dname = 'SALES';

SQL> select sql_id, child_number,plan_hash_value, sql_text from v$sql where sql_text like 'SELECT * FROM   emp%'

SQL_ID           CHILD_NUMBER PLAN_HASH_VALUE
------------- --------------- ---------------
SQL_TEXT
----------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
4ug91aycb85jh               0      1688427947
SELECT * FROM   emp        join dept USING(deptno) WHERE  dname = 'SALES'

6snrnnz2qfwms               0      1688427947
SELECT * FROM   emp,        dept WHERE  emp.deptno = dept.deptno        AND dept.dname = 'SALES'

Notice they both have the exact same PLAN_HASH_VALUE, this means they both use the exact same plan:
SQL> SELECT PLAN_TABLE_OUTPUT
  2      FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR
  3      ('4ug91aycb85jh',0,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
SQL_ID  4ug91aycb85jh, child number 0
-------------------------------------
SELECT * FROM   emp        join dept USING(deptno) WHERE  dname =
'SALES'

Plan hash value: 1688427947

-------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              |      1 |        |      6 |00:00:00.01 |      12 |
|   1 |  NESTED LOOPS                |              |      1 |        |      6 |00:00:00.01 |      12 |
|   2 |   NESTED LOOPS               |              |      1 |      4 |      6 |00:00:00.01 |      10 |
|*  3 |    TABLE ACCESS FULL         | DEPT         |      1 |      1 |      1 |00:00:00.01 |       8 |
|*  4 |    INDEX RANGE SCAN          | EMP_DEPT_IDX |      1 |      5 |      6 |00:00:00.01 |       2 |
|   5 |   TABLE ACCESS BY INDEX ROWID| EMP          |      6 |      4 |      6 |00:00:00.01 |       2 |
-------------------------------------------------------------------------------------------------------

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

   3 - filter("DEPT"."DNAME"='SALES')
   4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")
       filter("EMP"."DEPTNO" IS NOT NULL)

SQL> SELECT PLAN_TABLE_OUTPUT
  2      FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR
  3      ('6snrnnz2qfwms',0,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
SQL_ID  6snrnnz2qfwms, child number 0
-------------------------------------
SELECT * FROM   emp,        dept WHERE  emp.deptno = dept.deptno
AND dept.dname = 'SALES'

Plan hash value: 1688427947

-------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              |      1 |        |      6 |00:00:00.01 |      12 |
|   1 |  NESTED LOOPS                |              |      1 |        |      6 |00:00:00.01 |      12 |
|   2 |   NESTED LOOPS               |              |      1 |      4 |      6 |00:00:00.01 |      10 |
|*  3 |    TABLE ACCESS FULL         | DEPT         |      1 |      1 |      1 |00:00:00.01 |       8 |
|*  4 |    INDEX RANGE SCAN          | EMP_DEPT_IDX |      1 |      5 |      6 |00:00:00.01 |       2 |
|   5 |   TABLE ACCESS BY INDEX ROWID| EMP          |      6 |      4 |      6 |00:00:00.01 |       2 |
-------------------------------------------------------------------------------------------------------

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

   3 - filter("DEPT"."DNAME"='SALES')
   4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")
       filter("EMP"."DEPTNO" IS NOT NULL)

Is this going to be true for all statement?  I certainly doubt it, there are always exceptions.  My inclination is that switching between the two syntax types is likely to produce the same plan more times than not.    Of course a test is worth 1000 opinions. 

Database used for this test was:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Running on Windows 7 Professional.