Turning rows into columns

Penshaw Monument
Turning Moo’s into Columns?

It’s odd how some things pass you by in new Oracle releases. Today I discovered “sys.odcinumberlist” and “sys.odcivarchar2list“.

I was wanting to generate a pre-defined list for a join to ensure each value was represented, and came up with the new in Oracle 12 function “sys.odcinumberlist”, so not new! Well, there’s 2 straightforward functions: “sys.odcinumberlist” and “sys.odcivarchar2list”.

How are they used?

SELECT column_value AS number_list
FROM sys.odcinumberlist(1,2,5,7,9,5,2,-34,5,43,2,0)
ORDER BY number_list;

NUMBER_LIST
-----------
        -34
          0
          1
          2
          2
          2
          5
          5
          5
          7
          9
         43

12 rows selected.

Note how I have had to alias the "column value" virtual column to allow mne to sort the list
SELECT column_value AS char_list FROM sys.odcivarchar2list('A','V','DD');

CHAR_LIST
---------
A
V
DD

Nice, simple, with a limit of 32768 items in the list. So how was I using it? I was checking to see which profiles existed in a database (the list was somewhat longer than this, as was the SQL, but this is good for a demo) so I generated my full list of known profiles to join to the existing profiles in each database.

WITH my_profiles AS (
    SELECT
        column_value AS profile_name
    FROM
        sys.odcivarchar2list(
            'DEFAULT'
          , 'SOME_OTHER_PROFILE' --etc etc etc 
        )
)
SELECT
    mp.profile_name
  , nvl( profile , '<does-not-exist>') AS profile
  , resource_name
  , limit
FROM
    my_profiles  mp
    LEFT OUTER JOIN dba_profiles dp ON ( mp.profile_name = dp.profile )
ORDER BY
    profile
  , resource_name;

PROFILE_NAME         PROFILE              RESOURCE_NAME                  LIMIT
-------------------- -------------------- ------------------------------ -----------
SOME_OTHER_PROFILE   <does-not-exist>
DEFAULT              DEFAULT              COMPOSITE_LIMIT                UNLIMITED
DEFAULT              DEFAULT              CONNECT_TIME                   UNLIMITED
DEFAULT              DEFAULT              CPU_PER_CALL                   UNLIMITED
DEFAULT              DEFAULT              CPU_PER_SESSION                UNLIMITED
DEFAULT              DEFAULT              FAILED_LOGIN_ATTEMPTS          10
DEFAULT              DEFAULT              IDLE_TIME                      UNLIMITED
DEFAULT              DEFAULT              INACTIVE_ACCOUNT_TIME          UNLIMITED
DEFAULT              DEFAULT              LOGICAL_READS_PER_CALL         UNLIMITED
DEFAULT              DEFAULT              LOGICAL_READS_PER_SESSION      UNLIMITED
DEFAULT              DEFAULT              PASSWORD_GRACE_TIME            7
DEFAULT              DEFAULT              PASSWORD_LIFE_TIME             180
DEFAULT              DEFAULT              PASSWORD_LOCK_TIME             1
DEFAULT              DEFAULT              PASSWORD_REUSE_MAX             UNLIMITED
DEFAULT              DEFAULT              PASSWORD_REUSE_TIME            UNLIMITED
DEFAULT              DEFAULT              PASSWORD_ROLLOVER_TIME         -1
DEFAULT              DEFAULT              PASSWORD_VERIFY_FUNCTION       NULL
DEFAULT              DEFAULT              PRIVATE_SGA                    UNLIMITED
DEFAULT              DEFAULT              SESSIONS_PER_USER              UNLIMITED

There’s a number of other ways to do this, including using the UNPIVOT function, creating your own table type, etc, but this was the nice and simple option I was looking for.

Huge Pages on Exadata

Quick note to myself: Oracle has deprecated the initialisation parameter use_large_pages=”true” on Exadata as of 19.13 (OK – could be 19.12, I’ve not checked)

If you started your database in 19.11, and had this set, you got yourself a warning in the alert log (if you have a look):
“WARNING: use_large_pages=TRUE is not a recommended setting for database on Exadata”

from 19.13, this has changed to

“ERROR: use_large_pages = TRUE is not a supported setting for database on Exadata”
” : suggested values are AUTO_ONLY or ONLY”

So what’s the difference?

