Oracle Database

Oracle Database Proactive Bundle 12.1.0.2 April 2018 changes DataPump File Format Number

By | June 11th, 2018 | Category: 12cR1, Bugs | No Comments »

After applying Oracle Database Proactive Bundle Patch (DBBP) 12.1.0.2 April 2018 (Patch 27486326), the datapump dump file internal format is changed from version 4.1 to version 4.2. The effect is, that a dump file created with DataPump from Version 12.1.0.2 DBBP 180417 can no longer be imported in any lower database except when the datapump export is performed with VERSION=12.1 clause.

MOS 462488.1 gives a script to extract Dump File Version from file:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40

SQL> exec show_dumpfile_info('DATA_PUMP_DIR','test.dmp'); 
---------------------------------------------------------------------------- 
Purpose..: Obtain details about export dumpfile. Version: 18-DEC-2013 
Required.: RDBMS version: 10.2.0.1.0 or higher 
. Export dumpfile version: 7.3.4.0.0 or higher 
. Export Data Pump dumpfile version: 10.1.0.1.0 or higher 
Usage....: execute show_dumfile_info('DIRECTORY', 'DUMPFILE'); 
Example..: exec show_dumfile_info('MY_DIR', 'expdp_s.dmp') 
---------------------------------------------------------------------------- 
Filename.: test.dmp 
Directory: DATA_PUMP_DIR 
Disk Path: ...
Filetype.: 1 (Export Data Pump dumpfile) 
---------------------------------------------------------------------------- 
...Database Job Version..........: 12.01.00.02.00 
...Internal Dump File Version....: 4.2 
...Creation Date.................: Tue Jun 05 16:23:31 2018 
...File Number (in dump file set): 1 
...Master Present in dump file...: 1 (Yes) 
...Master in how many dump files.: 1 
...Master Piece Number in file...: 1 
...Operating System of source db.: x86_64/Linux 2.4.xx 
...Instance Name of source db....: myhost:MYINST 
...Characterset ID of source db..: 178 (WE8MSWIN1252) 
...Language Name of characterset.: WE8MSWIN1252 
...Job Name......................: "MYUSER"."SYS_EXPORT_FULL_01" 
...GUID (unique job identifier)..: 6DE6CAA3E6377A30E053A505120A8FB1 
...Block size dump file (bytes)..: 4096 
...Metadata Compressed...........: 1 (Yes) 
...Data Compressed...............: 0 (No) 
...Compression Algorithm.........: 3 (Basic) 
...Metadata Encrypted............: 0 (No) 
...Table Data Encrypted..........: 0 (No) 
...Column Data Encrypted.........: 0 (No) 
...Encryption Mode...............: 2 (None) 
...Internal Flag Values..........: 514 
...Max Items Code (Info Items)...: 23 
---------------------------------------------------------------------------- 
 
PL/SQL procedure successfully completed.

When trying to import dump in lower version database, this error is raised:

1
2
3

ORA-39001: invalid argument value 
ORA-39000: bad dump file specification 
ORA-39142: incompatible version number 4.2 in dump file "test.dmp"

Only workaround is to use option “VERSION=12.1” when performing the datapump export.

Update 14.06.2018: Oracle Support has published a MOS Note for this issue today: 2409523.1

Update 16.07.2018: A month after we had experienced the issue, oracle has published an “Alert” in MOS this MOS Note: Alert – Regression in DataPump After Applying 12.1.0.2.180417DBBP or 12.1.0.2.180717DBBP (Doc ID 2422236.1)
In addition, a patch is now available: 21480031


Beware of loopback MTU size with RAC on Oracle Linux 7

By | May 12th, 2018 | Category: 11gR2, Linux, Oracle Database | 7 comments

I recently had to troubleshoot a hung instance in a 2 node RAC system. 4 months earlier, the system was reinstalled in a rolling fashion due to the requirement of Linux Upgrade from Oracle Linux 5 to Oracle Linux 7. This was required because of lack of certification for a storage migration to an AllFlash Storage. The system has been stable when running with Oracle Linux 5 for several years. Around 4 months after the reinstallation, one node got hung with and traces showed these error messages:

1
2
3
4
5
6
7
8
9

Mon Feb 26 08:53:37 2018
skgxpvfynet: mtype: 61 process 15801 failed because of a resource problem in the OS. The OS has most likely run out of buffers (rval: 4)
Errors in file /u01/app/oracle/diag_p/diag/rdbms/prod/PROD2/trace/PROD2_ora_15801.trc (incident=480004):
ORA-00603: ORACLE server session terminated by fatal error
ORA-27504: IPC error creating OSD context
ORA-27300: OS system dependent operation:sendmsg failed with status: 105
ORA-27301: OS failure message: No buffer space available
ORA-27302: failure occurred at: sskgxpsnd2
Incident details in: /u01/app/oracle/diag_p/diag/rdbms/prod/PROD2/incident/incdir_480004/PROD2_ora_15801_i480004.trc

This was strange because OS /proc/meminfo was showing huge amounts of free memory for this physical host with 512GB of RAM.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48

[root@node17 ~]# cat /proc/meminfo 
zzz ***Mon Feb 26 08:53:09 CET 2018
MemTotal:       528028424 kB
MemFree:        14593828 kB
MemAvailable:   78305772 kB
Buffers:        28009752 kB
Cached:         46896496 kB
SwapCached:            0 kB
Active:         22627436 kB
Inactive:       66945168 kB
Active(anon):   14315300 kB
Inactive(anon):  2105748 kB
Active(file):    8312136 kB
Inactive(file): 64839420 kB
Unevictable:      363996 kB
Mlocked:          364020 kB
SwapTotal:      33554428 kB
SwapFree:       33554428 kB
Dirty:               404 kB
Writeback:             0 kB
AnonPages:      15768480 kB
Mapped:           709896 kB
Shmem:            945384 kB
Slab:            2462644 kB
SReclaimable:    2092232 kB
SUnreclaim:       370412 kB
KernelStack:       28336 kB
PageTables:       395568 kB
NFS_Unstable:          0 kB
Bounce:                0 kB
WritebackTmp:          0 kB
CommitLimit:    87853440 kB
Committed_AS:   22087384 kB
VmallocTotal:   34359738367 kB
VmallocUsed:     1106260 kB
VmallocChunk:   34085810172 kB
HardwareCorrupted:     0 kB
AnonHugePages:         0 kB
CmaTotal:          16384 kB
CmaFree:               0 kB
HugePages_Total:   204800
HugePages_Free:    20214
HugePages_Rsvd:     6266
HugePages_Surp:        0
Hugepagesize:       2048 kB
DirectMap4k:    36827756 kB
DirectMap2M:    78444544 kB
DirectMap1G:    423624704 kB

