Search

Top 60 Oracle Blogs

Recent comments

Oakies Blog Aggregator

From Database 18.3 to 18.5 (on Windows)

Contrary to wild rumours on the internet, it was not a fear of the number 13 that led to a numbering jump from version 12c to version 18c. The jump was part of our new, more flexible release mechanism so that we can get fixes and enhancements to customers on a more frequent and predictable schedule. In a nutshell, smaller bundles of features and fixes, more frequently.

I won’t dwell on that – if you’re unfamiliar with the new strategy, the best place to start is  MOS Note 2285040.1, which has a description and a FAQ. But in terms of (as the saying goes) eating one’s own dog food, I downloaded the 18.5 release update which came out this week, and applied it to my 18.3 installation and I thought I’d share the process.

You might be wondering: “Why am I skipping 18.4?”  Well that can be summarised simply as ignorance on my part – I missed the email a couple of few months back that told me 18.4 was available Smile You might also be wondering: “I read that there were 4 release updates per year, how come there is an 18.5?” That is a common misinterpretation. We aim to provide a release update each quarter, but we never said that this means there would only ever be 4.

Now I stress – I’m just sharing my experiences here, not claiming that this is your go-to guide for applying the release update. I was just updating 18c on a Windows laptop, not a server. You should always follow the installation documents that come with the release update. For example, I had to reboot my Windows machine a couple of times, because it had transpired that some webcam software I’d installed had opted to use the Microsoft C++ shared libraries in my 18c Oracle Home! I think we can safely assume your servers probably don’t have webcams on them!

Step 1: Download a fresh OPatch

Seriously…just do this every time. As it turned out, my OPatch was already at a high enough level to apply the release update, but for me, years ago I adopted the mindset: “If I am going to apply a patch, then download a new OPatch“. It just makes it a no-brainer.

Step 2: Shut everything down.

I am running single instance, no “rolling” patching for me.

Step 3: Unzip the patch, set the environment, and patch


C:\>set ORACLE_HOME=C:\oracle\product\18
C:\>set PATH=%ORACLE_HOME%\OPatch;%ORACLE_HOME%\perl\bin;%PATH%
C:\>set PERL5LIB=
C:\>cd \oracle\stage\29124511
C:\oracle\stage\29124511>opatch apply

Oracle Interim Patch Installer version 12.2.0.1.14
Copyright (c) 2019, Oracle Corporation.  All rights reserved.


Oracle Home       : C:\oracle\product\18
Central Inventory : C:\Program Files\Oracle\Inventory
   from           :
OPatch version    : 12.2.0.1.14
OUI version       : 12.2.0.4.0
Log file location : C:\oracle\product\18\cfgtoollogs\opatch\opatch2019-01-18_09-36-46AM_1.log

Verifying environment and performing prerequisite checks...

Conflicts/Supersets for each patch are:

Patch : 29124511

        Conflict with 28267731
        Conflict details:
        C:\oracle\product\18\javavm\admin\classes.bin
        C:\oracle\product\18\javavm\admin\lfclasses.bin
        C:\oracle\product\18\javavm\jdk\jdk8\admin\classes.bin
        C:\oracle\product\18\javavm\jdk\jdk8\admin\lfclasses.bin
        C:\oracle\product\18\rdbms\admin\oracle.sym
        C:\oracle\product\18\bin\oracle.exe

        Bug Superset of 27783303
        Super set bugs are:
        27748954, 27604293, 27747869, 27984028, 27550341, 27389352, 27433163, 27538461, 27341181,....

Well…that didn’t go so well Smile Now the first thing confused me was: “How could there be any pre-existing patch to conflict with?”. After all, this was a complete 18c installation that I had downloaded from OTN when it first became available. But then I remembered, this is 18.3. So while it’s true that I download it as a standalone complete piece of software, it is still a patched release of the database. This is so much better than the old days where if you wanted (say) version 9.2.0.8, you had to download 9.2.0.1 and then apply the patch on top of it before using the software. A listing of the patch inventory showed that I already had some patches installed with my initial fresh download.


C:\oracle\stage\29124511>opatch lsinventory
Oracle Interim Patch Installer version 12.2.0.1.14
Copyright (c) 2019, Oracle Corporation.  All rights reserved.


Oracle Home       : C:\oracle\product\18
Central Inventory : C:\Program Files\Oracle\Inventory
   from           :
OPatch version    : 12.2.0.1.14
OUI version       : 12.2.0.4.0
Log file location : C:\oracle\product\18\cfgtoollogs\opatch\opatch2019-01-18_09-39-23AM_1.log

Lsinventory Output file location : C:\oracle\product\18\cfgtoollogs\opatch\lsinv\lsinventory2019-01-18_09-39-23AM.txt

--------------------------------------------------------------------------------
Local Machine Information::
Hostname: gtx
ARU platform id: 233
ARU platform description:: Microsoft Windows (64-bit AMD)


Installed Top-level Products (1):

Oracle Database 18c                                                  18.0.0.0.0
There are 1 products installed in this Oracle Home.


Interim patches (3) :

Patch  28267731     : applied on Sat Aug 18 17:34:22 AWST 2018
Unique Patch ID:  22301563
Patch description:  "WINDOWS OJVM BUNDLE PATCH : 18.3.0.0.180717 (28267731)"
   Created on 8 Jul 2018, 06:48:38 hrs PST8PDT
   Bugs fixed:
     27642235, 27952586, 27304131, 27461740, 27636900, 27539876
   This patch overlays patches:
     27783303
   This patch needs patches:
     27783303
   as prerequisites

Patch  27908644     : applied on Sat Aug 18 17:31:26 AWST 2018
Unique Patch ID:  22299245
Patch description:  "UPDATE 18.3 DATABASE CLIENT JDK IN ORACLE HOME TO JDK8U171"
   Created on 29 Jun 2018, 02:51:19 hrs PST8PDT
   Bugs fixed:
     27908644