use_large_pages = TRUE – allocate memory out of huge pages. If there aren’t enough huge pages, allocate the rest out of standard pages. This can result in performance degradation and increased transaction lookaside buffer allocation to manage the standard pages (taking even more memory).

use_large_pages = ONLY – allocate memory out of large pages. If there aren’t enough large pages, fail to start the database.

use_large_pages = AUTO_ONLY – allocate memory out of large pages. If there aren’t enough large pages configured, configure some more by increasing the kernel parameter vm.nr_hugepages. If that fails (e.g. if it can’t get enough contiguous memory to deallocate from standard pages and allocate the new huge pages) fail to start the database. This does result in an “up-only” scenario for huge page allocation as there is no mechanism to automatically deallocate the hugepages once they have been allocated, but the changes are not written permanently to the kernel config so a reboot would take care of it.

So what should you do?

Well, the idea behind AUTO_ONLY is seems to be Oracle ongoing “death of the DBA”, whereby the DBA doesn’t need to understand huge pages and automatically gets the memory and performance benefits associated with using huge pages.

No huge pages allocated:
$ cat /proc/meminfo | grep -i huge
AnonHugePages: 0 kB
ShmemHugePages: 0 kB
FileHugePages: 0 kB
HugePages_Total: 0
HugePages_Free: 0
HugePages_Rsvd: 0
HugePages_Surp: 0
Hugepagesize: 2048 kB
Hugetlb: 0 kB

------------------------------------------------------------------------

SQL> ALTER system SET use_large_pages='AUTO_ONLY' sid='*' scope=spfile;
 
System altered.

SQL> shutdown immediate 
ORACLE instance shut down.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 3154114080 bytes
Fixed Size                  9139744 bytes
Variable Size            1207959552 bytes
Database Buffers         1929379840 bytes
Redo Buffers                7634944 bytes

(note: "startup nomount" is enough to allocate memory, so the huge_pages are generated at this point)

------------------------------------------------------------------------
And they have magically appeared! (note: total allocation will vary by a page or two)

$ cat /proc/meminfo | grep -i huge
AnonHugePages:         0 kB
ShmemHugePages:        0 kB
FileHugePages:         0 kB
HugePages_Total:    1506
HugePages_Free:        3
HugePages_Rsvd:        3
HugePages_Surp:        0
Hugepagesize:       2048 kB
Hugetlb:         3082240 kB

$ sysctl vm.nr_hugepages
vm.nr_hugepages = 1506
Oracle must be changing the setting and performing a "sysctl" to allocate the pages. If anyone knows what their algorithm is to decide if the pages should be allocated to SGA (and "stolen" from potential PGA and O/S utilisation), and at what percentage will Oracle stop taking from the PGA and O/S? 

An 8G (7972M available) server is allowed an SGA of 6,906M (86% of available memory). Not a lot left for PGA there.

set it too large and you could hit 
"ORA-27104: system-defined limits for shared memory was misconfigured"
which might result in
"ORA-00700: soft internal error, arguments: [sms physmem limit], [6828326912]... "
or
"ORA-00700: soft internal error, arguments: [pga physmem limit], [1048576000]..."

due to being unable to allocate PGA/SGA. I'm talking:

"WARNING: pga_aggregate_limit value is too high for the
amount of physical memory on the system


I'd upped the SGA_MAX_SIZE to 6516M, started the DB, lowered it to 3000M and restarted the DB without a server reboot and...

Total System Global Area 3154114080 bytes
Fixed Size                  9139744 bytes
Variable Size             637534208 bytes
Database Buffers         2499805184 bytes
Redo Buffers                7634944 bytes
Database mounted.
Database opened.

SQL > !cat /proc/meminfo | grep -i huge
AnonHugePages:         0 kB
ShmemHugePages:        0 kB
FileHugePages:         0 kB
HugePages_Total:    3258 <- that's a lot of 2M pages (6516M)
HugePages_Free:     1752 <- we've only allocated 3000M. No cleanup without a reboot
HugePages_Rsvd:        0
HugePages_Surp:        0
Hugepagesize:       2048 kB
Hugetlb:         6672384 kB


Happy huge pages everyone and keep checking that alert log.

Mash Program

As an experienced speaker and community advocate, I decided to join with a number of other like-minded community advocates to see if we can help others get started in the world of conference speaking.

