Wednesday 30 May 2012

HowTo: Resolve Oracle 11g X-Windows installer issues on UNIX

Have you run the new Oracle 11g installer on *NIX and received a nasty message?

It happened to me this week! So, let's say you download the oracle11g installer for AIX, start x-windows (I prefer CygWin), export your display, test with xclock, then go into the "database" folder and do: ./runInstaller

These are the errors I received:

Starting Oracle Universal Installer...

Checking Temp space: must be greater than 190 MB. Actual 11907 MB Passed
Checking swap space: must be greater than 150 MB. Actual 19968 MB Passed
Checking monitor: must be configured to display at least 256 colors. Actual 16777216 Passed
Preparing to launch Oracle Universal Installer from /oracle/tmp/OraInstall2010-03-11_12-25-37PM. Please wait ...
[oracle@myserver 12:26:03] (brent01) /oracle/INSTALL/database
$ Exception in thread "main" java.lang.UnsatisfiedLinkError: /oracle/tmp/OraInstall2010-03-11_12-25-37PM/jdk/jre/bin//motif21/libmawt.a ( 0509-022 Cannot load module /oracle/tmp/OraInstall2010-03-11_12-25-37PM/jdk/jre/bin//motif21/libmawt.a.
0509-150 Dependent module /usr/lpp/X11/lib/libXt.a(shr_64.o) could not be loaded.
0509-152 Member shr_64.o is not found in archive
0509-022 Cannot load module /oracle/tmp/OraInstall2010-03-11_12-25-37PM/jdk/jre/bin//motif21/libmawt.a.
0509-150 Dependent module /oracle/tmp/OraInstall2010-03-11_12-25-37PM/jdk/jre/bin//motif21/libmawt.a could not be loaded.)
at java.lang.ClassLoader.loadLibraryWithPath(ClassLoader.java:986)

Well, that's a fine mess!

Before you comb MetaLink and create soft links to other libraries, etc. do yourself a favor and try this:

$ env | grep -i libpath

If your LIBPATH environment variable contains the system's X11 lib, then that's your problem. Mine showed:

LIBPATH=/lib:/usr/lpp/X11/lib:/oracle/oracle8i/product/8.1.7/lib

Removing "/usr/lpp/X11/lib" piece from my LIBPATH resolved the error.

$ export LIBPATH="/lib"
$ ./runInstalle

Unix: vi command list


Vi Cheat Sheet



Click here for the Advanced VI Cheatsheet


Modes




Vi has two modes insertion mode and command mode. The editor begins in command mode, where the cursor movement and text deletion and pasting occur. Insertion mode begins upon entering an insertion or change command. [ESC] returns the editor to command mode (where you can quit, for example by typing :q!). Most commands execute as soon as you type them except for "colon" commands which execute when you press the ruturn key.


Quitting



:x
Exit, saving changes
:q
Exit as long as there have been no changes
ZZ
Exit and save changes if any have been made
:q!
Exit and ignore any changes


Inserting Text



i
Insert before cursor


I
Insert before line
a
Append after cursor
A
Append after line
o
Open a new line after current line
O
Open a new line before current line
r
Replace one character
R
Replace many characters


Motion



h
Move left
j
Move down
k
Move up
l
Move right
w
Move to next word
W
Move to next blank delimited word
b
Move to the beginning of the word
B
Move to the beginning of blank delimted word
e
Move to the end of the word
E
Move to the end of Blank delimited word
(
Move a sentence back
)
Move a sentence forward
{
Move a paragraph back
}
Move a paragraph forward
0
Move to the begining of the line
$
Move to the end of the line
1G
Move to the first line of the file
G
Move to the last line of the file
nG
Move to nth line of the file
:n
Move to nth line of the file
fc
Move forward to c
Fc
Move back to c
H
Move to top of screen
M
Move to middle of screen
L
Move to botton of screen
%
Move to associated ( ), { }, [ ]


Deleting Text



Almost all deletion commands are performed by typing d followed by a motion. For example, dw deletes a word. A few other deletes are:

x
Delete character to the right of cursor
X
Delete character to the left of cursor
D
Delete to the end of the line
dd
Delete current line
:d
Delete current line


Yanking Text



