Agenda: Undocumented Oracle utilities

Report
Guenadi Nedkov Jilevski
E-mail: [email protected]
BGOUG : Bulgaria April 2010









Top new features in Oracle 11g R1 & 11g R2
Monitoring & Performance Tools
Autotrace, SQL trace, TKPROF, DBMS_STATS,
DBMS_SQLTUNE
Oradebug
Hidden Oracle 11g R2 parameters
Useful V$ and X$ tables for Oracle 11g R2
Powerful system tools: dbx, strace, orakill
ORA-0600 Tips and tricks
KFOD for ASM Monitoring










ADR for monitoring
Data Recovery Advisor with RMAN for Oracle Backup and
Recovery with Oracle 11g
Real Application Testing (RAT) features with 11g
New partitioning features for Oracle 11g Data partitioning such as
(automatic interval, partitioning by reference, composite rangerange, composite list-range, composite list-list, composite listhash, virtual column) and partition advisor
Enhancements for Oracle 11g performance tuning (SQLPA,HM,
SQL plan management, Server/Client result cache, Enhanced
statistics, SQL tuning automation, PL/SQL native compilation
etc...)
Invisible indexes new feature for Oracle 11g
In-Memory Parallel Execution in 11g R2
Auto Degree of Parallelism (DOP) and Queuing in 11g R2
The DBMS_PARALLEL_EXECUTE package in 11g R2
Performance improvement of On-Commit Fast Refresh in 11g R2





ADR is the new Automatic Diagnostic monitor
with 11g release. Many new monitoring and
reporting features.
Completely new paradigm for Oracle DBA
monitoring.
Available in both command line interface (adrci) or
from Oracle 11g Grid/Database control.
Replaces alert.log, trace files text based monitoring
in releases prior to 11g.
Alert.log still available but recommended you
learn ADR.

The new Oracle 11g Automatic Diagnostic
Repository (ADR) has the following new
features:
One stop shop for monitoring and reporting on
Oracle 11g
 New feature for incident reporting and packaging
(IPS)
 Available with Oracle 11g Database/Grid Control
GUI as well as from command line utility (adrci)
 Many useful reporting and monitoring tools.
 Provide access to all of the Oracle 11g trace files for
networking, alert.log etc.



ADR stores the main configuration details in log.xml file
Default ADR directory set Oracle 11g initialization diagnostic_dest
parameter
SQL> show parameter diagnostic_dest
NAME
TYPE
VALUE
------------------------------------ ----------- -----------------------------diagnostic_dest
string /u01/app/oracle
SQL>
SQL> SELECT * FROM V$DIAG_INFO;
INST_ID NAME
VALUE
---------- ---------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------1 Diag Enabled
TRUE
1 ADR Base
/u01/app/oracle
1 ADR Home
/u01/app/oracle/diag/rdbms/racdb/RACDB_1
1 Diag Trace
/u01/app/oracle/diag/rdbms/racdb/RACDB_1/trace
1 Diag Alert
/u01/app/oracle/diag/rdbms/racdb/RACDB_1/alert
1 Diag Incident
/u01/app/oracle/diag/rdbms/racdb/RACDB_1/incident
1 Diag Cdump
/u01/app/oracle/diag/rdbms/racdb/RACDB_1/cdump
1 Health Monitor
/u01/app/oracle/diag/rdbms/racdb/RACDB_1/hm
1 Default Trace File
/u01/app/oracle/diag/rdbms/racdb/RACDB_1/trace/RACDB_1_ora_6723.trc
1 Active Problem Count
0
1 Active Incident Count
0
11 rows selected.


ADR home directory located under default:
<ADR_BASE>/diag/rdbms/<db_name>/<instance_id>
[[email protected] RACDB_1]$ pwd
/u01/app/oracle/diag/rdbms/racdb/RACDB_1
[[email protected] RACDB_1]$ ls
alert cdump hm incident incpkg ir lck metadata stage sweep trace
[[email protected] RACDB_1]$
[[email protected] RACDB_1]$ adrci
ADRCI: Release 11.2.0.1.0 - Production on Mon Jan 25 15:51:01 2010
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
ADR base = "/u01/app/oracle"
adrci>


Command line utility (adrci)
Help command with ADR: help and extended help
adrci> help
HELP [topic]
Available Topics:
CREATE REPORT
ECHO
EXIT
HELP
HOST
IPS
PURGE
RUN
SET BASE
SET BROWSER
SET CONTROL
SET ECHO
SET EDITOR
SET HOMES | HOME | HOMEPATH
SET TERMOUT
SHOW ALERT
SHOW BASE
SHOW CONTROL
SHOW HM_RUN
SHOW HOMES | HOME | HOMEPATH
SHOW INCDIR
SHOW INCIDENT
SHOW PROBLEM
SHOW REPORT
SHOW TRACEFILE
SPOOL
There are other commands intended to be used directly by Oracle, type
"HELP EXTENDED" to see the list
adrci>
[[email protected] ~]$ adrci
adrci> help extended
HELP [topic]
Available Topics:
BEGIN BACKUP
CD
CREATE STAGING XMLSCHEMA
CREATE VIEW
DDE
DEFINE
DELETE
DESCRIBE
DROP VIEW
END BACKUP
INSERT
LIST DEFINE
MERGE ALERT
MERGE FILE
MIGRATE
QUERY
REPAIR
SELECT
SET COLUMN
SHOW CATALOG
SHOW DUMP
SHOW SECTION
SHOW TRACE
SHOW TRACEMAP
SWEEP
UNDEFINE
UPDATE
VIEW
adrci>

Using ADR to view alert.log
Use the following command with ADR: show alert

[[email protected] ~]$ adrci
ADRCI: Release 11.2.0.1.0 - Production on Fri Jan 29 18:10:28 2010
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
ADR base = "/u01/app/oracle"
adrci> show alert
Choose the alert log from the following homes to view:
1: diag/asm/+asm/+ASM1
2: diag/tnslsnr/raclinux1/listener_scan1
3: diag/tnslsnr/raclinux1/listener
4: diag/rdbms/rac11g/RAC11G
5: diag/rdbms/rac11gr1/RAC11gR1
6: diag/rdbms/rac11gr1/RAC11GR1
7: diag/rdbms/racdb/RACDB_1
Q: to quit
Please select option: 7













































2010-01-24 18:18:32.252000 +02:00
Starting ORACLE instance (normal)
2010-01-24 18:18:33.725000 +02:00
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Interface type 1 eth1 10.10.2.0 configured from GPnP Profile for use as a cluster interconnect
Interface type 1 eth0 192.168.2.0 configured from GPnP Profile for use as a public interface
Shared memory segment for instance monitoring created
Picked latch-free SCN scheme 2
Using LOG_ARCHIVE_DEST_1 parameter default value as USE_DB_RECOVERY_FILE_DEST
Autotune of undo retention is turned on.
IMODE=BR
ILAT =27
LICENSE_MAX_USERS = 0
SYS auditing is disabled
2010-01-24 18:19:07.433000 +02:00
NOTE: Volume support enabled
Starting up:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, Oracle Label Security, OLAP,
Data Mining, Oracle Database Vault and Real Application Testing options.
Using parameter settings in client-side pfile /u01/app/oracle/admin/RACDB/pfile/init.ora on machine raclinux1.gj.com
System parameters with non-default values:
processes
= 150
memory_target
= 2336M
control_files
= "+DATA/racdb/control01.ctl"
control_files
= "+DATA/racdb/control02.ctl"
db_block_size
= 8192
compatible
= "11.2.0.0.0"
log_archive_format
= "%t_%s_%r.dbf"
db_recovery_file_dest = "+FLASH"
db_recovery_file_dest_size= 8000M
remote_login_passwordfile= "EXCLUSIVE"
db_domain
= ""
dispatchers
= "(PROTOCOL=TCP) (SERVICE=RACDBXDB)"
remote_listener
= "raclinu-cluster:1521"
audit_file_dest
= "/u01/app/oracle/admin/RACDB/adump"
audit_trail
= "DB"
db_name
= "RACDB"
open_cursors
= 300
diagnostic_dest
= "/u01/app/oracle"
Cluster communication is configured to use the following interface(s) for this instance
10.10.2.11
cluster interconnect IPC version:Oracle UDP/IP (generic)
IPC Vendor 1 proto 2

