Re: session hanged while executing package

From: oracle dba <aoracledba_at_gmail.com>
Date: 15 Jan 2007 00:53:20 -0800
Message-ID: <1168851200.100078.165010@51g2000cwl.googlegroups.com>

oracle dba wrote:
> whenever I execute one package my session got hanged, even while
> compile also.
> through another session I can see :
>
> select * from v$session_longops;
>
> SID SERIAL# OPNAME
> TARGET
> TARGET_DESC SOFAR TOTALWORK UNITS
> START_TIM LAST_UPDA TIME_REMAINING ELAPSED_SECONDS
> CONTEXT MESSAGE
>
>
>
>
>
> USERNAME
> SQL_ADDR SQL_HASH_VALUE QCSID
> ----- ----------
> ----------------------------------------------------------------
> ----------------------------------------------------------------
> -------------------------------- ---------- ----------
> -------------------------------- --------- --------- --------------
> --------------- ----------
> --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> ------------------------------ -------- -------------- ----------
> 7 3 Crash Recovery
> Log
> Files 1 1 Files
> 15-JAN-07 15-JAN-07 0 9
> 1 Crash Recovery: Log Files : 1 out of 1 Files done
>
>
>
>
>
> SYS
> 2A7C55C8 1991530103 0
> 7 3 Crash Recovery
> Redo
> Blocks 204800 204800 Blocks
> 15-JAN-07 15-JAN-07 0 9
> 2 Crash Recovery: Redo Blocks : 204800 out of 204800 Blocks done
>
>
>
>
>
> SYS
> 2A7C55C8 1991530103 0
> 7 33 Table Scan
> OA.CUSTOMER_HEADERS
> 16843 16843 Blocks
> 15-JAN-07 15-JAN-07 0 18
> 0 Table Scan: OA.CUSTOMER_HEADERS: 16843 out of 16843 Blocks done
>
>
>
>
>
> SYS
> 2AD13A9C 3901518157 0
> 7 33 Table Scan
> OA.CUSTOMER_HEADERS
> 29705 29705 Blocks
> 15-JAN-07 15-JAN-07 0 39
> 0 Table Scan: OA.CUSTOMER_HEADERS: 29705 out of 29705 Blocks done
>
>
>
>
>
> SYS
> 2AD13A9C 3901518157 0
> 7 33 Table Scan
> OA.CUSTOMER_SITES
> 10344 10344 Blocks
> 15-JAN-07 15-JAN-07 0 9
> 0 Table Scan: OA.CUSTOMER_SITES: 10344 out of 10344 Blocks done
>
>
>
>
>
> SYS
> 2AD13A9C 3901518157 0
> 7 33 Table Scan
> OA.CUSTOMER_SITES
> 12053 12053 Blocks
> 15-JAN-07 15-JAN-07 0 18
> 0 Table Scan: OA.CUSTOMER_SITES: 12053 out of 12053 Blocks done
>
>
>
>
>
> SYS
> 2AD13A9C 3901518157 0
> 7 33 Table Scan
> OA.EP_EST_LABOR_COSTS
> 54479 54479 Blocks
> 15-JAN-07 15-JAN-07 0 51
> 0 Table Scan: OA.EP_EST_LABOR_COSTS: 54479 out of 54479 Blocks
> done
>
>
>
>
>
> SYS 2AD13A9C 3901518157 0
> 7 33 Table Scan
> OA.EP_EST_MAKEREADIES
> 24609 24609 Blocks
> 15-JAN-07 15-JAN-07 0 24
> 0 Table Scan: OA.EP_EST_MAKEREADIES: 24609 out of 24609 Blocks
> done
>
>
>
>
>
> SYS 2AD13A9C 3901518157 0
> 7 33 Table Scan
> OA.EP_EST_RUN_PARAMETERS
> 13225 13225 Blocks
> 15-JAN-07 15-JAN-07 0 15
> 0 Table Scan: OA.EP_EST_RUN_PARAMETERS: 13225 out of 13225
> Blocks done
>
>
>
>
>
> SYS 2AD13A9C 3901518157
> 0
> 7 33 Table Scan
> OA.ERP_OAI_SMD_ORDER_LINES
> 11173 11173 Blocks
> 15-JAN-07 15-JAN-07 0 12
> 0 Table Scan: OA.ERP_OAI_SMD_ORDER_LINES: 11173 out of 11173
> Blocks done
>
>
>
>
>
> SYS 2AD13A9C 3901518157 0
> 7 33 Table Scan
> OA.JGORALOG
> 12512 12512 Blocks
> 15-JAN-07 15-JAN-07 0 12
> 0 Table Scan: OA.JGORALOG: 12512 out of 12512 Blocks done
>
>
>
>
>
> SYS
> 2AD13A9C 3901518157 0
>
> SID SERIAL# OPNAME
> TARGET
> TARGET_DESC SOFAR TOTALWORK UNITS
> START_TIM LAST_UPDA TIME_REMAINING ELAPSED_SECONDS
> CONTEXT MESSAGE
>
>
>
>
>
> USERNAME
> SQL_ADDR SQL_HASH_VALUE QCSID
> ----- ----------
> ----------------------------------------------------------------
> ----------------------------------------------------------------
> -------------------------------- ---------- ----------
> -------------------------------- --------- --------- --------------
> --------------- ----------
> --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> ------------------------------ -------- -------------- ----------
> 7 33 Table Scan
> OA.OA_FREIGHT_RATES_LARGE
> 10517 10517 Blocks
> 15-JAN-07 15-JAN-07 0 12
> 0 Table Scan: OA.OA_FREIGHT_RATES_LARGE: 10517 out of 10517
> Blocks done
>
>
>
>
>
> SYS 2AD13A9C 3901518157 0
> 7 33 Table Scan
> OA.OA_USER_LOCATIONS
> 31019 31019 Blocks
> 15-JAN-07 15-JAN-07 0 30
> 0 Table Scan: OA.OA_USER_LOCATIONS: 31019 out of 31019 Blocks done
>
>
>
>
>
> SYS
> 2AD13A9C 3901518157 0
> 7 33 Table Scan
> OA.ORDER_ITEMS
> 19525 19525 Blocks
> 15-JAN-07 15-JAN-07 0 24
> 0 Table Scan: OA.ORDER_ITEMS: 19525 out of 19525 Blocks done
>
>
>
>
>
> SYS
> 2AD13A9C 3901518157 0
> 7 33 Table Scan
> OA.ORDER_ITEMS
> 12792 12792 Blocks
> 15-JAN-07 15-JAN-07 0 12
> 0 Table Scan: OA.ORDER_ITEMS: 12792 out of 12792 Blocks done
>
>
>
>
>
> SYS
> 2AD13A9C 3901518157 0
> 7 33 Table Scan
> OA.ORDER_ITEMS
> 12992 12992 Blocks
> 15-JAN-07 15-JAN-07 0 18
> 0 Table Scan: OA.ORDER_ITEMS: 12992 out of 12992 Blocks done
>
>
>
>
>
> SYS
> 2AD13A9C 3901518157 0
> 7 33 Table Scan
> OA.ORDER_ITEMS
> 13546 13546 Blocks
> 15-JAN-07 15-JAN-07 0 15
> 0 Table Scan: OA.ORDER_ITEMS: 13546 out of 13546 Blocks done
>
>
>
>
>
> SYS
> 2AD13A9C 3901518157 0
> 7 33 Table Scan
> OA.ORDER_ITEMS
> 13981 13981 Blocks
> 15-JAN-07 15-JAN-07 0 15
> 0 Table Scan: OA.ORDER_ITEMS: 13981 out of 13981 Blocks done
>
>
>
>
>
> SYS
> 2AD13A9C 3901518157 0
> 7 33 Table Scan
> OA.ORDER_ITEM_CUSTOMERS
> 31139 31139 Blocks
> 15-JAN-07 15-JAN-07 0 39
> 0 Table Scan: OA.ORDER_ITEM_CUSTOMERS: 31139 out of 31139
> Blocks done
>
>
>
>
>
> SYS 2AD13A9C 3901518157
> 0
> 7 33 Table Scan
> OA.ORDER_ITEM_CUSTOMERS
> 20340 20340 Blocks
> 15-JAN-07 15-JAN-07 0 24
> 0 Table Scan: OA.ORDER_ITEM_CUSTOMERS: 20340 out of 20340
> Blocks done
>
>
>
>
>
> SYS 2AD13A9C 3901518157
> 0
> 7 33 Table Scan
> OA.ORDER_ITEM_CUSTOMERS
> 19850 19850 Blocks
> 15-JAN-07 15-JAN-07 0 27
> 0 Table Scan: OA.ORDER_ITEM_CUSTOMERS: 19850 out of 19850
> Blocks done
>
>
>
>
>
> SYS 2AD13A9C 3901518157
> 0
> 7 33 Table Scan
> OA.ORDER_ITEM_CUSTOMERS
> 20614 20614 Blocks
> 15-JAN-07 15-JAN-07 0 24
> 0 Table Scan: OA.ORDER_ITEM_CUSTOMERS: 20614 out of 20614
> Blocks done
>
>
>
>
>
> SYS 2AD13A9C 3901518157
> 0
>
> SID SERIAL# OPNAME
> TARGET
> TARGET_DESC SOFAR TOTALWORK UNITS
> START_TIM LAST_UPDA TIME_REMAINING ELAPSED_SECONDS
> CONTEXT MESSAGE
>
>
>
>
>
> USERNAME
> SQL_ADDR SQL_HASH_VALUE QCSID
> ----- ----------
> ----------------------------------------------------------------
> ----------------------------------------------------------------
> -------------------------------- ---------- ----------
> -------------------------------- --------- --------- --------------
> --------------- ----------
> --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> ------------------------------ -------- -------------- ----------
> 7 33 Table Scan
> OA.ORDER_ITEM_CUSTOMERS
> 20719 20719 Blocks
> 15-JAN-07 15-JAN-07 0 24
> 0 Table Scan: OA.ORDER_ITEM_CUSTOMERS: 20719 out of 20719
> Blocks done
>
>
>
>
>
> SYS 2AD13A9C 3901518157
> 0
> 7 33 Table Scan
> OA.SYSTEM_MESSAGES
> 25674 25674 Blocks
> 15-JAN-07 15-JAN-07 0 24
> 0 Table Scan: OA.SYSTEM_MESSAGES: 25674 out of 25674 Blocks done
>
>
>
>
>
> SYS
> 2AD13A9C 3901518157 0
> 7 33 Table Scan
> OA.SYSTEM_MESSAGES
> 11413 11413 Blocks
> 15-JAN-07 15-JAN-07 0 9
> 0 Table Scan: OA.SYSTEM_MESSAGES: 11413 out of 11413 Blocks done
>
>
>
>
>
> SYS
> 2AD13A9C 3901518157 0
> 13 3760 Index Fast Full Scan
> OA.CUSTOMER_SITES
> 4721 4721 Blocks
> 15-JAN-07 15-JAN-07 0 9
> 0 Index Fast Full Scan: OA.CUSTOMER_SITES: 4721 out of 4721 Blocks
> done
>
>
>
>
>
> SYS 2D8A77B0 1620018665 0
>
> ws selected.
>
> select * from v$session_wait;
>
> SID SEQ# EVENT
> P1TEXT
> P1 P1RAW P2TEXT
> P2 P2RAW P3TEXT
> P3 P3RAW WAIT_TIME SECONDS_IN_WAIT STATE
> ----- ----------
> ----------------------------------------------------------------
> ----------------------------------------------------------------
> ---------- --------
> ----------------------------------------------------------------
> ---------- --------
> ----------------------------------------------------------------
> ---------- -------- ---------- --------------- -------------------
> 1 3741 pmon timer
> duration
> 300 0000012C
> 0 00
> 0 00 0 11011 WAITING
> 2 4677 rdbms ipc message
> timeout
> 300 0000012C
> 0 00
> 0 00 0 132 WAITING
> 3 4690 rdbms ipc message
> timeout
> 300 0000012C
> 0 00
> 0 00 0 129 WAITING
> 6 15 rdbms ipc message
> timeout
> 180000 0002BF20
> 0 00
> 0 00 0 147 WAITING
> 4 8815 rdbms ipc message
> timeout
> 300 0000012C
> 0 00
> 0 00 0 0 WAITING
> 10 46750 db file sequential read
> file#
> 21 00000015 block#
> 20287 00004F3F blocks
> 1 00000001 0 0 WAITING
> 13 11321 db file sequential read
> file#
> 25 00000019 block#
> 19630 00004CAE blocks
> 1 00000001 0 0 WAITING
> 5 569 smon timer
> sleep time
> 300 0000012C failed
> 0 00
> 0 00 0 168 WAITING
> 14 85 SQL*Net message to client
> driver id
> 1413697536 54435000 #bytes
> 1 00000001
> 0 00 -1 0 WAITED
> KNOWN TIME
> 8 6028 SQL*Net message from client
> driver id
> 1413697536 54435000 #bytes
> 1 00000001
> 0 00 0 771
> WAITING
> 11 80 SQL*Net message from client
> driver id
> 675365956 28414444 #bytes
> 1 00000001
> 0 00 0 2566 WAITING
>
> SID SEQ# EVENT
> P1TEXT
> P1 P1RAW P2TEXT
> P2 P2RAW P3TEXT
> P3 P3RAW WAIT_TIME SECONDS_IN_WAIT STATE
> ----- ----------
> ----------------------------------------------------------------
> ----------------------------------------------------------------
> ---------- --------
> ----------------------------------------------------------------
> ---------- --------
> ----------------------------------------------------------------
> ---------- -------- ---------- --------------- -------------------
> 12 2528 SQL*Net message from client
> driver id
> 675365956 28414444 #bytes
> 1 00000001
> 0 00 0 2486 WAITING
> 9 3776 SQL*Net message from client
> driver id
> 675365956 28414444 #bytes
> 1 00000001
> 0 00 0 2671 WAITING
>
>
> Database is 9.2.0.8 on Windows XP and session where I am executing that
> package is 13. Is there any another way to come to know what is going
> on that session in package.

