Skip to content
pvmehta.com

pvmehta.com

  • Home
  • About Me
  • Toggle search form
  • Oracle Connections expire_time and firewall Oracle
  • Oracle GoldenGate lag monitoring shell script Linux/Unix
  • fdisk -l explaination about Primary-Logical-Extended Partitions Linux/Unix
  • crtgr.sql /* For creating trigger from data dictionary */ Oracle
  • Space padding in korn shell Linux/Unix
  • scripts to take listener.log backup Linux/Unix
  • When to rebuld B-tree index Oracle
  • 284785.1 How to check RAC Option is currently linked into the Oracle Binary Oracle
  • Find_table_size.sql Oracle
  • Running select from V$ views from remote server Linux/Unix
  • Building Our Own Namespaces with “Create Context” Oracle
  • cache buffer chain latch Oracle
  • Benefits and Usage of RMAN with Standby Databases Oracle
  • import-export with multiple files Oracle
  • grep multuple patterns Linux/Unix

Wait time tuning research

Posted on 02-Aug-2005 By Admin No Comments on Wait time tuning research

http://www.dba-oracle.com/art_otn_waits.htm

v$session.paddr = v$process.addr

v$session.sid = v$session_wait.sid

v$session.sql_address = v$sqlarea.address

v$session.sql_hash_value = v$sqlarea.hash_value

v$session.sql_id = v$sqlares.sql_id

v$session.server shows Dedicated or MTS configuration.

The fields module and action of v$session can be set with dbms_application_info.set_module.

The field client_info can be set with dbms_application_info.set_client_info

Join sid with v$sesstat if you want to get some statistical information for a particular sesssion.

A record in v$session contains sid and serial#. These numbers can be used kill a session (alter system kill session).

What a session is waiting for can be queried with v$session_wait. However, with Oracle 10g, this is not nessessary anymore,

as v$session_wait’s information will be exposed within v$session as well.

P1 refers sometimes to the datafile number. If this number is greater than db_files, it refers to a temp file.

********************************

V$SESSION_WAIT – Unlike the previous view, this view does not contain aggregated statistics; it contains statistics on the event the session

is waiting on at the moment. The P(n) columns are an important piece of information used to help determine where the problem exists.

———————–

SID – Session ID.

SEQ# – Sequence number that uniquely identifies the wait event. This sequence is incremented with each wait event completion.

EVENT – Name of wait event.

P1TEXT – A description of what the P1 value is used for. Not reliable, use PARAMETER(n) value from V$EVENT_NAME to determine.

P1 – This value definition is dependent on the event. Use PARAMETER1 value from V$EVENT_NAME to describe what this value is used for.

P1RAW – Same value as P1 except in hexadecimal format.

P2TEXT – A description of what the P2 value is used for. Not reliable, use PARAMETER(n) value from V$EVENT_NAME to determine.

P2 – This value definition is dependent on the event. Use PARAMETER2 value from V$EVENT_NAME to describe what this value is used for.

P2RAW – Same value as P2 except in hexadecimal format.

P3TEXT – A description of what the P3 value is used for. Not reliable, use PARAMETER(n) value from V$EVENT_NAME to determine.

P3 – This value definition is dependent on the event. Use PARAMETER3 value from V$EVENT_NAME to describe what this value is used for.

P3RAW – Same value as P3 except in hexadecimal format.

WAIT_CLASS_ID – ID of the wait class. Column is in 10g.

WAIT_CLASS# – Number of the class. Column is in 10g.

WAIT_CLASS – The name of the wait class (Idle, Network, System I/O, etc.). Column is in 10g.

WAIT_TIME – The last amount of time that the session waited for an event. A 0 value means the session is currently waiting on the event.

This value of 0 will not increase until the session completes the wait event. Time is in centiseconds.

SECONDS_IN_WAIT – If WAIT_TIME equals 0, then this is the number of seconds spent waiting on the event so far. This can give you complicated

results when a timeout occurs like with enqueues because it will reset to 0, but this is beyond the scope of this article.

STATE – State of the wait event: WAIT_TIME=0 – WAITING, the process is in the state of waiting on an event. WAIT_TIME=-1 – WAITED SHORT TIME, wait time

completed in less than a centisecond. WAIT_TIME=-2 – WAITED UNKOWN TIME, duration of wait is unknown because TIME_STATISTICS was set to false.

WAIT_TIME>0 – WAITED KNOWN TIME, wait completed. WAIT_TIME is the duration of the wait event in centiseconds.

Oracle, SQL scripts

Post navigation

Previous Post: crtgr.sql /* For creating trigger from data dictionary */
Next Post: sesswait.sql

Related Posts

  • In Addition to previous note, following grants needed on PERFSTAT user. Oracle
  • ORA-3136 Oracle
  • db_status.sql Oracle
  • Oracle Connections expire_time and firewall Oracle
  • ORA-01220 Oracle
  • 751131.1 New Article Error 2819 While Requesting a Systemstate Dump Oracle

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Categories

  • Ansible (0)
  • AWS (2)
  • Azure (1)
  • Linux/Unix (149)
  • MYSQL (5)
  • Oracle (393)
  • PHP/MYSQL/Wordpress (10)
  • POSTGRESQL (0)
  • Power-BI (0)
  • Python/PySpark (7)
  • RAC (17)
  • rman-dataguard (26)
  • shell (149)
  • SQL scripts (342)
  • SQL Server (6)
  • Uncategorized (0)
  • Videos (0)

Recent Posts

  • Trace a SQL session from another session using ORADEBUG30-Sep-2025
  • SQL Server Vs Oracle Architecture difference25-Jul-2025
  • SQL Server: How to see historical transactions25-Jul-2025
  • SQL Server: How to see current transactions or requests25-Jul-2025
  • T-SQL Vs PL/SQL Syntax25-Jul-2025
  • Check SQL Server edition25-Jul-2025
  • Checking SQL Server Version25-Jul-2025
  • Oracle vs MYSQL Architecture differences (For DBAs)24-Jul-2025
  • V$INSTANCE of Oracle in MYSQL24-Jul-2025
  • Day to day MYSQL DBA operations (Compared with Oracle DBA)24-Jul-2025

Archives

  • 2025
  • 2024
  • 2023
  • 2010
  • 2009
  • 2008
  • 2007
  • 2006
  • 2005
  • SQL Server: How to see historical transactions SQL Server
  • switchlogfile.sh Linux/Unix
  • Pending Distributed Transations Oracle
  • find checksum of a file. Linux/Unix
  • How to find password change date for user Oracle
  • run this before doing any dbchange pvm_pre_change.sql Oracle
  • Jai Shree Ram Oracle
  • plan10g.sql Oracle

Copyright © 2025 pvmehta.com.

Powered by PressBook News WordPress theme