Oracle Support then referred to this MOS Note:

ORA-27301: OS Failure Message: No Buffer Space Available / ORA-27302: failure occurred at: sskgxpsnd2 ( Doc ID 2322410.1 )

It turned out that on systems with a lot of physical memory and on Oracle Linux 7, the MTU size of loopback adapter lo0 has to be reduced from the default value of 64k to 16436 bytes to avoid memory fragmentation in RAC. The note also mentioned that the parameter vm.min_free_kbytes should be set to physmem * 0,004 * (here 2). This requirement was already considered during installation of the system, so the only thing missing was the MTU size.

I was very surprised that neither Cluster Verification Utility (CVU), nor orachk in most recent version did catch this problem at the point of installation. In my opinion, if default value of MTU size of loopback interface on Oracle Linux 7 has the potential to cause an outage, then this must be pre-checked by CVU at installation time or at least integrated into orachk. Unfortunately, this is not the case. It seems that in July we will know if the on-prem release 18.3.0 eventually will catch and enforce this configuration requirement during installation time.


Patch Recommendations for Oracle Database 12cR1 and Cloud Control 13cR2

By | May 11th, 2018 | Category: 12cR1, 12cR2, 13cR2, Oracle Database, Oracle Enterprise Manager | 1 Comment »

During my consulting engagements I see a lot of systems and many bugs. Most of the time, there is already a patch available to avoid the bug. I have collected all the recommended patches for the Oracle Database 12.1.0.2 (SE2 and EE) and Oracle Enterprise Manager 13cR2. This should help to avoid most critical known issues. Versions 12.2.0.1 and 18c will be added later this year.

Patch Recommendations

Update: 18.05.2018: I have added a list of recommended patches for release 12.2.0.1.
Update: 17.07.2018: Updated EM 13.2 Patches


BAAS: Battle Against Archiver Stuck – ALTERNATE Archive Location

By | April 27th, 2018 | Category: 10g, 11g, 11gR2, 12cR1, 12cR2, Oracle Database | No Comments »

If you are managing volatile systems, you might have encountered Archiver-Stuck in the past.

1
2

0RA-00257:archiver error, connect internal only until freed
ORA-16014:log 2 sequence# 1789 not archived, no available destinations

If this is the case, you might be interested in a rarely used feature called “ALTERNATE Archive Locations”. This is a second independent archive log destination, that is only used in case the primary destination is failing (full). In that case, the dest_2 is automatically enabled, preventing stuck database. As soon as the archivelog backup has cleaned up the full DEST_1 destination, there is an AUTOMATIC FALLBACK to the DEST_1 destination.

1
2
3
4

ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=/disk1 MANDATORY MAX_FAILURE=1 ALTERNATE=LOG_ARCHIVE_DEST_2';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1=ENABLE;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ALTERNATE;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='LOCATION=/disk2 MANDATORY ALTERNATE=LOG_ARCHIVE_DEST_1';

Testing:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

Thread 1 advanced to log sequence 174 (LGWR switch)
  Current log# 3 seq# 174 mem# 0: /u01/app/oracle/oradata/CDB122/redo03.log
2018-04-27T17:18:47.685736+02:00
ARC3: Encountered disk I/O error 19502
2018-04-27T17:18:47.685800+02:00
ARC3: Closing local archive destination LOG_ARCHIVE_DEST_1 '/disk1/1_173_971869447.dbf' (error 19502) (CDB122)
2018-04-27T17:18:47.687074+02:00
Errors in file /u01/app/oracle/diag/rdbms/cdb122/CDB122/trace/CDB122_arc3_31260.trc:
ORA-27072: File I/O error
Additional information: 4
Additional information: 65536
Additional information: 315392
ORA-19502: write error on file "/disk1/1_173_971869447.dbf", block number 65536 (block size=512)
2018-04-27T17:18:47.816938+02:00
Errors in file /u01/app/oracle/diag/rdbms/cdb122/CDB122/trace/CDB122_arc3_31260.trc:
ORA-19502: write error on file "/disk1/1_173_971869447.dbf", block number 65536 (block size=512)
ORA-27072: File I/O error
Additional information: 4
Additional information: 65536
Additional information: 315392
ORA-19502: write error on file "/disk1/1_173_971869447.dbf", block number 65536 (block size=512)
ARC3: I/O error 19502 archiving log 2 to '/disk1/1_173_971869447.dbf'

After the error, archivelogs are placed in /dir2 directory and as soon as space is available again, archive destination is switched back to /dir1 automatically.

Archiver Stuck never again, right?


Modifications to hidden parameters between 12.1.0.2 and 12.2.0.1

By | April 26th, 2018 | Category: 12cR1, 12cR2, Oracle Database, Uncategorized | No Comments »

I recently troubleshooted an issue with RMAN catalog database using seperate schemas for each target database and more than 300 schemas present. Catalog Database was on 12.2.0.1 and suffering from severe mutex contention because of huge version counts for RMAN catalog SQL statements. I then realized that the default number of max child cursors was increased from 1024 to 8192 in 12.2.0.1. This made me check what other parameters were changed. In my opinion, especially interesting are parameters, that were OFF / FALSE in 12.1.0.2 and ON / TRUE in 12.2.0.1 as this probably activates new code.

This SQL Statement shows all parameters, including hidden parameters:

1
2
3
4
5
6
7
8
9

-- file pd2.sql
SET LINES 3000 pages 0 feedback off trimspool ON
SELECT
 n.ksppinm ||'|'||
  c.ksppstvl ||'|'||
  n.ksppdesc
FROM sys.x$ksppi n, sys.x$ksppcv c
WHERE n.indx=c.indx
;

Then, I spooled Pipe-seperated output to text files for both releases:

1
2
3
4
5
6
7
8
9
10
11

. ~/121.env
sqlplus / as sysdba <<EOF
spool /tmp/121.txt
@/tmp/pd2.sql
EOF
 
. ~/122.env
sqlplus / as sysdba <<EOF
spool /tmp/122.txt
@/tmp/pd2.sql
EOF

Finally, I set up External Tables for accessing these files with SQL:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50

sqlplus / as sysdba
 
drop table db122 purge;
drop table db121 purge;
 