Everybody has a story to tell about their work. Talking about the challenges you face in projects, as well as the good and bad things which happen during your business-as-ususal (BAU) work can seem uninteresting. “It’s just what I do”. The world I inhabit, Corprate IT, can be extremely complex. What may seem second nature to me, with a fair bit of experience, can be a revlation to someone just starting out. You don’t need 30 years of experience, either. Just being on the next page of the book, a tiny bit ahead of someone else, can be enough. Tell me what you did. Tell me if it worked. Tell me what you would do differently if you were doing it again.

If you think you have a story to tell – and I’m pretty sure you do – have a look at the MASH Program that we have set up. We’re here to help you take the next step into a really interesting world.

Exadata System Statistics

Following on from last weeks Oracle Optimizer System Statistics post, I though it worthwhile adding a note about gathering system statistics in Exadata mode.

exec dbms_stats.gather_system_statistics('EXADATA');

So what is this, and why would you chose to do it? First of all, these are not workload system statistics (which I believe you should never gather without extraordinary proof of their effectiveness – all Oracle official documenation and MOS notes about workload stats should point to this blog post now, explaining why: https://blogs.oracle.com/optimizer/should-you-gather-system-statistics). Workload stats measure a number of I/O and CPU metrics. Exadata stats are a little bit of measurement (IO Transfer Speed – IOTFRSPEED) and the hard-setting of the Multi-block read count (MBRC). How are these set?

https://unsplash.com/photos/HwNCyLWw7hw

The IOTFRSPEED is the rate at which an Oracle database can read data in a single read request. It is measured and set (subject to bug 17501565.8 not getting in the way – Pre 12.1- and leaving it to default). The default is 4096, but after measuring and setting it will increase significantly. For the worked example below, lets say the speed was measures at 200,000 (about 200MB, which is a good number for an exadata)

The MBRC is set by copying in the db_file_multiblock_read_count in from the initialization parameters. By default this is not set explicitly and relies upon 2 hidden parameters. If your block size is 8k, it will probably be 128, and if your block size is 16k it will probably be 64. If your block size is any other size, I hope you have a good tested and proven reason for your edge-case configuration.

Each of these setting will change the balance the optimizer will use between single block reads (to read index blocks, and to read table rows by rowid), and performing full table scans. Higher MBRC and IOTFRSPEED’s mean table scans become more attractive to the optimizer. This means they will occur more frequently. What does an Exadata do very well? Offload table scans to storage cells.

DEFAULT, 8K Block Size
MBRC=8 (db_file_multblock_read is not set, so use _db_file_optimizer_read_count)
IOTFRSPEED=4096
Calculated Ratio 0.271, or 3.69 multiblocks for every single block.
DEFAULT, 16K Block Size
MBRC=8 (db_file_multblock_read is not set, so use _db_file_optimizer_read_count)
IOTFRSPEED=4096
Calculated Ratio 0.375, or 2.67 multiblocks for every single block.

16k block sizes mean indexes are slightly more likely to be used as we get less blocks per scan.
DEFAULT, 8K Block Size, db_file_multblock_read is set explicitly
MBRC=128 (db_file_multblock_read is explicitly set, so use explicit 128)
IOTFRSPEED=4096
Calculated Ratio 0.173, or 5.77 multiblocks for every single block.

Tablescans are a little more attractive than default. You get 5.77 blocks for every multiblock read instead of 3.69.
EXADATA GATHERED, 8K Block Size, db_file_multblock_read NOT set explicitly
MBRC=128 (db_file_multblock_read is not set, so set it to the value of _db_file_exec_read_count)
IOTFRSPEED=200000 - this was measured and set by the 'exadata' gather.
Calculated Ratio 0.012, or 84.32 multiblocks for every single block.

For an index to be used it is going to have to be much more efficient than table scan.
Pulling a few rows out of unique indexes will continue to work well but
any kind of predicate which returns a set of data will be pushed towards 
a FTS (or IFFS), and therefore the exadata will offload to the storage cells.

So how does this affect optimizer costing? Looking at a 10053 trace to see how the cost is changed:

I/O COST WITH DEFAULTS: 270,835
[10053] SINGLE TABLE ACCESS PATH
  Single Table Cardinality Estimation for COST_CHECK[COST_CHECK]
  SPD: Return code in qosdDSDirSetup: NOCTX, estType = TABLE
  Table: COST_CHECK  Alias: COST_CHECK
    Card: Original: 1000000.000000  Rounded: 1000000  Computed: 1000000.000000  Non Adjusted:1000000.000000
  Scan IO  Cost (Disk) =   270835.000000
  ...
I/O COST EXADATA GATHERED AS ABOVE: 11,860
[10053] SINGLE TABLE ACCESS PATH
  Single Table Cardinality Estimation for COST_CHECK[COST_CHECK]
  SPD: Return code in qosdDSDirSetup: NOCTX, estType = TABLE
  Table: COST_CHECK  Alias: COST_CHECK
    Card: Original: 1000000.000000  Rounded: 1000000  Computed: 1000000.000000  Non Adjusted:1000000.000000
  Scan IO  Cost (Disk) =   11860.000000
  ...

So, the I/O cost with “Exadata” system stats us reduces from 270,835 to 11,860. That’s quite a reduction.
Please bear in mind that this is only the Optimizer Cost calculation of a table scan, and not the reality of accessing the data. This is used to decide the access path.

By gathering ‘Exadata’ stats, you need to ensure you have the capacity on your exadata to offload the workload increase on the storage cells. For a classic “overnight load, daytime query, dedicated Exadata”, this is probably a good thing. Where the storage cells are already being worked hard, this will make things worse.

If you followed the advice of the Exadata salesman and marketing people and already removed your indexes, you should already be employing a performance expert to put (some of) the indexes back on…

How did I calculate the ratio’s above? Check the Oracle Optimizer System Statistics blog post from last week….

Oracle Optimizer System Statistics

System Statistics Are a Little Complex


Oh System statistics! Should we gather them? Should we not?
What do they mean? What are they doing to the optimizer?

[tl;dr – in almost all cases, DON’T GATHER SYSTEM STATISTICS. DON’T SET MBRC. LET THEM DEFAULT!]

First you need to be armed with a piece of information. When Oracle optimizes your SQL, it produces a COST to compare each SQL execution plan. Exactly what is this COST? It’s not seconds. It’s not quite the amount of I/O required. It’s not an arbitrary figure either. Oracle Optimizer Cost is the cost of getting your data in terms of SINGLE BLOCK READS. Remember this for later.

Lets have a look at the system stats defaults :

SELECT sname,pname,pval1 FROM SYS.AUX_STATS$ ORDER BY 1,2;
SNAME                PNAME                               PVAL1
-------------------- ------------------------------ ----------
SYSSTATS_INFO        DSTART
SYSSTATS_INFO        DSTOP
SYSSTATS_INFO        FLAGS                                   0
SYSSTATS_INFO        STATUS
SYSSTATS_MAIN        CPUSPEED
SYSSTATS_MAIN        CPUSPEEDNW                           2911 (this will vary)
SYSSTATS_MAIN        IOSEEKTIM                              10
SYSSTATS_MAIN        IOTFRSPEED                           4096
SYSSTATS_MAIN        MAXTHR
SYSSTATS_MAIN        MBRC
SYSSTATS_MAIN        MREADTIM
SYSSTATS_MAIN        SLAVETHR
SYSSTATS_MAIN        SREADTIM

What we are looking for here is the the 3 metrics highlighted in colour. As we can see, the are not set. By default, we need to calculate those, or know where to find the defaults for those values to get to them. Once we have those 3 metrics, we can calculate the RATIO used by the optimizer to convert MULTIBLOCK READ into a cost metric of SINGLE BLOCK READS.

The CPU speed is used to calculate the COST of the query in terms of CPU required. This is small percentage of the query cost but it may help decide which plan is chosen. This is also converted from CPU cost into a cost metric of SINGLE BLOCK READS.

Lets get the values for MBRC, MREADTIM and SREADTIM.

MBRC is easy. If it is not explicitly set, it uses the init.ora parameter “db_file_multiblock_read_count” and uses that. However, by default (and also my recommendation) this should not be set, meaning Oracle will use the hidden parameter “_db_file_optimizer_read_count” to cost your queries. This defaults to 8. [note: this is not the value used in execution. Oracle attempts to do 1MB reads, and uses the value in “_db_file_exec_read_count” to control the multiblock reads at execution time. For an 8K block size, this is set to 128.

SREADTIM and MREADTIM are calculations based upon information we now have:
SREADTIM = IOSEEKTIM + db_block_size        / IOTFRSPEED = 10+(8192 /4096) = 12
MREADTIM = IOSEEKTIM + db_block_size * MBRC / IOTFRSPEED = 10+(8192*8/4096) = 26

Right! Now we have more information and can calculate a ratio. The multi block cost-per-block. This will allow us to take the number of blocks in (for example) a table scan [DBA_TAB_STATISTICS.BLOCKS statistic] and covert it to the cost metric os SINGLE BLOCK READS, meaning we can compare (for example) a FULL TABLE SCAN’s I/O directly with the I/O required for an Index Range Scan and Table Lookup.

multi-block cost-per-block = 1/MBRC * MREADTIM/SREADTIM = 1/8 * 26/12 = 0.270833

If we pull some data from a 10053 trace, where I have a table scan of a table containing 1,000,000 blocks, we should see the 1,000,000 blocks being converted to 270,833 “single block read blocks” for COST purposes.

[10053] SINGLE TABLE ACCESS PATH
  Single Table Cardinality Estimation for COST_CHECK[COST_CHECK]
  SPD: Return code in qosdDSDirSetup: NOCTX, estType = TABLE
  Table: COST_CHECK  Alias: COST_CHECK
    Card: Original: 1000000.000000  Rounded: 1000000  Computed: 1000000.000000  Non Adjusted:1000000.000000
  Scan IO  Cost (Disk) =   270835.000000
  Scan CPU Cost (Disk) =   7411440000.000001
  Total Scan IO  Cost  =   270835.000000 (scan (Disk))
                       =   270835.000000
  Total Scan CPU  Cost =   7411440000.000001 (scan (Disk))
                       =   7411440000.000001
  Access Path: TableScan
    Cost:  271041.492812  Resp: 271041.492812  Degree: 0
      Cost_io: 270835.000000  Cost_cpu: 7411440000
      Resp_io: 270835.000000  Resp_cpu: 7411440000
  Best:: AccessPath: TableScan
         Cost: 271041.492812  Degree: 1  Resp: 271041.492812  Card: 1000000.000000  Bytes: 0.000000

Well we were close! There’s a fudge factor in play here, but 270,835 is pretty much 270,833 🙂

We can also work out the CPU element of the COST.
Scan IO  Cost (Disk) =   270835.000000
Cost: 271041.492812
so CPU cost must be 271041.492812 – 270835.000000 = 206.492812

Scan CPU Cost (Disk)=7411440000.000001 so doing the maths to convert to units of “single block read time”…

CPUSPEEDNW = 2,991 (Mhz, but we convert 2,991,000 Khz)

= 7,411,440,000 / 2,991,000 / SREADTIM
‭=  ‭2,477.913741223671013039 / 12
‭= 206.49281176863925108659311267135‬ cost of CPU in units of SREADTIM

So now you can see how your system statistics can fundamentally change the optimizer by changing the ratio of multiblock-reads to single block reads.

You want to play yourself and see what happens? Of course you do…

Here’s an excel spreadsheet where you can plug in your numbers and see what happens to the ratio between single and multi block reads to see how system stats influence the optimizer. By plugging in different numbers, you will see how complex the interactions are – so what’s the ideal numbers? Use the DEFAULTS in almost all cases** [WARNING – don’t go changing your system stats based on this statement. You might experience a significant amount of plan changes, some of which may be very very bad!]:


Also, this is my version of a script by Franck Pachot, and also based on work by Chris Antognini, which you can run against your system:

select pname,pval1,calculated,formula from sys.aux_stats$ where sname='SYSSTATS_MAIN'
model
reference sga on (
select 'Database Buffers' name,sum(bytes) value from v$sgastat where name in ('shared_io_pool','buffer_cache')
) dimension by (name) measures(value)
reference parameter on (
select name,decode(type,3,to_number(value)) value from v$parameter where name='db_file_multiblock_read_count' and ismodified!='FALSE'
union all
select name,decode(type,3,to_number(value)) value from v$parameter where name='sessions'
union all
select name,decode(type,3,to_number(value)) value from v$parameter where name='db_block_size'
union all
SELECT a.ksppinm name, to_number(b.ksppstvl) value FROM x$ksppi a, x$ksppsv b WHERE a.indx=b.indx AND ksppinm like '_db_file_optimizer_read_count'
)
dimension by (name) measures(value)
partition by (sname) dimension by (pname) measures (pval1,pval2,cast(null as number) as calculated,cast(null as varchar2(120)) as formula) rules(
calculated['MBRC']=coalesce(pval1['MBRC'],parameter.value['db_file_multiblock_read_count'],parameter.value['_db_file_optimizer_read_count'],8),
calculated['MREADTIM']=coalesce(pval1['MREADTIM'],pval1['IOSEEKTIM'] + (parameter.value['db_block_size'] * calculated['MBRC'] ) / pval1['IOTFRSPEED']),
calculated['SREADTIM']=coalesce(pval1['SREADTIM'],pval1['IOSEEKTIM'] + parameter.value['db_block_size'] / pval1['IOTFRSPEED']),
calculated['_multi block Cost per block']=round(1/calculated['MBRC']*calculated['MREADTIM']/calculated['SREADTIM'],4),
calculated['_single block Cost per block']=1,
formula['MBRC']=case when pval1['MBRC'] is not null then 'MBRC' when parameter.value['db_file_multiblock_read_count'] is not null then 'db_file_multiblock_read_count' when parameter.value['_db_file_optimizer_read_count'] is not null then '_db_file_optimizer_read_count (db_file_multiblock_read_count not set, which is good!)' else '= not sure so used 8' end,
formula['MREADTIM']=case when pval1['MREADTIM'] is null then '= IOSEEKTIM + db_block_size * MBRC / IOTFRSPEED' end||' = '||pval1['IOSEEKTIM']||'+('||parameter.value['db_block_size']||'*'||calculated['MBRC']||'/'||pval1['IOTFRSPEED']||')',
formula['SREADTIM']=case when pval1['SREADTIM'] is null then '= IOSEEKTIM + db_block_size / IOTFRSPEED' end||' = '||pval1['IOSEEKTIM']||'+('||parameter.value['db_block_size']||'/'||pval1['IOTFRSPEED']||')',
formula['_multi block Cost per block']='= 1/MBRC * MREADTIM/SREADTIM = 1/'||calculated['MBRC']||' * '||calculated['MREADTIM']||'/'||calculated['SREADTIM'],
calculated['_maximum mbrc']=sga.value['Database Buffers']/(parameter.value['db_block_size']*parameter.value['sessions']),
formula['_maximum mbrc']='= buffer cache blocks/sessions (small cache limiter) = ' || sga.value['Database Buffers']/parameter.value['db_block_size']||'/'||parameter.value['sessions'],
formula['_single block Cost per block']='relative to the multi blovk cost per block. Always 1!',
formula['CPUSPEED']='overrides CPUSPEEDNW when set',
formula['CPUSPEEDNW']='CPU speed Mhz - non workload',
formula['IOSEEKTIM']='IO seek time in ms',
formula['IOTFRSPEED']='IO transfer speed in KB/s',
formula['MAXTHR']='Maximum IO system throughput',
formula['SLAVETHR']='average parallel slave IO throughput'
) order by 1;

** there is a case for gathering ‘exadata’ system stats. Increasing the IOTRFSPEED to 200,000 and changing the MBRC to (probably) 128 or 64 will *really* change the ratios, forcing a lot of full table scans down onto the storage cells, instead of using mediocre indexes. This should be considered (and thoroughly tested) if you have a DW on a dedicated Exadata. From a little more on exadata stats, check this blog post

Goldengate Install Error

Sometimes you waste much more time than you can believe because the instrumentation of the system isn’t great. If you don’t know what a system is doing, you can’t easily fix it. Time should be take to instrument your code, and ensure that any outputs from that instrumentation are readily understandable by others.

Here’s a mild frustration from this week that I encountered. I was installing Goldengate…

./runInstaller -silent -waitforcompletion -responseFile /u01/migrate/gg/software/fbo_ggs_Linux_x64_shiphome/Disk1/install_source.rsp
Starting Oracle Universal Installer...

Checking Temp space: must be greater than 120 MB. Actual 21571 MB Passed
Checking swap space: must be greater than 150 MB. Actual 24575 MB Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2020-07-30_08-03-36PM. Please wait ...
[WARNING] [INS-08109] Unexpected error occurred while validating inputs at state 'installOptions'.
CAUSE: No additional information available.
ACTION: Contact Oracle Support Services or refer to the software manual.
SUMMARY:
- java.lang.NullPointerException

Oh my word! ACTION: Contact Oracle Support Services! java.lang.NullPointerException! Something is terribly wrong!

I’d better re-read this error message carefully… Unexpected error occurred while validating inputs at state ‘installOptions’. What? Validating inputs? Lets have a look at the inputs from the response file:

install_source.rsp

################################################################################
## ##
## Oracle GoldenGate installation option and details ##
## ##
################################################################################

#-------------------------------------------------------------------------------
# Specify the installation option.
# Specify ORA19c for installing Oracle GoldenGate for Oracle Database 19c or
# ORA18c for installing Oracle GoldenGate for Oracle Database 18c or
# ORA12c for installing Oracle GoldenGate for Oracle Database 12c or
# ORA11g for installing Oracle GoldenGate for Oracle Database 11g
#-------------------------------------------------------------------------------
INSTALL_OPTION=ORA11G

#-------------------------------------------------------------------------------
# Specify a location to install Oracle GoldenGate
#-------------------------------------------------------------------------------
SOFTWARE_LOCATION=/u01/gg

START_MANAGER=
MANAGER_PORT=
DATABASE_LOCATION=
INVENTORY_LOCATION=
UNIX_GROUP_NAME=

 

I only have 2 inputs; the INSTALL_OPTION and the SOFTWARE_LOCATION… and the INSTALL_OPTION is wrong! This field is case sensitive. ORA11G does not match ORA11g, so I get a dramatic null pointer exception and a failure. It would have been a much quicker remediation if the Oracle error had been curated a little better and the programmer a little more sympathetic to users of their code.

How about:
Installation parameter INSTALL_OPTION invalid,
rather than
Unexpected error occurred while validating inputs at state ‘installOptions’

Oracle Database GLOBAL_NAME, DB_DOMAIN and database links

I needed to create a database link on a 19.7C Oracle DB recently. Importantly, the database link name must match the database name as security dictates that GLOBAL_NAMES=TRUE for this set of databases. The DB was created using the gui on an ODA, meaning there were quite a few initialisation parameters set by the gui.

Lets see what happened and what I had to do to actually get a database link with the same name as the database.

> create database link ORA11A connect to neil identified by neil using 'ORA11A';
Database link created.

> select owner,db_link,username,host from dba_db_links;

OWNER DB_LINK                 USERNAME     HOST         
----- ----------------------- ------------ ------------ 
SYS   ORA11A.CHANDLER.UK.COM  NEIL         ORA11A       

> drop database link ORA11A;

What? Why was a domain being appended to the database link name?

> select name,value,description from v$parameter where name = 'db_domain';

NAME       VALUE            DESCRIPTION
---------- ---------------- --------------------------------------------------------------------------------
db_domain  chandler.uk.com  directory part of global database name stored with CREATE DATABASE

> alter system reset db_domain scope=spfile sid='*';
(restart db)
> select name,value,description from v$parameter where name = 'db_domain';

NAME       VALUE            DESCRIPTION
---------- ---------------- --------------------------------------------------------------------------------
db_domain                   directory part of global database name stored with CREATE DATABASE

> create database link ORA11A connect to neil identified by neil using 'ORA11A';
> select owner,db_link,username,host from dba_db_links;

OWNER DB_LINK                 USERNAME     HOST        
----- ----------------------- ------------ ------------
SYS   ORA11A.CHANDLER.UK.COM  NEIL         ORA11A      

So it’s not coming from the DB_DOMAIN! But there’s a clue in the description “directory part of global database name stored with CREATE DATABASE“. Lets check the global name:

select * from global_name;
GLOBAL_NAME
------------------------------
ORA19A.CHANDLER.UK.COM

So maybe it’s taking the domain from here? Can I prove that?

>alter database rename global_name to "ORA19A.COM";
Database altered.

> select * from global_name;
GLOBAL_NAME
------------------------------
ORA19A.COM

> create database link ORA11A connect to neil identified by neil using 'ORA11A';
Database link created.

> select owner,db_link,username,host from dba_db_links;
OWNER DB_LINK                 USERNAME     HOST 
----- ----------------------- ------------ ------------ 
SYS   ORA11A.CHANDLER.UK.COM   NEIL        ORA11A
SYS   ORA11A.COM               NEIL        ORA11A 

[note that I had not deleted the previous DB link so it was still there!]

So, all  I needs to do now is remove the “.COM”

> alter database rename global_name to "ORA19A";
Database altered.

> select * from global_name;
GLOBAL_NAME
------------------------------
ORA19A.COM

Oh dear. You can’t remove a domain suffix from a global name. If you don’t specify the domain, Oracle helpfully adds it right back in there for you.

You need to correct this in an unorthodox way.
The GLOBAL_NAME is really a value in SYS.PROPS$

> select text from dba_views where view_name = 'GLOBAL_NAME';

TEXT
-----------------------------------------------------------
select value$ from sys.props$ where name = 'GLOBAL_DB_NAME'

So we will have to update the value in SYS.PROPS$ directly.

THIS IS NOT A SUPPORTED ACTION.
DON’T DO IT WITHOUT A CONVERSATION WITH ORACLE SUPPORT.
[Check MOS note: 1018063.102]
***updating sys.props$ incorrectly can corrupt your entire database ***

 

> select value$,comment$ from sys.props$ where name = 'GLOBAL_DB_NAME';

VALUE$                         COMMENT$
------------------------------ --------------------------------------------------------------------------------
ORA19A.COM                     Global database name

> update sys.props$ set value$='ORA19A' where name = 'GLOBAL_DB_NAME';
1 row updated.

> commit;
Commit complete.

select value$,comment$ from sys.props$ where name = 'GLOBAL_DB_NAME';
VALUE$                         COMMENT$
------------------------------ --------------------------------------------------------------------------------
ORA19A                         Global database name

> create database link ORA11A connect to neil identified by neil using 'ORA11A';
Database link created.

> select owner,db_link,username,host from dba_db_links;
OWNER DB_LINK                 USERNAME     HOST 
----- ----------------------- ------------ ------------ 
SYS ORA11A.CHANDLER.UK.COM    NEIL         ORA11A
SYS ORA11A.COM                NEIL         ORA11A
SYS ORA11A                    NEIL         ORA11A

Finally I can use the DB_LINK with GLOBAL_NAME=TRUE!

If only I’d created the database without specifying a db_domain in the first place…

 

UKOUG Techfest19

I’ve just returned from Techfest19 – the UKOUG Flagship Oracle Tech conference – and it was simply excellent.

79374309_10158533454103840_8801012542173347840_o

Amazing Sunset from the Grand

It was held at The Grand Hotel in Brighton, and it was grand. If you missed it, you really missed out. 250 mostly amazing  presentations.

I did 2 main ones about Oracle Statistics, and also MC’d the Jonathan Lewis Optimizer Panel, alongside Martin Widlake. The highlight of my conference was asking a left-field question of an Oracle Architect about Full Table Scans, which has resulted in the potential for a change to the code to make them (possibly slightly) more efficient. Thank you for listening Roger!

78256648_10158533454068840_8935776057437454336_o

Oracle ACE Dinner

 

 

 

 


If you missed my 2 sessions on Database Statistics, you can find a webinar and a Blair-Witch style video of each of them here on my website:


78372397_10158533455058840_55580213999828992_o

UKOUG Guest Speaker, before the party

Oracle Tech Conferences

It’s mid-November 2019. The leaves have mostly fallen and the nights are drawing in. That can only mean… it’s time for Europes 2 biggest Independent Oracle User Groups to have their conferences.

The first, starting Tuesday 18th November, is DOAG – the German Oracle User Group – in Nuremberg.nuremberg I’m flying out there a couple of days beforehand as I have meetings, and it’s nice to travel and chill out too. This year I’m speaking about Oracle Database Statistics at both conferences, but I only have time for the “basics” talk at DOAG so I’ll try to get some Unconference time to talk about when it gets harder.

Then, from the 1st to the 4th December, it’s the UKOUG Techfest 2019. This is slightly different this year as we have split the main conference, comprising Tech, Business Apps and JDE into 3 separate components at different times of the year. The Tech-only conference has stayed in the December slot (with Business Apps moving to June).

We have also moved the conference to Brighton. New location, Great town.

I’ll be talking about  Oracle Database Statistics, but this time in 2 slots, Database Stats #1. Doing it Right, the Easy Way (Monday 2nd December 09:00), then in a bit more depth I’ll do Database Stats #2. Doing it Right, When it’s Harder (Wednesday 4th December 11:00)

For a UKOUG “conference survival guide”, I recommendbrighton-autumn UKOUG Techfest Survival Guide 2019. All you need to know about getting the most out of 3.5 days of Tech shenanigans. Turn up, learn, but also try to meet people and have fun! I hope to see you at one of both of these conferences, maybe have a coffee or somethings stronger – easy on the stalking though (read Martins Survival Guide first)

If you have not registered for Techfest 2019, you can do that here: UKOUG TechFest 2019!

There a full agenda of around 250 Oracle-related Tech talks. The problem isn’t trying to work out if there’s enough to see, but trying to work out how to prioritise all of them!

See you there!

techfest19_2000x400_techfest

Oracle Table Prefs

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