Home
E-mail Us
Oracle Articles
New Oracle Articles

Oracle Training
Oracle Tips
Oracle Forum
Class Catalog

Remote DBA
Oracle Tuning
Emergency 911
RAC Support
Apps Support
Analysis
Design
Implementation
Oracle Support

SQL Tuning
Security
Oracle UNIX
Oracle Linux
Monitoring
Remote support
Remote plans
Remote services
Application Server
Applications
Oracle Forms
Oracle Portal
App Upgrades
SQL Server
Oracle Concepts
Software Support
Remote Support
Development
Implementation

Consulting Staff
Consulting Prices
Help Wanted!

Oracle Posters
Oracle Books
Oracle Scripts
Ion
Excel-DB

Don Burleson Blog

Oracle Tracing of SQLNet round trips

Oracle Database Tips by Donald Burleson*

Burleson Consulting specializes in Oracle diagnostic services and you can call for immediate testing of your Oracle server.

This sample database was fetching 15 rows per fetch (the base SQL setpoint as pointed to in the trace file analysis) the following is a top 50 list of SQL hash addresses with high numbers of SQL*Net round trips. After the top 50 list I have provided example SQLs of the top 10 from the list.

These were harvested from the V$SQLAREA view using the SELECT:

select * from

(select

b.username,

executions,

fetches/executions, hash_value,

round((rows_processed/executions)/15)

roundtrips

from
v$sqlarea a,
dba_users b

where
b.user_Id=a.parsing_user_id
and

executions>0

and

(rows_processed/executions)>15

order by
rows_processed/executions desc)

where rownum < 51;

Once we have the HASH_VALUE determining the SQL becomes trivial:

select sql_text from v$sqltext where hash_value=&hash

order by piece;

Given the latency issues with network roundtrips these SQL statements should be investigated as to why they require so many roundtrips, are they restrictive enough? Perhaps there are issues in the way nested tables are being resolved.

USERNAME EXECUTIONS FETCHES/EXECUTIONS HASH_VALUE ROUNDTRIPS

-------------------- ---------- ------------------ ---------- ----------

OPS$ORACLE 3 1067 1767482789 10662

OPS$ORACLE 3 1067 512615030 10662

OPS$ORACLE 3 29121 1274659074 9707

OPS$ORACLE 3 29027 2058271585 9675

OPS$ORACLE 3 1348 421777705 5839

OPS$ORACLE 3 1252 1171039266 5839

OPS$ORACLE 3 1340 3720343595 5807

OPS$ORACLE 3 1340 3796874989 5807

OPS$ORACLE 3 1040 4111808100 3465

50 rows selected.

SQL> @get_it

Enter value for hash: 1767482789

SQL_TEXT

----------------------------------------------------------------

SELECT /*+NESTED_TABLE_GET_REFS+*/ "PRTS"."UPC_FNA".* FROM "PRTS

"."UPC_FNA"

SQL> @get_it

Enter value for hash: 512615030

SQL_TEXT

----------------------------------------------------------------

SELECT /*+NESTED_TABLE_GET_REFS+*/ "PRTSMV"."UPC_FNA".* FROM "PR

TSMV"."UPC_FNA"

SQL> @get_it

Enter value for hash: 1274659074

SQL_TEXT

----------------------------------------------------------------

SELECT /*+NESTED_TABLE_GET_REFS+*/ "PRTS"."ISSUE_USER_ROLE".* FR

OM "PRTS"."ISSUE_USER_ROLE"

SQL> @get_it

Enter value for hash: 2058271585

SQL_TEXT

----------------------------------------------------------------

SELECT /*+NESTED_TABLE_GET_REFS+*/ "PRTSMV"."ISSUE_USER_ROLE".*

FROM "PRTSMV"."ISSUE_USER_ROLE"

SQL> @get_it

Enter value for hash: 1171039266

SQL_TEXT

----------------------------------------------------------------

SELECT /*+NESTED_TABLE_GET_REFS+*/ "PRTS"."ISSUE_STEP_DTL_WRKFLW

".* FROM "PRTS"."ISSUE_STEP_DTL_WRKFLW"

SQL> @get_it

Enter value for hash: 3720343595

SQL_TEXT

----------------------------------------------------------------

SELECT /*+NESTED_TABLE_GET_REFS+*/ "PRTSMV"."TBL_ISSUE_STEP_DTL"

.* FROM "PRTSMV"."TBL_ISSUE_STEP_DTL"

SQL> @get_it

Enter value for hash: 3796874989

SQL_TEXT

----------------------------------------------------------------

SELECT /*+NESTED_TABLE_GET_REFS+*/ "PRTSMV"."ISSUE_STEP_DTL".* F

ROM "PRTSMV"."ISSUE_STEP_DTL"

SQL> @get_it

Enter value for hash: 4111808100

SQL_TEXT

----------------------------------------------------------------

SELECT /*+NESTED_TABLE_GET_REFS+*/ "PRTS"."LOOK_UP_CD".* FROM "P

RTS"."LOOK_UP_CD"

SQL> @get_it

Enter value for hash: 914551106

SQL_TEXT

----------------------------------------------------------------

SELECT /*+NESTED_TABLE_GET_REFS+*/ "PRTSMV"."LOOK_UP_CD".* FROM

"PRTSMV"."LOOK_UP_CD"

SQL> @get_it

Enter value for hash: 2999314088

SQL_TEXT

----------------------------------------------------------------

SELECT /*+NESTED_TABLE_GET_REFS+*/ "PRTS"."LOOK_UP_CD_1025".* FR

OM "PRTS"."LOOK_UP_CD_1025"

��

Oracle Tracing of SQL*Net round trips Oracle Database Tips by Donald Burleson

Oracle Tracing of SQLNet round trips

Oracle Database Tips by Donald Burleson*