CREATE TABLE db122 (
  param      VARCHAR2(2000),
  param_val      VARCHAR2(2000),
  param_desc     VARCHAR2(2000)
)
ORGANIZATION EXTERNAL (
  TYPE ORACLE_LOADER
  DEFAULT DIRECTORY tmp_dir
  ACCESS PARAMETERS (
    RECORDS DELIMITED BY NEWLINE
    FIELDS TERMINATED BY '|'
    MISSING FIELD VALUES ARE NULL
    (
      param CHAR(2000)   , 
      param_val  CHAR(2000),    
      param_desc CHAR(2000)
    )
  )
  LOCATION ('122.txt')
)
PARALLEL 1
REJECT LIMIT UNLIMITED;
 
CREATE TABLE db121 (
  param      VARCHAR2(2000),
  param_val      VARCHAR2(2000),
  param_desc     VARCHAR2(2000)
)
ORGANIZATION EXTERNAL (
  TYPE ORACLE_LOADER
  DEFAULT DIRECTORY tmp_dir
  ACCESS PARAMETERS (
    RECORDS DELIMITED BY NEWLINE
    FIELDS TERMINATED BY '|'
    MISSING FIELD VALUES ARE NULL
    (
      param   CHAR(2000)  , 
      param_val  CHAR(2000),    
      param_desc CHAR(2000)
    )
  )
  LOCATION ('121.txt')
)
PARALLEL 1
REJECT LIMIT UNLIMITED;

The final query now compares hidden parameters:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114

SET LINES 500 pages 2000 tab off
col param format a56
col db121_value format  a20
col db122_value format  a20
col param_desc heading DESCRIPTION format a100 word_wrap
SELECT db121.param, db121.param_val AS db121_value, db122.param_val AS db122_value, db122.param_desc FROM 
(SELECT param, param_val, param_desc FROM db121) db121,
(SELECT param, param_val, param_desc FROM db122) db122
WHERE db121.param = db122.param AND db121.param_val != db122.param_val  AND db121.param LIKE '\_%' escape '\'
and db121.param not IN (
 '__db_cache_size',
 '__pga_aggregate_target',
 '__pga_aggregate_target',
 '__shared_pool_size',
 '__streams_pool_size',
 '_diag_adr_trace_dest',
 '__large_pool_size')
order by db121.param; 
 
 
PARAM                                                    DB121_VALUE          DB122_VALUE          DESCRIPTION
-------------------------------------------------------- -------------------- -------------------- ----------------------------------------------------------------------------------------------------
_adaptive_scalable_log_writer_disable_worker_threshold   90                   50                   Percentage of overlap across multiple outstanding writes
_advanced_index_compression_options_value                20                   0                    advanced index compression options2
_aq_latency_absolute_threshold                           100                  300                  Absolute threshold greater than average latency
_aq_latency_relative_threshold                           30                   100                  Relative threshold of average latency
_aq_lb_stats_collect_cycle                               120                  45                   Time(seconds) between consecutive AQ load statistics collection
_aq_lookback_size                                        30                   60                   AQ PT Look Back Size
_aq_pt_shrink_frequency                                  30                   1450                 PT shrink window Size
_aq_pt_statistics_window                                 30                   60                   PT statistics sample window Size
_asm_compatibility                                       10.1                 11.2.0.2             default ASM compatibility level
_asm_max_cod_strides                                     5                    10                   maximum number of COD strides
_asm_scrub_unmatched_dba                                 3                    1024                 Scrub maximum number of blocks with unmatched DBA
_asm_xrov_nvios                                          4                    8                    Specify number of VIO processes
_asm_xrov_single                                         TRUE                 FALSE                Enable single issues of IOs
_bigdata_external_table                                  FALSE                TRUE                 enables use of ORACLE_HIVE and ORACLE_HDFS access drivers
_cache_fusion_pipelined_updates                          FALSE                TRUE                 Block ping without wait for log force
_catalog_foreign_restore                                 TRUE                 FALSE                catalog foreign file restore
_column_tracking_level                                   1                    21                   column usage tracking
_compression_compatibility                               12.1.0.2.0           12.2.0               Compression compatibility
_controlfile_enqueue_holding_time_tracking_size          10                   0                    control file enqueue holding time tracking size
_cursor_db_buffers_pinned                                1387                 1375                 additional number of buffers a cursor can pin at once
_cursor_obsolete_threshold                               1024                 8192                 Number of cursors per parent before obsoletion.
_db_block_buffers                                        444542               440775               Number of database blocks cached in memory: hidden parameter
_db_block_cache_protect                                  FALSE                false                protect database blocks (for debugging only)
_dbg_scan                                                0                    2048                 generic scan debug
_dbrm_dynamic_threshold                                  33490396             989922280            DBRM dynamic threshold setting
_dbrm_runchk                                             0                    32769000             Resource Manager Diagnostic Running Thread Check
_dbwr_stall_write_detection_interval                     0                    900                  dbwriter stall write detection interval
_enable_pdb_close_abort                                  FALSE                TRUE                 Enable PDB shutdown abort (close abort)
_gc_async_send                                           FALSE                TRUE                 if TRUE, send blocks asynchronously
_gc_element_percent                                      120                  105                  global cache element percent
_gc_latches                                              8                    32                   number of latches per LMS process
_gc_max_downcvt                                          256                  1024                 maximum downconverts to process at one time
_gc_msgq_buffers                                         128                  0                    set number of MSGQ buffers
_gc_policy_time                                          10                   20                   how often to make object policy decisions in minutes
_gc_trace_freelist_empty                                 TRUE                 FALSE                if TRUE, dump a trace when we run out of lock elements
_gc_transfer_ratio                                       2                    75                   dynamic object read-mostly transfer ratio
_gcs_latches                                             0                    128                  number of gcs resource hash latches to be allocated per LMS process
_hang_base_file_space_limit                              20000000             100000000            File space limit for current normal base trace file
_hang_int_spare2                                         FALSE                0                    Hang Management int 2
_hang_lws_file_space_limit                               20000000             100000000            File space limit for current long waiting session trace file
_hang_resolution_scope                                   PROCESS              OFF                  Hang Management hang resolution scope
_hang_terminate_session_replay_enabled                   FALSE                TRUE                 Hang Management terminates sessions allowing replay
_ilmstat_memlimit                                        10                   5                    Percentage of shared pool for use by ILM Statistics
_inmemory_fs_blk_inv_blk_percent                         20                   50                   in-memory faststart CU invalidation threshold(blocks)
_inmemory_fs_enable                                      FALSE                TRUE                 in-memory faststart enable
_inmemory_imcu_target_rows                               1048576              0                    IMCU target number of rows
_inmemory_private_journal_maxexts                        32000                5000                 Max number of extents per PJ
_inmemory_private_journal_sharedpool_quota               64                   20                   quota for transaction in-memory objects
_inmemory_repopulate_invalidate_rate_percent             100                  0                    In-memory repopulate invalidate rate percent
_inmemory_repopulate_threshold_scans                     2048                 0                    In-memory repopulate threshold number of scans
_inmemory_strdlxid_timeout                               327680               0                    max time to determine straddling transactions
_ipddb_enable                                            TRUE                 FALSE                Enable IPD/DB data collection
_kebm_suspension_time                                    82800                104400               kebm auto suspension time in seconds
_kgh_restricted_subheaps                                 60                   180                  number of subheaps in heap restricted mode
_kgl_message_locks                                       64                   256                  RAC message lock count
_kqr_optimistic_reads                                    FALSE                TRUE                 optimistic reading of row cache objects
_kse_snap_ring_suppress                                  608 1403 6510        942 1403             List of error numbers to suppress in the snap error history
_ksi_clientlocks_enabled                                 TRUE                 FALSE                if TRUE, DLM-clients can provide the lock memory
_ksxp_ipclw_enabled                                      0                    1                    enable ipclw for KSXP
_ktb_debug_flags                                         0                    8                    ktb-layer debug flags
_lm_comm_channel                                         ksxp                 msgq                 GES communication channel type
_lm_comm_tkts_calc_period_length                         5                    1000                 Weighted average calculation interval length (us)
_lm_drm_min_interval                                     300                  600                  minimum interval in secs between two consecutive drms
_lm_drmopt12                                             26                   56                   enable drm scan optimizations in 12
_lm_enable_aff_benefit_stats                             TRUE                 FALSE                enables affinity benefit computations if TRUE
_lm_procs                                                320                  1088                 number of client processes configured for cluster database
_lm_xids                                                 352                  1196                 number of transaction IDs configured for cluster database
_lock_next_constraint_count                              120                  3                    max number of attempts to lock _NEXT_CONSTRAINT
_lthread_cleanup_intv_secs                               5                    1                    interval for cleaning lightweight threads in secs
_max_fsu_segments                                        4096                 1024                 Maximum segments to track for fast space usage
_max_services                                            1024                 8200                 maximum number of database services
_multi_instance_pmr                                      FALSE                TRUE                 enable multi instance redo apply
_multiple_char_set_cdb                                   FALSE                TRUE                 Multiple character sets enabled in CDB
_number_cached_group_memberships                         32                   2048                 maximum number of cached group memberships
_number_group_memberships_per_cache_line                 3                    6                    maximum number of group memberships per cache line
_optimizer_ads_use_partial_results                       FALSE                TRUE                 Use partial results of ADS queries
_optimizer_dsdir_usage_control                           126                  0                    controls optimizer usage of dynamic sampling directives
_optimizer_undo_cost_change                              12.1.0.2             12.2.0.1             optimizer undo cost change
_parallel_server_sleep_time                              10                   1                    sleep time between dequeue timeouts (in 1/100ths)
_pmon_dead_blkrs_max_blkrs                               200                  50                   max blockers to check during cleanup
_posix_spawn_enabled                                     FALSE                TRUE                 posix_spawn enabled
_rcfg_parallel_verify                                    TRUE                 FALSE                if TRUE enables parallel verify at reconfiguration
_reliable_block_sends                                    TRUE                 FALSE                if TRUE, no side channel on reliable interconnect
_rowsets_max_rows                                        200                  256                  maximum number of rows in a rowset
_side_channel_batch_size                                 200                  240                  number of messages to batch in a side channel message (DFS)
_skip_trstamp_check                                      FALSE                TRUE                 Skip terminal recovery stamp check
_small_table_threshold                                   8890                 8815                 lower threshold level of table size for direct reads
_sql_plan_directive_mgmt_control                         3                    67                   controls internal SQL Plan Directive management activities
_step_down_limit_in_pct                                  1                    20                   step down limit in percentage
_target_log_write_size_timeout                           1                    0                    How long LGWR will wait for redo to accumulate (msecs)
 
