TM Lock Contention After 11g upgrade

i have seen some slowness issues after upgrade from 10g to 11g and “TM lock contention lock” is worth to mention although it been long time since upgrade is done. Below is the graph after upgrade and the red portion is showing that there is some serious issue with locking. This was not the case prior to upgrade.

Looking into the details of this locking issue, i have seen that main reason for this locking is because of “TM lock contention”. Below graph is showing that there is “TM locking issue” with the database because whatever session is responsible for locking is having the “TM lock contention” wait class.

Initial investigation revolves around the fact that this behaviour is seen when the indexes were missing on foreign key column of child tables. There’s very good explanation for this event in articles below. These are excellent source of information.

https://hoopercharles.wordpress.com/2010/01/07/deadlock-on-oracle-11g-but-not-on-10g/

http://richardfoote.wordpress.com/2010/11/10/oracle11g-new-locking-modes-when-policing-fk-constraints-a-wolf-at-the-door/

So, all the missing indexes on foreign key columns created and that helped little but the issue still remains same. Below are some handy links to identify the unindex foreign keys.

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4530093713805#26568859366976

http://bluefrog-oracle.blogspot.in/2013/08/indentify-unindexed-foreign-key-columns.html

After spending a lot of time finally the reason was found. This existed in 10g as well but some how it’s not causing any issue. Problem was with the APPEND hint that changed it’s behaviour from 10g to 11g. Documentaion says that direct path inserts (With APPEND hint) takes a exclusive lock at table level. Below screen is from 10g documentation itself and link of documentation is also mentioned.

http://docs.oracle.com/cd/B19306_01/server.102/b14231/tables.htm#i1009887

So, removing the APPEND hint from insert statements helped in this issue and all the queries return to normal. Oracle support says that APPEND hint in 10g (10.2.0.4) was being ignored so no issue there but in 11g it’s started following the hint and problems are seen.

There’s a very good support note 1317447.1 which explain about the change in behaviour of APPEND hint with different versions and also explained that APPEND_VALUE hint is available.

Hoping that this is save someone’s time in finding the solution for “TM Lock contention” issue 🙂

Happy New Year 2015

Wishing Very Happy New Year to all my friends, Family and readers of this blog.

Hoping to share knowledge, ideas, articals more frequently this year and also hoping to see your views, comments, suggestions more. Have a wonderful year ahead !!!!!!!!!

Install XDB on 11.2.0.4 database

Below are steps to install the XDB on a 11.2.0.4 database. XDB is a essential component with 11g onwards as it’s required for e-mail functionality to work from database. You need to create network ACL list for sending e-mails from database that i will be covering in another post. This post is limited to installation of XDB only. Prior to install the XDB or upgrade a database having XDB installed, look out for any objects that needs to be dropped. Follow the doc 1573175.1 on Oracle support to run the script for checking same. Follow the doc 1292089.1 on Oracle support for detailed information with respect to different versions of database. This installation is done on 11.2.0.4 and pre-install script as per doc 1573175.1 didn’t return any error.

Create a tablespace exclusively for XDB objects.

create tablespace XDB_DATA datafile '/u04/oradata/TESTDB/xdb_data01.dbf' size 1024M;

Now, run the catqm.sql script with parameters as below:

@$ORACLE_HOME/rdbms/admin/catqm.sql A B C D

where:

A : XDB user password
B : XDB Default tablespace
C : XDB user temporary tablespace
D : YES or NO (Yes will use secure File storage and No will use LOB’s)

@$ORACLE_HOME/rdbms/admin/catqm.sql xdb XDB_DATA TEMP NO

In 12c version Oracle XML DB is a mandatory component which cannot be uninstalled and if Oracle XML DB is not already installed in database prior to an upgrade to 12c, then it is automatically installed.

11.2.0.3 Database Performs Slow and Increase In Paging On AIX 7.1 Power Server 64 bit

After upgrading to 11.2.0.3 on AIX 7.1 Power system 64-bit systems we have experienced slow performance of database and high paging on AIX server. This is a general slowness with all the memory (Physical + Virtual) consumed in some time. Restarting of database didn’t help as memory does not get free after shutting down the database. Only server restart helps but that is again temporary.

Symptoms includes that background processed start taking more memory. After bearing with this issue, finally we got the issue as AIX 7.1 power system 64-bit bug due to which memory once occupied doesn’t get free on server. Both Oracle & IBM have note related to this issue and fix is to apply a patch on AIX system. Below are use full docs from Oracle & IBM which helped us. These are worth to look at if you are also having above symptoms.

Virtual Memory Consumption / Paging under AIX 7.1 (Doc ID 1666458.1)

Memory Consumption on AIX (Doc ID 259983.1)

http://www-01.ibm.com/support/docview.wss?uid=isg1IV53587