Use the show tracefile command for ADR with Oracle 11g
adrci> show tracefile
diag/rdbms/racdb/RACDB_1/trace/RACDB_1_ora_4592.trc
diag/rdbms/racdb/RACDB_1/trace/RACDB_1_ora_10352.trc
diag/rdbms/racdb/RACDB_1/trace/RACDB_1_ora_11557.trc
diag/rdbms/racdb/RACDB_1/trace/RACDB_1_ora_5887.trc
diag/rdbms/racdb/RACDB_1/trace/RACDB_1_ora_2678.trc
diag/rdbms/racdb/RACDB_1/trace/RACDB_1_ora_22084.trc
diag/rdbms/racdb/RACDB_1/trace/RACDB_1_ora_16417.trc
diag/rdbms/racdb/RACDB_1/trace/RACDB_1_ping_30558.trc
diag/rdbms/racdb/RACDB_1/trace/RACDB_1_ora_25283.trc
diag/rdbms/racdb/RACDB_1/trace/RACDB_1_ora_4914.trc
diag/rdbms/racdb/RACDB_1/trace/RACDB_1_ora_25584.trc
diag/rdbms/racdb/RACDB_1/trace/RACDB_1_qmnc_7024.trc
diag/rdbms/racdb/RACDB_1/trace/RACDB_1_ora_15058.trc
diag/rdbms/racdb/RACDB_1/trace/RACDB_1_ora_25409.trc
diag/rdbms/racdb/RACDB_1/trace/RACDB_1_ora_18403.trc
diag/rdbms/racdb/RACDB_1/trace/RACDB_1_ora_23833.trc
diag/rdbms/racdb/RACDB_1/trace/RACDB_1_ora_23604.trc
diag/rdbms/racdb/RACDB_1/trace/RACDB_1_ora_30828.trc
adrci>




ADR uses incidents for critical errors with
Oracle 11g R2
These incidents can be packaged by ADR and
sent to Oracle support for analysis
Useful new feature with Oracle 11g for
problem resolution and reporting
Perform many of the tasks used by the RDA
(Remote Diagnostic Assistant) for releases prior
to Oracle 11g.

Critical error appear as incidents. You can use show
incident command for ADR with Oracle 11g
adrci> set homepath diag/rdbms/racdb/RACDB_1
adrci> show incident
ADR Home = /u01/app/oracle/diag/rdbms/racdb/RACDB_1:
*************************************************************************
INCIDENT_ID PROBLEM_KEY CREATE_TIME
---------------------------------------------------------------------------------------------------------------------11009 ORA 600 [kssadpm: null parent] 2010-01-15 19:08:30.464549 -04:00
1 rows fetched

Detail view for incidents with ADR and Oracle 11g
adrci> show incident –mode detail –p “incident_id=11009”
ADR Home = /u01/app/oracle/diag/rdbms/ora11g/ORA11G:
*************************************************************************
**********************************************************
INCIDENT INFO RECORD 1
**********************************************************
INCIDENT_ID 11009
STATUS ready
CREATE_TIME 2010-01-15 19:08:30.464549 -04:00
PROBLEM_ID 1
ADR provide the following commands for
managing incidents:
 IPS CREATE PACKAGE for new incident with
Oracle 11g
 IPS ADD INCIDENT <insident_id> package
<package_id>
 IPS GENERATE PACKAGE <package_id> in
<directory>
This procedure creates a ZIP file with the trace
diagnostics for Oracle support to analyze for
critical errors.
 Use the IPS SHOW CONFIGURATION command
to check configuration for ADR incidents.

First we need to create a new package for the incident:
adrci> ips create package
Created package 1 without any contents, correlation level typical
Then we need to add the incident to the new package for ADR:
adrci> ips add incident 11009 package 1;
Added incident 11009 to package 1
Finally, we need to generate the package with data:
adrci> ips generate package 1 in /u01/app/oracle/adrci/support
Generated package 1 in file
/u01/app/oracle/adrci/support/IPSPKG_20080817214731_COM_1.zip, mode complete
The new file is generated as a zip file in the directory for support
Every DBA should use these tools for tuning
Oracle!
Autotrace
 SQL trace
 TKPROF
 DBMS_STATS
 DBMS_SQLTUNE package
 ORADEBUG



Autotrace useful quick and dirty tool for
tracing with Oracle 11g
Provides many tracing and tuning options:
Explain plan
 Costs associated with SQL queries
 Tuning statistics
Used with SQL*Plus interface
Excellent starting point for tuning SQL queries.

Use the set autotrace on command in SQL*Plus
to enable Autotrace and set autotrace off to
disable autotrace tool.
 Syntax for autotrace:
SQL> set autotrace
Usage: SET AUTOT[RACE] {OFF | ON |
TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]
SQL>

SQL> set autotrace on
SQL> select prod_name, prod_list_price from
sh.products where prod_list_price > 7.99 and
prod_list_price < 69.99;
52 rows selected.
Execution Plan
---------------------------------------------------------Plan hash value: 1954719464
-----------------------------------------------------------------------------| Id | Operation
| Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------| 0 | SELECT STATEMENT |
| 3 | 93 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| PRODUCTS | 3 | 93 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------Predicate Information (identified by operation id):
--------------------------------------------------1 - filter("PROD_LIST_PRICE"<69.99 AND "PROD_LIST_PRICE">7.99)
Statistics
---------------------------------------------------------7880 recursive calls
0 db block gets
1769 consistent gets
63 physical reads
0 redo size
2555 bytes sent via SQL*Net to client
452 bytes received via SQL*Net from client
5 SQL*Net roundtrips to/from client
88 sorts (memory)
0 sorts (disk)
52 rows processed
SQL>