Patch  27783303     : applied on Sat Aug 18 17:25:42 AWST 2018
Unique Patch ID:  22238986
Patch description:  "Windows Database Bundle Patch : 18.3.0.0.180717 (27783303)"
   Created on 16 Aug 2018, 07:58:43 hrs PST8PDT
   Bugs fixed:
     27026401, 27994333, 27680509, 27314206, 27345231, 24925863, 27447452
     26792891, 27236110, 27608669, 27670484, 27421101, 27240246, 27213140
     27740844, 27616657, 18701017, 27177852, 27697092, 27379956, 26598422
     27688099, 27580996, 27534509, 27602488, 27333978, 27098733, 27163313
     27551855, 27012915, 27603841, 27224987, 28165545, 27259983, 27919283
     28039953, 27357773, 27302730, 27263996, 27345498, 27517818, 27377219
     26882126, 27396377, 27701279, 27285557, 27779886, 27739006, 27585755
     27321834, 27748954, 27950708, 26646549, 26961415, 27061736, 27066519
     27498477, 28174926, 21806121, 24489904, 27570318, 27365139, 27028251
     27435537, 27869339, 27226913, 27192754, 27038986, 27537472, 27483974
     27329812, 27356373, 27714373, 27422874, 27334648, 27339115, 25035594
     27128580, 27952762, 27691717, 27534289, 27425622, 27434974, 27518227
     27426363, 27352600, 26450454, 27631506, 27143882, 27346949, 27181521
     27199245, 27252023, 27911160, 27365702, 27497950, 26586174, 12816839
     27389352, 25287072, 27215007, 27345190, 27475272, 25634405, 27813267
     27726269, 27463879, 27086406, 27283029, 27850736, 27338838, 27428790
     27395404, 27924147, 27284286, 27430254, 27262945, 27250547, 27346329
     27693713, 27347126, 27745220, 27341036, 27481765, 28174827, 27450355
     27214085, 27503413, 27451182, 27359178, 27625274, 27587672, 28320117
     27367194, 27782464, 27735534, 27396365, 27210872, 27501327, 27984028
     27309182, 27520070, 27999597, 27381383, 27302415, 27378103, 27861909
     27782339, 27254851, 27086821, 27101273, 27465480, 27232983, 27941514
     27486253, 27489719, 27222626, 27560562, 27244785, 27458829, 27262650
     27155549, 25743479, 27897639, 27615608, 27459909, 27267992, 27304936
     27663370, 27602091, 27448162, 27434486, 26933599, 26928317, 27586810
     27153755, 27348081, 27314390, 27786669, 27573408, 27532375, 26818960
     25824236, 27563767, 27060859, 27126666, 27284499, 27210038, 25726981
     9062315, 27301308, 27725967, 27452760, 28188330, 27834984, 27748321
     26990202, 27811439, 27376871, 27654039, 27080748, 27333664, 28059199
     27420715, 27315159, 27331374, 27398660, 27680162, 25035599, 27718914
     27599689, 27595801, 26615291, 27040560, 26476244, 27801774, 27450783
     27066451, 27935826, 28098865, 26226953, 27501465, 27558559, 27496806
     27381656, 27299455, 27124867, 27496224, 27707544, 27163928, 27147979
     27395416, 27532009, 21766220, 27727843, 27607805, 27271876, 26860285
     27997875, 28204423, 27204133, 27627992, 27592466, 27395794, 27430802
     27511196, 27302800, 27204476, 27941896, 27560702, 27053044, 24689376
     27447687, 27451049, 27302695, 26843664, 27181537, 27744211, 27445462
     26427905, 27106915, 27896388, 27812593, 27926113, 27487795, 27135647
     27496308, 28239335, 27452897, 26986173, 27434050, 27513114, 27114112
     27265816, 27274536, 27396624, 28090453, 27396666, 27573409, 27331069
     27379846, 27270197, 27016503, 27934468, 27595973, 27410595, 27833369
     27577758, 26966120, 27757979, 27434193, 27393421, 27032726, 27613080
     27208953, 27538461, 27581484, 27321179, 27263677, 26898279, 27573154
     27492916, 27961746, 27591842, 27868727, 27047831, 27783289, 27405242
     27970265, 27333693, 27506774, 27112686, 27379233, 27471876, 27425507
     28205874, 27544030, 27401637, 27740854, 27993298, 27193810, 27212208
     27184253, 27288230, 27399499, 27786772, 27978668, 26423085, 27873643
     27481406, 27182006, 27547732, 27889841, 27169796, 27501413, 27679488
     27518310, 27545630, 27346644, 27625010, 27360126, 27378959, 27772815
     27525909, 27945870, 27275136, 27321575, 28182503, 26336101, 27216224
     27375260, 27249544, 27174948, 27294480, 27957892, 27774539, 27664702
     27839732, 27166715, 27432355, 27257509, 27657920, 27190851, 27773602
     27774320, 27508936, 27457666, 27330161, 27339396, 27092991, 27101652
     27803665, 27396672, 27472969, 27579969, 27610269, 27302594, 27778433
     27339495, 25724089, 27739957, 28023410, 27433163, 27222121, 27851757
     28109698, 27732323, 27691809, 27503208, 26822620, 28264172, 26846077
     27189611, 27222423, 28132287, 27121566, 27282707, 27133637, 27451531
     27613247, 27560735, 27702244, 27341181, 28240153, 27479358, 27370933
     27396357, 27153641, 26827699, 27238258, 27364916, 27307868, 27791223
     27041253, 27090765, 27892488, 27034318, 27349393, 27412805, 27399762
     27302960, 27679664, 27815347, 27399985, 27241247, 26654411, 27077948
     26987877, 27354783, 27701795, 27304410, 27882176, 27119621, 26956033
     27300007, 27339165, 28106402, 27451187, 27058530, 21547051, 28025398
     27682288, 27398080, 27586895, 27679806, 27164122, 27243810, 13554903
     27993289, 27504190, 26587652, 27212837, 27274143, 27768034, 27550341
     27558861, 27060167, 27600706, 28022847, 27241221, 27131377, 26992964
     27690578, 27747407, 27305318, 27230645, 27818871, 27346709, 28057267
     27405696, 27523368, 27574335, 27526362, 27174938, 27931506, 27392187
     27221900, 27797290, 28184554, 27401618, 27410300, 26313403, 27864737
     27362190, 27439835, 24737581, 27368850, 27593587, 27751006, 23840305
     26996813, 27625050, 27657467, 27073066, 27302711, 27453225, 27984314
     27274456, 27522245, 27417186, 27469329, 27338946, 27396813, 27786699
     27285244, 27692215, 27519708, 23003564, 27339483, 27783059, 26882316
     27757567, 26527054, 27862636, 27563629, 27635508, 27508985, 26785169
     27472898, 27971575, 28413955, 27302681, 27609819, 27345450, 27788520
     27018734, 27766679, 27101527, 27906509, 27593389, 27625620, 27036408
     27358232, 27335682, 23698980, 27144533, 27585800, 27458164, 22734786
     27523800, 28169711, 27384222, 27723002, 27473800, 27310092, 27604293
     27731346, 27365993, 27005278, 27320985, 27445330, 27649707, 27709046
     27313687, 27249215, 25348956, 27065091, 26433972, 27339654, 27318988
     27333658, 27533819, 27403244, 27520900, 27534651, 27030974, 27427805
     27359368, 23310101, 27644757, 27314512, 27044575, 27223171, 27240570
     27156355, 27528204, 27989849, 27143756, 27679961, 27110878, 25943740
     27747869, 27734470, 27283960, 27682151, 27719187, 26894737, 27869283
     27652302, 27182064, 27467543, 27334353, 26843558, 27840386, 27457891
     27334316, 27372756, 27705761, 27484556, 27708711, 27753336, 27364891
     27964051, 27503318, 27423251, 27865439, 27780562, 26731697, 27358241
     27634676, 27726780, 27444727, 27441326, 27326204, 27812560, 27432338
     27577122, 27177551, 27275776, 27558557, 27375542, 26299684, 27301568
     27593263, 27258578, 27222938, 27703242, 27512439, 27508984, 27398223
     27330158, 27807441, 27079545, 27404599, 27259386, 27688692, 28039471
     27292213, 27392256, 27307934, 27617522, 27505603, 27450400, 27348707
     27460675, 27238077, 27346984, 27364947, 26943660, 27236052, 27338912
     27244337, 28021205, 28032758, 28033429, 27263276, 27579353, 27233563
     27220610, 28099592, 27433870, 27426277, 26647619, 27847259, 25929650
     27738679, 27502420, 25709124, 28045209, 27668379, 27318869, 27832643
     27302777, 28072130, 27442041, 27430219, 27614272, 27930478



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

OPatch succeeded.

The conflict was with 28267731, which is the OJVM patch. I remembered from older 12c versions that the remedy to this was to rollback the older JVM patch before applying the new one. So I ran


C:\oracle\stage\29124511>opatch rollback -id 28267731

and then downloaded the 18.5 OJVM patch (28790647) as well in readiness.

After that, everything went as planned. I applied the DB patch and then applied the 18.5 OJVM patch. The final step was to open my database (and all of the pluggable database) and run in the database-level patch changes using datapatch


Microsoft Windows [Version 10.0.17134.523]
(c) 2018 Microsoft Corporation. All rights reserved.

C:\>set ORACLE_HOME=C:\oracle\product\18

C:\>set PATH=%ORACLE_HOME%\OPatch;%ORACLE_HOME%\perl\bin;%PATH%

C:\>set PERL5LIB=

C:\>cd C:\oracle\product\18\bin

C:\oracle\product\18\bin>sqlplus / as sysdba

SQL*Plus: Release 18.0.0.0.0 - Production on Fri Jan 18 12:33:19 2019
Version 18.5.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.


Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.5.0.0.0


SQL> alter pluggable database all open;

Pluggable database altered.

SQL> exit
Disconnected from Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.5.0.0.0

C:\oracle\product\18\bin>cd %ORACLE_HOME%/OPatch

C:\oracle\product\18\OPatch>datapatch -verbose
SQL Patching tool version 18.0.0.0.0 Production on Fri Jan 18 12:33:32 2019
Copyright (c) 2012, 2019, Oracle.  All rights reserved.

