Upgrade to Oracle 19.3!

Oracle has postponed End of Support for 11.2.0.4 many, many times, but no mas!  In fact, if you're running EBS, 11.2.0.4 is STILL supported with extended support fees waived, almost 11 years after its initial release.  I know a couple of large EBS shops that are still running on 11.2.0.4.  To upgrade the database for EBS is a huge task...so its understandable why they'd put this off as long as possible...but time is running out. See 742060.1 for details.

Also, 12.1 premier support ran out almost a year ago, but I know there are a lot of people still running critical databases on that version.

At this point, everybody is aware that Oracle changed their naming convention...so you can think of 19.3 really being 12.2.X.  There are *many* great new features in 19.3...its the terminal release of Oracle 12.2.  Oracle recommends you not upgrade at this point to anything other that 19c.  I've heard rumors that the next db version that'll be supported for EBS is 19.3 (currently the max is 12.1...they're skipping 12.2 and 18.3.)

So its inevitable...you're going to go to 19c.  Here's are some things to keep in mind:

1. In order to upgrade your database to 19.3, you...of course...need to upgrade your ASM's Grid Infrastructure to 19.3

2. In order to upgrade your Grid Infrastructure to 19.3, (assuming you're on RHEL) you MUST be on RHEL 7.  18.X was fine with RHEL 6, but 19.3 requires RHEL 7.

3. There are a lot of changes in the OS requirements.  Make sure you review/verify the install documentation before you install it.

I'll post more about the changes in the 19.3 upgrade process later.

19c Linux x86-64  for on-prem/non-Exadata is available for download!

https://www.oracle.com/technetwork/database/enterprise-edition/downloads/oracle19c-linux-5462157.html

Some interesting things to know...

1. Its available as the traditional download or as an RPM to download.

2. Just like the Exadata version that requires OEL 7, the on-prem version requires:

      OEL 7.4, UEK 4 (4.1.12-112.16.7+)

      OEL 7.4, UEK 5 (4.14.35-1818.1.6+)

      OEL 7.4, RH Compat (3.10.0-693.5.2.0.1+)

      RHEL 7.4+ (3.10.0-693.5.2.0.1+)

      SUSE ES 12 SP3 (4.4.103-92.56+)

Have fun!

Yet one more End Of Support date push for 11.2.0.4

If you haven't heard, on 1/26 Oracle updated their release/end of life document (742060.1) to say Extended Support fees have been waived through 12/31/2018.  I understand Oracle trying to work with their customers, but a lot of people took that to mean, "Great, I can postpone working on this upgrade."

12.1.0.2 has been around since 7/22/2014, so we're coming up to 3 years to plan and execute an upgrade.  11.2 has been around since 9/1/2010...the terminal patchset isn't as old, but 7 years for a database is an eternity.

/* UPDATE */

As of 4/6/2019, if you're on Oracle E-Business Suite, database 11.2 and 12.1 extended support fee waiver has been extended again through Dec 2020...Wow!  That's an extension of almost 2 years! (2522948.1)  That means 11.2 will have had a 10+year supported run!

This is understandable since I've heard Oracle is skipping 12.2 database support, and 18.3 isn't the "long-term" version (and 12.2 and 18.X) isn't going to be supported by EBS.  This means the database version you would need to upgrade to for EBS is 19.2.  Unfortunately, 19.2 isn't available yet for non-Exadata/ODA, so there's nothing to upgrade to for the majority of EBS customers.  They couldn't end support on 12.1 at the end of June and release 19.2 support for EBS at that same time.  It typically takes months to upgrade an entire EBS landscape...so...this was inevitable.

Still, I hope you don't read this and think this grants you more time to procrastinate.  There are *a lot* of great features in 19.2!  Don't allow getting kicked off the old database version to be your primary motivation!

Poor query performance after stats were gathered?

Although the Oracle optimizer is brilliant, its not infallible.  Its vulnerability is that it depends on good table statistics to determine an optimal plan.  I optimized a query today that was projecting to use over 2PB of temp space.  It was a *horrible* plan...I started to try to rewrite it and thought...why is Oracle doing this...there isn't THAT much data.  The first thing I checked was the accuracy of the table stats...within about 10 minutes I had the query completing in ~5 seconds. 

When it comes to gathering stats, there are 2 schools of thought out there...those that gather stats frequently and those that gather stats that generate plans they're happy with, then lock them...or at least gather them much less frequently and intentionally.

Some people believe stats should be gathered frequently to always have the optimal query performance all the time.  If the table data changes sizes dramatically and frequently...this might be ok.  If the stats were gathered with estimates (which is commonly done), its possible that you'll gather stats based on a subset of your data that doesn't represent the whole table.  So then your stats aren't great...but even then...usually the optimizer gets the right plan or a plan that's "close enough" that it doesn't cause any pain.  Jonathan Lewis points out in "The Cost-Based Optimizer" (great book, by the way), that one of the primary purposes of table statistics are to mathematically create relativity between the table row counts in a join. 

This means, if the largest table in a join today is the largest table tomorrow...ie: if you have 1mil rows today and it grows to 2 mil rows, you probably don't want your plan to change and so you don't need to regather stats. 

Let's say the 1mil row table becomes a 1 row table, you could regather stats and get a new plan and everything would be great.  The next day a data load brings it up to 10 mil rows...suddenly the plan that ran great isn't finishing in its SLA.  A good plan for a query today isn't necessarily a good plan tomorrow.

My opinion is...if your table sizes fluctuate, you probably want to gather stats when the tables are large and lock them, which will cause your plans to be stable.  If the table is much smaller tomorrow, your plan might not be optimal, but it won't be worse than it is today...so you'll have ~ consistency in run time performance (and you meet your SLA's.)

Oracle has made great strides to improve this...with 12.1's OPTIMIZER_ADAPTIVE_FEATURES and 12.2's OPTIMIZER_ADAPTIVE_STATISTICS, Oracle will correct itself with statistics feedback...which will prevent you from "falling off the temp usage cliff."  Although these features are great...it would be better to not have a problem that needs to be corrected in the first place. 

Since these problems are usually on complex views (on views, on views, on views...ets...)  Here's a little query you can run to find the dependencies of the top level view, gather stats on its tables/indexes, and lock them (so the problem doesn't happen again.)  I'm gathering them w/null est % (ie:compute)...adjust that and the degree/method_opt to fit your needs.  This should generate pretty good stats and lock them, allowing the optimizer to make its brilliant decisions once again.



select
  distinct 'begin'||chr(13)||'
     SYS.DBMS_STATS.GATHER_TABLE_STATS (
    ownname=>'''||o2.owner||''',
    tabname=>'''||o2.object_name||''',
    estimate_percent  => NULL,
    method_opt=> ''FOR ALL INDEXED COLUMNS SIZE AUTO '',
    degree            => 32,
    cascade           => TRUE,
    no_invalidate  => FALSE);'||chr(13)||
    'end;'||chr(13)||
    'exec dbms_stats.lock_table_stats('''||o2.owner||''','''||o2.object_name||''');'
from   sys.dba_objects o1,
       sys.dba_objects o2,
      (Select object_id, referenced_object_id
       from   (select object_id, referenced_object_id
               from   public_dependency
               where  referenced_object_id <> object_id) pd
       start with  object_id = (select object_id from dba_objects where object_name='YOUR_TABLE_NAME' and owner='YOUR_TABLE_OWNER')
       connect by nocycle prior referenced_object_id =  object_id) o3
where o1.object_id = o3.object_id
and   o2.object_id = o3.referenced_object_id
and o2.object_type='TABLE'
and   o1.owner not in ('SYS', 'SYSTEM')
and   o2.owner not in ('SYS', 'SYSTEM')
and   o1.object_name <> 'DUAL'
and   o2.object_name <> 'DUAL';  

Effect of MBPS vs Latency

I do *a lot* of performance testing on high performance storage arrays for multiple vendors.  Usually if I'm involved, the client is expecting to put mission critical databases on their new expensive storage, and they need to know it performs well enough to meet their needs. 


So...parsing that out..."meet their needs"...means different things to different people.  Most businesses are cyclical, so the performance they need today is likely not the performance they need at their peek.  For example...Amazon does much more business the day after Thanksgiving than they do in a random day in May.  If you gather the usage stats being used in May and size it appropriately, you're going to get a call in a few months when performance is exposed. 


Before I talk about latency, let me just say AWR does a great job of keeping performance data, if you have your data kept long enough...preferably at least 2 business cycles so you can do comparisons and projections.


This statement will keep AWR data for 3 years, capturing it at an aggressive 15 minute interval:


execute dbms_workload_repository.modify_snapshot_settings (interval => 15,retention => 1576800);


...at that point, see my other post re:gathering IOPS and Throughput requirements.


Anyway, I often have discussions with people who don't understand the effect of latency on OLTP databases.  This is a overly-focused serial example, but its enough to make the point.  Think about this...let's say you have a normal 8K block Oracle database using Netapp or EMC NFS on an active-active 10Gb network.  Let's say your amazing all-SSD storage array is capable of flowing 10Gb between multiple paths.   So...the time to move 8K over a 10Gb pipe is...


Throughput...
10Gb/s=10485760Kb/s
(8Kb/s)/(10485760Kb/s)=0.000000762939453125 seconds to copy 8Kb over the 10Gb pipe.


Latency...by the time it passes through your FC network, gets processed by the storage array, gets retrieved from disk, and makes it back to your server can easily exceed a few ms...but for fun let's say we're getting an 8ms response time.  That's .008 seconds.


.008/0.000000762939453125=10,485.76...


...so the effect of latency on your block is 10,485X greater than the effect of throughput.  If your throughput magically got faster but your latency stayed the same...performance wouldn't really improve very much.  If you went from 8ms to 5ms, on the other hand, this would have a huge effect on your database performance.


There's a lot that can affect latency...usually the features in use on the storage array play a big part.  CPU utilization on the storage array can become too high.  This is ultra complicated for the storage array guys to diagnose.  On EMC VMAX3's for example, CPU is allocated to "pools" for different features.  So...even though you may not use eNAS, by default, you allocate a lot of your VMAX CPU cores to it.  When your FC traffic pegs its cores and latency tanks...the administrator may think to look at the CPU utilization and not see an issue...there's free CPU available...just not in the pool used for the FC front end cores, so it creates a bottleneck.  Awesome performance improvements are possible by working closely with your storage vendor to reduce latency during testing...about 6 months ago I worked with a team that achieved improvements by over 50% from the standard VMAX3 as delivered by adjusting those allocations.


All this to say...Latency is very important for common OLTP databases.  Don't ignore throughput, but don't focus on it.

The last secret tweak for improving datapump performance (Part 2)

In my previous post, I mentioned some of the common datapump performance tweaks we see.  In this one, I want to talk about one that's never mentioned, and it might be the best of all.


5.  ADOP - The last tweak...I don't think I've seen any blog posts or Oracle documentation about this as it applies to datapump...is ADOP-Auto degree of parallelization.  This can be a *huge*...by 4X or more...improvement on imports, which is typically where most of your datapump time is spent.  This has been around since 11.2, but until recently (12.1,12.2) its been a little difficult to control how parallel things would run at.  To enable it, you simply set:


parallel_degree_policy=auto
parallel_min_time_threshold=60


(which means, if the optimizer thinks this statement will take more than 60 seconds, it will consider parallelizing it)


This is nice because quick queries will run without the overhead of parallelism, and long running queries might find value in parallelism.


Today in 12c, we have the parallel_degree_level parameter, but in 11.2 we could tweak the parallelism by adjusting max_pmbps in sys.resource_io_calibrate$.  From my testing, 200=~parallel 2 or 3.  A SMALLER value increases the amount of parallelism (50=~parallel 20.)  Effectively this gives us the same effect as the new 12c feature...which is to make Oracle make rational decisions on how parallel the auto parallelism should be. 


Datapump is a logical copy (as opposed to a physical backup/restore) so it can't copy the original indexes, it has to rebuild them.  If you have a typical import with 10,000 tables and indexes, the last 100 are big, the last 10 are huge.  Datapump's parallelism will rip through the small objects very quickly, with one process per object.  When the time comes to rebuild the indexes on the huge tables, datapump will again assign one process per index rebuild.  When the create index statement is analyzed by the optimizer, it will create it in parallel based on the algorithm derived from max_pmbps  (even though the create index statement may be parallel 2 or noparallel).  This will save many hours on a large datapump import.  Its crazy to see a serialized create index statement with 50 busy parallel slaves...but that's what can happen.


When its done, the indexes all have the original parallel spec they started with.  Nothing is any different than it would have been if you hadn't used ADOP (other than it was done much, much faster.)


One word of caution:  You have to watch your system resources and parallel limits.  If you have datapump running at parallel 50, that means potentially you'll be rebuilding 50 indexes simultaneously.  If they're each "large" and the optimizer thinks they'll take over [parallel_min_time_threshold] seconds to rebuild, each of them could be built parallel and you could have hundreds (datapump 50 * adop 50) of parallel processes.  This is a wonderful thing if your system can handle it.  Depending on your parallel limit parameters, ADOP may queue the statements until you have enough parallel processes...to prevent the system from overloading.  IMHO, that's also a wonderful thing, but it may be unexpected.  The truly unfortunate situation is when you have them too high.  You'll use up your server resources and inefficiently use CPU...and may even swap if you run low on RAM.  So test!


...but that's what dry runs are for.  I hope this last tweak helps you.  I've seen it make miraculous differences meeting otherwise impossible SLA's for datapump export/imports.  Two other posts you may want to read are:
1. Gwen Shapira has the best post on it, IMHO)
2. Kerry Osborne has a nice post on the 12c changes.




Previous post -> The last secret tweak for improving datapump performance (Part 1)
This post -> The last secret tweak for improving datapump performance (Part 2)

The last secret tweak for improving datapump performance (Part 1)

There are 10,000 blog posts and oracle docs on the internets (thank you, Mr Bush) for improving datapump performance.  This is one of the features used very frequently in Oracle shops around the world.  And DBA's are under huge stress to meet impossible downtime SLA's for their export/import. I think they all miss the best tweak (ADOP)...but they basically summarize to one simple fact...outside of parallelism, if you have a well-tuned database on fast storage, there's not a lot more you can do to improve performance more than a few percentage.  The obvious improvements are:




1. Parallelize! To quote Vizzini, "I do not think it means what you think it means."




This will create multiple processes and each will take one object and work with it, each one serialized (usually).  This is great, and if all your objects are equally sized, this is perfect...but that's not typically reality.  Usually you have a few objects that are much bigger than the rest and each of them by default will only get a single process.  This limit really hurts during imports, when you need to rebuild large indexes...more on this later.

Check that its working as expected by hitting control-c and typing in status.  Ideally, you should see all parallel slaves working.  Check not just that they exist, but that they're working (verify you used %U in your dump filename if they aren't.)  ie: DUMPFILE=exaexp%U.dmp PARALLEL=100




2. If you're importing into a new database, you have the flexibility to make some temporary changes to tweak things.  Verify Disk Asynchronous IO is set to true (DISK_ASYNCH_IO=true) and disable all the block verification (DB_BLOCK_CHECK=FALSE, DB_BLOCK_CHECKSUM=FALSE)  These aren't "game changers" but they'll give you 10-20% improvements, depending on how you had them set previously.




3. Memory Settings - Datapump parallelization uses some of the streams API's, and so the streams pool is used.  Make sure you have enough memory for the shared_pool, streams_pool and the db_cache_size parameters.  Consult your gv$streams_pool_advice, gv$shared_pool_advice, gv$db_cache_advice and gv$sga_target_advice  views.  I like to tune it so as the delta in ESTD_DB_TIME_FACTOR from one row to the row below it approaches zero, the corresponding size of the pool is close to 1.  (Any more than that is a waste, any less than that is lost performance.) 


Sometimes you'll see a huge dropoff and its more clear than this example...but you get the idea.  If you're importing into a new database, you'll need to run the import dry run and then check these views to make sure this is tuned well.

SGA_SIZE	SGA_SIZE_FACTOR	ESTD_DB_TIME	ESTD_DB_TIME_FACTOR	Time Factor Diff
158720	0.5	12162648	1.2045
178560	0.5625	11421479	1.1311	0.0734
198400	0.625	10937800	1.0832	0.0479
218240	0.6875	10607608	1.0505	0.0327
238080	0.75	10604578	1.0502	0.0003
257920	0.8125	10375361	1.0275	0.0227
277760	0.875	10222886	1.0124	0.0151
297600	0.9375	10101716	1.0004	0.012
317440	1	10097674	1	0.0004
337280	1.0625	10017905	0.9921	0.0079
357120	1.125	9955300	0.9859	0.0062
376960	1.1875	9908850	0.9813	0.0046
396800	1.25	9905821	0.981	0.0003
416640	1.3125	9870479	0.9775	0.0035
436480	1.375	9844225	0.9749	0.0026
456320	1.4375	9826049	0.9731	0.0018
476160	1.5	9821001	0.9726	0.0005

4. Something often missed when importing into a new database, size your redo logs to be relatively huge.  The redo logs will work like a cache and cycle around.  Eventually if you're adding data extremely fast, the last log will fill and can't switch until the next log is cleared.  "Huge" is relative to the size, speed of your database and hardware.  While you're running your import, select * from v$log and make sure you see at least one "inactive" logs in front of the current log.


The best, virtually unused tweak is in the next post....







This post:  The last secret tweak for improving datapump performance (Part 1)
Next post: The last secret tweak for improving datapump performance (Part 2)