90 rows selected.

Blocking Session not visible in v$ Views

By | February 16th, 2018 | Category: 11gR2, JDBC, Oracle Database | No Comments »

I had to troubleshoot an issue on a company ERP systems database, where several sessions were waiting for ‘enq: TX row lock contention’. It was a table containing print server nodes and this caused no more printouts. Usually troubleshooting locking issues is quite straightforward in recent versions of oracle database, but this time, the column “v$session.blocking_session” was not populated and there was no entry in v$lock for the blocker. This was strange, so I involved Oracle Support. They requested a system state dump.

SQL> oradebug setmypid
SQL> oradebug unlimit;
SQL> select * from dual;
SQL> oradebug dump systemstate 258
SQL> oradebug dump systemstate 258

In the system state dump, there was a reference for the hanging session:

Waiter:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62

  ----------------------------------------
  SO: 0x000000075C68F8B8, type: 2, owner: 0x0000000000000000, flag: INIT/-/-/0x00 if: 0x3 c: 0x3
   proc=0x000000075C68F8B8, name=process, file=ksu.h LINE:12740, pg=0
  (process) Oracle pid:161, ser:247, calls cur/top: 0x000000075BC6A370/0x000000075BC6A370
            flags : (0x0) -
            flags2: (0x0),  flags3: (0x10) 
            intr error: 0, call error: 0, sess error: 0, txn error 0
            intr queue: empty
    ksudlp FALSE at location: 0
  (post info) last post received: 0 0 0
              last post received-location: No post
              last process to post me: none
              last post sent: 0 0 0
              last post sent-location: No post
              last process posted by me: none
    (latch info) wait_event=0 bits=0x0
    Process Group: DEFAULT, pseudo proc: 0x000000076068CE68
    O/S info: user: SYSTEM, term: HOSTNAME, ospid: 4600 
    OSD pid info: Windows thread id: 4600, image: ORACLE.EXE (SHAD)
    Short stack dump: 