Log file for this invocation: C:\oracle\cfgtoollogs\sqlpatch\sqlpatch_16792_2019_01_18_12_33_32\sqlpatch_invocation.log

Connecting to database...OK
Gathering database info...done

Note:  Datapatch will only apply or rollback SQL fixes for PDBs
       that are in an open state, no patches will be applied to closed PDBs.
       Please refer to Note: Datapatch: Database 12c Post Patch SQL Automation
       (Doc ID 1585822.1)

Bootstrapping registry and package to current versions...done
Determining current state...done

Current state of interim SQL patches:
Interim patch 28267731 (WINDOWS OJVM BUNDLE PATCH : 18.3.0.0.180717 (28267731)):
  Binary registry: Not installed
  PDB CDB$ROOT: Applied successfully on 23-AUG-18 10.31.18.372000 AM
  PDB PDB$SEED: Applied successfully on 23-AUG-18 10.36.41.858000 AM
  PDB PDB1: Applied successfully on 23-AUG-18 10.36.41.858000 AM
Interim patch 28790647 (OJVM RELEASE UPDATE: 18.5.0.0.190115 (28790647)):
  Binary registry: Installed
  PDB CDB$ROOT: Not installed
  PDB PDB$SEED: Not installed
  PDB PDB1: Not installed

Current state of release update SQL patches:
  Binary registry:
    18.5.0.0.0 Release_Update 1812202039: Installed
  PDB CDB$ROOT:
    Applied 18.3.0.0.0 Release_Update 1808132056 successfully on 23-AUG-18 10.31.18.366000 AM
  PDB PDB$SEED:
    Applied 18.3.0.0.0 Release_Update 1808132056 successfully on 23-AUG-18 10.36.41.852000 AM
  PDB PDB1:
    Applied 18.3.0.0.0 Release_Update 1808132056 successfully on 23-AUG-18 10.36.41.852000 AM

Adding patches to installation queue and performing prereq checks...done
Installation queue:
  For the following PDBs: CDB$ROOT PDB$SEED PDB1
    The following interim patches will be rolled back:
      28267731 (WINDOWS OJVM BUNDLE PATCH : 18.3.0.0.180717 (28267731))
    Patch 29124511 (Windows Database Bundle Patch : 18.5.0.0.190115 (29124511)):
      Apply from 18.3.0.0.0 Release_Update 1808132056 to 18.5.0.0.0 Release_Update 1812202039
    The following interim patches will be applied:
      28790647 (OJVM RELEASE UPDATE: 18.5.0.0.190115 (28790647))

Installing patches...
Patch installation complete.  Total patches installed: 9

Validating logfiles...done
Patch 28267731 rollback (pdb CDB$ROOT): SUCCESS
  logfile: C:\oracle\cfgtoollogs\sqlpatch\28267731\22301563/28267731_rollback_DB18_CDBROOT_2019Jan18_12_34_14.log (no errors)
Patch 29124511 apply (pdb CDB$ROOT): SUCCESS
  logfile: C:\oracle\cfgtoollogs\sqlpatch\29124511\22646084/29124511_apply_DB18_CDBROOT_2019Jan18_12_34_47.log (no errors)
Patch 28790647 apply (pdb CDB$ROOT): SUCCESS
  logfile: C:\oracle\cfgtoollogs\sqlpatch\28790647\22646085/28790647_apply_DB18_CDBROOT_2019Jan18_12_35_21.log (no errors)
Patch 28267731 rollback (pdb PDB$SEED): SUCCESS
  logfile: C:\oracle\cfgtoollogs\sqlpatch\28267731\22301563/28267731_rollback_DB18_PDBSEED_2019Jan18_12_35_23.log (no errors)
Patch 29124511 apply (pdb PDB$SEED): SUCCESS
  logfile: C:\oracle\cfgtoollogs\sqlpatch\29124511\22646084/29124511_apply_DB18_PDBSEED_2019Jan18_12_35_37.log (no errors)
Patch 28790647 apply (pdb PDB$SEED): SUCCESS
  logfile: C:\oracle\cfgtoollogs\sqlpatch\28790647\22646085/28790647_apply_DB18_PDBSEED_2019Jan18_12_36_07.log (no errors)
Patch 28267731 rollback (pdb PDB1): SUCCESS
  logfile: C:\oracle\cfgtoollogs\sqlpatch\28267731\22301563/28267731_rollback_DB18_PDB1_2019Jan18_12_35_24.log (no errors)
Patch 29124511 apply (pdb PDB1): SUCCESS
  logfile: C:\oracle\cfgtoollogs\sqlpatch\29124511\22646084/29124511_apply_DB18_PDB1_2019Jan18_12_35_41.log (no errors)
Patch 28790647 apply (pdb PDB1): SUCCESS
  logfile: C:\oracle\cfgtoollogs\sqlpatch\28790647\22646085/28790647_apply_DB18_PDB1_2019Jan18_12_36_12.log (no errors)
SQL Patching tool complete on Fri Jan 18 12:36:17 2019
C:\oracle\product\18\OPatch>
C:\oracle\product\18\OPatch>
C:\oracle\product\18\OPatch>sqlplus / as sysdba

SQL*Plus: Release 18.0.0.0.0 - Production on Fri Jan 18 12:36:28 2019
Version 18.5.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.


Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.5.0.0.0


SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 2936010432 bytes
Fixed Size                  9033408 bytes
Variable Size            1023410176 bytes
Database Buffers         1895825408 bytes
Redo Buffers                7741440 bytes
Database mounted.
Database opened.
SQL> alter pluggable database all open;

Pluggable database altered.

SQL> @?/rdbms/admin/utlrp.sql

Session altered.


TIMESTAMP
----------------------------------------------------------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN              2019-01-18 12:37:58

DOC>   The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC>   objects in the database. Recompilation time is proportional to the
DOC>   number of invalid objects in the database, so this command may take
DOC>   a long time to execute on a database with a large number of invalid
DOC>   objects.
DOC>
DOC>   Use the following queries to track recompilation progress:
DOC>
DOC>   1. Query returning the number of invalid objects remaining. This
DOC>      number should decrease with time.
DOC>         SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC>   2. Query returning the number of objects compiled so far. This number
DOC>      should increase with time.
DOC>         SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC>   This script automatically chooses serial or parallel recompilation
DOC>   based on the number of CPUs available (parameter cpu_count) multiplied
DOC>   by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC>   On RAC, this number is added across all RAC nodes.
DOC>
DOC>   UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC>   recompilation. Jobs are created without instance affinity so that they
DOC>   can migrate across RAC nodes. Use the following queries to verify
DOC>   whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC>   1. Query showing jobs created by UTL_RECOMP
DOC>         SELECT job_name FROM dba_scheduler_jobs
DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC>   2. Query showing UTL_RECOMP jobs that are running
DOC>         SELECT job_name FROM dba_scheduler_running_jobs
DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#

PL/SQL procedure successfully completed.


TIMESTAMP
----------------------------------------------------------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END              2019-01-18 12:38:01

DOC> The following query reports the number of invalid objects.
DOC>
DOC> If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#

OBJECTS WITH ERRORS
-------------------
                  0

DOC> The following query reports the number of exceptions caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC> Note: Typical compilation errors (due to coding errors) are not
DOC>       logged into this table: they go into DBA_ERRORS instead.
DOC>#

ERRORS DURING RECOMPILATION
---------------------------
                          0


Function created.


PL/SQL procedure successfully completed.


Function dropped.


PL/SQL procedure successfully completed.

SQL>

================================

And that was all there was to it. I’ve now picked up over a 1000 fixes and improvements to the software. If you are one of those people who likes to patch rarely, I strongly recommend you have a re-think and take a closer look at the release update cycle. We want to get you better software, faster.

Enjoy your 18.5 release!

Ok, good to know.

Ok, good to know. I know that development was looking at it after my post. So SR should be considered.

DML Tablescans

This note is a follow-up to a recent comment a blog note about Row Migration:

So I wonder what is the difference between the two, parallel dml and serial dml with parallel scan, which makes them behave differently while working with migrated rows. Why might the strategy of serial dml with parallel scan case not work in parallel dml case? I am going to make a service request to get some clarifications but maybe I miss something obvious?

The comment also referenced a couple of MoS notes:

  • Bug 17264297 “Serial DML with Parallel scan performs single block reads during full table scan when table has chained rows in 11.2”
  • Doc ID 1514011.1 “Performance decrease for parallel DML on compressed tables or regular tables after 11.2 Upgrade

The latter document included a comment to the effect that 11.2 uses a “Head Piece Scan” while 11.1 uses a “First Piece scan”, which is a rather helpful comment. Conveniently the blog note itself referenced an earlier note on the potential for differentiating between migrated and chained rows through a “flag” byte associated with each row piece. The flag byte has an H bit for the row head piece, an F bit for the row first piece, and L bit for the row last piece and {no bits set} for a row piece in the middle of a chained row.

Side note: A “typical” simple row will be a single row-piece with the H, F and L bits all set; a simple migrated row will start with an “empty” row-piece in one block with the H bit set and a pointer (nrid – next rowid) to a row in another block that will have the F and L bits set and a pointer (hrid – head rowid) back to the head piece. A chained row could start with a row piece holding a few columns and the H and F bits set and a pointer to the next row piece which might lead to a long chain of row pieces with no bits set each pointing to the next row piece until you get to a row piece with the L bit set.  Alternatively you might have row which had migrated and chained – which means it could start with an empty row piece with just the H bit and a pointer to the next row piece, then a row piece with the F bit set, a back pointer to the header, and a next pointer to the next row piece, which could lead to a long chain of row pieces with no bits set until you reach a row piece with the L bit set.

Combining the comments about “head piece” and “first piece” scans with the general principles of DML and locking it’s now possible to start makings some guesses about why the Oracle developers might want updates through tablescans to behave differently for serial and parallel tablescans. There are two performance targets to consider:

  • How to minimise random (single block) I/O requests
  • How to minimise the risk of deadlock between PX server processes.

Assume you’re doing a serial tablescan to find rows to update – assume for simplicity that there are no chained rows in the table. When you hit a migrated row (H bit only) you could follow the next rowid pointer (nrid) to find and examine the row. If you find that it’s a row that doesn’t need to be updated you’ve just done a completely redundant single block read; so it makes sense to ignore row pieces which are “H”-only row pieces and do a table scan based on “F” pieces (which will be FL “whole row” pieces thanks to our assumption of no chained rows). If you find a row which is an F row and it needs to be updated then you can do a single block read using the head rowid pointer (hrid) to lock the head row piece then lock the current row piece and update it; you only do the extra single block read for rows that need updates, not for all migrated rows. So this is (I guess) the “First Piece Scan” referenced in Doc ID 1514011.1. (And, conversely, if you scan the table looking only for row pieces with the H flag set this is probably the “Head Piece Scan”).

But there’s a potential problem with this strategy if the update is a parallel update. Imagine parallel server process p000 is scanning the first megabyte of a table and process p001 is scanning the second megabyte using the “first piece” algorithm.  What happens if p001 finds a migrated row (flags = FL) that needs to be updated and follows its head pointer back into a block in the megabyte being scanned by p000?  What if p000 has been busy updating rows in that block and there are no free ITLs for p001 to acquire to lock the head row piece? You have the potential for an indefinite deadlock.

On the other hand, if the scan is using the “head piece” algorithm p000 would have found the migrated row’s head piece and followed the next rowid pointer into a block in the megabyte being scanned by p001. If the row needs to be updated p000 can lock the head piece and the migrated piece.

At this point you might think that the two situations are symmetrical – aren’t you just as likely to get a deadlock because p000 now wants an ITL entry in a block that p001 might have been updating? Statistically the answer is “probably not”. When you do lots of updates it is possible for many rows to migrate OUT of a block; it is much less likely that you will see many rows migrate INTO a specific block. This means that in a parallel environment you’re more likely to see several PX servers all trying to acquire ITL entries in the same originating block than you are  to see several PX servers trying to acquire ITL entries in the same destination block. There’s also the feature that when a row (piece) migrates into a block Oracle adds an entry to the ITL list if the number of inwards migrated pieces is more than the current number of ITL entries.

Conclusion

It’s all guesswork of course, but I’d say that for a serial update by tablescan Oracle uses the “first piece scan” to minimise random I/O requests while for a parallel update by tablescan Oracle uses the “head piece scan” to minimise the risk of deadlocks – even though this is likely to increase the number of random (single block) reads.

Finally (to avoid ambiguity) if you’ve done an update which does a parallel tablescan but a serial update (by passing rowids to the query co-ordinator) then I’d hope that Oracle would use the “first piece scan” for the parallel tablescan because there’s no risk of deadlock when only the query co-ordinator is the only process doing the locking and updating, which makes it safe to use the minimum I/O strategy. (And a paralle query with serial update happens quite frequently because people forget to enable parallel dml.)

Footnote

While messing around to see what happened with updates and rows that were both migrated and chained I ran the following script to create one nasty row. so that I could dump a few table blocks to check for ITLs, pointers, and locks. The aim was to get a row with a head-only piece (“H” bit), an F-only piece, a piece with no bits set, then an L-only piece. With an 8KB block size and 4,000 byte maximum for varchar2() this is what I did:


rem
rem     Script:         migrated_lock.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Jan 2019
rem     Purpose:
rem
rem     Last tested
rem             18.3.0.0
rem

create table t1 (
        n1 number,
        l1 varchar2(4000),
        s1 varchar2(200),
        l2 varchar2(4000),
        s2 varchar2(200),
        l3 varchar2(4000),
        s3 varchar2(200)
);

insert into t1 (n1,l1,s1) values(0,rpad('X',4000,'X'),rpad('X',200,'X'));
commit;

insert into t1 (n1,l1) values(1,null);
commit;

update t1 set
        l1 = rpad('A',4000),
        s1 = rpad('A',200),
        l2 = rpad('B',4000),
        s2 = rpad('B',200),
        l3 = rpad('C',4000),
        s3 = rpad('C',200)
where
        n1 = 1
;

commit;

execute dbms_stats.gather_table_stats(user,'t1');

update t1 set
        s1 = lower(s1),
        s2 = lower(s2),
        s3 = lower(s3)
where
        n1 = 1
;

alter system flush buffer_cache;

select
        dbms_rowid.rowid_relative_fno(rowid)    rel_file_no,
        dbms_rowid.rowid_block_number(rowid)    block_no,
        count(*)                                rows_starting_in_block
from
        t1
group by
        dbms_rowid.rowid_relative_fno(rowid),
        dbms_rowid.rowid_block_number(rowid)
order by
        dbms_rowid.rowid_relative_fno(rowid),
        dbms_rowid.rowid_block_number(rowid)
;