After applying the patch mentioned in above notes, issue is resolved. Hope this will help to others facing this slowness issue for which we spend few weeks to find out.

SGA_MAX_Size & MEMORY_MAX_TARGET

Recently, we have interesting situation where a development server is upgraded to 11g and it got crashed due to insufficient shared pool size, while running the automatic maintenance task which is part of daily maintenance window. Problem was that by mistake only MEMORY_MAX_TARGET parameter was set to enable the Automatic memory management AMM. MEMORY_TARGET parameter was missed in pfile, while SGA_MAX_SIZE is set with SGA_TARGET is again missed. In summary following was situation:

MEMORY_MAX_TARGET — set to value 6G
MEMORY_TARGET — Missed
SGA_MAX_SIZE — set to value 6G
SGA_TARGET — Missed

Now, to enable the automatic memory management AMM, it’s compulsory to set the MEMORY_TARGET parameter. From 11g onwards, it’s recommended to use the MEMORY* parameters only instead of using SGA* parameters. With above parameters set, when we start the database, then it will start with minimum amount of SGA allocated which is required to start the DB. In case the more memory is required by any component of SGA, automatic increase will not happen because AMM is not enabled. Below are the cases which confirms this behavior. Instance is started with parameters as shown below:

sga_max_size=5G
memory_max_target=6g


SQL> conn sys as sysdba
Enter password:
Connected to an idle instance.
SQL> create spfile from pfile;

File created.

SQL> startup
ORACLE instance started.

Total System Global Area 5344731136 bytes
Fixed Size                  2230912 bytes
Variable Size            5167383936 bytes
Database Buffers          167772160 bytes
Redo Buffers                7344128 bytes
Database mounted.
Database opened.

SQL> show parameter memory

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address             integer     0
memory_max_target                    big integer 6G
memory_target                        big integer 0
shared_memory_address                integer     0
SQL> show parameter shared

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address             integer     0
max_shared_servers                   integer
shared_memory_address                integer     0
shared_pool_reserved_size            big integer 17616076
shared_pool_size                     big integer 336M
shared_server_sessions               integer
shared_servers                       integer     0
SQL> show parameter sga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     FALSE
sga_max_size                         big integer 5G
sga_target                           big integer 0


SQL> select * from v$sgainfo;

NAME                                  BYTES RES
-------------------------------- ---------- ---
Fixed SGA Size                      2230912 No
Redo Buffers                        7344128 No
Buffer Cache Size                 167772160 Yes
Shared Pool Size                  352321536 Yes
Large Pool Size                           0 Yes
Java Pool Size                     33554432 Yes
Streams Pool Size                         0 Yes
Shared IO Pool Size                       0 Yes
Granule Size                       16777216 No
Maximum SGA Size                 5344731136 No
Startup overhead in Shared Pool   291714216 No

NAME                                  BYTES RES
-------------------------------- ---------- ---
Free SGA Memory Available        4781506560


SQL> show parameter db_cache

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_cache_advice                      string      ON
db_cache_size                        big integer 48M

Now, see the difference in size of components with MEMORY_TARGET set and AMM is automatically enabled. See the components comparison at end of this code example.

memory_max_target=6g
memory_target=5G

PDEV.__java_pool_size=16777216
PDEV.__large_pool_size=16777216
PDEV.__pga_aggregate_target=2147483648
PDEV.__sga_target=3221225472
PDEV.__shared_io_pool_size=0
PDEV.__shared_pool_size=603979776
PDEV.__streams_pool_size=0

SQL> show parameter memory

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address             integer     0
memory_max_target                    big integer 6G
memory_target                        big integer 5G
shared_memory_address                integer     0
SQL> show parameter shared

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address             integer     0
max_shared_servers                   integer
shared_memory_address                integer     0
shared_pool_reserved_size            big integer 30198988
shared_pool_size                     big integer 0
shared_server_sessions               integer
shared_servers                       integer     0
SQL> show parameter sga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     FALSE
sga_max_size                         big integer 6G
sga_target                           big integer 0
SQL> select * from v$sgainfo;

NAME                                  BYTES RES
-------------------------------- ---------- ---
Fixed SGA Size                      2233480 No
Redo Buffers                       19324928 No
Buffer Cache Size                2533359616 Yes
Shared Pool Size                  603979776 Yes
Large Pool Size                    16777216 Yes
Java Pool Size                     16777216 Yes
Streams Pool Size                         0 Yes
Shared IO Pool Size                       0 Yes
Granule Size                       16777216 No
Maximum SGA Size                 6413680640 No
Startup overhead in Shared Pool   328714312 No

NAME                                  BYTES RES
-------------------------------- ---------- ---
Free SGA Memory Available        3221225472

12 rows selected.

SQL> show parameter db_cache

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_cache_advice                      string      ON
db_cache_size                        big integer 0
SQL>