ksedsts()+585<-ksdxfstk()+44<-ksdxcb()+2402<-ssthreadsrgruncallback()+629<-OracleOradebugThreadStart()+829
<-0000000076D559CD<-0000000076E8A561<-0000000076EABD7A<-000007FEFCE910AC<-skgpwwait()+182<-ksliwat()+2459
<-kslwaitctx()+184<-ksqcmi()+7848<-ksqgtlctx()+6814<-ksqgelctx()+829<-ktuGetTxForXid()+190<-ktcwit1()+526
<-kdddgb()+9636<-kddlkr()+284<-qerfuProcessFetchedRow()+1019<-__PGOSF308_qerfuLockingRowProcedure()+89
<-qertbFetchByRowID()+2708<-qerfuStart()+590<-selexe0()+3495<-opiexe()+22377<-kpoal8()+2624<-opiodr()+1646
<-ttcpip()+1481<-opitsk()+2166<-opiino()+1246<-opiodr()+1646<-opidrv()+862<-sou2o()+98<-opimai_real()+158
<-opimai()+191<-OracleThreadStart()+724<-0000000076D559CD<-0000000076E8A561
01-04-2018 21:16:06.636
    ----------------------------------------
    SO: 0x00000007606D9B88, type: 4, owner: 0x000000075C68F8B8, flag: INIT/-/-/0x00 if: 0x3 c: 0x3
     proc=0x000000075C68F8B8, name=session, file=ksu.h LINE:12748, pg=0
    (session) sid: 68 ser: 20263 trans: 0x0000000755DB9838, creator: 0x000000075C68F8B8
              flags: (0x41) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
              flags2: (0x40009) -/-/INC
              DID: , short-term DID: 
              txn branch: 0x0000000000000000
              edition#: 100              oct: 3, prv: 0, sql: 0x00000007708AA2E8, psql: 0x000000076BDCDED8, user: 0/SYS
    ksuxds FALSE at location: 0
    service name: SYS$USERS
    client details:
      O/S info: user: username, term: hostname, ospid: 5968:4960
      machine: hostname program: sqlplus.exe
      application name: sqlplus.exe, hash value=254292535
    Current Wait Stack:
     0: waiting for 'enq: TX - row lock contention'
        name|mode=0x54580006, usn<<16 | slot=0x130009, sequence=0xb394f
        wait_id=10 seq_num=11 snap_id=1
        wait times: snap=8 min 15 sec, exc=8 min 15 sec, total=8 min 15 sec
        wait times: max=infinite, heur=8 min 15 sec
        wait counts: calls=166 os=166
        in_wait=1 iflags=0x15a0
    Wait State:
      fixed_waits=0 flags=0x22 boundary=0x0000000000000000/-1
...
      ----------------------------------------
      SO: 0x0000000778A7FCA8, type: 8, owner: 0x000000075BC6A370, flag: INIT/-/-/0x00 if: 0x1 c: 0x1
       proc=0x000000075C68F8B8, name=enqueue, file=ksq1.h LINE:380, pg=0
      (enqueue) TX-00130009-000B394F	DID: 0001-00A1-0003F59C
      lv: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00  res_flag: 0x6
      req: X, lock_flag: 0x10, lock: 0x0000000778A7FD00, res: 0x0000000760A2BD80
      own: 0x00000007606D9B88, sess: 0x00000007606D9B88, proc: 0x000000075C68F8B8, prv: 0x0000000760A2BDA0
    ----------------------------------------

In line 60 you can see that the session is requesting an eXclusive lock for resource res: 0x0000000760A2BD80. When searching for the next appearance of res: 0x0000000760A2BD80, we find a reference to the exclusive enqueue after the last process in a section PSEUDO PROCESS / DETACHED BRANCHES.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47

PSEUDO PROCESS for group DEFAULT:
  ----------------------------------------
  SO: 0x000000076068CE68, type: 2, owner: 0x0000000000000000, flag: INIT/-/-/0x00 if: 0x3 c: 0x3
   proc=0x000000076068CE68, name=process, file=ksu.h LINE:12740, pg=0
  (process) Oracle pid:0, ser:0, calls cur/top: 0x0000000000000000/0x0000000000000000
            flags : (0x20) PSEUDO
            flags2: (0x0),  flags3: (0x0) 
            intr error: 0, call error: 0, sess error: 0, txn error 0
            intr queue: empty
    ksudlp FALSE at location: 0
  (post info) last post received: 0 0 0
              last post received-location: No post
              last process to post me: none
              last post sent: 0 0 0
              last post sent-location: No post
              last process posted by me: none
    (latch info) wait_event=0 bits=0x0
    Process Group: DEFAULT, pseudo proc: 0x000000076068CE68
    O/S info: user: , term: , ospid:  (DEAD)
    OSD pid info: Windows thread id: 0, image: PSEUDO
  PSO child state object changes :
Dump of memory from 0x00000007605E5698 to 0x00000007605E58A0
7605E5690                   00000000 00000000          [........]
7605E56A0 00000000 00000000 00000000 00000000  [................]
  Repeat 31 times
BEGIN DUMP RESERVED PROCESSES
END DUMP RESERVED PROCESSES
DETACHED BRANCHES:
----------------------------------------
SO: 0x0000000755E7C0F0, type: 58, owner: 0x0000000000000000, flag: INIT/-/-/0x00 if: 0x3 c: 0x3
 proc=0x0000000000000000, name=branch, file=ktccts.h LINE:420, pg=0
(branch) trn = 0x0000000000000000, flg = 0x40, state = 0x00 bno=0 ser=0 evt=0
..
  SO: 0x0000000755E2B378, type: 58, owner: 0x0000000755E7C0F0, flag: INIT/-/-/0x00 if: 0x3 c: 0x3
   proc=0x000000077860EB80, name=branch, file=ktccts.h LINE:420, pg=0
  (branch) trn = 0x0000000755E01068, flg = 0x41090, state = 0x00 bno=1 ser=10 evt=10
   creator = 0x00000007607940E8 uid = 0x42 serial# = 7929 ttl = 0x15180 dtm = 0x5a4db9f5
   2PCrole = (nch = 0x0 flg = 0x0) DTP svc = 0x773ffc978
...
    SO: 0x0000000755E01068, type: 56, owner: 0x0000000755E2B378, flag: INIT/-/-/0x00 if: 0x3 c: 0x3
     proc=0x000000077860EB80, name=transaction, file=ktccts.h LINE:410, pg=0
    (trans) flg = 0x00401e03, flg2 = 0x04080000, flg3 = 0x00000000, prx = 0x0000000000000000, ros = 2147483647, crtses=0x00000007607940E8
...
    (enqueue) TX-00130009-000B394F	DID: 0001-00E6-000010CD
    lv: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00  res_flag: 0x6
    mode: X, lock_flag: 0x0, lock: 0x0000000755E010E0, res: 0x0000000760A2BD80
    own: 0x0000000755E01068 (TXN), sess: 0x0000000000000000, prv: 0x0000000760A2BD90

