1 2 Previous Next 15 Replies Latest reply on Feb 18, 2009 12:34 PM by Charles Hooper Go to original post
      • 15. Re: enq TX - row lock contention
        Charles Hooper
        I do not think that this is the correct trace file. The SQL*Net message from client waits appear at the very end where you are issuing ALTER SYSTEM, and again when the session disconnects.
        ----------------------------------------------------------------------------------
        Cursor 1   Ver 1   Parse at 0.000000
        ALTER SESSION SET NLS_LANGUAGE= 'AMERICAN' NLS_TERRITORY= 'AMERICA' NLS_CURRENCY= '$' NLS_ISO_CURRENCY= 'AMERICA' NLS_NUMERIC_CHARACTERS= '.,' NLS_CALENDAR= 'GREGORIAN' NLS_DATE_FORMAT= 'DD-MON-RR' NLS_DATE_LANGUAGE= 'AMERICAN' NLS_SORT= 'BINARY' TIME_ZONE= '+03:30' NLS_COMP= 'BINARY' NLS_DUAL_CURRENCY= '$' NLS_TIME_FORMAT= 'HH.MI.SSXFF AM' NLS_TIMESTAMP_FORMAT= 'DD-MON-RR HH.MI.SSXFF AM' NLS_TIME_TZ_FORMAT= 'HH.MI.SSXFF AM TZR' NLS_TIMESTAMP_TZ_FORMAT= 'DD-MON-RR HH.MI.SSXFF AM TZR'
        
        Cursor 1   Ver 1   Parse at -0.000008 (Parse to Parse -0.000008),CPU Time 0.001000,Elapsed Time 0.001138,Rows Affected 0,Blks from Buff 0,Blks from Disk 0,**Shared Pool Misses** 1,Goal=CHOOSE
        Cursor 1   Ver 1 Execute at 0.000549 (Parse to Exec  0.000549),CPU Time 0.001000,Elapsed Time 0.000203,Rows Affected 0,Blks from Buff 0,Blks from Disk 0,
        Cursor 0   Ver 0            0.000935  0.000007   SQL*Net message to client
        Cursor 0   Ver 0            0.001285  0.000292   SQL*Net message from client
        Cursor 0   Ver 0            0.001338  0.000001   SQL*Net message to client
        Cursor 0   Ver 0            0.001571  0.000211   SQL*Net message from client
        Cursor 1   Ver 1            0.003027  0.000258   library cache pin
        Cursor 0   Ver 0            0.003907  0.000104   library cache pin
        Cursor 0   Ver 0            0.004367  0.000097   library cache pin
        ----------------------------------------------------------------------------------
        Cursor 3   Ver 1   Parse at 0.005159  (0.005159)
        select inst_id,ksmsdnam,ksmsdval from x$ksmsd
        
        Cursor 3   Ver 1   Parse at 0.005153 (Parse to Parse -0.000006),CPU Time 0.000000,Elapsed Time 0.001125,Rows Affected 0,Blks from Buff 0,Blks from Disk 0,**Shared Pool Misses** 1,Goal=CHOOSE
        ----------------------------------------------------------------------------------
        Cursor 2   Ver 1   Parse at 0.006089  (0.000930)
        select NAME , VALUE from GV$SGA where inst_id = USERENV('Instance')
        
        Cursor 2   Ver 1   Parse at 0.006085 (Parse to Parse -0.000004),CPU Time 0.002000,Elapsed Time 0.002781,Rows Affected 0,Blks from Buff 0,Blks from Disk 0,**Shared Pool Misses** 1,Goal=CHOOSE
        ----------------------------------------------------------------------------------
        Cursor 1   Ver 2   Parse at 0.007335  (0.001246)
        SELECT DECODE(null,'','Total System Global Area','') NAME_COL_PLUS_SHOW_SGA, SUM(VALUE), DECODE (null,'', 'bytes','') units_col_plus_show_sga FROM V$SGA UNION ALL SELECT NAME NAME_COL_PLUS_SHOW_SGA , VALUE, DECODE (null,'', 'bytes','') units_col_plus_show_sga FROM V$SGA
        
        Cursor 1   Ver 2   Parse at 0.007329 (Parse to Parse -0.000006),CPU Time 0.004999,Elapsed Time 0.005715,Rows Affected 0,Blks from Buff 0,Blks from Disk 0,**Shared Pool Misses** 1,Goal=ALL_ROWS
        Cursor 1   Ver 2 Execute at 0.007528 (Parse to Exec  0.000193),CPU Time 0.000000,Elapsed Time 0.000121,Rows Affected 0,Blks from Buff 0,Blks from Disk 0,
        Cursor 1   Ver 2            0.007581  0.000004   SQL*Net message to client
        Cursor 1   Ver 2   Fetch at 0.007731 (Parse to Fetch 0.000396),CPU Time 0.000000,Elapsed Time 0.000101,Rows Retrievd 1,Blks from Buff 0,Blks from Disk 0
        Cursor 1   Ver 2            0.008077  0.000270   SQL*Net message from client
        Cursor 1   Ver 2            0.008141  0.000001   SQL*Net message to client
        Cursor 1   Ver 2   Fetch at 0.008171 (Parse to Fetch 0.000836),CPU Time 0.000000,Elapsed Time 0.000058,Rows Retrievd 4,Blks from Buff 0,Blks from Disk 0
        Cursor 1   Ver 2            0.008532  0.000283   SQL*Net message from client
        Cursor 0   Ver 0            0.008699  0.000002   SQL*Net message to client
        Cursor 0   Ver 0            1.669841  1.661116   SQL*Net message from client
        ----------------------------------------------------------------------------------
        Cursor 3   Ver 2   Parse at 1.670453  (1.663118)
        alter system set event='' scope=spfile
        
        Cursor 3   Ver 2   Parse at 1.670448 (Parse to Parse -0.000005),CPU Time 0.001000,Elapsed Time 0.000499,Rows Affected 0,Blks from Buff 0,Blks from Disk 0,**Shared Pool Misses** 1,Goal=ALL_ROWS
        Cursor 3   Ver 2 Execute at 1.758337 (Parse to Exec  0.087884),CPU Time 0.018997,Elapsed Time 0.087823,Rows Affected 0,Blks from Buff 0,Blks from Disk 0,
        Cursor 3   Ver 2            1.758518  0.000004   SQL*Net message to client
        Cursor 3   Ver 2            1.758786  0.000233   SQL*Net message from client
        Cursor 0   Ver 0            1.758890  0.000001   SQL*Net message to client
        Cursor 0   Ver 0            6.934526  5.175611   SQL*Net message from client
        Charles Hooper
        IT Manager/Oracle DBA
        K&M Machine-Fabricating, Inc.
        1 2 Previous Next