Neil Chandler's DB Blog

Oracle Table Prefs

29/09/2019 2 Comments

V. Quick post for me really: Some SQL to tell me all of the Table Prefs for any specific table (All Oracle 19 prefs)
You could always just query DBA_TAB_STAT_PREFS

select * from user_tab_stat_prefs where table_name = '&&TABLE';

But that only tells you what you have explicitly set for a table, which will override any GLOBAL prefs. The order that prefs are used is TABLE -> GLOBAL -> DEFAULT. If some DBA has changed a global pref, how would you know that it’s affecting this particular table?

Here’s a quick piece of SQL to do that, which formats nice and readably (for me!)

undefine TABLE
prompt Enter Table Name 
prompt &&TABLE

select rpad('ANDV_ALGO_INTERNAL_OBSERVE : ',42)||dbms_stats.get_prefs(pname=>'ANDV_ALGO_INTERNAL_OBSERVE', tabname=>'&&TABLE') prefs_for__&&TABLE FROM dual UNION ALL
select rpad('APPROXIMATE_NDV : ',42)||dbms_stats.get_prefs(pname=>'APPROXIMATE_NDV', tabname=>'&&TABLE') prefs_for__&&TABLE FROM dual UNION ALL
select rpad('APPROXIMATE_NDV_ALGORITHM : ',42)||dbms_stats.get_prefs(pname=>'APPROXIMATE_NDV_ALGORITHM', tabname=>'&&TABLE') prefs_for__&&TABLE FROM dual UNION ALL
select rpad('AUTO_STAT_EXTENSIONS : ',42)||dbms_stats.get_prefs(pname=>'AUTO_STAT_EXTENSIONS', tabname=>'&&TABLE') prefs_for__&&TABLE FROM dual UNION ALL
select rpad('AUTOSTATS_TARGET : ',42)||dbms_stats.get_prefs(pname=>'AUTOSTATS_TARGET', tabname=>'&&TABLE') prefs_for__&&TABLE FROM dual UNION ALL
select rpad('AUTO_TASK_INTERVAL : ',42)||dbms_stats.get_prefs(pname=>'AUTO_TASK_INTERVAL', tabname=>'&&TABLE') prefs_for__&&TABLE FROM dual UNION ALL
select rpad('AUTO_TASK_MAX_RUN_TIME : ',42)||dbms_stats.get_prefs(pname=>'AUTO_TASK_MAX_RUN_TIME', tabname=>'&&TABLE') prefs_for__&&TABLE FROM dual UNION ALL
select rpad('AUTO_TASK_STATUS : ',42)||dbms_stats.get_prefs(pname=>'AUTO_TASK_STATUS', tabname=>'&&TABLE') prefs_for__&&TABLE FROM dual UNION ALL
select rpad('CASCADE : ',42)||dbms_stats.get_prefs(pname=>'CASCADE', tabname=>'&&TABLE') prefs_for__&&TABLE FROM dual UNION ALL
select rpad('CONCURRENT : ',42)||dbms_stats.get_prefs(pname=>'CONCURRENT', tabname=>'&&TABLE') prefs_for__&&TABLE FROM dual UNION ALL
select rpad('COORDINATOR_TRIGGER_SHARD : ',42)||dbms_stats.get_prefs(pname=>'COORDINATOR_TRIGGER_SHARD', tabname=>'&&TABLE') prefs_for__&&TABLE FROM dual UNION ALL
select rpad('DEBUG : ',42)||dbms_stats.get_prefs(pname=>'DEBUG', tabname=>'&&TABLE') prefs_for__&&TABLE FROM dual UNION ALL
select rpad('DEGREE : ',42)||dbms_stats.get_prefs(pname=>'DEGREE', tabname=>'&&TABLE') prefs_for__&&TABLE FROM dual UNION ALL
select rpad('ENABLE_HYBRID_HISTOGRAMS : ',42)||dbms_stats.get_prefs(pname=>'ENABLE_HYBRID_HISTOGRAMS', tabname=>'&&TABLE') prefs_for__&&TABLE FROM dual UNION ALL
select rpad('ENABLE_TOP_FREQ_HISTOGRAMS : ',42)||dbms_stats.get_prefs(pname=>'ENABLE_TOP_FREQ_HISTOGRAMS', tabname=>'&&TABLE') prefs_for__&&TABLE FROM dual UNION ALL
select rpad('ESTIMATE_PERCENT : ',42)||dbms_stats.get_prefs(pname=>'ESTIMATE_PERCENT', tabname=>'&&TABLE') prefs_for__&&TABLE FROM dual UNION ALL
select rpad('GATHER_AUTO : ',42)||dbms_stats.get_prefs(pname=>'GATHER_AUTO', tabname=>'&&TABLE') prefs_for__&&TABLE FROM dual UNION ALL
select rpad('GATHER_SCAN_RATE : ',42)||dbms_stats.get_prefs(pname=>'GATHER_SCAN_RATE', tabname=>'&&TABLE') prefs_for__&&TABLE FROM dual UNION ALL
select rpad('GLOBAL_TEMP_TABLE_STATS : ',42)||dbms_stats.get_prefs(pname=>'GLOBAL_TEMP_TABLE_STATS', tabname=>'&&TABLE') prefs_for__&&TABLE FROM dual UNION ALL
select rpad('GRANULARITY : ',42)||dbms_stats.get_prefs(pname=>'GRANULARITY', tabname=>'&&TABLE') prefs_for__&&TABLE FROM dual UNION ALL
select rpad('INCREMENTAL : ',42)||dbms_stats.get_prefs(pname=>'INCREMENTAL', tabname=>'&&TABLE') prefs_for__&&TABLE FROM dual UNION ALL
select rpad('INCREMENTAL_INTERNAL_CONTROL : ',42)||dbms_stats.get_prefs(pname=>'INCREMENTAL_INTERNAL_CONTROL', tabname=>'&&TABLE') prefs_for__&&TABLE FROM dual UNION ALL
select rpad('INCREMENTAL_LEVEL : ',42)||dbms_stats.get_prefs(pname=>'INCREMENTAL_LEVEL', tabname=>'&&TABLE') prefs_for__&&TABLE FROM dual UNION ALL
select rpad('INCREMENTAL_STALENESS : ',42)||dbms_stats.get_prefs(pname=>'INCREMENTAL_STALENESS', tabname=>'&&TABLE') prefs_for__&&TABLE FROM dual UNION ALL
select rpad('JOB_OVERHEAD : ',42)||dbms_stats.get_prefs(pname=>'JOB_OVERHEAD', tabname=>'&&TABLE') prefs_for__&&TABLE FROM dual UNION ALL
select rpad('JOB_OVERHEAD_PERC : ',42)||dbms_stats.get_prefs(pname=>'JOB_OVERHEAD_PERC', tabname=>'&&TABLE') prefs_for__&&TABLE FROM dual UNION ALL
select rpad('MAINTAIN_STATISTICS_STATUS : ',42)||dbms_stats.get_prefs(pname=>'MAINTAIN_STATISTICS_STATUS', tabname=>'&&TABLE') prefs_for__&&TABLE FROM dual UNION ALL
select rpad('METHOD_OPT : ',42)||dbms_stats.get_prefs(pname=>'METHOD_OPT', tabname=>'&&TABLE') prefs_for__&&TABLE FROM dual UNION ALL
select rpad('NO_INVALIDATE : ',42)||dbms_stats.get_prefs(pname=>'NO_INVALIDATE', tabname=>'&&TABLE') prefs_for__&&TABLE FROM dual UNION ALL
select rpad('OPTIONS : ',42)||dbms_stats.get_prefs(pname=>'OPTIONS', tabname=>'&&TABLE') prefs_for__&&TABLE FROM dual UNION ALL
select rpad('PREFERENCE_OVERRIDES_PARAMETER : ',42)||dbms_stats.get_prefs(pname=>'PREFERENCE_OVERRIDES_PARAMETER', tabname=>'&&TABLE') prefs_for__&&TABLE FROM dual UNION ALL
select rpad('PUBLISH : ',42)||dbms_stats.get_prefs(pname=>'PUBLISH', tabname=>'&&TABLE') prefs_for__&&TABLE FROM dual UNION ALL
select rpad('ROOT_TRIGGER_PDB : ',42)||dbms_stats.get_prefs(pname=>'ROOT_TRIGGER_PDB', tabname=>'&&TABLE') prefs_for__&&TABLE FROM dual UNION ALL
select rpad('SCAN_RATE : ',42)||dbms_stats.get_prefs(pname=>'SCAN_RATE', tabname=>'&&TABLE') prefs_for__&&TABLE FROM dual UNION ALL
select rpad('STALE_PERCENT : ',42)||dbms_stats.get_prefs(pname=>'STALE_PERCENT', tabname=>'&&TABLE') prefs_for__&&TABLE FROM dual UNION ALL
select rpad('STAT_CATEGORY : ',42)||dbms_stats.get_prefs(pname=>'STAT_CATEGORY', tabname=>'&&TABLE') prefs_for__&&TABLE FROM dual UNION ALL
select rpad('SYS_FLAGS : ',42)||dbms_stats.get_prefs(pname=>'SYS_FLAGS', tabname=>'&&TABLE') prefs_for__&&TABLE FROM dual UNION ALL
select rpad('TABLE_CACHED_BLOCKS : ',42)||dbms_stats.get_prefs(pname=>'TABLE_CACHED_BLOCKS', tabname=>'&&TABLE') prefs_for__&&TABLE FROM dual UNION ALL
select rpad('TRACE : ',42)||dbms_stats.get_prefs(pname=>'TRACE', tabname=>'&&TABLE') prefs_for__&&TABLE FROM dual UNION ALL
select rpad('WAIT_TIME_TO_UPDATE_STATS : ',42)||dbms_stats.get_prefs(pname=>'WAIT_TIME_TO_UPDATE_STATS', tabname=>'&&TABLE') prefs_for__&&TABLE
FROM dual;