The SEV1 support engineer could not be convinced that the entry DETACHED BRANCHES has nothing to do with the last session listed in the system state dump. I delivered multiple system state dumps and he insisted that the lock was always held by the very last session of the system state dump – what a coincidence. He just scrolled up from the line 46 until the first occurence of “(session)” and was convinced to have found the blocker, not realizing that he overjumped the sections “DETACHED BRANCHES”/”PSEUDO PROCESS for group DEFAULT”.

After some research I found this note:

Solving locking problems in a XA environment ( Doc ID 1248848.1 )

This note explains that with XA (distributed transactions), e.g. DB Links, there can be situations where blocker is not visible. Unfortunately, the symptoms disappeared from the production system before the diagnostic steps from the MOS Note could be tried. Frequently, when there is an issue that remains unsolved, it comes back at a later point and so it was in this case. This time, I was armed with knowledge of the MOS note 1248848.1 and could use the XA rollback script provided there to free up the “session-less” blocking lock.

No entry in DBA_2PC_PENDING but the Query from MOS Note shows Active XA Trans:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49

select --+ ORDERED
'----------------------------------------'||'
Curent Time : '|| substr(to_char(sysdate,' HH24.MI.SS'),1,22) ||'
'||'TX start_time: '||t.KTCXBSTM||'
'||'FORMATID: '||g.K2GTIFMT ||'
'||'GTXID: '||g.K2GTITID_EXT ||'
'||'Branch: '||g.K2GTIBID ||'
Local_Tran_Id ='||substr(t.KXIDUSN||'.'||t.kXIDSLT||'.'||t.kXIDSQN,1,15)||'
'||'KTUXESTA='|| x.KTUXESTA ||'
'||'KTUXEDFL='|| x.KTUXECFL ||'
Lock_Info: ID1: ' || ((t.kXIDUSN*64*1024)+ t.kXIDSLT)
||' ID2: '|| t.kXIDSQN
XA_transaction_INFO
from x$k2gte g, x$ktcxb t, x$ktuxe x
where g.K2GTDXCB =t.ktcxbxba and
x.KTUXEUSN = t.KXIDUSN(+) and
x.KTUXESLT = t.kXIDSLT(+) and
x.KTUXESQN =t.kXIDSQN(+);
 
"----------------------------------------
Curent Time : 11.35.27
TX start_time: 02/05/18 15:35:17
FORMATID: 131075
GTXID: 312D613331303063613A653165623A35613732336438373A31326335323962
Branch: 613331303063613A653165623A35613732336438373A31326335326166
Local_Tran_Id =25.18.61640
KTUXESTA=ACTIVE
KTUXEDFL=NONE
Lock_Info: ID1: 1638418 ID2: 61640"
"----------------------------------------
Curent Time : 11.35.27
TX start_time: 02/05/18 15:35:17
FORMATID: 131075
GTXID: 312D613331303063613A653165623A35613732336438373A31326335326135
Branch: 613331303063613A653165623A35613732336438373A31326335326165
Local_Tran_Id =16.26.2292874
KTUXESTA=ACTIVE
KTUXEDFL=NONE
Lock_Info: ID1: 1048602 ID2: 2292874"
"----------------------------------------
Curent Time : 11.35.27
TX start_time: 02/05/18 15:35:17
FORMATID: 131075
GTXID: 312D613331303063613A653165623A35613732336438373A31326335326161
Branch: 613331303063613A653165623A35613732336438373A31326335326232
Local_Tran_Id =3.30.3767984
KTUXESTA=ACTIVE
KTUXEDFL=NONE
Lock_Info: ID1: 196638 ID2: 3767984"

This output proved that it was CASE 2: No Entry in dba_2pc_pending und Status ACTIVE. Then I could successfully avoid an instance restart and rollback the global transaction to free up the locks.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

set JAVA_HOME=c:\oracle\product\11.2.0.4\dbhome_1\jdk\jre
set CLASSPATH=c:\dba\scripts\;c:\oracle\product\11.2.0.4\dbhome_1\jdbc\lib\ojdbc5.jar:%CLASSPATH%
C:\DBA\scripts>c:\oracle\product\11.2.0.4\dbhome_1\jdk\jre\bin\java XA_rb jdbc:oracle:thin:@hostname:1521:PROD -ROLLBACK 25 18 61640
++ URL: jdbc:oracle:thin:@hostname:1521:PROD
++ Rollback of Local_tran_ID : 25.18.61640
++ got XA resource handle
++ got Connection handle
++ SQL : select g.K2GTIFMT, g.K2GTITID_EXT, g.K2GTIBID, rawtohex( g.K2GTITID_EXT), rawtohex(g.K2GTIBID) from sys.vw_x$k2gte g, sys.vw_x$ktcxb t, sys.vw_x$ktuxe x where g.K2GTDXCB =t.KTCXBXBA and x.KTUXEUSN
SLT(+) and x.KTUXESQN =t.KXIDSQN(+) and t.KXIDUSN=25 and t.kXIDSLT=18 and t.kXIDSQN= 61640
++ Getting XID for Local_Tran_ID: 25.18.61640
+ Found global XID: -->
++ Format Id: 131075
++ Group Id: 312D613331303063613A653165623A35613732336438373A31326335323962
++ Branch Id: 613331303063613A653165623A35613732336438373A31326335326166
 
-> Would your really like to continue (yes or no):
yes
... doing Rollback
 
... Rollback done !

With Redo Log Mining for the transaction ID, we could get some minor details about the session which initially opened this global transaction.

So whenever you find the strange situation that sessions are blocked on ‘enq: TX – row lock contention’ but there is no blocker in v$session blocking_session column or no entry of a session holding the lock in v$lock, then this MOS Note might be helpful in cleaning up the lock. Sadly, another case where even SEV1 support was no help at all. I have to mention however, that i recently had a SEV1 issue with an excellent and ambitious engineer. So there is still hope…


Super-Sizing PGA Workareas in Oracle 12c

By | September 17th, 2017 | Category: 12cR1, Performance Tuning | No Comments »

After a customer asked me for possibilities of super-sizing PGA workareas in version 12c, I took the chance to revisit the topic and perform some tests. Great material has already been posted by late Alex Fatkulin (Hotsos Symposium 2014) and Norbert Debes (Secrets of the Oracle Database, Apress), but I wanted to verify, how the current release is behaving and I found some interesting things.

Super-Sizing PGA Workareas in 12c

ORA-4031 Troubleshooting – Issue with durations in 11gR2

By | September 3rd, 2017 | Category: 11g, 11gR2, Oracle Database | No Comments »