Now I try to find it from v$sess_io, I saw physical reads are increasing very fast, what should I do. Should I increase buffer cache.

select

   sid,
   username,
   round(100 * total_user_io/total_io,2) tot_io_pc from
(select

     b.sid sid,
     nvl(b.username,p.name) username,
     sum(value) total_user_io
 from
     sys.v_$statname c,
     sys.v_$sesstat a,
     sys.v_$session b,
     sys.v_$bgprocess p
 where
      a.statistic#=c.statistic# and
      p.paddr (+) = b.paddr and
      b.sid=a.sid and b.sid=13 and
      c.name in ('physical reads',
                 'physical writes',
                 'physical writes direct',
                 'physical reads direct',
                 'physical writes direct (lob)',
                 'physical reads direct (lob)')
group by
      b.sid, nvl(b.username,p.name)),
(select
      sum(value) total_io
 from
      sys.v_$statname c,
      sys.v_$sesstat a
 where
      a.statistic#=c.statistic# and
      c.name in ('physical reads',
                 'physical writes',
                 'physical writes direct',
                 'physical reads direct',
                 'physical writes direct (lob)',
                 'physical reads direct (lob)'))
order by
      3 desc;

  SID USERNAME                        TOT_IO_PC
----- ------------------------------ ----------
   13 SYS                                  86.6

  SID USERNAME                        TOT_IO_PC
----- ------------------------------ ----------
   13 SYS                                 86.61

  SID USERNAME                        TOT_IO_PC
----- ------------------------------ ----------
   13 SYS                                 86.62

Received on Mon Jan 15 2007 - 02:53:20 CST