Sample Output


PREFS_FOR__INTERVAL_TAB
------------------------
ANDV_ALGO_INTERNAL_OBSERVE :              FALSE
APPROXIMATE_NDV :                         TRUE
APPROXIMATE_NDV_ALGORITHM :               REPEAT OR HYPERLOGLOG
AUTO_STAT_EXTENSIONS :                    OFF
AUTOSTATS_TARGET :                        AUTO
AUTO_TASK_INTERVAL :                      900
AUTO_TASK_MAX_RUN_TIME :                  3600
AUTO_TASK_STATUS :                        OFF
CASCADE :                                 DBMS_STATS.AUTO_CASCADE
CONCURRENT :                              OFF
COORDINATOR_TRIGGER_SHARD :               FALSE
DEBUG :                                   0
DEGREE :                                  NULL
ENABLE_HYBRID_HISTOGRAMS :                3
ENABLE_TOP_FREQ_HISTOGRAMS :              3
ESTIMATE_PERCENT :                        DBMS_STATS.AUTO_SAMPLE_SIZE
GATHER_AUTO :                             AFTER_LOAD
GATHER_SCAN_RATE :                        HADOOP_ONLY
GLOBAL_TEMP_TABLE_STATS :                 SESSION
GRANULARITY :                             AUTO
INCREMENTAL :                             TRUE
INCREMENTAL_INTERNAL_CONTROL :            TRUE
INCREMENTAL_LEVEL :                       PARTITION
INCREMENTAL_STALENESS :                   USE_STALE_PERCENT,USE_LOCKED_STATS
JOB_OVERHEAD :                            -1
JOB_OVERHEAD_PERC :                       1
MAINTAIN_STATISTICS_STATUS :              FALSE
METHOD_OPT :                              FOR ALL COLUMNS SIZE 50
NO_INVALIDATE :                           DBMS_STATS.AUTO_INVALIDATE
OPTIONS :                                 GATHER
PREFERENCE_OVERRIDES_PARAMETER :          FALSE
PUBLISH :                                 TRUE
ROOT_TRIGGER_PDB :                        FALSE
SCAN_RATE :                               0
STALE_PERCENT :                           10
STAT_CATEGORY :                           OBJECT_STATS, REALTIME_STATS
SYS_FLAGS :                               1
TABLE_CACHED_BLOCKS :                     1
TRACE :                                   0
WAIT_TIME_TO_UPDATE_STATS :               15

