Planner Oracle Ref

Oracle Reference
Tuning Methodology
1.Get feed back from users to determine the performance goals.
2.Get a full set of operating system, database and application statistics.
3.Look for top Oracle mistakes.
a) Bad connection management (is the app maintaining a persistent
connection)
b) Bad use of cursors and shared pool (use cursors and bind variables)
c) I/O distribution
d) Use undo tablespace or insure that there's adequate redo logs
e) Hot data blocks in buffer cache (data blocks w/ high concurrency
need additional freelists and higher INITRANS value)
f) Long table scans (missing indexes, poor SQL optimization)
g) Disk sorts (poor transaction design,missing indexes)
h) High volume of recursive SQL by sys (could indicate space
management issues)
i) Schema errors and optimizer problems (Migration to a new
environment could result in missing statement or indexes. Parameter
settings could also be different)
j) Non-standard initialization parameters (self explanatory)
Sudden Performance Degradation (v8.0)

Identify the session you want to trace using the SPID column in the
V$PROCESS (not V$SESSION).
Once you identify the session, you can join the V$SESSTAT.PADDR
column to V$PROCESS.ADDR to find the system process ID
Trace one of the slow sessions and examine the trace file to determine the
wait events.
1. Get the os process id (SPID column) for the session from v$session
SVRMGR> oradebug setospid <SPID>
SVRMGR> oradebug unlimit
SVRMGR> oradebug event 10046 trace name context forever, level 8
...wait for a few minutes of slow response time to allow oracle to collect
some data...
SVRMGR> oradebug event 10046 trace name context off

2. Examine the trace file in user_dump_dest
(ora_<SPID>_$ORACLE_SID.trc)
The lines beginning 'WAIT #' tell you the names of the events which are
causing the session to slow down. The ela= field shows the elapsed time in
seconds and the p1=, p2=, p3= show the parameters for each wait event.
Appendix A of the Oracle Reference manual details the wait events and
gives descriptions of what the parameters to each event mean.
Buffer Busy Waits:

Oracle Note 62161.1
Oracle Note 181306.1
INITRANS AND MAXTRANS STORAGE PARAMETERS

Oracle Note: 1020573.102
Export with a compress:

mknod <pipe_file> p
compress <pipe_file> <compressed_file_name> &
exp username/password file=<pipe_file>
Import with an uncompress

mknod <pipe_file> p
uncompress <compressed_file_name> (redirection) > <pipe_file> &
imp username/password file=<pipe_file>
Export with a pipe to a remote file system

#!/bin/ksh
FILENAME=”/u01/oraadata/exp.`date ‘+%Y%m%d_%H%M’`.dmp”
export FILENAME
PIPE=”/tmp/exp_pipe_$$.dmp”
export PIPE
echo “Exported started: `date`”>>exp.log
mknod ${PIPE} p
exp userid=un/pw recordlength=65535 file=${PIPE} direct=y full=y

log=filename &
cat ${PIPE} | rsh servername cat \> /path/exp.dmp

echo “Export ended: `date`” >>exp.log
#cat `echo ${FILE_NAME}.* |sort` | uncompress >{PIPE} &

#imp userid=un/pw file=${PIPE} show=y full=y
rm –f ${PIPE}
SQL*Plus: Generating sql and running it, simultaneously

** Pass user/pw as a parameter in the script
sqlplus -s << EOF1
$user/pw
'sqlplus -s <<EOF2
$user/pw
<sql stament>'
EOF2
EOF1
SQL*Plus: /nologn option (required for later versions)

sqlplus /nologon << EOF
connect / as sysdba
select sysdate from dual;
exit
EOF
Password (change and restore):

select ‘alter user [username]
identified by values ‘’’||password||’’’;’
from dba_users where username = ‘[username]’;
use the returned statement to restore the users original password
Missing file after Refresh

1.Shutdown database
2.copy missing backup file to directory
3.Edit init.ora file, add parameter "_allow_resetlogs_corruption=true"
4.Startup mount
5.alter database datafile "filename" online
6.recover database using backup controlfile until cancel
7.cancel immediately
8.alter database open resetlogs
Patching Oracle Applications

1. Download patch to /u01/app/applmgr/patches/11.03nca_server/TAR for
database server and apps server
2. Run adpatch on database server and apply patches c, d
3. Check for invalid objects and recompile
4. Run adpatch and apply patche g.
5. Run adpatch on application server but only apply patches c and g.
Reset DBID Manually:

Oracle note 139271.1
1. Shutdown normal
2. startup mount
3. select dbid, name from v$database;
4. alter database backup controlfile to trace.
5. execute dbms_backup_restore.zeroDbid(0)
6. shutdown normal
7. startup nomount
8. *Modify the backup controlfile to set database name fro the same
name (“set database samename resetlogs”)
9. run the controlfile script to create the new controlfile
10. alter database open resetlogs;
11. select dbid, name from v$database;
** Note: related notes 1062736.6 and 1058332.6
Reset Logs:
1. startup mount
2. recover database until cancel;
3. alter database open resetlogs;
SYS.LINK$
Name Null? Type
------------ -------- ---------------
OWNER# NOT NULL NUMBER
NAME NOT NULL VARCHAR2(128)
CTIME NOT NULL DATE
HOST VARCHAR2(2000)
USERID VARCHAR2(30)
PASSWORD VARCHAR2(30)
FLAG NUMBER
AUTHUSR VARCHAR2(30)
AUTHPWD VARCHAR2(30)

Planner Oracle Ref

Uploaded by

Copyright:

Available Formats

Planner Oracle Ref

Uploaded by

Document Information

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Planner Oracle Ref

Uploaded by

Copyright:

Available Formats

Oracle Reference

Sudden Performance Degradation (v8.0)

SVRMGR> oradebug event 10046 trace name context off

Buffer Busy Waits:

INITRANS AND MAXTRANS STORAGE PARAMETERS

Export with a compress:

Import with an uncompress

Export with a pipe to a remote file system

exp userid=un/pw recordlength=65535 file=${PIPE} direct=y full=y

cat ${PIPE} | rsh servername cat \> /path/exp.dmp

#cat `echo ${FILE_NAME}.* |sort` | uncompress >{PIPE} &

SQL*Plus: Generating sql and running it, simultaneously

SQL*Plus: /nologn option (required for later versions)

Password (change and restore):

Missing file after Refresh

Patching Oracle Applications

Reset DBID Manually:

You might also like