SQL Trace is powerful tool for the serious
Oracle Performance expert
Event based: 10046 (SQL Trace), 10053 ( Cost
based Optimizer – CBO)
Can be set at instance or session level
Use DBMS_SUPPORT, DBMS_SYSTEM,
DBMS_SESSION, ORADEBUG, or SQL*PLUS
commands to enable tracing activities with
Oracle 11g R2.
SQL> EXEC DBMS_SESSION.set_sql_trace(sql_trace => TRUE);
SQL> EXEC DBMS_SESSION.set_sql_trace(sql_trace => FALSE);
SQL> EXEC DBMS_SYSTEM.set_sql_trace_in_session(sid=>123, serial#=>1234, sql_trace=>TRUE);
SQL> EXEC DBMS_SYSTEM.set_sql_trace_in_session(sid=>123, serial#=>1234, sql_trace=>FALSE);
SQL> EXEC DBMS_SYSTEM.set_ev(si=>123, se=>1234, ev=>10046, le=>8, nm=>' ');
SQL> EXEC DBMS_SYSTEM.set_ev(si=>123, se=>1234, ev=>10046, le=>0, nm=>' ');
-- All versions, requires DBMS_SUPPORT package to be loaded.
SQL> EXEC DBMS_SUPPORT.start_trace(waits=>TRUE, binds=>FALSE);
SQL> EXEC DBMS_SUPPORT.stop_trace;
SQL> EXEC DBMS_SUPPORT.start_trace_in_session(sid=>123, serial=>1234, waits=>TRUE,
binds=>FALSE);
SQL> EXEC DBMS_SUPPORT.stop_trace_in_session(sid=>123, serial=>1234);
The DBMS_SUPPORT package is not present by default, but can be loaded as the SYS user by executing the
@$ORACLE_HOME/rdbms/admin/dbmssupp.sql script.
For methods that require tracing levels the following are valid values:

0 - No trace. Like switching sql_trace off.

2 - The equivalent of regular sql_trace.

4 - The same as 2, but with the addition of bind variable values.

8 - The same as 2, but with the addition of wait events.

12 - The same as 2, but with both bind variable values and wait events.


Oracle Metalink Note 199081.1 provides good
overview for SQL trace
Using SQL*PLUS commands for SQL trace at
session/instance levels:
alter system/session set sql_trace=true;
alter system/session set timed_statistcs=true;
Optional for SQL timing tracing details
ALTER SYSTEM/SESSION SET EVENTS’10046 trace name context
forever, level <level>’;

Output SQL trace files to user_dump_dest as .trc
file
SHOW PARAMETER USER_DUMP_DEST




Should use SYS enabled account to run the SQL trace.
Need to have explain plan enabled (i.e.: utlxplan.sql)
Enable parameters for SQL tracing: timed_statistics,
sql_trace
Recommended use ALTER SESSION as ALTER SYSTEM
can place load on Oracle 11g system and impact
performance
SQL> alter session set timed_statistics=true;
Session altered.
SQL> alter session set sql_trace=true;
Session altered.
SQL> show parameter user_dump_dest
NAME
TYPE
VALUE
----------------------------------------------------------------------------user_dump_dest
string
/u01/app/oracle/diag/rdbms/ora
11g/ORA11G/trace

Use the ALTER SESSION SET TRACEFILE_IDENTIFIER command to
name trace file for easy identification
SQL> alter session set tracefile_identifire=‘products_sqltune’;
Session altered
SQL> show parameter tracefile_identifier
content of sample SQL Trace file
=====================
PARSING IN CURSOR #2 len=109 dep=0 uid=88 oct=3 lid=88 tim=1264443780269154 hv=1524029202 ad='8e65be74'
sqlid='gkc6r0xdddpsk'
select prod_name, prod_list_price from sh.products where prod_list_price > 7.99 and prod_list_price < 69.99
END OF STMT
PARSE #2:c=0,e=42,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1954719464,tim=1264443780269152
EXEC #2:c=0,e=24,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1954719464,tim=1264443780269240
FETCH #2:c=0,e=45,p=0,cr=3,cu=0,mis=0,r=1,dep=0,og=1,plh=1954719464,tim=1264443780269346
FETCH #2:c=0,e=19,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=1,plh=1954719464,tim=1264443780269523
FETCH #2:c=0,e=26,p=0,cr=2,cu=0,mis=0,r=15,dep=0,og=1,plh=1954719464,tim=1264443780269889
FETCH #2:c=0,e=16,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=1,plh=1954719464,tim=1264443780270211
FETCH #2:c=0,e=14,p=0,cr=1,cu=0,mis=0,r=6,dep=0,og=1,plh=1954719464,tim=1264443780270463
*** 2010-01-25 20:23:01.766
CLOSE #2:c=0,e=13,dep=0,type=3,tim=1264443781766794
=====================


TRACEFILE_IDENTIFIER - Another useful parameter
Many a times you need to enable tracing at a session level. And when you do that, you have to make a little bit
of effort in digging out your session's trace file. Why? because the trace file is created in the user_dump_dest
directory and there would be so many other trace files, and all of them would have similar naming convention
"SID_ora_nnnn.trc". However with the help of the parameter "TRACEFILE_IDENTIFIER", you can easily
distinguish your trace file. Very useful specifically when you want to use trace analyzer etc.
Here is how?
1. Set the tracefile identifier as you want, this will be appended to trace file name.
alter session set tracefile_identifier=‘filename_sql_trace';
2. Enable your session level tracing
alter session set sql_trace=true;
alter session set events '10046 trace name context forever, level 12' ;
3. Execute your queries/statements
4. Turn-off tracing
alter session set events '10046 trace name context off';
alter session set sql_trace=false;
5. Check the user_dump_dest folder and you will find a file name "SID_ora_nnnn_filname_sql_trace.trc
See now it's so easy to identify. Having said that you can still find out the trace file name without using
TRACEFILE_IDENTIFIER parameter using the following SQL but when Oracle has provided an easier
method, why not use it?
-- sql to find out trace file name for your session.
select c.value || '/' || d.instance_name ||'_ora_' || a.spid || '.trc' trace
from v$process a, v$session b, v$parameter c, v$instance d
where a.addr = b.paddr
and b.audsid = userenv('sessionid')
and c.name = 'user_dump_dest'
/




TKPROF is a trace kernel profiler – useful for
formatting trace files for tuning purposes
Flexible options for performance report
statistics
Cleans up the raw SQL trace files into human
readable tuning reports.
Oracle Metalink Note: 41634.1 TKPROF
Simplistic overview



TKPROF takes the trace file and output report
file plus performance specific parameters
Many parameter options for using tkprof
Syntax for tkprof:
tkprof trace_file.trc output_file.rpt [waits={yes|no}]
[sort={yes|no}] [print=option] [aggregate={yes|no}]
[insert={filename}] [sys={yes|no}]
[table={schema.table}] [explain={schema/password}]
[record=filename] [width=n]

TKPROF Help :
[[email protected] ~]$ tkprof
Usage: tkprof tracefile outputfile [explain= ] [table= ]
[print= ] [insert= ] [sys= ] [sort= ]
table=schema.tablename Use 'schema.tablename' with 'explain=' option.
explain=user/password Connect to ORACLE and issue EXPLAIN PLAN.
print=integer List only the first 'integer' SQL statements.
aggregate=yes|no
insert=filename List SQL statements and data inside INSERT statements.
sys=no
TKPROF does not list SQL statements run as user SYS.
record=filename Record non-recursive statements found in the trace file.
waits=yes|no Record summary for any wait events found in the trace file.
sort=option
Set of zero or more of the following sort options:
prscnt number of times parse was called
prscpu cpu time parsing
prsela elapsed time parsing
prsdsk number of disk reads during parse
prsqry number of buffers for consistent read during parse
prscu number of buffers for current read during parse
prsmis number of misses in library cache during parse
execnt number of execute was called
execpu cpu time spent executing
exeela elapsed time executing
exedsk number of disk reads during execute
exeqry number of buffers for consistent read during execute
execu number of buffers for current read during execute
exerow number of rows processed during execute
exemis number of library cache misses during execute
fchcnt number of times fetch was called
fchcpu cpu time spent fetching
fchela elapsed time fetching
fchdsk number of disk reads during fetch
fchqry number of buffers for consistent read during fetch
fchcu number of buffers for current read during fetch
fchrow number of rows fetched
userid userid of user that parsed the cursor
[[email protected] ~]$

Example
[[email protected] trace]$ tkprof
RACDB1_ora_9163_products_sqltune.trc tune_sql.rpt
waits=yes aggregate=yes sys=yes explain=sys/oracle
********************************************************************************
SQL ID: gkc6r0xdddpsk
Plan Hash: 1954719464
select prod_name, prod_list_price
from
sh.products where prod_list_price > 7.99 and prod_list_price < 69.99
call count
cpu elapsed
disk
query current
rows
------- ------ -------- ---------- ---------- ---------- ---------- ---------Parse
3
0.00
0.02
0
0
0
0
Execute
4
0.00
0.00
0
0
0
0
Fetch
15
0.00
0.00
0
24
0
156
------- ------ -------- ---------- ---------- ---------- ---------- ---------total
22
0.00
0.03
0
24
0
156
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 88
********************************************************************************

What is the DBMS_SQLTUNE package?


Automates SQL tuning by using profiles to tune
SQL.
DBMS_SQLTUNE package is used as part of the SQL
Tune Advisors (STA) with Oracle 11g

Example
SQL> DECLARE
sql_task_name VARCHAR2(30);
tune_sqltext CLOB;
BEGIN
tune_sqltext :='select * '||'FROM sh.sales '||' where prod_id=20 and '||'
cust_id=200 ';
sql_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_text=>tune_sqltext,
User_name=>'SH',
Scope=>'COMPREHENSIVE',
Time_limit=>60,
Task_name=>'TUNE_SH_TASK',
Description=>'New task for tuning SH query');
END;
/
2 3 4 5 6 7 8 9 10 11 12 13 14
PL/SQL procedure successfully completed.


DBMS_SQLTUNE uses the
CREATE_TUNING_TASK function as part of SQL
Tuning Advisor (STA) with 11g
Once we have created the tuning task, we need to
execute it with the
DBMS_SQLTUNE.EXECUTE_TUNING_TASK:
SQL> begin
DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name=>'TUNE_S
H_TASK');
End;
/ 2 3 4
PL/SQL procedure successfully completed.
SQL>

To view SQL Tuning report query the DBMS_SQLTUNE.REPORT_TUNING_TASK;
SQL> select dbms_sqltune.report_tuning_task(‘TUNE_SH_TASK’) drom DUAL;
SQL> /
DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNE_SH_TASK')
-------------------------------------------------------------------------------GENERAL INFORMATION SECTION
------------------------------------------------------------------------------Tuning Task Name : TUNE_SH_TASK
Tuning Task Owner : SYS
Workload Type : Single SQL Statement
Execution Count : 2
Current Execution : EXEC_86
Execution Type : TUNE SQL
Scope
: COMPREHENSIVE
Time Limit(seconds): 60
Completion Status : COMPLETED
DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNE_SH_TASK')
-------------------------------------------------------------------------------Started at
: 01/26/2010 16:34:26
Completed at
: 01/26/2010 16:34:27
------------------------------------------------------------------------------Schema Name: SH
SQL ID : 8z3nbpj9f0nsa
SQL Text : select * FROM sh.sales where prod_id=20 and cust_id=200
------------------------------------------------------------------------------FINDINGS SECTION (1 finding)
------------------------------------------------------------------------------DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNE_SH_TASK')
-------------------------------------------------------------------------------1- Index Finding (see explain plans section below)
-------------------------------------------------The execution plan of this statement can be improved by creating one or more
indices.
Recommendation (estimated benefit: 93.13%)
------------------------------------------ Consider running the Access Advisor to improve the physical schema design
or creating the recommended index. If you choose to create the
recommended index, consider dropping the index "SH"."SALES_CUST_BIX”


What is Oradebug?
Why should you use it?
Performance Tuning
 Hang Analysis
 Dump Analysis for Oracle 11g Memory
 Suspend and Resume Oracle processes





Oradebug introduced in version 7.X release of
Oracle
Poorly documented utility
Recommend caution when using oradebug
Syntax for Oradebug

Oradebug has MANY options!!!
Use the oradebug help command to list all the main options available:

SQL> oradebug help
HELP
[command]
Describe one or all commands
SETMYPID
Debug current process
SETOSPID
<ospid>
Set OS pid of process to debug
SETORAPID <orapid> ['force']
Set Oracle pid of process to debug
SETORAPNAME <orapname>
Set Oracle process name to debug
SHORT_STACK
Get abridged OS stack
CURRENT_SQL
Get current SQL
DUMP
<dump_name> <lvl> [addr] Invoke named dump
DUMPSGA
[bytes]
Dump fixed SGA
DUMPLIST
Print a list of available dumps
EVENT
<text>
Set trace event in process
SESSION_EVENT <text>
Set trace event in session
DUMPVAR
<p|s|uga> <name> [level] Print/dump a fixed PGA/SGA/UGA variable
DUMPTYPE
<address> <type> <count> Print/dump an address with type info
SETVAR
<p|s|uga> <name> <value> Modify a fixed PGA/SGA/UGA variable
PEEK
<addr> <len> [level] Print/Dump memory
POKE
<addr> <len> <value> Modify memory
WAKEUP
<orapid>
Wake up Oracle process
SUSPEND
Suspend execution
RESUME
Resume execution
FLUSH
Flush pending writes to trace file
CLOSE_TRACE
Close trace file
TRACEFILE_NAME
Get name of trace file
LKDEBUG
Invoke global enqueue service debugger
NSDBX
Invoke CGS name-service debugger
-G
<Inst-List | def | all> Parallel oradebug command prefix
-R
<Inst-List | def | all> Parallel oradebug prefix (return output
SETINST
<instance# .. | all> Set instance list in double quotes
SGATOFILE
<SGA dump dir>
Dump SGA to file; dirname in double quotes
DMPCOWSGA <SGA dump dir> Dump & map SGA as COW; dirname in double quotes
MAPCOWSGA <SGA dump dir>
Map SGA as COW; dirname in double quotes
HANGANALYZE [level] [syslevel]
Analyze system hang
FFBEGIN
Flash Freeze the Instance
FFDEREGISTER
FF deregister instance from cluster
FFTERMINST
Call exit and terminate instance
FFRESUMEINST
Resume the flash frozen instance
FFSTATUS
Flash freeze status of instance
SKDSTTPCS <ifname> <ofname>
Helps translate PCs to names
WATCH
<address> <len> <self|exist|all|target> Watch a region of memory
DELETE
<local|global|target> watchpoint <id> Delete a watchpoint
SHOW
<local|global|target> watchpoints
Show watchpoints
DIRECT_ACCESS <set/enable/disable command | select query> Fixed table access
CORE
Dump core without crashing process
IPC
Dump ipc information
UNLIMIT
Unlimit the size of the trace file
PROCSTAT
Dump process statistics
CALL
[-t count] <func> [arg1]...[argn] Invoke function with arguments

First we need to obtain SPID from v$SESSION and
V$PROCESS
SQL> select username, sid, serial#, paddr from v$session where username='SH';
USERNAME
SID SERIAL# PADDR
------------------------------ ---------- ---------- -------SH
153
3370 994694F4
SQL> select addr, pid , spid from v$process where addr='994694F4';
ADDR
PID SPID
-------- ---------- -----------------------994694F4
35 20933
SQL>

Then we use the SPID in oradebug to trace
session:
SQL> oradebug setospid 20933
Oracle pid: 35, Unix process pid: 20933, image:
[email protected] (TNS V1-V3)
SQL>

We then set the unlimit parameter for the trace
file with Oradebug:
SQL> oradebug unlimit
Statement processed.
SQL>

Now we are ready to start the trace for our
session with Oradebug
SQL> oradebug event 10046 trace name context forever, level 4
Statement processed.
SQL>





Oradebug uses Events for tracing options
Some common trace events:
10053 – Cost Based Optimizer (CBO) trace
10046 – SQL Trace
To start tracing with Oradebug
SQL> oradebug event 10046 trace name context forever, level 4
Statement processed.
SQL>

To stop and turn off tracing for Oradebug:
SQL> oradebug event 10046 trace name context off
Statement processed.
Trace file output based on user_dump_dest parameter
 Naming convention for the trace file
sid_ora_pid_traceid.trc
 Easy way find trace file is to set trace_identifier parameter:
alter session set tracefile_identifier=‘mytrace’;
 Useful query to find trace file

Select
u_dump.value||’/’||db_name.value||’_ora_’||v$PROCESS.spid||n
vl2(v$process.traceid,’_’||v$process.traceid,
Null )||’.trc’ “Trace File” from v$parameter u_dump cross join
v$parameter db_name cross join v$process join v$session on
v$process.addr = ‘usr_dump_dest’ and db_name.name = ‘db_name’ and
v$session.audsid=sys_context(‘userenv’,’sessionid’);


Locate the trace file for oradebug
Format it with tkprof
cp RACDB_1_ora_20933_test_sh_query.trc trc.trc
[[email protected] trace]$ tkprof trc.trc rpt.rpt aggregate=yes waits=yes sys=no explain=sh/sh
TKPROF: Release 11.2.0.1.0 - Development on Tue Jan 26 18:29:25 2010
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
[[email protected] trace]$

Open the trace file with text editor
$vi rpt.rpt
SQL ID: 3j4064f628jmk
Plan Hash: 1954719464
select prod_name, prod_list_price
from
sh.products where prod_list_price > 7.99 and prod_list_price < 69.99
call count
cpu elapsed
disk query current
rows
------- ------ -------- ---------- ---------- ---------- ---------- ---------Parse
6 0.00
0.00
0
0
0
0
Execute 6 0.00
0.00
0
0
0
0
Fetch
30 0.00
0.00
0
48
0
312
------- ------ -------- ---------- ---------- ---------- ---------- ---------total
42 0.00
0.00
0
48
0
312
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 88 (SH)
Rows Row Source Operation
------- --------------------------------------------------52 TABLE ACCESS FULL PRODUCTS (cr=8 pr=0 pw=0 time=0 us cost=3 size=1820 c
ard=52)
Rows Execution Plan
------- --------------------------------------------------0 SELECT STATEMENT MODE: ALL_ROWS
52 TABLE ACCESS MODE: ANALYZED (FULL) OF 'PRODUCTS' (TABLE)



Oradebug is excellent tool for solving database
hang issue.
Use the HANGANALYZE option with oradebug
Syntax:
oradebug hanganalyze <level>

For Oracle RAC environment the syntax is:
oradebug –g all hanganalyze <level>


Level indicates amount of details to collect for a
hang.
Refer to Metalink Note: 175006.1 and
Note:215858.1

The debug levels defined for HANGANALYZE
with oradebug are as follows Note 215858.1:
Level 10: Dump all process ( IGN state)
 Level5: Level 4 + Dump all processes involved with
chain (NLEAF)
 Level 4: Level 3 + Dump leaf nodes (blocker) in wait
chains (LEAF, LEAF_NW, ING_DMP state)
 Level 3: Level 2 + Dump only processes considered
in hang condition (IN_HANG state)
 Levels 1-2: Generate ONLY HANGANALYZE
output with no process dump


Set environment for hang analysis:
SQL> oradebug setmypid
Statement process.
SQL> oradebug unlimit
Statement processed.

Perform Hang Analysis:
SQL> oradebug -g all hanganalyze 10
Hang Analysis Dumps in
/u01/app/oracle/diag/rdbms/racdb/RACDB_1/trace/RACDB
_1_diag_6112.trc
SQL>exit

Review output hang analysis from trace file:
Trace file /u01/app/oracle/diag/rdbms/racdb/RACDB_1/trace/RACDB_1_diag_6112.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, Oracle Label Security, OLAP,
Data Mining, Oracle Database Vault and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1
System name: Linux
Node name:
raclinux1.gj.com
Release:
2.6.18-164.el5PAE
Version:
#1 SMP Thu Sep 3 02:28:20 EDT 2009
Machine:
i686
Instance name: RACDB_1
Redo thread mounted by this instance: 0 <none>
Oracle process number: 5
Unix process pid: 6112, image: [email protected] (DIAG)
*** 2010-01-26 13:02:55.529
*** SESSION ID:(127.1) 2010-01-26 13:02:55.529
*** CLIENT ID:() 2010-01-26 13:02:55.529
*** SERVICE NAME:() 2010-01-26 13:02:55.529
*** MODULE NAME:() 2010-01-26 13:02:55.529
*** ACTION NAME:() 2010-01-26 13:02:55.529
Node id: 0
List of nodes: 0,
*** 2010-01-26 13:02:55.565
Reconfiguration starts [incarn=0]
*** 2010-01-26 13:02:55.566
I'm the master node
*** 2010-01-26 13:02:55.566
Reconfiguration completes [incarn=1]
Group reconfiguration cleanup
Use oradebug to dump contents of UGA and PGA memory
Useful for debugging memory leak issues

Dump Analysis for Oracle 11g Memory

Describe events
To start diagnosing a problem with the process size, such as a suspected leak,
a heapdump of the offending process is required (Also helps to check with pmap –x <pid> for memory leaks):


$sqlplus
SQL> connect '/ as sysdba'
SQL> oradebug setospid <pid>
SQL> oradebug unlimit
SQL> oradebug dump heapdump 5 --this dumps PGA and UGA heaps
The above information should be provided to Oracle Support Services.
Oracle 8i / 9i / 10g
SQL> oradebug dump events <level>
Level 1 - Dump session group's event settings
Level 2 - Dump process group's event settings
Level 4 - Dump system group's event settings (Ie the instance wide events)
Oracle 10g / 11g and above
SQL> oradebug eventdump <level>
SESSION - Dump session group's event settings
PROCESS - Dump process group's event settings
SYSTEM - Dump system group's event settings (Ie the instance wide events)
SQL> oradebug setmypid
Statement processed.
SQL> oradebug eventdump session
Statement processed.
SQL> oradebug eventdump system
Statement processed.
SQL> alter system set events '942 trace name errorstack level 3';
System altered.
SQL> oradebug eventdump system
942 trace name errorstack level 3
SQL>
SQL> oradebug eventdump session
942 trace name errorstack level 3

Use the oradebug dumpsga command:
SQL> oradebug setmypid
Statement processed.
SQL> oradebug unlimit
Statement processed.
SQL> oradebug dumpsga
Statement processed.
SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/racdb/RACDB_1/trace/RACDB_1_ora_2
5351.trc
SQL> exit

Contents of SGA dump in oradebug trace file
Trace file /u01/app/oracle/diag/rdbms/racdb/RACDB_1/trace/RACDB_1_ora_25351.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, Automatic Storage Management,
Oracle Label Security,
OLAP, Data Mining, Oracle Database Vault and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1
System name: Linux
Node name: raclinux1.gj.com
Release:
2.6.18-164.el5PAE
*** SESSION ID:(154.6347) 2010-01-26 19:14:21.438
*** CLIENT ID:() 2010-01-26 19:14:21.438
*** SERVICE NAME:(SYS$USERS) 2010-01-26 19:14:21.438
*** MODULE NAME:([email protected] (TNS V1-V3)) 2010-01-26 19:14:21.438
*** ACTION NAME:() 2010-01-26 19:14:21.438
Processing Oradebug command 'setmypid'
*** 2010-01-26 19:14:21.438
Oradebug command 'setmypid' console output: <none>
*** 2010-01-26 19:14:30.092
Processing Oradebug command 'unlimit'
*** 2010-01-26 19:14:30.092
Oradebug command 'unlimit' console output: <none>
*** 2010-01-26 19:15:11.671
----- Fixed Areas Dump (level=2) --------- Dump of the Fixed SGA ----ksmsgft ksmsgf_ [20000000, 20001000) = 00000000 00000000 00000000 00000000 ...
Dump of memory from 0x20000010 to 0x20001000
20000010 00000000 00000000 00000000 00000000 [................]
Repeat 254 times
kywmr * kywmrsga_ [20001000, 20001004) = 990CD020



Oradebug useful for stopping and restarting
Oracle sessions
Be careful when using oradebug in production
environments!
Syntax for suspending process with oradebug
oradebug suspend

To resume a process that has been suspended
oradebug resume

Useful when need to delay batch process for
more critical job.

Find the session information to suspend:
SQL> select s.username, s.pid, p.spid
2
From
3
V$session s, v$process p
4
Where s.paddr=p.addr
5
And
6
S.username <> ‘SYS’;
USERNAME
SID
----------------------------SH
140
SPID
---------18335

Oradebug suspend
SQL> oradebug setospid 18335
SQL> oradebug suspend
Statement processed.

Oradebug resume
SQL> oradebug resume
Statement processed.


oradebug is used to trace issues related to IPC
in interconnects and RAC
Syntax for IPC memory dump with Oracle
RAC:
SQL> oradebug ipc

Example
SQL> oradebug setmypid
Statement processed.
SQL>oradebug unlimit
Statement processed.
SQL>oradebug ipc
Information written to trace file.
SQL> oradebug trace_file
/u01/app/oracle/admin/RACDB/udump/racdb1_ora_6391.tr
c
SQL>exit

Review trace file for interconnect trace with Oradebug
for RAC:
SSKGXPT 0xcc75e9c flags SSKGXPT_READPENDING info for network 0
socket no 7 IP 10.10.10.11 UDP 2247
sflagsSSKGXPT_UP
info for network 1
socket no 0 IP 0.0.0.0 UDP 0
sflagsSSKGXPT_DOWN
active 0 actcnt1
context timestamp 0
no ports
sconnoacconoerttstate seq# sent asyncsync rtransacks


Oracle 11g has many new additional hidden
parameters!
There are more than 2050 hidden parametrs for
Oracle 11g R2!
SQL> select count(*) from x$ksppi where substr(ksppinm,1,1)='_';
COUNT(*)
---------2057
SQL>

Query to find out these hidden Oracle 11g R2
parameters:
Select ksppinm from x$ksppi where substr(ksppinm,1,1)=‘_’;


Some hidden parameters for 11g new features
Hang manager hidden parameters for Oracle 11g
R2:
_hm_analysis_ouput_disk
_hm_analysis_oradebug_node_dump_level
_hm_analysis_oradebug_sys_dump_level
_hm_verification_interval
_hm_log_incidents
Be careful using these
 First change in test and sandbox
 Best to work with Oracle support since those are
undocumented parameters!




Each release of Oracle adds many new V$ and X$
performance view tables.
More than 945 different X$ tables in Oracle 11g R2.
There are over 525 different v$ performance views
in Oracle 11g R2 .
Query to find the V$ performance views for Oracle
11g R2:
Select name, type from v$fixed_table where name like ‘V$%’;

Query to find the list of X$ tables for Oracle 11g
R2:
Select name, type from v$fixed_table where name like ‘X$%’;

Some interesting new V$ Dynamic Performance Views
V$MEMORY_DYNAMIC_COMPONENTS - displays information about the dynamic
SGA components. This view summarizes information based on all completed SGA
resize operations since instance startup. All sizes are expressed in bytes.
V$MEMORY_TARGET_ADVICE - information about how the MEMORY_TARGET
parameter should be sized based on current sizing and satisfaction metrics.
V$ALERT_TYPES – information for server alert types
V$IR_FAILURE – provides information on the failure. Note that records in this view
can have parent records within this view. (DRA)
V$IR_REPAIR – show the results of repair failure commands. (DRA)
V$IR_MANUAL_CHECKLIST – detailed information messages related to the failure.
Messages provide information on how to manually correct the problem. (DRA)
V$IR_FAILURE_SET – list of advice records associated with the failure. (DRA)
V$HM_CHECK - information about all the checks registered with Health Monitor.
Each check is uniquely identified by a name or an ID.
V$HM_CHECK_PARAM - information about the input parameters of all Health
Monitor checks.
V$HM_RUN - information about all Health Monitor checks and their status. All checks
ran.
V$HM_FINDING - information about all the findings of various Health Monitor runs.
V$HM_RECOMMENDATION - information about any recommendation from Health
check runs.
V$HM_INFO - Information about runs, finding and recommendations.
Perform a describe on each of these V$ dynamic performance views
for detail information.

These are some useful new X$ tables for Oracle
11g R2:
X$DIAG_INFO
X$IR_WORKING_FAILURE_SET – incident reporting
X$IR_WORKING_REPAIR_SET – incident reporting
X$IR_REPAIR_OPTION – incident reporting
X$IR_MANUAL_OPTION – incident reporting
X$IR_REPAIR_STEP – incident reporting
When Oracle tools are not enough, these OS tools are essential:
 dbx (Solaris and Linux)
 gdb (Linux) similar to dbx
 truss (Solaris)
 strace (Linux)
 orakill (Windows)
 STACKX (UNIX) Metalink Note: 362791.1
Useful References on Metalink for core Debug Analysis:
Oracle Metalink Note:16534.1 “debug Core Dumps / core files
on Unix”
Oracle Metalink Note: 1007808.6 “How to handle core dumps
on UNIX”




DBX is trace debugger for Solaris and also
available for free download from Sun.com as part
of Solaris Developer Studio.
Provides comprehensive debugger level
commands for Solaris and Linux OS systems calls
and Oracle 11g instance and network level internal
processes.
DBX is an advanced complex tool with complex
syntax.
Complete reference guide from SUN:
Sun Studio 12: Debugging a Program with dbx:
http:/docs.sun.com/app/docs


DBX can be used to trace Oracle 11g processes
Getting help with dbx
[[email protected] bin]# ./dbx
For information about new features see `help changes'
To remove this message, put `dbxenv suppress_startup_message 7.7' in your .dbxrc
(dbx) help
dbx: warning: Help file from 'C' locale is used. No help file in the locale 'en_US.UTF-8'
dbx: warning: Help file from 'C' locale is used. No help file in the locale 'en_US.UTF-8'
command summary
Use `commands' to see a command summary consisting of one-line
descriptions of each dbx command.
Execution and Tracing
cancel
catch
clear
cont
fix
fixed
handler ignore
next
pop
replay
rerun
run
runargs
save
status
stop
trace
unintercept when
delete
intercept
restore
step
whocatches
Displaying and Naming Data
assign
call
dis
display
down
dump
examine exists
fortran_modules
frame
hide
print
undisplay unhide
up
watch
whatis
where
whereami
whereis
which
Accessing Source Files
bsearch
cd
edit
file
func
funcs
line
list
loadobjects module
modules
search
files
loadobject
pathmap pwd
Debugging Multiple Threads
lwp
lwps
sync
syncs
thread
threads

Find the PID for Oracle process
[[email protected] bin]# ps -ef | grep oracleRACDB
oracle 9546 9545 0 18:26 ?
00:00:00 oracleRACDB_1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
root 15103 12453 0 20:02 pts/3 00:00:00 grep oracleRACDB
[[email protected] bin]#

Now we need to get a trace for Oracle
[[email protected] bin]# ./dbx /u01/app/oracle/product/11.2.0/db_1/bin/oracle 9546
For information about new features see `help changes'
To remove this message, put `dbxenv suppress_startup_message 7.7' in your .dbxrc
Reading oracle
Reading ld-linux.so.2
……………………………
Reading libnque11.so
Attached to process 9546
[email protected] ([email protected]) stopped in (unknown) at 0xdcd410
0x00dcd410: popl %ebp
(dbx)

Enable Collection Process and continue trace - The collector command
collects performance data for analysis by the Performance Analyzer. It is
valid only in native mode.
(dbx) collector enable
Back to our DBX trace session:
(dbx) cont

Use the dbx thread command to look at Oracle 11g R2 memory process stack. Use the dbx thread command. Displays
the current thread.
(dbx) thread [email protected]
[email protected] ([email protected]) stopped in (unknown) at 0xdcd410
0x00dcd410: popl %ebp
(dbx) where
current thread: [email protected]
[1] 0xdcd410(0x2010, 0x108ba596, 0x7cb763, 0x105d4874, 0xf4351a0, 0x9), at 0xdcd410
[2] __read_nocancel(0x108b1108, 0x108b1770, 0x108ae8f8, 0x108ba596, 0xbfaa3d10, 0x0), at 0x7cb763
[3] ntpfprd(0x108ae790, 0x108ba596, 0x2010, 0xbfaa3e24, 0x8), at 0xf4350d9
[4] nsbasic_brc(0x108a5c48, 0x108a5d64, 0xbfaa3fa4, 0x0), at 0xf41b0b7
[5] nsbrecv(0x108a5c48, 0x108a5d64, 0xbfaa3fa4, 0x0), at 0xf41dba6
[6] nioqrc(0x106dad04, 0x0, 0xbfaa5f0c, 0x1, 0x0, 0x1), at 0xf422429
[7] __PGOSF25_opikndf2(0x106dad04, 0x2, 0xbfaa5f0c, 0x1, 0x0), at 0xf1c88e6
[8] opitsk(0x0, 0x0), at 0x8f3b9fb
[9] opiino(0x3c, 0x4, 0xbfaa6e14), at 0x8f3f89b
[10] opiodr(0x3c, 0x4, 0xbfaa6e14, 0x0), at 0xf1cb036
[11] opidrv(0x3c, 0x4, 0xbfaa6e14), at 0x8f382f3
[12] sou2o(0xbfaa6df8, 0x3c, 0x4, 0xbfaa6e14), at 0x92ed66c
[13] opimai_real(0x2, 0xbfaa6f48, 0x108721a8, 0x0, 0x0, 0x651f14), at 0x8542cdb
[14] ssthrdmain(0x2, 0xbfaa6f48), at 0x92f1f2d
[15] main(0x2, 0xbfaa7044, 0xbfaa7050, 0x640810, 0x0, 0x1), at 0x8542c57
(dbx)
(dbx) thread -info
Thread [email protected] (0x0) at priority 0
state: active on [email protected]
base function: 0x0: 0x00000000() stack: 0x0[0]
flags: (none)
masked signals: (none)
Currently active at 0x00dcd410
(dbx)

The debug function with dbx is useful for
Oracle 11g R2 (debug lists the program being
debugged) :
(dbx) debug –f
Debugging: oracle ( process id 24976)


Truss provides ability to walk through trace at
step by step level for Oracle 11g internal
process
Syntax:
truss [-fcaeil ] [-[tvx] [!]syscall..] [-s [!] signal..] [-m [!]fault..] [[rw] [!]fd..] [-o outfile] command | -p pid
Typical options for truss:
-o output to file
-f trace child processes
-c count system calls
-p trace calls based on Unix pid

Example
solaris02$truss –cp 3316
^C
Syscall
seconds
calls
Read
Write
Times
Yield
2
2
33
226
.000
.000
.000
.000
errors


Strace is similar to truss for Linux O/S tracing
with Oracle 11g
Powerful tool with command line syntax:
usage: strace [-dffhiqrtttTvVxx] [-a column] [-e expr] ... [-o file]
[-p pid] ... [-s strsize] [-u username] [-E var=val] ...
[command [arg ...]]
or: strace -c [-e expr] ... [-O overhead] [-S sortby] [-E var=val]
...
[command [arg ...]]

Options available for strace
[[email protected] ~]# strace
usage: strace [-dffhiqrtttTvVxx] [-a column] [-e expr] ... [-o file]
[-p pid] ... [-s strsize] [-u username] [-E var=val] ...
[command [arg ...]]
or: strace -c [-e expr] ... [-O overhead] [-S sortby] [-E var=val] ...
[command [arg ...]]
-c -- count time, calls, and errors for each syscall and report summary
-f -- follow forks, -ff -- with output into separate files
-F -- attempt to follow vforks, -h -- print help message
-i -- print instruction pointer at time of syscall
-q -- suppress messages about attaching, detaching, etc.
-r -- print relative timestamp, -t -- absolute timestamp, -tt -- with usecs
-T -- print time spent in each syscall, -V -- print version
-v -- verbose mode: print unabbreviated argv, stat, termio[s], etc. args
-x -- print non-ascii strings in hex, -xx -- print all strings in hex
-a column -- alignment COLUMN for printing syscall results (default 40)
-e expr -- a qualifying expression: option=[!]all or option=[!]val1[,val2]...
options: trace, abbrev, verbose, raw, signal, read, or write
-o file -- send trace output to FILE instead of stderr
-O overhead -- set overhead for tracing syscalls to OVERHEAD usecs
-p pid -- trace process with process id PID, may be repeated
-s strsize -- limit length of print strings to STRSIZE chars (default 32)
-S sortby -- sort syscall counts by: time, calls, name, nothing (default time)
-u username -- run command as username handling setuid and/or setgid
-E var=val -- put var=val in the environment for command
-E var -- remove var from the environment for command
[[email protected] ~]#

Perform system call trace for Oracle 11g:
[[email protected] ~]$ strace oracle
execve("/u01/app/oracle/product/11.2.0/db_1/bin/oracle", ["oracle"], [/* 36 vars */]) = 0
brk(0)
= 0x10792000
mmap2(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x8b9000
access("/etc/ld.so.preload", R_OK) = -1 ENOENT (No such file or directory)
open("/u01/app/oracle/product/11.2.0/db_1/lib/tls/i686/sse2/libodm11.so", O_RDONLY) = -1 ENOENT (No such file or directory)
stat64("/u01/app/oracle/product/11.2.0/db_1/lib/tls/i686/sse2", 0xbfa7b348) = -1 ENOENT (No such file or directory)
open("/u01/app/oracle/product/11.2.0/db_1/lib/tls/i686/libodm11.so", O_RDONLY) = -1 ENOENT (No such file or directory)
stat64("/u01/app/oracle/product/11.2.0/db_1/lib/tls/i686", 0xbfa7b348) = -1 ENOENT (No such file or directory)
open("/u01/app/oracle/product/11.2.0/db_1/lib/tls/sse2/libodm11.so", O_RDONLY) = -1 ENOENT (No such file or directory)
stat64("/u01/app/oracle/product/11.2.0/db_1/lib/tls/sse2", 0xbfa7b348) = -1 ENOENT (No such file or directory)
open("/u01/app/oracle/product/11.2.0/db_1/lib/tls/libodm11.so", O_RDONLY) = -1 ENOENT (No such file or directory)
stat64("/u01/app/oracle/product/11.2.0/db_1/lib/tls", 0xbfa7b348) = -1 ENOENT (No such file or directory)
open("/u01/app/oracle/product/11.2.0/db_1/lib/i686/sse2/libodm11.so", O_RDONLY) = -1 ENOENT (No such file or directory)
stat64("/u01/app/oracle/product/11.2.0/db_1/lib/i686/sse2", 0xbfa7b348) = -1 ENOENT (No such file or directory)
open("/u01/app/oracle/product/11.2.0/db_1/lib/i686/libodm11.so", O_RDONLY) = -1 ENOENT (No such file or directory)
stat64("/u01/app/oracle/product/11.2.0/db_1/lib/i686", 0xbfa7b348) = -1 ENOENT (No such file or directory)
open("/u01/app/oracle/product/11.2.0/db_1/lib/sse2/libodm11.so", O_RDONLY) = -1 ENOENT (No such file or directory)
stat64("/u01/app/oracle/product/11.2.0/db_1/lib/sse2", 0xbfa7b348) = -1 ENOENT (No such file or directory)
open("/u01/app/oracle/product/11.2.0/db_1/lib/libodm11.so", O_RDONLY) = 3
read(3, "\177ELF\1\1\1\0\0\0\0\0\0\0\0\0\3\0\3\0\1\0\0\0\254\6\0\0004\0\0\0"..., 512) = 512
fstat64(3, {st_mode=S_IFREG|0644, st_size=8857, ...}) = 0
mmap2(NULL, 6768, PROT_READ|PROT_EXEC, MAP_PRIVATE|MAP_DENYWRITE, 3, 0) = 0x40d000
mmap2(0x40e000, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_FIXED|MAP_DENYWRITE, 3, 0) = 0x40e000
close(3)
=0
open("/u01/app/oracle/product/11.2.0/db_1/lib/libcell11.so", O_RDONLY) = 3
read(3, "\177ELF\1\1\1\0\0\0\0\0\0\0\0\0\3\0\3\0\1\0\0\0\340|\0\0004\0\0\0"..., 512) = 512
fstat64(3, {st_mode=S_IFREG|0644, st_size=383547, ...}) = 0
Similar to truss:
Check for system call errors for Oracle 11g with strace

[[email protected] ~]$ strace -c oracle
% time seconds usecs/call calls errors syscall
------ ----------- ----------- --------- --------- ---------------40.53 0.000308
308
1
execve
15.79 0.000120
0
338
49 open
15.13 0.000115
7
16
16 mkdir
12.37 0.000094
1
75
read
8.29 0.000063
0
132
munmap
7.89 0.000060
0
293
close
0.00 0.000000
0
20
write
0.00 0.000000
0
1
chmod
0.00 0.000000
0
3
lseek
0.00 0.000000
0
3
3 access
0.00 0.000000
0
1
dup
0.00 0.000000
0
4
times
0.00 0.000000
0
4
brk
0.00 0.000000
0
2
setrlimit
0.00 0.000000
0
5
gettimeofday
0.00 0.000000
0
5
uname
0.00 0.000000
0
9
mprotect
0.00 0.000000
0
3
_llseek
0.00 0.000000
0
6
rt_sigaction
0.00 0.000000
0
5
rt_sigprocmask
0.00 0.000000
0
5
getrlimit
0.00 0.000000
0
307
mmap2
0.00 0.000000
0
48
19 stat64
0.00 0.000000
0
7
2 lstat64
0.00 0.000000
0
26
fstat64
0.00 0.000000
0
2
getuid32
0.00 0.000000
0
1
geteuid32
0.00 0.000000
0
6
fcntl64
0.00 0.000000
0
1
gettid
0.00 0.000000
0
2
futex
0.00 0.000000
0
1
set_thread_area
0.00 0.000000
0
1
set_tid_address
0.00 0.000000
0
1
set_robust_list
0.00 0.000000
0
3
socket
0.00 0.000000
0
1
bind
0.00 0.000000
0
2
2 connect
0.00 0.000000
0
5
shmat
0.00 0.000000
0
5
shmdt
0.00 0.000000
0
12
9 shmget
------ ----------- ----------- --------- --------- ---------------100.00 0.000760
1362
100 total
[[email protected] ~]$

Example : using strace to find system calls for Oracle DBWR process
oracle 6123 1 0 12:16 ?
00:00:01 ora_dbw0_RACDB_1
oracle 31050 8191 0 20:24 pts/2 00:00:00 grep dbw
[[email protected] admin]$ strace -c -p 6123
Process 6123 attached - interrupt to quit
Process 6123 detached
% time seconds usecs/call calls errors syscall
------ ----------- ----------- --------- --------- ---------------57.30 0.049795
732
68
io_submit
35.52 0.030866
123
250
pread64
3.14 0.002729
0 20064
getrusage
1.55 0.001350
11
123
io_getevents
1.15 0.000999
4
235
125 semtimedop
0.34 0.000297
2
151
semctl
0.32 0.000279
0 17913
gettimeofday
0.25 0.000219
4
58
mmap2
0.25 0.000214
4
50
sendmsg
0.10 0.000087
2
57
open
0.08 0.000068
1
57
read
0.00 0.000000
0
57
close
0.00 0.000000
0
766
times
------ ----------- ----------- --------- --------- ---------------100.00 0.086903
39849
125 total
[[email protected] admin]$




Useful tool available for Oracle 11g on
Windows called orakill
Oracle Metalink Note: 69882.1
Useful for killing runaway processes on
Oracle 11g on Windows as well as for zombie
and hung sessions
Syntax:
orakill <sid> ,<thread>
Can use QSLICE.exe to find threads ( available
on Windows NT/200X Resource kit from
Microsoft)

Query if not using QSLICE.EXE for details:
C:\Users\User>orakill
Usage: orakill sid thread
where sid = the Oracle instance to target
thread = the thread id of the thread to kill
The thread id should be retrieved from the spid column of a query such as:
select spid, osuser, s.program from
v$process p, v$session s where p.addr=s.paddr;
C:\Users\User>
QSLISE.EXE OUTPUT

Use Qslice to find
Oracle processes
Than use orakill to end
process that is hung
C:> orakill ora11g 985
Kill of thread id 985 in instance
ora11g successfully signaled.




ORA-0600: Internal errors
Call Oracle support first
Leverage Metalink ORA-0600 support matrix
Collect dumps with oradebug if possible




Internal Oracle errors generate kernel memory
dump.
Dump core file and trace files.
Useful Metalink Notes: 146580.1, 146581.1
Use the Oracle Metalink ORA-0600 lookup tool
for addressing these internal errors.
Note 153788.1 – Troubleshoot an ORA-0600 Error
using the ORA-600 Argument lookup tool
From Oracle support see Note 153788.1
Troubleshoot an ORA-600 Error using the ORA-600 argument lookup tool
Can also be used to review and analyze ORA-7445 errors




Work closely with Oracle Support
Usually caused by bug such as memory leak
Test recent changes between development, test,
QA and production to ensure continuity of
issue.
Ideal to have same release of Oracle to be able
to reproduce the error for all environments
and platforms.

Use ADR to review incident and associated
trace file
Dump continued from file:
/u01/app/oracle/diag/rdbms/ora11g/ORA11G/trace/ORA11G_ora
_12562.trc
ORA-00600: internal error code, arguments: [kssadpm: null parent],
[], [], [], [], [], [], []


Plug in the arguments from trace file to ORA0600 Metalink troubleshooting lookup tool.
The related solution will then be displayed:
Known Issues: Bug# 5605370 * See Note 5605370.8* This bug is
alerted in Note 454464.1Various dumps / instance crash possible
Fixed: 10.2.0.4, 11.1.0.6


KFOD for ASM monitoring
KFOD help=y
[[email protected] ~]$ kfod help=y
_asm_a/llow_only_raw_disks
KFOD allow only raw devices [_asm_allow_only_raw_disks=TRUE/(FALSE)]
_asm_l/ibraries
ASM Libraries[_asm_libraries=lib1,lib2,...]
_asms/id
ASM Instance[_asmsid=sid]
a/sm_diskstring
ASM Diskstring [asm_diskstring=discoverystring, discoverystring ...]
c/luster
KFOD cluster [cluster=TRUE/(FALSE)]
db/_unique_name
db_unique_name for ASM instance[db_unique_name=dbname]
di/sks
Disks to discover [disks=raw,asm,all]
ds/cvgroup
Include group name [dscvgroup=TRUE/(FALSE)]
g/roup
Disks in diskgroup [group=diskgroup]
h/ostlist
hostlist[hostlist=host1,host2,...]
metadata_a/usize
AU Size for Metadata Size Calculation
metadata_c/lients
Client Count for Metadata Size Calculation
metadata_d/isks
Disk Count for Metadata Size Calculation
metadata_n/odes
Node Count for Metadata Size Calculation
metadata_r/edundancy
Redundancy for Metadata Size Calculation
n/ohdr
KFOD header suppression [nohdr=TRUE/(FALSE)]
o/p
KFOD options type
[OP=DISKS/CANDIDATES/MISSING/GROUPS/INSTS/VERSION/CLIENTS/RM/RMVERS/DFLTDSTR
/GPNPDSTR/METADATA/ALL]
p/file
ASM parameter file [pfile=parameterfile]
s/tatus
Include disk header status [status=TRUE/(FALSE)]
v/erbose
KFOD verbose errors [verbose=TRUE/(FALSE)]
KFOD-01000: USAGE: kfod op=<op> asm_diskstring=... | pfile=...
[[email protected] ~]$ kfod disk=all
-------------------------------------------------------------------------------Disk
Size Path
User Group
================================================================================
1: 15358 Mb /dev/sdb1
oracle oinstall
2: 15358 Mb /dev/sdc1
oracle oinstall
3: 15358 Mb /dev/sdd1
oracle oinstall
4: 40954 Mb /dev/sde1
oracle oinstall
-------------------------------------------------------------------------------ORACLE_SID ORACLE_HOME
================================================================================
+ASM1 /u01/app/11.2.0/grid
[[email protected] ~]$ kfod op=groups
-------------------------------------------------------------------------------Group
Size
Free Redundancy Name
================================================================================
1: 40954 Mb 30440 Mb EXTERN FLASH
2: 46074 Mb 42080 Mb NORMAL DATA
[[email protected] ~]$
[[email protected] ~]$ kfod op=insts
-------------------------------------------------------------------------------ORACLE_SID ORACLE_HOME
================================================================================
+ASM1 /u01/app/11.2.0/grid
[[email protected] ~]$
[[email protected] ~]$ kfod op=version
-------------------------------------------------------------------------------ORACLE_SID RAC VERSION
================================================================================
+ASM1 YES 11.2.0.1.0
[[email protected] ~]$ kfod op=clients
-------------------------------------------------------------------------------ORACLE_SID VERSION
================================================================================
RACDB_1 11.2.0.1.0
+ASM1 11.2.0.1.0
+ASM1 11.2.0.1.0
RACDB_1 11.2.0.1.0
[[email protected] ~]$
[[email protected] ~]$ kfod op=rm
-------------------------------------------------------------------------------Rolling Migration State
================================================================================
Inactive
[[email protected] ~]$ kfod op=rmvers
-------------------------------------------------------------------------------Rolling Migration Compatible Versions
================================================================================
11.1.0.6.0
11.1.0.7.0
[[email protected] ~]$



Thank you for attending
Fill out your evaluation
Questions & Answers

similar documents