Filed under Administration, Performance and Tuning Tagged with APPROXIMATE_NDV, dba_tab_stat_prefs, dbms_stats, dbms_stats.get_prefs, ENABLE_HYBRID_HISTOGRAMS, ESTIMATE_PERCENT, GRANULARITY, INCREMENTAL_STALENESS, trace, user_tab_stat_prefs

Parallel Madness

19/03/2019 14 Comments

usain-bolt-1 I’ve noticed at a few clients with data warehouses recently that the Developers and, upon occasion, Business Users have a real fondness for hinting the SQL they are producing with one particular hint. PARALLEL.

As any fule kno, this IS the magic go-faster hint. PARALLEL(2) is obviously twice as fast as serial execution. PARALLEL(4) is amazing and PARALLEL(64) like Usain Bolt on Red Bull.

The problem is that, like all database features, parallel query comes with a cost.

When you specify /*+ PARALLEL(n) */ you are telling the optimizer that it has a lot more resources to use to complete this particular query. Not a single thread but many. PARALLEL(10) will use 21 processes to complete its execution – 20 Parallel Execution Server (10 producers, 10 consumers) and a coordinator (which is your connections shadow process) which will deal with any aspects of the parallel plan which cannot be parallelised.

Allowed free reign to use PARALLEL, devs and users will quickly consume all of the resources available on a given server, causing contentions which will inevitably slow down the overall execution of every piece of code executing on there. To illustrate this, I’d like to use an example I came across a while ago to show how excess PARALLEL of a single statement can be problematic itself.

Lets say I have a single server with 16 cores, lots of memory and a decent SSD array so the problem will centre around the CPU. Inevitably your 16 cores will be hyperthreaded. This then looks to Oracle like you have 32 cores. Whilst Oracle knows you have 16 hyperthreaded cores, you get CPU_COUNT=32

NOTE: 16 cores hyperthreaded DO NOT have the power of 32 cores, especially when dealing with databases. Some database workloads are actually WORSE with hyperthreading enabled (e.g. Data Warehouse systems on SQL Server). Inevitably the server admins will have enabled it unless you can provide cast-iron evidence to have it disabled.

I have a statement which the users are complaining about. It starts with the following code: SELECT /*+ PARALLEL */ (complex multi-table join)

So what does this unrestricted (and therefore DEFAULT!) degree of parallelism (DOP) do in this case?
The default DOP is defined as PARALLEL_THREADS_PER_CPU x CPU_COUNT=2 x 32 = PARALLEL(64)
On RAC it is PARALLEL_THREADS_PER_CPU x CPU_COUNT x INSTANCE_COUNT!

Lets have a look at the ACTIVITY of this PARALLEL(64) query:

You can see from the screenshot that Oracle knows there are 16 cores but it has gone PARALLEL(64), using 128 parallel exection slaves and fully expecting to have the available resources to run PARALLEL(64) efficiently. The execution plan is calculated around this assumption. There are 64 parallel execution slaves attempting to work on this at the same time. It’s worth looking at the metrics associated with this query.

Peaks of 2GB/s disk, 140GB of TEMP and 32 CPU’s.

The query took 36.9 minutes to complete.

I had the query changed to inject a modicum of realism into the available resources at the time of the run, and restricted the DOP to PARALLEL(8).

Oracle is restricted to the limited amount of resource, which is availble. The execution plan is different, to reflect the lower amount of available resources. Looking at the metrics:

Peaks of 1GB/s, 3GB of TEMP and 12 CPU’s.

The query took 10.3 minutes to complete. 3 times quicker!

It is worth noting that testing the query in isolation with PARALLEL(16) took 7 minutes to complete, but that DOP would have resource-starved the server as a whole causing everything else currently executing to slow down, and was discounted as an option.

With PARALLEL, less can be better.
Using PARALLEL for everything can be counter-productive.
Co-ordinating PARALLEL across multiple RAC nodes can be disasterous without careful design to take advantage of this feature (using PARALLEL_LOCAL_FORCE=TRUE will restrict parallel processing to a single node). Oracle recommend you don’t do this. Other opinions are available and I generally recommend setting this to TRUE.

We have a limited amount of resources on our servers. Working within those resource limitations will provide substantial benefits.

Before using Parallel processing, I’d recommend thoroughly reading the VLDB and Partitioning Guide appropriate to your database release!

If you use PARALLEL:

Use Resource Manager to restrict DOP appropriately for different groups of users.

Consider setting PARALLEL_LOCAL_FORCE=TRUE
Consider setting PARALLEL_THREAD_PER_CPU=1, especially where you have hyperthreading enabled.
Consider your overall workload, not just the SQL you are working with right now.

Filed under Administration, Performance and Tuning Tagged with CPU_COUNT, DOP, INSTANCE_COUNT, oracle, PARALLEL, PARALLEL_LOCAL_FORCE, PARALLEL_THREADS_PER_CPU, performance

Where’s my Oracle SQL Plan Baseline?

18/03/2019 2 Comments