I recently had to troubleshoot an ORA-4031 issue at a client site. The issue reappeared 3 times within 2 months and only after escalating the SR to SEV1 and being quite persistent for an explanation, the second engineer attempting to solve the issue finally got it right.  Being curious, I digged into the trace files again to confirm and understand the issue here. This blog post describes the method to analyze the issue and how to troubleshoot.

  • First, when the issue first appears, it creates a 4031 trace file, which does not really give enough information to solve the problem. So I asked Oracle Support for specific trace events to properly diagnose it. He correctly mentioned event 10235 level 65536 and heapdump level 536870914  but required an instance restart. After explaining that this is not really possible and researching myself, I found these two events, which do not really need an instance restart. Event 10235 was not really necessary in this case, because the description of the allocations did not matter here.
ALTER system SET events '4031 trace name HEAPDUMP level 536870914, lifetime 1; name errorstack level 3, lifetime 1';
  • Now, after the issue appeared again, we finally had the relevant diagnostic information.

 

Allocation Request Summary Informaton
=====================================
Allocation request for: kglsim object batch
...
Heap: 0x6007b278, size: 3896
******************************************************
HEAP DUMP heap name="sga heap(3,0)"  desc=0x6007b278
extent sz=0xfe0 alt=248 het=32767 rec=9 flg=-126 opc=2
...
durations enabled for this heap
reserved granules for root 0 (granule size 134217728)
Total heap size    =2684352800. (2559 MB)
Total free space   =    38360
Total reserved free space   =120837856 (115MB) 
Permanent space    =2563474760
 
==============================================
TOP 10 MEMORY USES FOR SGA HEAP SUB POOL 1
----------------------------------------------
"free memory               "       6597 MB 71%
"gcs resources             "       1080 MB 12%
"gcs shadows               "        748 MB  8%
"db_block_hash_buckets     "        200 MB  2%
"kglsim object batch       "        189 MB  2%
"kglsim heap               "        112 MB  1%
"gcs res hash bucket       "         64 MB  1%
"ges big msg buffers       "         54 MB  1%
"dbktb: trace buffer       "         51 MB  1%
"KGLH0                     "         48 MB  1%
-----------------------------------------
free memory                        6597 MB
memory alloc.                      2747 MB
Sub total                          9344 MB
==============================================
TOP 10 MEMORY USES FOR SGA HEAP SUB POOL 2
----------------------------------------------
"free memory               "       6641 MB 70%
"gcs resources             "       1080 MB 11%
"gcs shadows               "        747 MB  8%
"db_block_hash_buckets     "        512 MB  5%
"gcs res hash bucket       "         64 MB  1%
"kglsim object batch       "         59 MB  1%
"dbktb: trace buffer       "         51 MB  1%
"KGLH0                     "         44 MB  0%
"kglsim heap               "         35 MB  0%
"ges enqueues              "         22 MB  0%
-----------------------------------------
free memory                        6641 MB
memory alloc.                      2831 MB
Sub total                          9472 MB
==============================================
TOP 10 MEMORY USES FOR SGA HEAP SUB POOL 3
----------------------------------------------
"free memory               "       6488 MB 71%
"gcs resources             "       1080 MB 12%
"gcs shadows               "        747 MB  8%
"db_block_hash_buckets     "        200 MB  2%
"kglsim object batch       "        105 MB  1%
"gc name table             "         72 MB  1%
"gcs res hash bucket       "         64 MB  1%
"kglsim heap               "         63 MB  1%
"dbktb: trace buffer       "         51 MB  1%
"KGLH0                     "         46 MB  1%
-----------------------------------------
free memory                        6488 MB
memory alloc.                      2600 MB
Sub total                          9088 MB
==============================================
TOP 10 MEMORY USES FOR SGA HEAP SUB POOL 4
----------------------------------------------
"free memory               "       6663 MB 70%
"gcs resources             "       1080 MB 11%
"gcs shadows               "        749 MB  8%
"db_block_hash_buckets     "        512 MB  5%
"kglsim object batch       "         59 MB  1%
"dbktb: trace buffer       "         51 MB  1%
"KGLH0                     "         47 MB  0%
"kglsim heap               "         35 MB  0%
"FileOpenBlock             "         35 MB  0%
"ges enqueues              "         22 MB  0%
-----------------------------------------
free memory                        6663 MB
memory alloc.                      2809 MB
Sub total                          9472 MB
TOTALS ---------------------------------------
Total free memory                    26 GB
Total memory alloc.                  11 GB
Grand total                          37 GB
  • This shows that 4031 was triggered for memory allocation request in sga heap (3,0) with size of 3896. This is smaller than 4400 bytes (_shared_pool_reserved_min_alloc), so the request can not be served the the shared pool reserved area.  Next, we can see that we have 4 subpools (kghdsidx_count) and each of them has around 6 GB of free memory. So, in total, we have 37 GB shared pool with 26 GB of it free, but one mini-subpool (duration 0) of subpool 3 still has run out of free contigous memory bigger than 3792 bytes.
  • Running Tanel´s heapdump_analyzer, I could verify that the largest chunk for sga heap(3,0) was indeed 3792 bytes and that all bigger chunks were allocated by “PERM” allocations.

 

 ./heapdump_analyzer  ./PROD1_ora_27396.trc > heap.txt
 
grep -e "---" -e "Total_size" -e "sga heap(3,0)"  heap.txt
 
