Uncategorized

connor_mc_d's picture

18c merge partition online

One of the cool things in 18c is the ability to merge partitions without causing a service interruption.  Here’s a video demonstration of that in action:

This is just an accompanying blog post to let you grab the scripts for the demo so that you can try this yourself on livesql.oracle.com, or any of the Oracle Cloud services that will be running 18c in the near future.

connor_mc_d's picture

18c–If you can’t wait

You’ve seen the tweet !!

image

but perhaps the accompanying blog post has tempered your enthusiasm Sad smile

image

You might be thinking:

Jonathan Lewis's picture

Huge Pages

A useful quick summary from Neil Chandler replying to a thread on Oracle-L:

Topic: RAC install on Linux

You should always be using Hugepages.

They give a minor performance improvement and a significant memory saving in terms of the amount of memory needed to handle the pages – less Transaction Lookaside Buffers, which also means less TLB misses (which are expensive).

You are handling the memory chopped up into 2MB pieces instead of 4K. But you also have a single shared memory TLB for Hugepages.

connor_mc_d's picture

Execution plans on LiveSQL

To protect the integrity of people’s data, and isolate sessions on LiveSQL, we lock down the environment.  Clearly if you are doing some testing with sensitive data, you don’t want an anonymous member of the user community mining V$SQL to see what commands you have been running.  Conversely, we want to allow people to perform most of the tasks that would do on a standard database installation without having to install or configure anything.  That’s the great thing about LiveSQL.

So for that reason, you don’t get access to all of the features of DBMS_XPLAN.  But since we do provide access to a limited set of V$ view access, you can get execution plan details by going back to first principles.  Here’s some scripts you can use

connor_mc_d's picture

Oracle Database 18c

Yup…it’s arrived!

New name obviously, because we’ve jumped to our new naming model to align with the calendar year as opposed to version number.  You might be thinking “So what?” but it’s a significant change in the way we getting software to customer.  Mike Dietrich blogged about this at length here so I won’t repeat what has been said, but in TL;DR form:

More frequent releases, with smaller amounts of change per release

In this way, the approach lets us focus more of solidifying existing features, and being able to quickly respond to bugs that arise.

So 18c is more an incremental release on 12.2 (in fact, internally it was referred to as “12.2.0.2” for most of it’s build cycle) focussed on stability and hardening of existing features.

connor_mc_d's picture

AskTOM Office Hours for DBA’s

We had the first AskTOM Office Hours Q&A for Database Administrators yesterday.  Thanks to everyone that showed up, and thanks for the questions.

If you missed it, you can catch a replay here

connor_mc_d's picture

NVL vs COALESCE

Jonathan Lewis just published a blog post about NVL and COALESCE and the optimizer costings for each.

There is also perhaps a significant difference between NVL and COALESCE in that the former seems to have an in-built optimization for handling bind variables and nulls. 

Consider an application where users optionally pass in search criteria and you have to query a table based on that criteria.  You have three natural choices here to implement that:

WHERE column = :search_criteria or :search_criteria is null

or

WHERE column = nvl(:search_criteria ,column)

or

WHERE column = coalesce(:search_criteria,column)

Functionally they are identical*, but the implementation detail shows a nice little optimizer trick that only works with NVL.

connor_mc_d's picture

NULL’s vs NOT NULL’s and Performance

When it comes to giving the cost based optimiser the best possible chance to make the “right” decisions, many DBA’s are diligent in keeping statistics up to date, using histograms where appropriate, creating more indexes (or removing surplus indexes).

However one often neglected area is that the the null-ness of columns also impacts the optimiser decisions. NULL and NOT NULL do more than just act as constraints, they also add (or detract) to the value of indexes on those columns. Here’s an example of how the null-ness of a column impacts optimizer decisions. I have a table T which is a copy of DBA_OBJECTS, indexed on OBJECT_ID.

connor_mc_d's picture

Getting started…adding an account to use

If you’ve read my previous post about getting started with the Oracle database, then hopefully you now have your very own database installed and running, and you have a explored a little with the sample schemas using SQL Developer.  Perhaps now you want to venture out into your own database development, and for that, you will want to create your own user account and create your own tables.  Here’s another video which will guide you through the process.

khailey's picture

Is NFS on ZFS slowing you down?

If you think so, check out shell script “ioh.sh” from github at  https://github.com/khailey/ioh

Introduction and Goals

The goal of ioh.sh is to measure both the throughput and latency of the different code layers when using NFS mounts on a ZFS appliance. The ZFS appliance code layers inspected with the script are I/O from the disks, ZFS layer and the NFS layer. For each of these layers the script measures the throughput, latency and average I/O size. Some of the layers are further broken down into other layers. For example NFS writes are broken down into data sync, file sync and non-sync operations and NFS reads are broken down into cached data reads and reads that have to go to disk.

The primary three questions ioh is used to answer are

To prevent automated spam submissions leave this field empty.
Syndicate content