Archive

Archive for the ‘Performance’ Category

Ora-04031 – Unable to allocate 16440 bytes of shared memory


We were getting an issue with our live database after some recent downtime whereby the users were seeing memory issues when trying to load the application:

ora-04031

Upon reviewing the alert log I could see the following errors:

Thu Mar 21 13:24:02 2019
Errors in file /tpp/oracle/diag/rdbms/rblive/RBLIVE/trace/RBLIVE_ora_5636552.trc
(incident=25358680):
ORA-04031: unable to allocate 14272 bytes of shared memory (“shared pool”,”WORK_EVENTS_”,”PLMCD^fbf4850c”,”BAMIMA: Bam Buffer”)
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Errors in file /tpp/oracle/diag/rdbms/rblive/RBLIVE/trace/RBLIVE_ora_47252140.trc  (incident=25359355):
ORA-04031: unable to allocate 16440 bytes of shared memory (“shared pool”,”WORK_EVENTS_API
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
DDE: Problem Key ‘ORA 4031’ was completely flood controlled (0x6)
Further messages for this problem key will be suppressed for up to 10 minutes

We had recently increased the memory allocations on this database, and although I did not think it could be a caching issue I executed the following to temporarily resolve the issue and allow users to continue with their work:

alter system flush shared_pool;

alter system flush buffer_cache;

Further investigation is required to resolve this issue

Further Investigation:

It is likely that the above error is caused by poor code.

First we would look through the shared pool for potential duplicate SQL statements – we search for the first 40 characters of the SQL statement and use that to identify statements that are similar

with potential_duplicates as(select
SUM(executions) as “Executions”,
SUM(elapsed_time/1000000) as “Elapsed Time”,
SUM(cpu_time/1000000) as “CPU Time”,
COUNT(*) as “Statements”,
SUBSTR(sql_text,1,40) as “SQL”
from v$sqlarea
group by
SUBSTR(sql_text,1,40)
having COUNT(*) > 5
order by count(*) DESC)
select *
from potential_duplicates
where rownum < 11;

Executions Elapsed Time CPU Time    Statements SQL
———-    ————    ———-     ———-     —————————————-
314289       538.434676   275.59749   640              begin wwv_flow.g_computation_result_vc :
7066           1.674196       .817793       68                begin wwv_flow.g_boolean := :request = ‘
66                .179716        .086868       66                SELECT msg_no, type, text FROM messages
202              12.023259    5.778725     58                select h_utils.drilldown_link
159              10.510691    5.017982     58                SELECT h_utils.drilldown_link ( ‘
151              1.727624      .691142       57                SELECT aac_aat_description “Act
163929        96424.8735  40388.1749 49                 declare rc__ number; simple_list__ owa
30390          3.183714      1.560978     37                 begin — Required to reset variable as t
1133            .870898        .452353       36                 begin wwv_flow.g_boolean := :request in
22128          14.951797    7.166428     36                 declare function x return varchar2 is be

10 rows selected.

There are 66 statements using “SELECT msg_no…” and each of these has only been executed once making them a prime suspect for SQLs that are identical and are parsed with uniquely with literal values instead of bind variables. We can use the first 40 characters from the first statement to look through the shared pool for SQLs that match:

SQL> select sharable_mem,
sql_text
from v$sqlarea
where sql_text like ‘SELECT msg_no, type, text FROM messages%’;

SHARABLE_MEM SQL_TEXT
————           ————————————————————————————————————
19398                  SELECT msg_no, type, text FROM messages WHERE session_marker = 9182298 ORDER BY msg_no ASC
19398                  SELECT msg_no, type, text FROM messages WHERE session_marker = 9182308 ORDER BY msg_no ASC
19406                  SELECT msg_no, type, text FROM messages WHERE session_marker = 9182328 ORDER BY msg_no ASC
19406                  SELECT msg_no, type, text FROM messages WHERE session_marker = 9182288 ORDER BY msg_no ASC
19398                  SELECT msg_no, type, text FROM messages WHERE session_marker = 9182303 ORDER BY msg_no ASC
19406                  SELECT msg_no, type, text FROM messages WHERE session_marker = 9182324 ORDER BY msg_no ASC
19382                  SELECT msg_no, type, text FROM messages WHERE session_marker = 9182329 ORDER BY msg_no ASC
19406                  SELECT msg_no, type, text FROM messages WHERE session_marker = 9182292 ORDER BY msg_no ASC
19406                  SELECT msg_no, type, text FROM messages WHERE session_marker = 9182326 ORDER BY msg_no ASC
19382                  SELECT msg_no, type, text FROM messages WHERE session_marker = 9182293 ORDER BY msg_no ASC
19406                  SELECT msg_no, type, text FROM messages WHERE session_marker = 9182318 ORDER BY msg_no ASC
19406                  SELECT msg_no, type, text FROM messages WHERE session_marker = 9182330 ORDER BY msg_no ASC
19406                  SELECT msg_no, type, text FROM messages WHERE session_marker = 9182322 ORDER BY msg_no ASC
19398                  SELECT msg_no, type, text FROM messages WHERE session_marker = 9182304 ORDER BY msg_no ASC
19382                  SELECT msg_no, type, text FROM messages WHERE session_marker = 9182323 ORDER BY msg_no ASC
19406                  SELECT msg_no, type, text FROM messages WHERE session_marker = 9182333 ORDER BY msg_no ASC

16 rows selected.

Here we can see that the application is not using bind variables for filtering and is generating a unique SQL with a literal predicate for each query. To resolve the application

Advertisements

PL/SQL lock timer wait event


A business user requested I investigate why an application job was not progressing as expected. First I checked the usual suspects, checking for table/object locks

SQL> select
(select username || ‘ – ‘ || osuser from v$session where sid=a.sid) blocker,
a.sid || ‘, ‘ ||
(select serial# from v$session where sid=a.sid) sid_serial,
‘ is blocking ‘,
(select username || ‘ – ‘ || osuser from v$session where sid=b.sid) blockee,
b.sid || ‘, ‘ ||
(select serial# from v$session where sid=b.sid) sid_serial
from v$lock a, v$lock b
where a.block = 1
and b.request > 0
and a.id1 = b.id1
and a.id2 = b.id2;

no rows selected

Then I checked to see the long-running jobs:

CODE

REM Displays the long running operations

SET LINESIZE 200

COLUMN operation FORMAT a15
COLUMN username FORMAT a15
COLUMN object FORMAT a25

SELECT a.sid,
a.serial#,
b.username ,
opname OPERATION,
target OBJECT,
TRUNC(elapsed_seconds, 5) “ET (s)”,
TO_CHAR(start_time, ‘HH24:MI:SS’) start_time,
ROUND((sofar/totalwork)*100, 2) “COMPLETE (%)”
FROM v$session_longops a,
v$session b
WHERE a.sid = b.sid AND
b.username not IN (‘SYS’, ‘SYSTEM’) AND
totalwork > 0
AND a.sid=304;

Within Toad I then looked in the session browser where under the “session” tab I can see that it is waiting on the PL/SQL Lock timer event.

lock timer

I then check under the IO tab to see if the job is processing anything at all

reads

Here I can see that the “consistent gets” is increasing very, very slowly, less than 1 per second.

Under the “waits” tab I can see that PL/SQL lock timer is showing very high waits.

BRHOLW5 - plsql lock timer

PL/SQL lock timer wait event is called through the DBMS_LOCK.SLEEP procedure and will most likely originate  from procedures written by the user. This event is an idle event, nothing is happening except Oracle is waiting for a work request from the application. Sleeps are most likely put into an application for serialization of transnational events or spinning on queues until something happens. When an event occurs, the application continues its work. This is in contrast to the application triggering an action. The sleep event lasts 5 seconds each time it is occurs.

In our instance, we pushed back to the application support team to amend the code to reduce the amount of time this particular job spent sleeping.

Oracle – Find consuming sql from process id

September 5, 2011 3 comments

If you find an oracle process is consuming a high amount of CPU, you can find the sql_text that is related to that process id using the following code:

SELECT SQL_TEXT
FROM v$sql
WHERE sql_id = (SELECT sql_id FROM v$session WHERE paddr = (sELECT addr
FROM v$process
WHERE spid = '&process_id'));

You will need to find the process id using either top (linux), topas -P (aix), or task manager (windows). Equally you can use Enterprise Manager to find this information, by looking under the performance monitor and any alerts regarding tuning / high CPU consumption.

Oracle – Disable dbms gather_stats_job or optimizer stats


To disable gather_stats_job in 10g:

BEGIN
DBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB');
END;
/

To disable gather_stats_job (also known as optimizer stats) in 11g:

BEGIN
DBMS_AUTO_TASK_ADMIN.DISABLE(
client_name => 'auto optimizer stats collection',
operation => NULL,
window_name => NULL);
END;
/

Oracle – RMAN 11g List Failure


The 11g version of RMAN has a useful feature for diagnosing and repairing any failures.

Find any database failures:

RMAN> list failure;

Determine manual or automatic repair:

RMAN> advise failure;

From the output of the advise, try to manually fix the failure. If this fails to work, then try the repair command:

RMAN> repair failure;

Further Reading

Oracle® Database Backup and Recovery User’s Guide

Oracle – Check Auditing Policy


To check what auditing has been set on your Oracle database you can query the following:

SQL> SELECT *
FROM dba_stmt_audit_opts;

Further Reading

DBA_STMT_AUDIT_OPTS
Auditing in Oracle 10g Release 2

Oracle – Blocking Session


Show blocking sessions

Blocking sessions occur when an insert, update, delete is being issued and a commit has not been performed, this locks the row and prevents other users from making any changes to it.

SELECT blocking_session
, username
, sid
, serial#
, wait_class
, seconds_in_wait
FROM v$session
WHERE blocking_session IS NOT NULL
ORDER BY blocking_session;

From here we can see which user(s) are blocking the sessions and go and find out why.