Like deletion, almost all yank commands are performed by typing y followed by a motion. For example, y$ yanks to the end of the line. Two other yank commands are:

yy
Yank the current line
:y
Yank the current line


Changing text



The change command is a deletion command that leaves the editor in insert mode. It is performed by typing c followed by a motion. For wxample cw changes a word. A few other change commands are:

C
Change to the end of the line
cc
Change the whole line


Putting text



p
Put after the position or after the line
P
Put before the poition or before the line


Buffers



Named buffers may be specified before any deletion, change, yank or put command. The general prefix has the form "c where c is any lowercase character. for example, "adw deletes a word into buffer a. It may thereafter be put back into text with an appropriate "ap.


Markers



Named markers may be set on any line in a file. Any lower case letter may be a marker name. Markers may also be used as limits for ranges.

mc
Set marker c on this line
`c
Go to beginning of marker c line.
'c
Go to first non-blank character of marker c line.


Search for strings



/string
Search forward for string
?string
Search back for string
n
Search for next instance of string
N
Search for previous instance of string


Replace



The search and replace function is accomplished with the :s command. It is commonly used in combination with ranges or the :g command (below).

:s/pattern/string/flags
Replace pattern with string according to flags.
g
Flag - Replace all occurences of pattern
c
Flag - Confirm replaces.
&
Repeat last :s command


Regular Expressions



. (dot)
Any single character except newline
*
zero or more occurances of any character
[...]
Any single character specified in the set
[^...]
Any single character not specified in the set
^
Anchor - beginning of the line
$
Anchor - end of line
\<
Anchor - begining of word
\>
Anchor - end of word
\(...\)
Grouping - usually used to group conditions
\n
Contents of nth grouping

[...] - Set Examples
[A-Z]
The SET from Capital A to Capital Z
[a-z]
The SET from lowercase a to lowercase z
[0-9]
The SET from 0 to 9 (All numerals)
[./=+]
The SET containing . (dot), / (slash), =, and +
[-A-F]
The SET from Capital A to Capital F and the dash (dashes must be specified first)
[0-9 A-Z]
The SET containing all capital letters and digits and a space
[A-Z][a-zA-Z]
In the first position, the SET from Capital A to Capital Z
In the second character position, the SET containing all letters

Regular Expression Examples
/Hello/
Matches if the line contains the value Hello
/^TEST$/
Matches if the line contains TEST by itself
/^[a-zA-Z]/
Matches if the line starts with any letter
/^[a-z].*/
Matches if the first character of the line is a-z and there is at least one more of any character following it
/2134$/
Matches if line ends with 2134
/\(21|35\)/
Matches is the line contains 21 or 35
Note the use of ( ) with the pipe symbol to specify the 'or' condition
/[0-9]*/
Matches if there are zero or more numbers in the line
/^[^#]/
Matches if the first character is not a # in the line
Notes:
1. Regular expressions are case sensitive
2. Regular expressions are to be used where pattern is specified


Counts



Nearly every command may be preceded by a number that specifies how many times it is to be performed. For example, 5dw will delete 5 words and 3fe will move the cursor forward to the 3rd occurence of the letter e. Even insertions may be repeated conveniently with thismethod, say to insert the same line 100 times.


Ranges



Ranges may precede most "colon" commands and cause them to be executed on a line or lines. For example :3,7d would delete lines 3-7. Ranges are commonly combined with the :s command to perform a replacement on several lines, as with :.,$s/pattern/string/g to make a replacement from the current line to the end of the file.

:n,m
Range - Lines n-m
:.
Range - Current line
:$
Range - Last line
:'c
Range - Marker c
:%
Range - All lines in file
:g/pattern/
Range - All lines that contain pattern


Files



:w file
Write to file
:r file
Read file in after line
:n
Go to next file
:p
Go to previos file
:e file
Edit file
!!program
Replace line with output from program


Other



~
Toggle upp and lower case
J
Join lines
.
Repeat last text-changing command
u
Undo last change
U
Undo all changes to line


Page produced by Lagmonster - Oct 2000

Saturday 19 May 2012

THE CHECKS/STEPS LISTED HERE SHOULD BE PERFORMED AFTER DATABASE CLONE BEFORE APPLICATIONS CLONE



1 Check the Target - If its in ARCHIVELOG / NOARCHIVELOG MODE as desired.

2 Create the Temp datafiles , using the commands taken from the control file , if the temporary tablespace tempfiles is not existing.

ALTER TABLESPACE TEMP ADD TEMPFILE __________

Verify dba_temp_files, for all the temp file entries added to TEMP

SQL> select file_name from dba_temp_files;

Check for the Default Temporary file –
===========================
select * from database_properties where property_name = 'DEFAULT_TEMP_TABLESPACE';

Confirm that this TEMP.
If the default is not TEMP,

SQL>alter database default temporary tablespace TEMP

3 Update global_name if it contains source

select * from global_name;
update global_name set global_name='';
commit;

SQL> select * from global_name;

GLOBAL_NAME
--------------------------------------------------------------------------------
DEV

4 Restoration of files backed up on Target Instance prior to the clone.

- You may need to restore the tnsnames.ora , listener.ora (which was backed up for the target instance prior to clone)
- You may need to restore the spfile/init file , so that the target instance takes the original init parameters (memory/performance parameters) and restart the database.

5 Change the directories – if any need to be changed

set heading off
set pages 1000
set linesize 100
select 'CREATE OR REPLACE DIRECTORY '||DIRECTORY_NAME||' AS '||''''||DIRECTORY_PATH||''''||';'
from all_directories;
spool off

- Edit the commands by replacing PROD with DEV ie source with target and Execute.

- You would also be required to check and recreate the DB links as per tns entries and requirements in the Target Instance.


6 Update apps.wf_systems
(This should show the target Instance and not the source Instance)

SQL>select name,display_name from apps.wf_systems;
SQL>update apps.wf_systems set name=’’,display_name=’ .’;
SQL>COMMIT;

Example after change
SQL> select name,display_name from apps.wf_systems;

NAME DISPLAY_NAME
------------------------------ --------------------------------------------------------------------------------
DEV DEV.DBALOUNGE.COM


7 Update Notification status
(This needs to be bone before running adcfgclone on the apps tier , to avoid any Notifications to be sent from the Target Instance)

UPDATE wf_notifications SET status ='CLOSED', mail_status ='SENT', end_date ='01-JAN-01' WHERE mail_status in ('MAIL','INVALID','OPEN') ;

Commit;

update wf_agents set address = replace ( address,'PROD','DEV' ) ;
select name ,display_name from wf_systems;

update wf_systems set
DISPLAY_NAME = replace ( DISPLAY_NAME, 'PROD.DBALOUNGE.COM','DEV.DBALOUNGE.COM');

Commit;


8 Update the target node in CM fnd_concurrent_queues table :

update fnd_concurrent_queues set node_name='' where node_name='';

commit;

THE CHECKS/STEPS LISTED HERE SHOULD BE PERFORMED AFTER APPLICATIONS CLONE BUT with APPS SERVICES DOWN

9 You may want to Restore the backed up target xml file (DEV xml) file to its proper location.
We may want to use the same xml file which was in place prior to the clone so that the same ports / configuration is in use.

10 remove the log files from APPLCSF/APPLLOG:

Make sure Dir are existing ; Use command to confirm
cd $APPLCSF/$APPLLOG

Delete all files under $APPLCSF/$APPLLOG


11 You can set the logfile to NULL

update fnd_concurrent_requests set logfile_name = null, logfile_node_name = null,
outfile_name = null, outfile_node_name = null;

commit;

12 You may need to run

exec FND_CONC_CLONE.SETUP_CLEAN;
commit;

to clear FND_NODES entries and rerun autoconfig (on all application tiers) as per step given below

For Details , Refer to : How-to-cleanup-FND_NODES-table-to-clear-corrupted-setup

13 Run Autoconfig

14. Change apps , system and sysadmin and a module specific passwords.
Change apps password in wdbsvr.app and CGIcmd.dat files

Refer to :
FNDCPASS-to-Change-Passwords

15 Log in to the target system application tier node as the applications User

Run the following tasks in adadmin for all products:
- generate JAR files
- generate message files
- relink executables


16 Run cmclean.sql
(This may be required to clean Concurrent Queue Status and Request Status)


THE CHECKS/STEPS LISTED HERE SHOULD BE PERFORMED AFTER APPLICATIONS CLONE with APPS SERVICES UP and RUNNING

17 Bring up all services

18 Set the Site Name Profile Option

19 Set the Java Color profile option
Changing-Look-and-feel-of-Oracle-Application&highlight=java+color

20 Configure Workflow Mailer

Login to OAM. Click on
Notification Mailer => Edit Button => Advanced.
Make sure to type in the Target instance password.

PROCESS and DISCARD should exist on the Server for wf user.
The name of the Process and the Discard directory can be got from the workflow configuration

21 Check and Schedule the Concurrent Requests - these would include Gather Statistics , Purge old data and Workflow related Concurrent Requests.
You can submit these Conc. Requests using forms or use CONCSUB as described in Concsub-to-Quickly-submit-Concurrent-Requests

Purge-concurrent-request-and-or-manager-data
Purge-Obsolete-Workflow-Runtime-Data-Concurrent-Program
Purge-Obsolete-Workflow-Runtime-Data-Concurrent-Program

22 Do a comprehensive Status check

23 Release the Instance to Users

Thursday 17 May 2012

Oracle SQL Tuning 101


Oracle Tuning And Diagnostics Script

--Active Session Info
SELECT b.sid, b.serial#, a.spid, b.sql_id, b.program, b.osuser, b.machine,
b.TYPE, b.event, b.action, b.p1text, b.p2text, b.p3text, b.state, c.sql_text,b.logon_time
FROM v$process a, v$session b, v$sqltext c
WHERE a.addr=b.paddr
AND b.sql_hash_value = c.hash_value
AND b.STATUS = 'ACTIVE'
ORDER BY a.spid, c.piece
 
--Trace SQL Query Average Execution Time Using SQL ID
SELECT sql_id, child_number, plan_hash_value plan_hash, executions execs,
(elapsed_time/1000000)/decode(nvl(executions,0),0,1,executions) avg_etime,
buffer_gets/decode(nvl(executions,0),0,1,executions) avg_lio, sql_text
FROM v$sql s
WHERE s.sql_id='4n01r8z5hgfru'
 
--Get The Detail Explain Plan Using SQL ID
SELECT plan_table_output FROM TABLE(dbms_xplan.display_cursor('dtdqt19kfv6yx'))
 
--Session Elapsed Processing Time 
SELECT s.sid, s.username, s.module,
round(t.VALUE/1000000,2) "Elapsed Processing Time (Sec)"
FROM v$sess_time_model t, v$session s
WHERE t.sid = s.sid
AND t.stat_name = 'DB time'
AND s.username IS NOT NULL
AND t.VALUE/1000000 >= '1' --running more than 1 second
ORDER BY round(t.VALUE/1000000,2) DESC
 
--Session Elapsed Processing Time Statistic By SID
SELECT  a.sid, b.username, a.stat_name, ROUND((a.VALUE/1000000),2) "Time (Sec)"
FROM v$sess_time_model a, v$session b
WHERE a.sid = b.sid
AND b.sid = '194'  
ORDER BY ROUND((a.VALUE/1000000),2) DESC
 
--Use Longops To Check The Estimation Query Runtime
SELECT sid, serial#, opname, target, sofar, totalwork, units, start_time, 
last_update_time, time_remaining "REMAIN SEC", round(time_remaining/60,2) "REMAIN MINS", 
elapsed_seconds "ELAPSED SEC", round(elapsed_seconds/60,2) "ELAPSED MINS", 
round((time_remaining+elapsed_seconds)/60,2)"TOTAL MINS", message TIME 
FROM v$session_longops
WHERE sofar<>totalwork
AND time_remaining <> '0'
 
--Detect Blocking Session
SELECT sid, serial#, username, STATUS, state, event, 
blocking_session, seconds_in_wait, wait_time, action, logon_time
FROM gv$session 
WHERE state IN ('WAITING') 
AND wait_class != 'Idle'
AND event LIKE '%enq%'
AND TYPE='USER'
 
--Active Table Locking
SELECT b.sid, b.serial#, b.program, b.osuser, b.machine, b.TYPE, b.action,
c.sql_text,b.logon_time, e.owner, e.object_name "Table Lock"
FROM v$session b, v$sqltext c, v$locked_object d, dba_objects e
WHERE b.sql_address = c.address
AND b.sid = d.session_id
AND d.object_id = e.object_id
AND b.STATUS = 'ACTIVE'
ORDER BY b.sid, c.piece
 
--RAC Active Table Locking
SELECT b.sid, b.serial#, a.spid, b.program, b.osuser, b.machine, 
b.TYPE, b.event, b.action, b.p1text, b.p2text, b.p3text, 
b.state, c.sql_text,b.logon_time, 
b.STATUS,  e.owner, e.object_name "Table Lock"
FROM gv$process a, gv$session b, gv$sqltext c, gv$locked_object d, dba_objects e
WHERE a.addr=b.paddr
AND b.sql_address = c.address
AND b.sid = d.session_id
AND d.object_id = e.object_id
AND b.STATUS = 'ACTIVE'
ORDER BY a.spid, c.piece
 
--Monitor Top Waiting Event Using Active Session History (ASH)
SELECT h.event,
SUM(h.wait_time + h.time_waited) "Total Wait Time (ms)"
FROM v$active_session_history h, v$sqlarea SQL, dba_users u, v$event_name e 
WHERE h.sample_time BETWEEN sysdate - 1/24 AND sysdate --event in the last hour
AND h.sql_id = SQL.sql_id
AND h.user_id = u.user_id
AND h.event# = e.event#
GROUP BY h.event
ORDER BY SUM(h.wait_time + h.time_waited) DESC
 
--Monitor Highest SQL Wait Time Using Active Session History (ASH)
SELECT h.session_id, h.session_serial#, h.sql_id, h.session_state, 
h.blocking_session_status, h.event, e.wait_class, h.module, u.username, SQL.sql_text,
SUM(h.wait_time + h.time_waited) "Total Wait Time (ms)"
FROM v$active_session_history h, v$sqlarea SQL, dba_users u, v$event_name e 
WHERE h.sample_time BETWEEN sysdate - 1/24 AND sysdate --event in the last hour
AND h.sql_id = SQL.sql_id
AND h.user_id = u.user_id
AND h.event# = e.event#
GROUP BY h.session_id, h.session_serial#, h.sql_id, h.session_state, 
h.blocking_session_status, h.event, e.wait_class, h.module, u.username, SQL.sql_text
ORDER BY SUM(h.wait_time + h.time_waited) DESC
 
--Monitor Highest Object Wait Time Using Active Session History (ASH)
SELECT o.owner, o.object_name, o.object_type, h.session_id, h.session_serial#, 
h.sql_id, h.module, SUM(h.wait_time + h.time_waited) "Total Wait Time (ms)"
FROM v$active_session_history h, dba_objects o, v$event_name e 
WHERE h.sample_time BETWEEN sysdate - 1/24 AND sysdate --event in the last hour
AND h.current_obj# = o.object_id
AND e.event_id = h.event_id
GROUP BY  o.owner, o.object_name, o.object_type, h.session_id, h.session_serial#,
h.sql_id, h.module
ORDER BY SUM(h.wait_time + h.time_waited) DESC
 
--Monitor Highest Event Wait Time Using Active Session History (ASH)
SELECT h.event "Wait Event", SUM(h.wait_time + h.time_waited) "Total Wait Time (ms)"
FROM v$active_session_history h, v$event_name e
WHERE h.sample_time BETWEEN sysdate - 1/24 AND sysdate --event in the last hour
AND h.event_id = e.event_id
AND e.wait_class <> 'Idle'
GROUP BY h.event
ORDER BY SUM(h.wait_time + h.time_waited) DESC
 
--Database Time Model Statistic
SELECT wait_class, NAME, ROUND (time_secs, 2) "Time (Sec)",
ROUND (time_secs * 100 / SUM (time_secs) OVER (), 2) pct
FROM 
(SELECT n.wait_class, e.event NAME, e.time_waited / 100 time_secs
FROM v$system_event e, v$event_name n
WHERE n.NAME = e.event 
AND n.wait_class <> 'Idle'
AND time_waited > 0
UNION
SELECT 
'CPU', 
'Server CPU', 
SUM (VALUE / 1000000) time_secs
FROM v$sys_time_model
WHERE stat_name IN ('background cpu time', 'DB CPU'))
ORDER BY time_secs DESC;
 
--Monitor I/O On Data Files
SELECT vfs.file#, dbf.file_name, dbf.tablespace_name, dbf.bytes, vfs.phyrds/vfs.phywrts,
vfs.phyblkrd/vfs.phyblkwrt, vfs.readtim, vfs.writetim 
FROM v$filestat vfs, dba_data_files dbf
WHERE vfs.file# = dbf.file_id
 
--I/O Stats For Data Files & Temp Files
SELECT file_no,
filetype_name, 
small_sync_read_reqs "Synch Single Block Read Reqs",
small_read_reqs "Single Block Read Requests",
small_write_reqs "Single Block Write Requests",
round(small_sync_read_latency/1000,2) "Single Block Read Latency (s)",
large_read_reqs "Multiblock Read Requests",
large_write_reqs "Multiblock Write Requests",
async_io "Asynch I/O Availability"
FROM v$iostat_file
WHERE filetype_id IN (2,6) --data file and temp file
 
--I/O Stats By Functionality
SELECT function_name,
small_read_reqs "Single Block Read Requests",
small_write_reqs "Single Block Write Requests",
large_read_reqs "Multiblock Read Requests",
large_write_reqs "Multiblock Write Requests",
number_of_wait "I/O Waits",
round(wait_time/1000,2) "Total Wait Time (ms)"
FROM v$iostat_function
ORDER BY function_name
 
--Temporary Tablespace Usage By SID
SELECT tu.username, s.sid, s.serial#, s.sql_id, s.sql_address, tu.segtype, 
tu.extents, tu.blocks, SQL.sql_text
FROM v$tempseg_usage tu, v$session s, v$sql SQL
WHERE tu.session_addr = s.addr
AND tu.session_num = s.serial#
AND s.sql_id = SQL.sql_id
AND s.sql_address = SQL.address
 
--Monitor Overall Oracle Tablespace 
SELECT d.STATUS "Status",
d.tablespace_name "Name",
d.contents "Type",
d.extent_management "Extent Management",
d.initial_extent "Initial Extent",
TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99,999,990.900') "Size (M)",
TO_CHAR(NVL(a.bytes - NVL(f.bytes, 0), 0)/1024/1024,'99,999,999.999') "Used (M)",
TO_CHAR(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0), '990.00') "Used %",
TO_CHAR(NVL(a.maxbytes / 1024 / 1024, 0),'99,999,990.900') "MaxSize (M)",
TO_CHAR(NVL((a.bytes - NVL(f.bytes, 0)) / a.maxbytes * 100, 0), '990.00') "Used % of Max"
FROM sys.dba_tablespaces d,
(SELECT tablespace_name, 
SUM(bytes) bytes, 
SUM(decode(autoextensible,'NO',bytes,'YES',maxbytes))
maxbytes FROM dba_data_files GROUP BY tablespace_name) a,
(SELECT tablespace_name, SUM(bytes) bytes FROM dba_free_space 
GROUP BY tablespace_name) f
WHERE d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = f.tablespace_name(+)
ORDER BY 10 DESC;
 
--Cache Hit Ratio
SELECT ROUND(((1-(SUM(DECODE(name,
'physical reads', VALUE,0))/
(SUM(DECODE(name, 'db block gets', VALUE,0))+
(SUM(DECODE(name, 'consistent gets', VALUE, 0))))))*100),2)
|| '%' "Buffer Cache Hit Ratio"
FROM v$sysstat --Use gv$sysstat if running on RAC environment
 
--Library Cache Hit Ratio
SELECT SUM(pins) "Total Pins", SUM(reloads) "Total Reloads",
SUM(reloads)/SUM(pins) *100 libcache
FROM v$librarycache --Use v$librarycache if running on RAC environment
 
--DB Session Memory Usage
SELECT se.sid,n.name, MAX(se.VALUE) maxmem
FROM v$sesstat se, v$statname n
WHERE n.statistic# = se.statistic#
AND n.name IN ('session pga memory','session pga memory max',
'session uga memory','session uga memory max')
GROUP BY n.name, se.sid
ORDER BY MAX(se.VALUE) DESC

Useful Oracle Hint For Tuning – Gather_Plan_Statistics

The hint /*+ gather_plan_statistics */ enables collection of rowsource execution statistics for the statement.
The hint is useful if you want to have clear overview of the execution plan chosen by Oracle together with estimated rows or actual rows fetch in the SQL execution.
SET serveroutput off;
SET linesize 300;
 
SELECT /*+  gather_plan_statistics */ SUM(nvl(period_net_dr,0) - nvl(period_net_cr,0)) 
FROM gl_balances
WHERE code_combination_id = '133110'
GROUP BY code_combination_id;
 
SELECT * FROM TABLE(dbms_xplan.display_cursor(NULL, NULL, 'last allstats'));

Oracle Wait Events FAQ

Buffer Busy Waits

Area: Buffer Cache, DBWR
Possible Causes: The block is being read into the buffer by another session, so the waiting session must wait for the block read to complete.
Examine: Examine V$SESSION while the problem is occurring to determine the type of block in contention. Tune SQL to access rows with fewer block reads by adding indexes, adjusting the database writer or adding freelists to tables and indexes. Even if there is a huge db_cache_size, the DBA may still see buffer busy waits and increasing the buffer size will not help in this case.

Free Buffer Waits

Area: Buffer cache, DBWR, I/O
Possible Causes: Slow DBWR (possibly due to I/O?). Cache too small
Examine: Examine write time using operating system statistics. Check buffer cache statistics for evidence of too small cache.

DB File Scattered Read

Area: I/O, SQL statement tuning
Possible Causes: Poorly written SQL. Slow I/O system
Examine: Investigate V$SQLAREA to see whether there are SQL statements performing many disk reads. Cross-check I/O system and V$FILESTAT for poor read time.

DB File Sequential Read

Area: I/O, SQL statement tuning
Possible Causes: Poorly written SQL. Slow I/O system
Examine: Investigate V$SQLAREA to see whether there are SQL statements performing many disk reads. Cross-check I/O system and V$FILESTAT for poor read time.

Enqueue Waits (enq:)

Area: Locks
Possible Causes: Depends on type of enqueue
Examine: Look at V$ENQUEUE_STAT

Library Cache Latch Waits (librarycache, library cache pin and library cache lock)

Area: Latch contention
Possible Causes: SQL parsing or sharing
Examine: Check V$SQLAREA to see whether there are SQL statements with a relatively high number of parse calls or a high number of child cursors (column VERSION_COUNT). Check parse statistics inV$SYSSTAT and their corresponding rate for each second.

Log Buffer Space

Area: Log buffer, I/O
Possible Causes: Log buffer small. Slow I/O system
Examine: Check the statistic redo buffer allocation retries in V$SYSSTAT. Check configuring log buffer section in configuring memory chapter. Check the disks that house the online redo logs for resource contention.

Log File Sync

Area: I/O, over-commit
Possible Causes: Slow disks that store the online logs. Unbatched commits
Examine: Check the disks that house the online redo logs for resource contention. Check the number of transactions (commits + rollbacks) each second, from V$SYSSTAT.

Instance Tuning Using Performance Views – Oracle

V$ACTIVE_SESSION_HISTORY – view displays active database session activity, sampled once every second.
V$SESS_TIME_MODEL and V$SYS_TIME_MODEL – views contain time model statistics, including DB time which is the total time spent in database calls
V$SESSION_WAIT – view displays the resources or events for which active sessions are waiting.
V$SESSION – view contains the same wait statistics that are contained in the V$SESSION_WAIT view. If applicable, this view also contains detailed information on the object that the session is currently waiting for (object number, block number, file number, and row number), plus the blocking session responsible for the current wait.
V$SESSION_EVENT – view provides summary of all the events the session has waited for since it started.
V$SESSION_WAIT_CLASS – view provides the number of waits and the time spent in each class of wait events for each session.
V$SESSION_WAIT_HISTORY – view provides the last ten wait events for each active session.
V$SYSTEM_EVENT – view provides a summary of all the event waits on the instance since it started.
V$EVENT_HISTOGRAM – view displays a histogram of the number of waits, the maximum wait, and total wait time on a per-child cursor basis.
V$FILE_HISTOGRAM – view displays a histogram of times waited during single block reads for each file.
V$SYSTEM_WAIT_CLASS – view provides the instance wide time totals for the number of waits and the time spent in each class of wait events. This view also shows the object number for which the session is waiting.
V$TEMP_HISTOGRAM – view displays a histogram of times waited during single block reads for each temporary file.

Tuning Oracle Shared Pool

Sometimes your database might hit the error ORA-04031: unable to allocate 4048 bytes of shared memory (“shared pool”,”unknown object”,”sga heap(1,0)”,”library cache”).
You can use the dbms_shared_pool package to pin large packages, reduce your use of shared memory or increase the amount of available shared memory by increasing the value of the INIT.ORA parameters “shared_pool_reserved_size” and “shared_pool_size”.
Increasing the shared pool size might resolve the problem but it is not necessary will improve the performance of library caching.
Always check the advisory with v$shared_pool_advice view to see the estimated time improvement statistic if you increase the shared pool area.
--Free Memory Available In Shared Pool
SELECT *
FROM v$sgastat
WHERE name = 'free memory'
AND pool = 'shared pool'
 
--Database Memory Setting
SELECT component, current_size/1024/1024 "Current Size (MB)",
min_size/1024/1024 "Min Size (MB)", max_size/1024/1024 "Max Size (MB)",
user_specified_size/1024/1024 "User Specified Size (MB)", 
last_oper_type "Type" 
FROM v$sga_dynamic_components
 
--Estimation Of Shared Memory Pool Size vs. Time Saved
SELECT shared_pool_size_for_estimate "Pool Size (MB)",
estd_lc_size "Lib Cache Size (MB)", 
estd_lc_time_saved/1000000 "Lib Cache Time Saved (Sec)"
FROM v$shared_pool_advice

Tuning Oracle Buffer Cache

v$db_cache_advice contains rows that predict the number of physical reads for the cache size corresponding to each row. This view is populated when the DB_CACHE_ADVICE initialization parameter is set to ON.
When the advisory is enabled, there is a small increase in CPU usage as overhead associated with this advisory.
The rows also compute a “physical read factor” which is the ratio of the number of estimated reads to the number of reads actually performed by the real buffer cache during the measurement interval.
--Estimation Of Buffer Cache Size vs. Physical Reads
SELECT size_for_estimate "Cache Size (MB)", 
buffers_for_estimate "Buffers", 
estd_physical_read_factor "Estd Phys|Read Factor", 
estd_physical_reads "Estd Phys| Reads"
FROM v$db_cache_advice
WHERE name = 'DEFAULT'
AND block_size = (SELECT VALUE FROM V$PARAMETER WHERE name = 'db_block_size')
AND advice_status = 'ON'
 
                                Estd Phys    Estd Phys
 Cache SIZE (MB)      Buffers READ Factor        Reads
---------------- ------------ ----------- ------------
              30        3,802       18.70  192,317,943      10% OF CURRENT SIZE 
              60        7,604       12.83  131,949,536
              91       11,406        7.38   75,865,861
             121       15,208        4.97   51,111,658
             152       19,010        3.64   37,460,786
             182       22,812        2.50   25,668,196
             212       26,614        1.74   17,850,847
             243       30,416        1.33   13,720,149
             273       34,218        1.13   11,583,180
             304       38,020        1.00   10,282,475      CURRENT SIZE 
             334       41,822         .93    9,515,878
             364       45,624         .87    8,909,026
             395       49,426         .83    8,495,039
             424       53,228         .79    8,116,496
             456       57,030         .76    7,824,764
             486       60,832         .74    7,563,180
             517       64,634         .71    7,311,729
             547       68,436         .69    7,104,280
             577       72,238         .67    6,895,122
             608       76,040         .66    6,739,731      200% OF CURRENT SIZE
The following output shows that if the cache was 212 MB, rather than the current size of 304 MB, the estimated number of physical reads would increase by a factor of 1.74 or 74%. This means it would not be advisable to decrease the cache size to 212MB.
However, increasing the cache size to 334MB would potentially decrease reads by a factor of .93 or 7%. If an additional 30MB memory is available on the host machine and the SGA_MAX_SIZE setting allows the increment, it would be advisable to increase the default buffer cache pool size to 334MB.

Related Posts

Number of Visitors