The query with all the calls to dbms_rowid gave me the file and block number of the row I was interested in, so I dumped the block, then read the trace file to find the next block in the chain, and so on. The first block held just the head piece, the second block held the n1 and l1 columns (which didn’t get modified by the update), the third block held the s1 and l2 columns, the last block held the s2, l3 and s3 columns. I had been expecting to see the split as (head-piece(, (n1, l1, s1), (l2, s2), (l3, s3) – but as it turned out the unexpected split was a bonus.

Here are extracts from each of the blocks (in the order they appeared in the chain), showing the ITL information and the “row overhead” information. If you scan through the list you’ll see that three of the 4 blocks have an ITL entry for transaction id (xid) of 8.1e.df3, using three consecutive undo records in undo block 0x0100043d. My update has locked 3 of the 4 rowpieces – the header and the two that have changed. It didn’t need to “lock” the piece that didn’t change. (This little detail was the bonus of the unexpected split.)


Block 184
---------
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x000a.00b.00000ee1  0x01000bc0.036a.36  C---    0  scn  0x00000000005beb39
0x02   0x0008.01e.00000df3  0x0100043d.0356.2e  ----    1  fsc 0x0000.00000000

...

tab 0, row 1, @0xf18
tl: 9 fb: --H----- lb: 0x2  cc: 0
nrid:  0x00800089.0



Block 137       (columns n1, l1 - DID NOT CHANGE so no ITL entry acquired)
---------       (the lock byte relates to the previous, not cleaned, update) 
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x000a.00b.00000ee1  0x01000bc0.036a.35  --U-    1  fsc 0x0000.005beb39
0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x03   0x0000.000.00000000  0x00000000.0000.00  C---    0  scn  0x0000000000000000

...

tab 0, row 0, @0xfcb
tl: 4021 fb: ----F--- lb: 0x1  cc: 2
hrid: 0x008000b8.1
nrid:  0x00800085.0



Block 133 (columns s1, l2)
--------------------------
Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x000a.00b.00000ee1  0x01000bc0.036a.34  C---    0  scn  0x00000000005beb39
0x02   0x0008.01e.00000df3  0x0100043d.0356.2f  ----    1  fsc 0x0000.00000000
0x03   0x0000.000.00000000  0x00000000.0000.00  C---    0  scn  0x0000000000000000

...

tab 0, row 0, @0xf0b
tl: 4213 fb: -------- lb: 0x2  cc: 2
nrid:  0x008000bc.0



Block 188 (columns s2, l3, s3)
------------------------------
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x000a.00b.00000ee1  0x01000bc0.036a.33  C---    0  scn  0x00000000005beb39
0x02   0x0008.01e.00000df3  0x0100043d.0356.30  ----    1  fsc 0x0000.00000000
0x03   0x0000.000.00000000  0x00000000.0000.00  C---    0  scn  0x0000000000000000

...

tab 0, row 0, @0xe48
tl: 4408 fb: -----L-- lb: 0x2  cc: 3

Note, by the way, how there are nrid (next rowid) entries pointing forward in every row piece (except the last), but it’s only the “F” (First) row-piece has the hrid (head rowid) pointer pointing backwards.

 

Oracle — Table lock modes

Oracle — Table lock modes

Here is a post with a few links to previous blog/article/video about Oracle table lock modes. And remember that in 12cR2 the event 10704 has been replaced by UTS tracing:

alter session set events 'trace[ksq] disk medium';

1. An explanation of the compatibility matrix (RS, RX, Share, SSX, X):


2. the KSD tracing to see which locks are acquired:

Investigating Oracle lock issues with event 10704 - Blog dbi services

3. A presentation on table locks:

Agenda was:

  • 0:45 — Basics
  • 5:03 — TX lock demo
  • 9:30 — TM lock info
  • 21:04 — Foreign key index demo
  • 28:48–10g, 11g, 12c locking differences
  • 41:50 — New online operations in 12c
  • 46:26 — Event 10704 trace
  • 50:34 — Deadlock demo
  • 53:42 — Q&A

The slides seem to be there:

All About Table Locks: DML, DDL, Foreign Key, Online Operations,... - PDF

4. An article on lock modes:

Hint Reports

Nigel Bayliss has posted a note about a frequently requested feature that has now appeared in Oracle 19c – a mechanism to help people understand what has happened to their hints.  It’s very easy to use, it’s just another format option to the “display_xxx()” calls in dbms_xplan; so I thought I’d run up a little demonstration (using an example I first generated 18 years and 11 versions ago) to make three points: first, to show the sort of report you get, second to show you that the report may tell you what has happened, but that doesn’t necessarily tell you why it has happened, and third to remind you that you should have stopped using the /*+ ordered */ hint 18 years ago.

I’ve run the following code on livesql:


rem
rem     Script:         c_ignorehint.sql
rem     Author:         Jonathan Lewis
rem     Dated:          March 2001
rem


drop table ignore_1;
drop table ignore_2;

create table ignore_1
nologging
as
select
        rownum          id,
        rownum          val,
        rpad('x',500)   padding
from    all_objects
where   rownum <= 3000
;

create table ignore_2
nologging
as
select
        rownum          id,
        rownum          val,
        rpad('x',500)   padding
from    all_objects
where   rownum <= 500
;

alter table ignore_2
add constraint ig2_pk primary key (id);


explain plan for
update
        (
                select
                        /*+
                                ordered
                                use_nl(i2)
                                index(i2,ig2_pk)
                        */
                        i1.val  val1,
                        i2.val  val2
                from
                        ignore_1        i1,
                        ignore_2        i2
                where
                        i2.id = i1.id
                and     i1.val <= 10
        )
set     val1 = val2
;

select * from table(dbms_xplan.display(null,null,'hint_report'));

explain plan for
update
        (
                select
                        /*+
                                use_nl(i2)
                                index(i2,ig2_pk)
                        */
                        i1.val  val1,
                        i2.val  val2
                from
                        ignore_1        i1,
                        ignore_2        i2
                where
                        i2.id = i1.id
                and     i1.val <= 10
        )
set     val1 = val2
;

select * from table(dbms_xplan.display(null,null,'hint_report'));

As you can see I’ve simply added the format option “hint_report” to the call to dbms_xplan.display(). Before showing you the output I’ll just say a few words about the plans we might expect from the two versions of the update statement.

Given the /*+ ordered */ hint in the first statement we might expect Oracle to do a full tablescan of ignore_1 then do a nested loop into ignore_2 (obeying the use_nl() hint) using the (hinted) ig2_pk index. In the second version of the statement, and in the absence of the ordered hint, it’s possible that the optimizer will still use the same path but, in principle, it might find some other path.

So what do we get ? In order here are the two execution plans:


Plan hash value: 3679612214
 
--------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT                      |          |    10 |   160 |   111   (0)| 00:00:01 |
|   1 |  UPDATE                               | IGNORE_1 |       |       |            |          |
|*  2 |   HASH JOIN                           |          |    10 |   160 |   111   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| IGNORE_2 |   500 |  4000 |    37   (0)| 00:00:01 |
|   4 |     INDEX FULL SCAN                   | IG2_PK   |   500 |       |     1   (0)| 00:00:01 |
|*  5 |    TABLE ACCESS STORAGE FULL          | IGNORE_1 |    10 |    80 |    74   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("I2"."ID"="I1"."ID")
   5 - storage("I1"."VAL"<=10)
       filter("I1"."VAL"<=10)
 
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 3 (U - Unused (1))
---------------------------------------------------------------------------
   1 -  SEL$DA9F4B51
           -  ordered
 
   3 -  SEL$DA9F4B51 / I2@SEL$1
         U -  use_nl(i2)
           -  index(i2,ig2_pk)




Plan hash value: 1232653668
 
------------------------------------------------------------------------------------------
| Id  | Operation                     | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT              |          |    10 |   160 |    76   (0)| 00:00:01 |
|   1 |  UPDATE                       | IGNORE_1 |       |       |            |          |
|   2 |   NESTED LOOPS                |          |    10 |   160 |    76   (0)| 00:00:01 |
|   3 |    NESTED LOOPS               |          |    10 |   160 |    76   (0)| 00:00:01 |
|*  4 |     TABLE ACCESS STORAGE FULL | IGNORE_1 |    10 |    80 |    74   (0)| 00:00:01 |
|*  5 |     INDEX UNIQUE SCAN         | IG2_PK   |     1 |       |     0   (0)| 00:00:01 |
|   6 |    TABLE ACCESS BY INDEX ROWID| IGNORE_2 |     1 |     8 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - storage("I1"."VAL"<=10)
       filter("I1"."VAL"<=10)
   5 - access("I2"."ID"="I1"."ID")
 
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 2
---------------------------------------------------------------------------
   5 -  SEL$DA9F4B51 / I2@SEL$1
           -  index(i2,ig2_pk)
           -  use_nl(i2)

As you can see, the “Hint Report” shows us how many hints have been seen in the SQL text, then the body of the report shows us which query block, operation and table (where relevant) each hint has been associated with, and whether it has been used or not.

The second query has followed exactly the plan I predicted for the first query and the report has shown us that Oracle noted, and used, the use_nl() and index() hints to access table ignore2, deciding for itself to visit the tables in the order ignore_1 -> ignore_2, and doing a full tablescan on ignore_1.

The first query reports three hints, but flags the use_nl() hint as unused. (There is (at least) one other flag that could appear against a hint – “E” for error (probably syntax error), so we can assume that this hint is not being ignored because there’s something wrong with it.) Strangely the report tells us that the optimizer has used the ordered hint but we can see from the plan that the tables appear to be in the opposite order to the order we specified in the from clause, and the chosen order has forced the optimizer into using an index full scan on ig2_pk because it had to obey our index() hint.  Bottom line – the optimizer has managed to find a more costly plan by “using but apparently ignoring” a hint that described the cheaper plan that we would have got if we hadn’t used the hint.

Explanation

Query transformation can really mess things up and you shouldn’t be using the ordered hint.

I’ve explained many times over the years that the optimizer evaluates the cost of an update statement by calculating the cost of selecting the rowids of the rows to be updated. In this case, which uses an updatable join view, the steps taken to follow this mechanism this are slightly more complex.  Here are two small but critical extracts from the 10053 trace file (taken from an 18c instance):


CVM:   Merging SPJ view SEL$1 (#0) into UPD$1 (#0)
Registered qb: SEL$DA9F4B51 0x9c9966e8 (VIEW MERGE UPD$1; SEL$1; UPD$1)

...

SQE: Trying SQ elimination.
Query after View Removal
******* UNPARSED QUERY IS *******
SELECT
        /*+ ORDERED INDEX ("I2" "IG2_PK") USE_NL ("I2") */
        0
FROM    "TEST_USER"."IGNORE_2" "I2",
        "TEST_USER"."IGNORE_1" "I1"
WHERE   "I2"."ID"="I1"."ID"
AND     "I1"."VAL"<=10


The optimizer has merged the UPDATE query block with the SELECT query block to produce a select statement that will produce the necessary plan (I had thought that i1.rowid would appear in the select list, but the ‘0’ will do for costing purposes). Notice that the hints have been preserved as the update and select were merged but, unfortunately, the merge mechanism has reversed the order of the tables in the from clause. So the optimizer has messed up our select statement, then obeyed the original ordered hint!

Bottom line – the hint report is likely to be very helpful in most cases but you will still have to think about what it is telling you, and you may still have to look at the occasional 10053 to understand why the report is showing you puzzling results. You should also stop using a hint that was replaced by a far superior hint more than 18 years ago – the ordered hint in my example should have been changed to /*+ leading(i1 i2) */ in Oracle 9i.

SYS.STATS_TARGET$

Here is a little note about the SYS.STATS_TARGET$ table used by the automatic statistics gathering job run at maintenance window, or when running it manually with:

exec dbms_auto_task_immediate.gather_optimizer_stats

This table is not documented and has no view on it, so those are only my guesses about what I observed, and comments are welcome. Basically, this table is used by the Auto Stats job to list the tables to process, from one execution to the other.

Note that in 12c the same information is updated into DBA_OPTSTAT_OPERATION_TASKS and visible through DBMS_STATS.REPORT_STATS_OPERATIONS. But I still use STATS_TARGET$ so see in real-time what is currently processed.

Columns description

STATUS

When the Auto Stats job lists the objects to process, they are in state PENDING (STATUS=0).

Then, when it is its turn to be processed, the START_TIME is set to current timestamp and it can be SKIPPED (STATUS=4) or processed IN PROGRESS (STATUS=1)

Then, when processing ends the END_TIME is set to current timestamp and the status can be COMPLETED (STATUS=2) if successful, or FAILED (STATUS=3) in case of error. If it ends before completion at the end of the maintenance window, the IN PROGRESS and PENDING become TIMED OUT (STATUS=5)

Note that START_TIME and END_TIME have been introduced in 12c

STALENESS

This is similar, but more precise, than the STALE column in DBA_TAB_STATISTICS. Rather than YES/NO we have here an evaluation of staleness (comparing the modifications from DBA_TAB_MODIFICATIONS with the number of rows) in a logarithmic scale between -1.0 and 1.0 where the lowest is the more stale.

TYPE# and OBJ#

This is the OBJECT_TYPE and OBJECT_ID from DBA_OBJECTS. The decode of TYPE# to OBJECT_TYPE is in the DBA_OBJECTS view on OBJ$ definition. Rather than hardcoding it in my queries, I get it from:

select /*+ RESULT_CACHE (SYSOBJ=TRUE)*/ 
distinct type#,object_type
from (select object_id obj#,object_type from dba_objects)
natural join sys.obj$

OSIZE

This is the estimated object size, from the known number of blocks and blocksize, and can be used to estimate roughly the time it takes to gather statistics.

BO# and PART#

Those are the physical identifiers for partitions and subpartitions, tables: it makes the links with the parent object, and between index and table, probably for the purpose of CASCADE gathering.

FLAGS

This is a bitmap with some information about the staleness and the status. Here is my decode (which may be wrong as it is not documented)

ltrim(
case when bitand(flags,1)=1 then ',RETRY' end
|| case when bitand(flags,2)=2 then ',NON-SEGMENT' end
|| case when bitand(flags,4)=4 then ',?' end
|| case when bitand(flags,8)=8 then ',TIMED OUT' end
|| case when bitand(flags,16)=16 then ',?' end
|| case when bitand(flags,32)=32 then ',GATHER GLOBAL' end
|| case when bitand(flags,64)=64 then ',GATHER PARTITION' end
|| case when bitand(flags,128)=128 then ',MISSING COL STATS' end
|| case when bitand(flags,256)=256 then ',STALE STATS' end
|| case when bitand(flags,512)=512 then ',NO STATS' end
|| case when bitand(flags,1024)=1024 then ',HAS DIRECTIVES' end
|| case when bitand(flags,2048)=2048 then ',MISSING EXTENSION' end
|| case when bitand(flags,4096)=4096 then ',MISSING HISTOGRAM' end
|| case when bitand(flags,8192)=8192 then ',CASCADED' end
|| case when bitand(flags,16384)=16384 then ',REPORTING RUN' end
|| case when bitand(flags,32768)=32768 then ',FIXED TABLE' end
|| case when bitand(flags,65536)=65536 then ',SYNOPSIS MISMATCH' end
,',') flags

RETRY is for the gathering which was IN PROGRESS and was stopped with TIMED OUT.

SID, SERIAL#

The last session running the Auto Stats (i.e PENDING, IN PROGRESS, TIMED OUT) is identified here so you can join with V$SESSION or even V$ACTIVE_SESSION_HISTORY to get more information afterward about the duration (which is END_TIME-START_TIME). You can also find the long-running ones in V$SQL_MONITOR.

Query examples

details

Here is an example while the Auto Stats job is running. This shows which table is currently gathered (IN PROGRESS), already done (COMPLETED) or to be done (PENDING). The flags indicate that the previous execution was TIMED OUT, and which is global level gathering.

I also join with V$SESSION in order to see the currently running job (and its login time) as I’m in 11g without the START_TIME:

Here is the query I used for this output:

select logon_time
--,start_time,end_time,end_time-start_time duration
--,start_time-lag(end_time)over(partition by sid,serial# order by start_time) after_previous,
,object_type,owner,object_name,subobject_name,sid||','||serial# "sid/ser#",round(osize/1024/1024/1024) GBytes
,rtrim(case status when 0 then 'PENDING' when 1 then 'IN PROGRESS' when 2 then 'COMPLETED' when 3 then 'FAILED'
when 4 then 'SKIPPED' when 5 then 'TIMEOUT' end) STATUS,status status#
,case staleness when -100 then 'MISSING' when -99 then null
else rtrim('STALE '||lpad(' ',round(2*(1+(staleness))),'+')) end staleness
,ltrim(
case when bitand(flags,1)=1 then ',TIMED OUT' end
|| case when bitand(flags,2)=2 then ',NON-SEGMENT' end
|| case when bitand(flags,4)=4 then ',4' end
|| case when bitand(flags,8)=8 then ',TIMED_OUT' end
|| case when bitand(flags,16)=16 then ',16' end
|| case when bitand(flags,32)=32 then ',GATHER GLOBAL' end
|| case when bitand(flags,64)=64 then ',GATHER PARTITION' end
|| case when bitand(flags,128)=128 then ',MISSING CSTATS' end
|| case when bitand(flags,256)=256 then ',STALE STATS' end
|| case when bitand(flags,512)=512 then ',NO STATS' end
|| case when bitand(flags,1024)=1024 then ',HAS DIRECTIVES' end
|| case when bitand(flags,2048)=2048 then ',MISSING EXTENSION' end
|| case when bitand(flags,4096)=4096 then ',MISSING HISTOGRAM' end
|| case when bitand(flags,8192)=8192 then ',CASCADED' end
|| case when bitand(flags,16384)=16384 then ',REPORTING RUN' end
|| case when bitand(flags,32768)=32768 then ',FIXED TABLE' end
|| case when bitand(flags,65536)=65536 then ',SYNOPSIS MISMATCH' end
,',') flags
from (
select *
from STATS_TARGET$
natural left outer join (select /*+ result_cache */ distinct type#,object_type from (select object_id obj#,object_type from dba_objects) natural join sys.obj$)
natural left outer join (select object_id obj#,owner,object_name,subobject_name from dba_objects)
natural left outer join (select sid,serial#,program,sql_id,logon_time from gv$session)
left outer join (select bo#,part#,hiboundval,analyzetime,obj# part_obj# from sys.tabpart$) using(bo#,part#)
) v
--order by end_time desc nulls last, start_time desc nulls last
order by logon_time desc nulls last,status#
/

summary

Here is another query which checks the global status while the Auto Stats job is running:

select logon_time,status,staleness,count(*),sum(GBytes) GBytes
,object_type,round(100*sum(GBytes)/max(TotGB)) "%"
from (
select logon_time,object_type,round(osize/1024/1024/1024) GBytes,sum(osize/1024/1024/1024)over(partition by logon_time,object_type) TotGB
,rtrim(case status when 0 then 'PENDING' when 1 then 'IN PROGRESS' when 2 then 'COMPLETED' when 3 then 'FAILED' when 4 then 'SKIPPED' when 5 then 'TIMEOUT' end) STATUS
,case staleness when -100 then 'MISSING' when -99 then null else 'STALE' end staleness
from STATS_TARGET$
natural left outer join (select /*+ result_cache */ distinct type#,object_type from (select object_id obj#,object_type from dba_objects) natural join sys.obj$)
natural left outer join (select sid,serial#,program,sql_id,logon_time from gv$session)
)
group by logon_time,object_type,status,staleness having logon_time is not null
order by logon_time nulls last,status,object_type,staleness;
LOGON_TIME        STATUS      STALENE   COUNT(*)     GBYTES
----------------- ----------- ------- ---------- ----------
15-JAN-2019 08:44 COMPLETED STALE 288 2177
15-JAN-2019 08:44 IN PROGRESS STALE 1 210
15-JAN-2019 08:44 PENDING MISSING 58744 18311
15-JAN-2019 08:44 PENDING STALE 2567 8579
15-JAN-2019 08:44 PENDING 55 0

Processing order

As this table is used to list the tables to process, the columns are used to order it. We know that the Auto Stats starts with the most stale (and MISSING is the first there). But before that, the tables are listed before the partitions, and partitions before subpartition. And tables are processed before indexes.

EXPORT not GATHER with DBMS_STATS

Just a short post today on something that came in as a question for the upcoming Office Hours session which I thought could be covered quickly in a blog post without needing a lot of additional discussion for which Office Hours is more suited to.

The question was:

“When I gather statistics using DBMS_STATS, can I just create a statistic table and pass that as a parameter to get the results of the gather”

And the answer simply is “No” Smile but let me clear up the confusion.

Many DBMS_STATS routines allow you to pass the name of the “statistics table” into which statistical information for the table, schema, database etc will be stored. For example, you can see the STAT-prefixed parameter to GATHER_TABLE_STATS


PROCEDURE GATHER_TABLE_STATS
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 OWNNAME                        VARCHAR2                IN
 TABNAME                        VARCHAR2                IN
 PARTNAME                       VARCHAR2                IN     DEFAULT
 ESTIMATE_PERCENT               NUMBER                  IN     DEFAULT
 BLOCK_SAMPLE                   BOOLEAN                 IN     DEFAULT
 METHOD_OPT                     VARCHAR2                IN     DEFAULT
 DEGREE                         NUMBER                  IN     DEFAULT
 GRANULARITY                    VARCHAR2                IN     DEFAULT
 CASCADE                        BOOLEAN                 IN     DEFAULT
 STATTAB                        VARCHAR2                IN     DEFAULT
 STATID                         VARCHAR2                IN     DEFAULT
 STATOWN                        VARCHAR2                IN     DEFAULT
 NO_INVALIDATE                  BOOLEAN                 IN     DEFAULT
 STATTYPE                       VARCHAR2                IN     DEFAULT
 FORCE                          BOOLEAN                 IN     DEFAULT
 CONTEXT                        CCONTEXT                IN     DEFAULT
 OPTIONS                        VARCHAR2                IN     DEFAULT

The statistics table must be of a certain structure, which is managed via the API as well using the CREATE_STAT_TABLE routine.


SQL> exec dbms_stats.create_stat_table('','st')

PL/SQL procedure successfully completed.

SQL> desc ST
 Name                          Null?    Type
 ----------------------------- -------- --------------------
 STATID                                 VARCHAR2(128)
 TYPE                                   CHAR(1)
 VERSION                                NUMBER
 FLAGS                                  NUMBER
 C1                                     VARCHAR2(128)
 C2                                     VARCHAR2(128)
 C3                                     VARCHAR2(128)
 C4                                     VARCHAR2(128)
 C5                                     VARCHAR2(128)
 C6                                     VARCHAR2(128)
 N1                                     NUMBER
 N2                                     NUMBER
 N3                                     NUMBER
 N4                                     NUMBER
 N5                                     NUMBER
 N6                                     NUMBER
 N7                                     NUMBER
 N8                                     NUMBER
 N9                                     NUMBER
 N10                                    NUMBER
 N11                                    NUMBER
 N12                                    NUMBER
 N13                                    NUMBER
 D1                                     DATE
 T1                                     TIMESTAMP(6) WITH TI
                                        ME ZONE
 R1                                     RAW(1000)
 R2                                     RAW(1000)
 R3                                     RAW(1000)
 CH1                                    VARCHAR2(1000)
 CL1                                    CLOB
 BL1                                    BLOB

But when calling the GATHER_xxx routines, if you pass the name of the statistic table, please note that this is for getting a copy of the relevant statistics before the fresh gathering of statistics is done. We can see this with a simple demo.


SQL> create table t1 as select * from dba_objects;

Table created.

SQL>
SQL> select num_rows
  2  from   user_tables
  3  where  table_name = 'T1';

  NUM_ROWS
----------
     83608

1 row selected.

So we can see that the table T1 has ~83,000 rows in it. Since I’m running this on 12c, there was no need to gather statistics after this initial load, because they were collected automatically as part of the loading process. (Very nifty!).

I now add another ~250,000 rows and perform a new GATHER_TABLE_STATS call, this time passing in the name of the statistics table (ST) that I just created.


SQL>
SQL> insert into t1
  2  select * from t1;

83608 rows created.

SQL>
SQL> insert into t1
  2  select * from t1;

167216 rows created.

SQL>
SQL> begin
  2  dbms_stats.gather_table_stats
  3   ( ownname=>'MCDONAC',
  4     tabname=>'T1',
  5     stattab=>'ST',
  6     statid=>'STATS'
  7  );
  8  end;
  9  /

PL/SQL procedure successfully completed.

Digging into the statistic table data, you can see that the number of rows recorded for T1 is 83597, which is the before image of the optimizer stat, not the after image.


SQL> select * from st where c1 = 'T1' and type = 'T'
  2  @pr
==============================
STATID                        : STATS
TYPE                          : T
VERSION                       : 8
FLAGS                         : 2
C1                            : T1
C2                            :
C3                            :
C4                            :
C5                            : MCDONAC
C6                            :
N1                            : 83597
N2                            : 1607
N3                            : 129
N4                            : 83597
N5                            :
N6                            :
N7                            :
N8                            :
N9                            : 0
N10                           :
N11                           :
N12                           :
N13                           :
D1                            : 16-JAN-19
T1                            :
R1                            :
R2                            :
R3                            :
CH1                           :
CL1                           :
BL1                           :

PL/SQL procedure successfully completed.

So just remember that if you want to get the DBMS_STATS information that is the result of a GATHER_xxx call, then you can follow it up with the appropriate EXPORT_xxx call to dump the data.

Announcement: New “Oracle Diagnostics and Performance Tuning” Seminar Now Available !!

It’s been a work in progress for quite some time, with many of my customers asking when will it be completed. Well, I’m very excited to announce that I have finally completed my new 2 day “Oracle Diagnostics and Performance Tuning” seminar and that it’s ready to be presented. I already have a number of […]

My APEX was fine and then it wasn’t

I got a nasty shock this morning when I fired up my local Application Expression installation.

image

It had been working fine and all of a sudden…just dead. I sounded like all of those family members that as I.T practitioners we have to deal with (and that we’re so sceptical of) when they say: “I didn’t change anything…it just stopped!” Smile

In keeping with the treatment of family members, I then adopted the advice that I normally give them first.

turning_on_and_off

and upon restart, I saw the following during the startup


C:\oracle\ords181>java -jar ords.war standalone
2019-01-11 11:47:36.071:INFO::main: Logging initialized @1378ms to org.eclipse.jetty.util.log.StdErrLog
Jan 11, 2019 11:47:36 AM
INFO: HTTP and HTTP/2 cleartext listening on port: 8080
Jan 11, 2019 11:47:36 AM
INFO: The document root is serving static resources located in: C:\oracle\ords181\conf\ords\standalone\doc_root
2019-01-11 11:47:36.409:INFO:oejs.Server:main: jetty-9.4.z-SNAPSHOT, build timestamp: 2017-11-22T05:27:37+08:00, git hash: 82b8fb23f757335bb3329d540ce37a2a2615f0a8
2019-01-11 11:47:36.422:INFO:oejs.session:main: DefaultSessionIdManager workerName=node0
2019-01-11 11:47:36.423:INFO:oejs.session:main: No SessionScavenger set, using defaults
2019-01-11 11:47:36.423:INFO:oejs.session:main: Scavenging every 600000ms
Jan 11, 2019 11:47:37 AM
WARNING: The pool named: |apex|| is invalid and will be ignored: The connection pool named: apex is not correctly configured, due to the following error(s): ORA-28001: the password has expired

Jan 11, 2019 11:47:37 AM
WARNING: The pool named: |apex|al| is invalid and will be ignored: The connection pool named: apex_al is not correctly configured, due to the following error(s): ORA-28001: the password has expired

Since security is our #1 thing for 2019 and probably should be the #1 item on your agenda for 2019, this was caused by some improvements to the Oracle defaults when you perform a database installation. Rather than the default being an “flexible” (aka loose Smile) policy we used to have when it comes to password management, we’ve gone for some more sensible options out of the box.


SQL> select * from dba_profiles order by 1,2
PROFILE          RESOURCE_NAME                    RESOURCE LIMIT
---------------- -------------------------------- -------- -----------
DEFAULT          COMPOSITE_LIMIT                  KERNEL   UNLIMITED
DEFAULT          CONNECT_TIME                     KERNEL   UNLIMITED
DEFAULT          CPU_PER_CALL                     KERNEL   UNLIMITED
DEFAULT          CPU_PER_SESSION                  KERNEL   UNLIMITED
DEFAULT          FAILED_LOGIN_ATTEMPTS            PASSWORD 10
DEFAULT          IDLE_TIME                        KERNEL   UNLIMITED
DEFAULT          INACTIVE_ACCOUNT_TIME            PASSWORD UNLIMITED
DEFAULT          LOGICAL_READS_PER_CALL           KERNEL   UNLIMITED
DEFAULT          LOGICAL_READS_PER_SESSION        KERNEL   UNLIMITED
DEFAULT          PASSWORD_GRACE_TIME              PASSWORD 7
DEFAULT          PASSWORD_LIFE_TIME               PASSWORD 180
DEFAULT          PASSWORD_LOCK_TIME               PASSWORD 1
DEFAULT          PASSWORD_REUSE_MAX               PASSWORD UNLIMITED
DEFAULT          PASSWORD_REUSE_TIME              PASSWORD UNLIMITED
DEFAULT          PASSWORD_VERIFY_FUNCTION         PASSWORD NULL
DEFAULT          PRIVATE_SGA                      KERNEL   UNLIMITED
DEFAULT          SESSIONS_PER_USER                KERNEL   UNLIMITED

So if you want your APEX public accounts (and I stress, no others!) to have a non-expiring password, then you should create a custom profile for those accounts and assign them accordingly.


create profile no_expire 
limit  password_life_time  unlimited;
  
alter user apex_public_user      profile no_expire ;
alter user apex_rest_public_user profile no_expire ;
alter user apex_listener         profile no_expire ;
alter user ords_public_user      profile no_expire ;

This will ensure you don’t get an unexpected drama next time you want to fire up Application Express.

Flamegraph with function annotation

Recently, Franck Pachot tweeted the following:
https://fritshoogland.files.wordpress.com/2019/01/tweet-franck.png?w=84&... 84w, https://fritshoogland.files.wordpress.com/2019/01/tweet-franck.png 310w" sizes="(max-width: 168px) 100vw, 168px" />
This is a very clever way of using Brendan Gregg’s flame graphs for Oracle database process flame graphs and using my ora_functions project database.

However, the awk script uses a full match using f[$2]:
– f is an array with the function name annotations filled using {f[$1]=$2;next}.
– // means to only work on lines that have in it.
– The lines have the oracle function stuck to , for example: kcbgtcr.
– The first sub() function (substitute); which is sub(“”,”& “) places a space after “” so it looks like: kcbgtcr; “&” means “the first argument of the sub function”.
– FS=” ” means the field separator is a space.
– The next sub() function; which is sub(“”,””f[$2]”&”) substitutes on the pattern, and uses the f array using the second field ($2) of the input, which now is the oracle function thanks to the field separator and the previous sub() function.

But…the function names in the database/csv file are build up on layers. I done this deliberately to avoid having to type all the layer names for a function when entering new ones, but more importantly so I can actually identify the code layers in the oracle executable. Because of the layers, I don’t need the full function descriptions, I can still get an understanding what is going on by looking in what layer a function is called.

To get the layers in the function names, I added a script in my ora_functions repository called ‘annotate_flamegraph.awk’, which takes the function name csv file actually exactly like Franck did, but now builds up the annotation of every function in an existing flamegraph. It simply takes an existing flamegraph SVG file with oracle database functions as an input, and outputs the annotated version on STDOUT, so this is how it’s run to get an annotated SVG:

$ ./annotate_flamegraph.awk dt_11856.txt_1.fold.svg > dt_11856.txt_1.fold.annotated.svg

This is how it looks like (please mind these are snapshots from flamegraph output in a browser):
https://fritshoogland.files.wordpress.com/2019/01/flamegraph-annotation1... 600w, https://fritshoogland.files.wordpress.com/2019/01/flamegraph-annotation1... 150w" sizes="(max-width: 300px) 100vw, 300px" />
(click on the graph to see it in the original size)
If you hoover over a row in the flamegraph, it will show the full information at the bottom row, in this case I hoovered over ttcpip.
If you click a row, it will zoom in to the row. The next picture is a good way to show that the function layers and the stack make it obvious what is going on, while most of the functions do not have the full annotation:
https://fritshoogland.files.wordpress.com/2019/01/flamegraph-annotation3... 600w, https://fritshoogland.files.wordpress.com/2019/01/flamegraph-annotation3... 150w" sizes="(max-width: 300px) 100vw, 300px" />
(click on the graph to see it in the original size)

Again, thanks to Franck for the inspiration. I hope this will be useful for investigating what oracle is doing!