No so long ago I was having fun creating SQL Plan Baselines in a old 11.2.0.3 database due to be decomissioned but which needs to keep running for a while (no doubt several years) – so minimal time/money to be expended on it. Then, one day, I couldn’t create a baseline and needed to figure out why…

We get the occasional painful plan change, so pinning down an acceptable historic plan using a Baseline is becoming a regular occurrence. The standard route for this is:

1. Notice plan isn’t good and that we have been running with a good plan historically
2. Identify an plan hash value which is acceptable
3. Load it from the library cache if that plan is in there (unlikely)

[ Kerry Osborne has a useful blog post/script for this so I won’t reproduce here: http://kerryosborne.oracle-guy.com/2012/03/displaying-sql-baseline-plans/ ]

4. If not, create a SQL Tuning Set from AWR with that specific plan and convert the SQL Tuning Set into a Baseline. You will need the begin-and-end snap id’s containing the plan, the sql_id and the plan_hash_value for the specific plan you want to baseline:

Please ensure you have suitable licensing before running any of the following code!

declare
 baseline_ref_cur DBMS_SQLTUNE.SQLSET_CURSOR;
begin
 DBMS_SQLTUNE.CREATE_SQLSET('NEIL');
 open baseline_ref_cur for
 select VALUE(p) from table(
 DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(&begin_snap_id, &end_snap_id,'sql_id='||CHR(39)||'4jcxvz3adqbs2'||CHR(39)||'and plan_hash_value=1905606778',NULL,NULL,NULL,NULL,NULL,NULL,'ALL')) p;
 DBMS_SQLTUNE.LOAD_SQLSET('NEIL', baseline_ref_cur);
end;
/

Did we get it?

select * from dba_sqlset_statements where sqlset_name = 'NEIL';

SQLSET_NAME, SQLSET_OWNER, SQLSET_ID, SQL_ID,       FORCE_MATCHING_SIGNATURE, SQL_TEXT,                                   PARSING_SCHEMA_NAME, PLAN_HASH_VALUE ...
----------------------------------------------------------------------------------------------------------------------------------------------------------
NEIL         NEIL_DBA             35  4jcxvz3adqbs2 6134983393191283611       INSERT WHEN CNT = 1 THEN INTO SOME_TABLE...  APP                  1905606778      ...

Cool! I have captured the SQL. Lets create the baseline using: dbms_spm.load_plans_from_sqlset

declare
cnt number;
begin
 cnt := dbms_spm.load_plans_from_sqlset(sqlset_name=>'NEIL',SQLSET_OWNER=>'NEIL_DBA');
 dbms_output.put_line('Plans Loaded : '||to_char(cnt));
end;
/
... and I get ...

Plans Loaded : 0

Zero plans? So what plans are in there? Has my plan actually appeared?

select SQL_HANDLE,PLAN_NAME,CREATOR,ORIGIN,PARSING_SCHEMA_NAME, ENABLED,ACCEPTED,REPRODUCED,CREATED
  from dba_sql_plan_baselines where origin like 'MANUAL-LOAD%' order by created desc;

SQL_HANDLE           PLAN_NAME                      CREATOR   ORIGIN      PARSING_SCHEMA_NAME  ENABLED  ACCEPTED  REPRODUCED  CREATED
-----------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_23829f7bf3712438 SQL_PLAN_270nzggtr291s1a6ce30c NEIL_DBA  MANUAL-LOAD APP                  YES      YES       YES         2018-12-31 00.00.00.000000000

No! But how do I prove that? Baselines don’t have the SQL ID associated with them?

Here’s some code from Marcin Przepiorowski [blog post here: http://oracleprof.blogspot.com/2011/07/how-to-find-sqlid-and-planhashvalue-in.html ] which will take the SQL_Handle and display the SQL ID and Plan Hash Value (you may need an explicit grant to use DBMS_CRYPTO in your PDB for this to work):

For SQL Handle: SQL_23829f7bf3712438 

declare
v_sqlid VARCHAR2(13);
v_num number;
BEGIN
 dbms_output.put_line('SQL_ID '||' '|| 'PLAN_HASH_VALUE' || ' ' || 'SQL_HANDLE ' || ' ' || 'PLAN_NAME');
 dbms_output.put_line('-------------'||' '|| '---------------' || ' ' || '------------------------------' || ' ' || '--------------------------------');
 for a in (select sql_handle, plan_name, trim(substr(g.PLAN_TABLE_OUTPUT,instr(g.PLAN_TABLE_OUTPUT,':')+1)) plan_hash_value, sql_text
from (select t.*, c.sql_handle, c.plan_name, c.sql_text from dba_sql_plan_baselines c, table(dbms_xplan.DISPLAY_SQL_PLAN_BASELINE(c.sql_handle, c.plan_name)) t
where c.sql_handle = '&sql_handle') g
where PLAN_TABLE_OUTPUT like 'Plan hash value%') loop
  v_num := to_number(sys.UTL_RAW.reverse(sys.UTL_RAW.SUBSTR(dbms_crypto.hash(src => UTL_I18N.string_to_raw(a.sql_text || chr(0),'AL32UTF8'), typ => 2),9,4)) || sys.UTL_RAW.reverse(sys.UTL_RAW.SUBSTR(dbms_crypto.hash(src => UTL_I18N.string_to_raw(a.sql_text || chr(0),'AL32UTF8'), typ => 2),13,4)),RPAD('x', 16, 'x'));
  v_sqlid := '';
  FOR i IN 0 .. FLOOR(LN(v_num) / LN(32))
  LOOP
   v_sqlid := SUBSTR('0123456789abcdfghjkmnpqrstuvwxyz',FLOOR(MOD(v_num / POWER(32, i), 32)) + 1,1) || v_sqlid;
  END LOOP;
 dbms_output.put_line(v_sqlid ||' ' || rpad(a.plan_hash_value,15) || ' ' || rpad(a.sql_handle,30) || ' ' || rpad(a.plan_name,30));
end loop;
end;
/

SQL_ID        PLAN_HASH_VALUE SQL_HANDLE                     PLAN_NAME
------------- --------------- ------------------------------ --------------------------------
7f4n59twq8mrj 3036703685      SQL_23829f7bf3712438           SQL_PLAN_270nzggtr291s1a6ce30c

That baseline is definitely not mine. It’s for the wrong SQL ID!
The “Plans Loaded” message was correct when it said “0“!

Why? There was no error message, no output other than the number of plans loaded. That sucks dbms_spm!

I need to trace it. Do that by using dbms_spm.configure

(thank you Timur Akhmadeev for helping me! It’s not easy to google/MOS how to do this, hence this post so I don’t forget again!)

declare
cnt number;
begin
 dbms_spm.configure('spm_tracing',1);
 cnt := dbms_spm.load_plans_from_sqlset(sqlset_name=>'NEIL',SQLSET_OWNER=>'NEIL_DBA');
 dbms_output.put_line('Plans Loaded : '||to_char(cnt));
 dbms_spm.configure('spm_tracing',0);
end;
/

Looking in the trace file, it tells you what went wrong:

*** 2019-01-01 12:00:00.007
load sts: STS=NEIL, owner=NEIL_DBA
load sts: cursor opened
load sts: sql_id=4jcxvz3adqbs2 phv=1905606778
load sts: plan has empty outline, skipping it
load sts: plans total=0 plans loaded=0

So why does my plan have an empty outline?

The SQL_TEXT began “INSERT WHEN CNT = 1 THEN INTO SOME_TABLE… “.

It’s a multi-table insert, inserting into different tables depending upon a condition.
Baselines for Multi-table Inserts are NOT supported by SPM.

https://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_9014.htm#SQLRF01604

I hope this helps you with your baseline creation troubleshooting!

Having baseline creation problems, you should check MOS article 789520.1

Filed under Administration, Performance and Tuning, spm Tagged with awr, baseline, create, DBMS_SPM, dbms_sqltune, execution, library cache, oracle, plan, spm, sql_id, trace

Proactive Oracle Support?

01/03/2019 Leave a comment

You may not be aware but Oracle have been talking about their new ProActive support with user groups like the UKOUG. It’s basically trying to show you best practices you should be implementing before stuff goes wrong, and many other useful bits of information.

It covers a lot of areas and is really worth a look, whether you’re using Oracle Database, Linux, MySQL, EBS, JDEdwards or indeed most Oracle products.

The open URL for headline information is: https://www.oracle.com/GetProactive

The place to look is at MOS document: 432.1

https://support.oracle.com/epmos/faces/DocumentDisplay?id=432.1

However, I do have to wonder just how up to date some of the content is given the “Trending” topic in the Oracle Database area is about planning your move to Oracle 12.1. Really Oracle? Really? You might want to dedicate a bit of time to getting this changed to 18 or 19?

This slideshow requires JavaScript.

Filed under Administration Tagged with 432.1, database, ebs, jde, mysql, oracle, proactive, support

Oracle 19C New Feature Availability

15/02/2019 Leave a comment

trump-exasperated Since Oracle Open World 2018, Oracle have been trumpetting a few cool new features in the Oracle 19C database, the headline two for administrators being “Automatic Indexing” and “Real-Time Statistics“.

With the release of Oracle 19.2 on Exadata (on-premises – not yet on Cloud!) this week, we also got the documentation released which allow us to answer a very important question: on which platforms will we be able to use these 2 (and other) new awesome features. We may not like the answers – Oracle has decreed that we cannot have them on-premises for SE2, Enterprise Edition or on an ODA.

This is confined purely to Exadata on-prem and the cloud offerings. That’s it. There are no technical reasons why this should be the case – it’s just code – so the restrictions can only be marketing-based (like Hybrid Columnar Compression).

I’m disappointed, but it does mean that the over 80% of Oracle database clients still not living in the cloud should continue to need that aspect of my services!

Oracle Factoring

OracleLicensingFactoring

Filed under Administration, oracle Tagged with 19c, automatic, features, indexing, new, oracle, real-time, statistics

Oracle SQL Monitor not monitoring my SQL

22/01/2019 Leave a comment

I needed to monitor a SQL statement in 11.2.0.3 (the limits mentioned below are the same in 12.1, 12.2, 18.4 and 19C) to determine what is was doing and why it was slow. sql_monitor

Usually I would use SQL Monitor [NOTE: You need to license the Oracle Tuning Pack to use SQL Monitor] for this but the SQL was not appearing in there, despite running for over 5 seconds, and being a parallel SQL (both of which qualify to be included in SQL Monitor). So I asked Twitter why, and thought I’d share the output here.

https://twitter.com/ChandlerDBA/status/1075692070952677376

It was nailed immediately by Jonathan Lewis, with added help from Ivica Arsov. (thank you!)

There is a hidden parameter “_sqlmon_max_planlines” which states that any SQL with a plan in excess of 300 lines should not be monitored (see below for SQLMon hidden parameters – and change them at your own risk, preferably with the backing of an SR from Oracle Support). This execution plan had well over 300 lines. The solution is to change either the session or the system to allow monitoring to happen when the plan is over 300 lines.

e.g.

alter system  set "_sqlmon_max_planlines"=500 scope=memory sid='*';
or
alter session set "_sqlmon_max_planlines"=500;

The negative side effect it that the monitoring will use more resources (primarily memory and CPU), which is why there are default limits on this feature. You might want to change it back when you’re finished to conserve resources.

Note that if you change the system parameter whilst the SQL is running, it will start to monitor the SQL at that point, so you will only get a partial picture of what is taking place, which is less valuable.

select ksppinm, ksppstvl, ksppdesc
  from sys.x$ksppi a, sys.x$ksppsv b
 where a.indx=b.indx
  and lower(ksppinm) like lower('%sqlmon%')
order by ksppinm;

KSPPINM                   KSPPSTVL  KSPPDESC
------------------------- --------- --------------------------------------------------------------------------------
_sqlmon_binds_xml_format  default   format of column binds_xml in [G]V$SQL_MONITOR
_sqlmon_max_plan          640       Maximum number of plans entry that can be monitored. Defaults to 20 per CPU
_sqlmon_max_planlines     300       Number of plan lines beyond which a plan cannot be monitored
_sqlmon_recycle_time      60        Minimum time (in s) to wait before a plan entry can be recycled
_sqlmon_threshold         5         CPU/IO time threshold before a statement is monitored. 0 is disabled

You may also notice a few other parameters in there. The “_sqlmon_recycle_time” hows the amount of time that the SQLMon plan will be guaranteed to be retained. Any retention time after that will be a bonus and depend upon the amount of SQL needing to be monitored. I see monitoring plans disappearing after 2-3 minutes in some systems, so you need to be quick, and you should save the plans down to disk.

save_sqlmon

The mad thing is that I was aware of this restriction before I posted by request for help on Twitter but I’d completely forgotten about it. So here’s the blog post to help me remember!

Filed under Administration, Performance and Tuning Tagged with 11.2, 12.1, 12.2, 18c, 19c, MISSING, monitoring, oracle, PARALLEL, SQLMON, SQLMONITOR

Data Guard Unexpected Lag

03/01/2019 6 Comments

facepalm When configuring a physical standby database for Oracle using Data Guard, you need to create Standby Redo logs to allow the redo to be applied in (near) real time to the Standby. Without standby redo logs, Oracle will wait for an entire Archive Log to be filled and copied across to the standby before it will apply changes, which could take quite a while.

Which leads me to the problem I encountered a while ago, and due to being forgetful, still encounter today when creating standby redo logs in Oracle 12C and 18C.

I have a Primary database with 3 Redo logs, and I have created a Physical Standby database with 3 Redo logs and then 4 Standby Redo logs created as follows:

alter database add standby logfile group 4 '/u01/.../redo_standby04.dbf' size 200M;
alter database add standby logfile group 5 '/u01/.../redo_standby05.dbf' size 200M;
alter database add standby logfile group 6 '/u01/.../redo_standby06.dbf' size 200M;
alter database add standby logfile group 7 '/u01/.../redo_standby07.dbf' size 200M;

And this is what they look like:

SQL> select group#,thread#,sequence#,bytes/1024/1024 MB,status from v$log order by group#;
GROUP#     THREAD#    SEQUENCE#  MB         STATUS
---------- ---------- ---------- ---------- ----------------
1                   1          0        200 UNUSED
2                   1          0        200 UNUSED
3                   1          0        200 UNUSED

SQL> select group#,thread#,sequence#,bytes/1024/1024 MB,status from v$standby_log order by group#;
GROUP#     THREAD#    SEQUENCE#  MB         STATUS
---------- ---------- ---------- ---------- ----------
4                   1        111        200 ACTIVE
5                   0          0        200 UNASSIGNED
6                   0          0        200 UNASSIGNED
7                   0          0        200 UNASSIGNED

SQL> select * from v$logfile order by group#;
GROUP#     STATUS  TYPE    MEMBER 
---------- ------- ------- --------------------------------------------------------------------------------
         1         ONLINE  /u01/.../redo01.dbf
         2         ONLINE  /u01/.../redo02.dbf
         3         ONLINE  /u01/.../redo03.dbf
         4         STANDBY /u01/.../redo_standby01.dbf
         5         STANDBY /u01/.../redo_standby02.dbf
         6         STANDBY /u01/.../redo_standby03.dbf
         7         STANDBY /u01/.../redo_standby04.dbf

So what’s the problem?

Well, Dataguard works as expected for a while – applying change immediately – then it lags for ages and you get an OEM alert that it’s lagging. Then it works fine for a while again and then another lag.

The problem is that when I created the Standby Redo log files, I did not specify the Thread. But why do I need to specify the thread? This is not RAC. It does not have multiple threads for redo – one for each node. We have Thread 1 and that’s it.

When I used to do this in Oracle 11.2, the Standby Redo logs got Thread 1 by default. Always Thread 1.

From Oracle 12.1, and also in 12.2.0.1, 18C (12.2.0.2) and probably 19C (12.2.0.3), Oracle creates the Standby Redo logs with Thread 0 by default, although sometimes it creates them on Thread 1 (although this seems to be only in 12.1 but let me know if you know better). I don’t know why.

Standby Redo log MUST belong to the SAME Thread as the Online Redo logs for them to be used. Having (in this case) 1 x redo on Thread 1 and 3 x Redo on Thread 0, I only have 1 x Standby Redo usable for my online Redo.

The solution is stop the Apply, DROP the Standby Redo on Thread 0, and create new Standby Redo Logs explicity stating Thread 1, and restart the Apply.

dgmgrl> edit database orcl12dr set state='APPLY-OFF';

sql> alter database drop standby logfile          group 4 '/u01/.../redo_standby04.dbf';
sql> alter database add  standby logfile thread 1 group 4 '/u01/.../redo_standby04.dbf' size 200M;

dgmgrl> edit database orcl12dr set state='APPLY-ON';

And all is good with the world.

It is worth saying that MOS note 1595034.1 gets the corrective advice EXACTLY WRONG, with the incorrect snippet reproduced here:

"The standby redo logs can't be used, since they are assigned to thread 0. 
Standby RedoLogs should not be assigned to a specific Thread. 
Please use fallowing statements:
...
SQL> ALTER DATABASE DROP STANDBY LOGFILE GROUP <Group#>; 
and create new SRLs with
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP <Group#> SIZE ;"

No MOS analyst! Standby Redo Logs MUST be assigned to a specific thread. Thread 0 Standby Logs are ignored!

And don’t forget, if you are using RAC you need to have Standby Redo Logs for EVERY thread accessible to EVERY instance, just like the Online Redo logs.

If you have single instance, ensure you specify Thread 1, and ensure you have 1 more Standby Redo log available than Online Redo log.

As a final note on Data Guard, from Oracle 12.1 you really should be using the Data Guard Broker to configure and manage Data Guard. It’s simple and it’s much better and safer than configuring Data Guard manually and has some nice validation and monitoring features too.

Addendum: For clarity, not specifying a Thread means that Oracle is supposed to auto-adjust the thread to match what thread is needed, using the Thread 0 as a pool so you presumably need less redo defined in RAC environment, but it just doesn’t work very well (or not at all) leading to the fast/slow/fast/slow lag symptoms. It’s also a pretty pointless saving as redo logs are small relative to the database size. If you have a LOT of nodes in the cluster meaning a lot of standby redo logs, and they aren’t all committing redo heavily (when they will all need all of their standby redo logs anyway) why do you have so many nodes? And validating the setup will report that you don’t have enough standby redo logs. Looks like a solution to something that isn’t a problem for anyone.

So this is an undocumented problem-introducing enhancement in Oracle 12 solving no practical real-world problem.

I’ll let you know if it is fixed in Oracle 19C.

Here’s some real-world output showing the apply lag with Thread 0 SRL’s, and how it disappears when Thread 1 SRL’s are explicitly created. Please note the units and date. I can’t provide the alert log output in this blog to prove my annotations.

SQL> select * from v$standby_event_histogram order by LAST_TIME_UPDATED;

NAME                       TIME UNIT                  COUNT LAST_TIME_UPDATED        CON_ID
-------------------- ---------- ---------------- ---------- -------------------- ----------
apply lag                    43 seconds                   3 01/03/2019 17:22:38           0
apply lag                     9 minutes                  58 01/03/2019 17:30:54           0
apply lag                    11 minutes                  60 01/03/2019 17:32:55           0
apply lag                    12 minutes                  59 01/03/2019 17:33:54           0
apply lag                    13 minutes                  57 01/03/2019 17:34:55           0
apply lag                    14 minutes                  60 01/03/2019 17:35:55           0
apply lag                    15 minutes                  47 01/03/2019 17:36:42           0
apply lag                    10 minutes                  61 01/03/2019 17:36:44           0
-- First SRL Thread 1 created : 2019-01-03T17:39:43.890329+00:00
apply lag                    27 seconds                   9 01/03/2019 17:45:39           0
apply lag                    30 seconds                   5 01/03/2019 17:45:42           0
apply lag                    31 seconds                   7 01/03/2019 17:45:44           0
apply lag                    32 seconds                   6 01/03/2019 17:45:45           0
apply lag                    33 seconds                  10 01/03/2019 17:45:46           0
apply lag                    34 seconds                   8 01/03/2019 17:45:47           0
apply lag                    35 seconds                   9 01/03/2019 17:45:48           0
apply lag                    36 seconds                   7 01/03/2019 17:45:49           0
apply lag                    37 seconds                   5 01/03/2019 17:45:50           0
apply lag                    38 seconds                   8 01/03/2019 17:45:51           0
apply lag                    39 seconds                   7 01/03/2019 17:45:52           0
apply lag                    40 seconds                   3 01/03/2019 17:45:53           0
apply lag                    41 seconds                   3 01/03/2019 17:45:54           0
apply lag                    42 seconds                   4 01/03/2019 17:45:55           0
apply lag                    44 seconds                   4 01/03/2019 17:45:56           0
apply lag                    45 seconds                   7 01/03/2019 17:45:57           0
apply lag                    46 seconds                   5 01/03/2019 17:45:58           0
apply lag                    47 seconds                   7 01/03/2019 17:45:59           0
apply lag                    48 seconds                   4 01/03/2019 17:46:00           0
apply lag                    49 seconds                   8 01/03/2019 17:46:01           0
apply lag                    50 seconds                   5 01/03/2019 17:46:02           0
apply lag                    51 seconds                   6 01/03/2019 17:46:03           0
apply lag                    52 seconds                   8 01/03/2019 17:46:04           0
apply lag                    53 seconds                   7 01/03/2019 17:46:05           0
apply lag                    54 seconds                   6 01/03/2019 17:46:06           0
apply lag                    55 seconds                  10 01/03/2019 17:46:07           0
apply lag                    56 seconds                  11 01/03/2019 17:46:09           0
apply lag                    57 seconds                   8 01/03/2019 17:46:10           0
apply lag                    58 seconds                   6 01/03/2019 17:46:11           0
apply lag                    59 seconds                   7 01/03/2019 17:46:12           0
-- Final SRL Thread 1 created 2019-01-03T17:51:58.694308+00:00
apply lag                     8 minutes                 103 01/03/2019 17:52:55           0
apply lag                     7 minutes                 120 01/03/2019 17:52:56           0
apply lag                     6 minutes                 130 01/03/2019 17:52:58           0
apply lag                     5 minutes                 180 01/03/2019 17:53:00           0
apply lag                     4 minutes                 181 01/03/2019 17:53:03           0
apply lag                     3 minutes                 183 01/03/2019 17:53:06           0
apply lag                     2 minutes                 260 01/03/2019 17:53:11           0
-- All SRL Thread 0's deleted, then ALTER SYSTEM ARCHIVE LOG issued. 2019-01-03T18:19:33.757994+00:00
-- No more APPLY lag for 3 days (any lag reported below -of a few seconds- is due to other reasons)
apply lag                    23 seconds                  10 01/06/2019 18:14:59           0
apply lag                    29 seconds                   8 01/06/2019 18:15:03           0
apply lag                    26 seconds                  10 01/06/2019 18:15:09           0
apply lag                    28 seconds                  10 01/06/2019 18:15:11           0
apply lag                    24 seconds                  12 01/06/2019 18:15:12           0
apply lag                    25 seconds                   9 01/06/2019 18:15:14           0
apply lag                    16 seconds                  33 01/07/2019 11:02:37           0
apply lag                    20 seconds                  17 01/09/2019 11:01:37           0
apply lag                    21 seconds                  29 01/09/2019 11:01:40           0
apply lag                    22 seconds                  19 01/09/2019 11:01:42           0
apply lag                    14 seconds                  50 01/10/2019 00:08:19           0
apply lag                    15 seconds                  44 01/10/2019 00:08:25           0
apply lag                    17 seconds                  17 01/10/2019 00:08:27           0
apply lag                    18 seconds                  23 01/10/2019 00:08:28           0
apply lag                    19 seconds                  21 01/10/2019 00:08:30           0
apply lag                    13 seconds                  73 01/10/2019 00:30:51           0
apply lag                     9 seconds                 170 01/10/2019 00:33:11           0
apply lag                     6 seconds                 426 01/10/2019 01:34:29           0
apply lag                     4 seconds                1304 01/10/2019 11:00:42           0
apply lag                     7 seconds                 294 01/10/2019 11:00:54           0
apply lag                     8 seconds                 222 01/10/2019 11:00:55           0
apply lag                    10 seconds                 117 01/10/2019 11:00:56           0
apply lag                    11 seconds                 109 01/10/2019 11:00:57           0
apply lag                    12 seconds                  90 01/10/2019 11:00:58           0
apply lag                     5 seconds                 726 01/10/2019 11:01:38           0
apply lag                     3 seconds                2911 01/10/2019 11:12:46           0
apply lag                     2 seconds                7866 01/10/2019 11:56:28           0
apply lag                     1 seconds               58544 01/10/2019 11:59:15           0
apply lag                     0 seconds              758505 01/10/2019 12:00:07           0

Filed under Administration, DataGuard, RAC Tagged with apply, DataGuard, dgmgrl, lag, log, oracle, rac, redo, ship, standby

UKOUG Oracle Conferences – why bother?

21/11/2018 1 Comment

The excellent DOAG Oracle conference is currently taking place in Nuremberg in Germany.

I’ve been thinking about the UKOUG Conference due to take place in Liverpool on 3rd-5th December 2018. About 2 weeks away.

Why would you bother paying to attend these conferences? Why should I, or my company, use our money to attend? Oracle provide free conferences and 1-day events around the world talking about the Oracle product set. Oracle Partners also provide free events with their speakers too. They are FREE! FREE! FREE!

I only need to convince my boss to take time out of the office. They don’t need to raid their budget – they just need to let me out!

BUT! Those events are effectively a sales pitch. If you are not paying in cash you are paying in other ways, with partners and Oracle looking for you to adopt them and pay them via licensing, support and consultancy. I’m not saying those events are not without worth; learning about Oracle’s new product and partner offerings certainly has value and I would encourage people to attend those events. But what are they not offering? Running those events isn’t free and large corporations are not known for their philanthropy.

An Independent User Group, like the UKOUG, doesn’t do sales pitches. It has speakers from around the world coming to share their SUCCESSES AND FAILURES! They talk about what worked, and what didn’t. They talk about the limitations and the excellence of the software and services which you are being sold.

If you want to know what happens AFTER you’ve bought the software. AFTER you’ve seen the amazing new features and where they ACTUALLY WORK or FAIL, you need to talk to people who are doing it, not those who are selling it.

And you can talk to them, and me, at the UKOUG. There are networking events; you can look into the eyes of the speaker who just told you something was great and ask them a question about how they know it’s great and how it could be great for you! And the networking events are, above all, FUN. It’s not all work. It’s also play. Have a drink, a meal, a chat, a coffee, a beer with like-minded individuals.

The event is in Liverpool, well known as somewhere to enjoy yourself as well as seeing the historic buildings and great sports teams. It’s just over 2 hours to get there on the train from London. Not as far as you might think!

See past the sales? See you there!

Filed under UKOUG Tagged with enjoy, fun, learn, liverpool, oracle, teach, tech18, ukoug

Oracle Open World Presentation: REST Enabled Neural Networks

22/10/2018 1 Comment

Hi! I’m currently at Oracle Open World 2018 in San Francisco and I’ve so I thought I would provide a copy of my slide deck for the presentations this morning.

If you are interested in how to provide good Neural Networks Machine Learning to developers in a simple to ingest REST interface, this is the place for you!

All of the URL’s are live, running in the Oracle Cloud, at the time of writing; including the somewhat open AutoREST interface to a simple data set. Enjoy!

The PDF is only a few meg:

1 – Code1-Nnets_REST-joint-ver2

rest_enabled_presentation_splash

Filed under Development Tagged with autorest, dev, learning, machine, networks, neural, oow18, openworld, oracle, REST

Off to Spain!

21/09/2018 Leave a comment

Next week I will be in Madrid to speak at the Spanish Oracle User Group – SpOUG!

I am really looking forward to this. Great content, an opportunity to look around Madrid – somewhere I have not been for 26 years – and catch up with fellow techies, some of whom I have not seen in thousands of miles.

I have never been to SpOUG, and I’m afraid my Spanish is barely enough to order 2 beers and some tapas so I will be speaking in English.

I hope you can make it there too, and help send your career soaring to new heights – this conference is taking place in a vertical wind tunnel where you can don a special suit and fly! It’s like sky diving without the 2 mile plummet to the ground (I’m never doing THAT again!)

¡Te veo allí!

Filed under SpOUG, UKOUG Tagged with madrid, oracle, spain, SpOUG

← Older posts

Newer posts →