Saving you 15 minutes: TX Row Lock Contention and Corrective Actions Part 1

TX row lock contention issues are a bit of a pain as they are normally intermittent and require a bit of co-ordination between DBAs and application developers at the exact time they are happening.  To try and circumvent this I wanted to be able to automatically notify the application developers when this issue was occurring and what the affected order was.

To do this I generated a bit of SQL:

select distinct decode(sql_id,
 '716c1whwnhn77','select * from.apps.order ',
 '0jhvf4y5xmmhn','select * from apps.order_item ',
 '98sfcwp2zp6kx','select * from apps.order_relationship ','SQL NOT CURRENTLY KNOWN')||'
 where rowid = dbms_rowid.rowid_create (1,'||row_wait_obj#||','||row_wait_file#||','||row_wait_block#||','||row_wait_row#||')'
 from v$session where
 state='WAITING'
 and event='enq: TX - row lock contention'
 and seconds_in_wait > 640;

Using ASH I could determine the SQL_ID candidates which were the most likely to be waiting in a TX lock issue.  As it seemed to be a couple of SQL IDs, I then embedded this in a decode statement.

The decode statement wouldtake the relevant SQL ID and then translate that into a select statement, this would then be concatenated with a translation of row_wait_obj#, row_wait_file#, and row_wait_row# from v$session to get a rowid.  When you put these together you would then get the relevant select statement which would allow application support to pinpoint the actual orders which were being locked.

select * from apps_order where rowid = dbms_rowid.rowid_create (1,1256151,16,357335);

So the next to do is setup 12c to fire the SQL when an application row lock occurs.

Saving you 15 minutes: Re-starting Oracle BI Publisher in OEM

If you bounce your 12.1.0.3 OEM then you may notice that BI Publisher won’t automatically start.  It seems the note; EM 12c: Creating scripts to Start the Enterprise Manager 12c Cloud Control BI Publisher Service Automatically or from the Command Line (Doc ID 1634085.1) is classified….

Therefore if you need to restart this then you will have to do it from the WebLogic console.  Probably like most DBAs you may have ignored all they told you in Application Server finishing school, but if you need to do this there you can do the following.  Go to your OMS Oracle Middleware home install directory and cat the setupinfo.txt file.  This will give you the URL for the Weblogic console.

Admin Server URL: https://myOMS:7103/console

The username will be weblogic and the password will have been specified at domain creation time so you may not have that anymore; if so have a look at this note:

12c Cloud Control: Steps for Modifying the Password for Weblogic and Nodemanager User Accounts in the Enterprise Manager Installation (Doc ID 1450798.1)

When you’re in WebLogic console go to GCDomain – Environment – Servers, here you will see BIP.  If will be shutdown if you want to start it click the CONTROL tab at the top, then tick the box next to BIP and click START.

BIPUB

 

 

Saving You 15 Minutes: String Based Metric Extensions

I hit a couple of interesting ‘features’ of Metric Extensions the other day.  I was writing a quick query to try and ascertain if any users in the database were using advanced compression table features.

The query was pretty simple:

select distinct owner from
(select a.owner from dba_tables a where a.compress_for in ('FOR ALL OPERATIONS', 'OLTP', 'ADVANCED')
union all
select  a.table_owner from dba_tab_partitions a where a.compress_for in ('FOR ALL OPERATIONS', 'OLTP', 'ADVANCED')
union all
select a.table_owner  from dba_tab_subpartitions a  where a.compress_for in ('FOR ALL OPERATIONS', 'OLTP', 'ADVANCED')

When I created the metric extension and deployed this against a target which I knew has advanced compression enabled it didn’t generate a notification as expected.  So firstly I checked to see if the metric had been deployed:

select * from MGMT$METRIC_CURRENT where metric_name like 'ME$ADV%' and target_name='DB1'

This query didn’t return any results.  It turns out that although it didn’t create an error there is a bug related to metric extensions which are only defined with single columns (MOS 1669200.1).

The note highlights that metric extensions require data and key columns to function correctly.  The nature of my particular metric extension was to pull back individual schema names which had advanced compression objects in rather than the name of each individual object, otherwise I could have created a tidal wave of violations.

The modified query was:

select distinct owner, count(*) from
(select a.owner from dba_tables a where a.compress_for in ('FOR ALL OPERATIONS', 'OLTP', 'ADVANCED')
union all
select  a.table_owner from dba_tab_partitions a where a.compress_for in ('FOR ALL OPERATIONS', 'OLTP', 'ADVANCED')
union all
select a.table_owner  from dba_tab_subpartitions a  where a.compress_for in ('FOR ALL OPERATIONS', 'OLTP', 'ADVANCED'))
group by owner

This now created a key column, unique owner, but then also a data column which was a count of the objects which had the feature enabled.

But when I looked at other metric extensions which were up and running and generating the notifications it seemed they were created with single data columns.  However the key difference was those metric extensions where using numeric values as the data column type whereas I was using a string type.  So this leaves an unanswered question, I think that only metric extensions with single string data columns don’t work due to the aforementioned bug, whereas numeric single data columns do work.  Although I have a solution now it just niggles not to have a completely definitive answer.

Tweet Me @pbedba

OEM 12c – How patched are you?

It is a common mistake for many people to get their OEM 12c environment up and running, and then neglect to keep it patched up. All the while they will be cursing at how many error messages and bugs they hit day after day. This article explains the various OEM components which you’re going to want to think about patching every couple of months to get the most out of OEM.

Continue reading

What to do with Jython: Agent Deployment and Preferred Credentials ‘Cross-Pollination’ Part 2

So with the query:

select a.target_name hostname, a.cred_name, b.target_name, b.target_guid from 
(SELECT t.target_name, nc.cred_name
FROM sysman.MGMT_TARGETS t
LEFT OUTER JOIN sysman.EM_TARGET_CREDS tc ON t.target_guid = tc.target_guid 
LEFT OUTER JOIN sysman.EM_NC_CREDS nc ON nc.cred_guid = tc.cred_guid
where t.target_type ='host' and tc.set_name='HostCredsNormal') a, sysman.MGMT$OH_HOME_INFO b
where a.target_name=b.host_name
and (b.OUI_HOME_NAME is not null and b.OUI_HOME_NAME not like 'OraDb%')

I can enter in a host preferred credential and work out what the agent host credential needs to be set to.  Rather than trying to embed the whole query in an Jython script I have created a view and then will just select data from the view.

CREATE OR REPLACE VIEW  XX_HOST_CREDS_AGENT_CREDS as (
select a.target_name hostname, a.cred_name, b.target_name, b.target_guid from 
(SELECT t.target_name, nc.cred_name
FROM sysman.MGMT_TARGETS t
LEFT OUTER JOIN sysman.EM_TARGET_CREDS tc ON t.target_guid = tc.target_guid 
LEFT OUTER JOIN sysman.EM_NC_CREDS nc ON nc.cred_guid = tc.cred_guid
where t.target_type ='host' and tc.set_name='HostCredsNormal') a, sysman.MGMT$OH_HOME_INFO b
where a.target_name=b.host_name
and (b.OUI_HOME_NAME is not null and b.OUI_HOME_NAME not like 'OraDb%'))

So now my query becomes:

select cred_name, target_name from XX_HOST_CREDS_AGENT_CREDS where hostname =’XXXX’;

Remember to grant this view to MGMT_VIEW otherwise it will give you a ORA-00942.

The next thing to do is create the script (if you want a copy email or tweet me, as copying Jython from a browser to notepad will be a pain as it is whitespace sensitive)

JythonPerfCreds

All we do now is just execute it from the command line and the output should be as follows:

JythonPerfCreds1

Tweet Me @pbedba