-- Heapdump Analyzer v1.00 by Tanel Poder ( http://www.tanelpoder.com )
 
Total_size #Chunks  Chunk_size,        From_heap,       Chunk_type,  Alloc_reason
---------- ------- ------------ ----------------- ----------------- -----------------
 
114123720      17    6713160 ,    sga heap(3,0),           R-free,
28442152       1   28442152 ,    sga heap(3,0),             perm,  perm
...
7485000       1    7485000 ,    sga heap(3,0),             perm,  perm
6713080       1    6713080 ,    sga heap(3,0),           R-free,
6712104       1    6712104 ,    sga heap(3,0),           R-perm,  perm
...
3028568       1    3028568 ,    sga heap(3,0),             perm,  perm
...
 
8928       4       2232 ,    sga heap(3,0),             perm,  perm
7864       1       7864 ,    sga heap(3,0),             perm,  perm
5488       2       2744 ,    sga heap(3,0),             perm,  perm
3792       1       3792 ,    sga heap(3,0),             free,  <<<<<<<<<<<<<<
...

This shows that the first chunk of chunk_type “free” is of 3792 bytes, but request was for 3896 bytes. The
subpool (3,0) only has R-free (for shared_pool_reserved area) and PERM allocations of bigger size. The subpool (3,0) consists of 20 Extents á (128M Granule size). Each extent has

Researching the issue with “PERM” allocations brought me to:

ORA-4031: unable to allocate 4160 bytes of shared memory (“shared pool”,”unknown object”,”sga heap(4,0)”,”modification “) (Doc ID 1675470.1)

This note also confirms that the classification in 4 durations leads to a weak spot of duration 0 in any subpool for memory allocations due to high PERM utilization. The note mentions patch 8857940, which was also finally recommended by the support engineer.

HEAP DUMP heap name="sga heap(1,0)"  desc=0x600680e8
Total heap size    =2818570440
Total free space   =    28008 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< 
Total reserved free space   =127549960
HEAP DUMP heap name="sga heap(1,1)"  desc=0x60069940
Total heap size    =805305840
Total free space   =632590232
Total reserved free space   = 40243520
HEAP DUMP heap name="sga heap(1,2)"  desc=0x6006b198
Total heap size    =1879046960
Total free space   =1734275840
Total reserved free space   = 93975928
HEAP DUMP heap name="sga heap(1,3)"  desc=0x6006c9f0
Total heap size    =4294964480
Total free space   =4072007248
Total reserved free space   =214821040
HEAP DUMP heap name="sga heap(2,0)"  desc=0x600719b0
Total heap size    =2952788080
Total free space   =    19144 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
Total reserved free space   =147689440
HEAP DUMP heap name="sga heap(2,1)"  desc=0x60073208
Total heap size    =671088200
Total free space   =524561448
Total reserved free space   = 33565720
HEAP DUMP heap name="sga heap(2,2)"  desc=0x60074a60
Total heap size    =1879046960
Total free space   =1738701544
Total reserved free space   = 93984160
HEAP DUMP heap name="sga heap(2,3)"  desc=0x600762b8
Total heap size    =4429182120
Total free space   =4202264536
Total reserved free space   =221534200
HEAP DUMP heap name="sga heap(3,0)"  desc=0x6007b278
Total heap size    =2684352800
Total free space   =    38360 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
Total reserved free space   =120837856
HEAP DUMP heap name="sga heap(3,1)"  desc=0x6007cad0
Total heap size    =671088200
Total free space   =526361840
Total reserved free space   = 33565720
HEAP DUMP heap name="sga heap(3,2)"  desc=0x6007e328
Total heap size    =1879046960
Total free space   =1736230384
Total reserved free space   = 93977784
HEAP DUMP heap name="sga heap(3,3)"  desc=0x6007fb80
Total heap size    =4294964480
Total free space   =4076323080
Total reserved free space   =214821040
HEAP DUMP heap name="sga heap(4,0)"  desc=0x60084b40
Total heap size    =2952788080
Total free space   =     8536 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
Total reserved free space   =140977632
HEAP DUMP heap name="sga heap(4,2)"  desc=0x60087bf0
Total heap size    =1879046960
Total free space   =1735347144
Total reserved free space   = 93984160
HEAP DUMP heap name="sga heap(4,3)"  desc=0x60089448
Total heap size    =4429182120
Total free space   =4198496144
Total reserved free space   =221534200

For reference, these are the relevant parameters. Please note that all of these are set to default, except _kghdsidx_count which was reduced from 4 earlier and _ksmg_granule_size which was reduced to 128M.

NAME                                     VALUE
---------------------------------------- --------------------
__shared_pool_size                       38923141120
_dm_max_shared_pool_pct                  1
_enable_shared_pool_durations            TRUE
_io_shared_pool_size                     4194304
_kghdsidx_count                          4
_ksmg_granule_size                       134217728
_memory_imm_mode_without_autosga         TRUE
_shared_pool_max_size                    0
_shared_pool_minsize_on                  FALSE
_shared_pool_reserved_min_alloc          4400
_shared_pool_reserved_pct                5
shared_pool_reserved_size                1946157056
shared_pool_size                         38654705664

Thanks to Tanel, Riyaj and Hatem Mahmoud


Oracle Database Result Cache Troubleshooting

By | February 20th, 2017 | Category: 12cR1, Oracle Database | No Comments »

I was troubleshooting an issue where the Oracle Database Result Cache did not get used when the RESULT_CACHE hint was specified inside a subquery. As the main query has a sysdate inside, the RESULT_CACHE Hint can not be specified for the main query, but only for the subquery.

1
2

SELECT SQ.*, sysdate FROM
(SELECT * FROM tableX, ....) SQ;

When using “/*+ RESULT_CACHE */ inside of Subquery, result cache was not enabled.

1
2
3

 
SELECT SQ.*, sysdate FROM
(SELECT /*+ RESULT_CACHE */ * FROM tableX, ....) SQ;

I found out that there is a trace event which can be set for diagnostics.

1

ALTER SESSION SET EVENTS '43905 trace name context forever, level 1';

This generated a trace file containing a clue to the reason:

1

QKSRC: ViewText[ecode=942] = SELECT  /*+ RESULT_CACHE */ ......

So, it seemed that there was an internal ORA-942 somewhere that prevented result caching from being used. The solution was to qualify the objects inside the subquery. Now the result cache was used correctly:

1
2

SELECT SQ.*, sysdate FROM
(SELECT /*+ RESULT_CACHE */ * FROM ownerY.tableX, ....)

 

 

 


Problems with big SGAs (>200G) on Linux

By | October 19th, 2016 | Category: 12cR1, Linux, Oracle Database | No Comments »

I recently had an issue where a database with 240GB SGA (1 huge shared memory segment) configured with hugepages on a system with 512G RAM was suddenly becoming instable and new logons were denied with these error message:


ORA-01034: ORACLE not available
ORA-27123: unable to attach to shared memory segment
Linux-x86_64 Error: 22: Invalid argument
Additional information: 2667
Additional information: 1736718
Additional information: 215016800256

This was strange because ipcs -a showed all shared memory segments and all processes existed. It turned out that there is a known issue with the Linux feature “prelink” and very big SGAs (>200g). This MOS Note gives some details:

Connected to an idle instance, while database instance is running (Process J000 died, see its trace file kkjcre1p: unable to spawn jobq slave process ) (Doc ID 1578491.1)

Obviously, exadata customers also suffered from this issue and therefore prelink is disabled beginning with exadata version 11.2.3.3. Redhat is reporting the same issue in combination with Informix databases in Knowledgebase article: https://access.redhat.com/solutions/1186953


« Older articles