ASH Report – sql_id

SELECT
  trunc(begin_interval_time, 'WW'),
  ROUND(sum(elapsed_time_delta)/sum(executions_delta)/1000)
FROM dba_hist_sqlstat a
JOIN dba_hist_snapshot b
ON a.snap_id = b.snap_id
WHERE sql_id        = '&sql_id'
AND executions_delta>0
GROUP BY trunc(begin_interval_time, 'WW')
ORDER by trunc(begin_interval_time, 'WW') DESC

http://portrix-systems.de/blog/brost/tracking-historic-sql-execution-time-with-ash/

Oracle Top 5 Event – SQL*Net message to client

SQL*Net message to client
Time it takes to pack a message to be sent to the client
Doesn’t include network timing

SQL*Net message to client - time to pack a message (no network time included) possibly tune SDU

http://datavirtualizer.com/oracle-sqlnet-wait-events/

http://blog.tanelpoder.com/2008/02/07/sqlnet-message-to-client-wait-gotcha/

http://blog.tanelpoder.com/2008/02/10/sqlnet-message-to-client-vs-sqlnet-more-data-to-client/

Database Net Services Administrator’s Guide

http://docs.oracle.com/cd/E11882_01/network.112/e41945/intro.htm#i453017

AWR Top 5 Timed Foreground Events

set pagesize 10000
set linesize 10000
select case wait_rank when 1 then inst_id end "Inst Num",
 case wait_rank when 1 then snap_id end "Snap Id",
 case wait_rank when 1 then begin_snap end "Begin Snap",
 case wait_rank when 1 then end_snap end "End Snap",
 event_name "Event",
 total_waits "Waits",
 time_waited "Time(s)",
 round((time_waited/total_waits)*1000) "Avg wait(ms)",
 round((time_waited/db_time)*100, 2) "% DB time",
 substr(wait_class, 1, 15) "Wait Class"
from (
select
  inst_id,
  snap_id, to_char(begin_snap, 'DD-MM-YY hh24:mi:ss') begin_snap,
  to_char(end_snap, 'hh24:mi:ss') end_snap,
  event_name,
  wait_class,
  total_waits,
  time_waited,
  dense_rank() over (partition by inst_id, snap_id order by time_waited desc)-1 wait_rank,
  max(time_waited) over (partition by inst_id, snap_id) db_time
from (
select
  s.instance_number inst_id,
  s.snap_id,
  s.begin_interval_time begin_snap,
  s.end_interval_time end_snap,
  event_name,
  wait_class,
  total_waits-lag(total_waits, 1, total_waits) over
   (partition by s.startup_time, s.instance_number, stats.event_name order by s.snap_id) total_waits,
  time_waited-lag(time_waited, 1, time_waited) over
   (partition by s.startup_time, s.instance_number, stats.event_name order by s.snap_id) time_waited,
  min(s.snap_id) over (partition by s.startup_time, s.instance_number, stats.event_name) min_snap_id
from (
 select dbid, instance_number, snap_id, event_name, wait_class, total_waits_fg total_waits, round(time_waited_micro_fg/1000000, 2) time_waited
  from dba_hist_system_event
  where wait_class not in ('Idle', 'System I/O')
 union all
 select dbid, instance_number, snap_id, stat_name event_name, null wait_class, null total_waits, round(value/1000000, 2) time_waited
  from dba_hist_sys_time_model
  where stat_name in ('DB CPU', 'DB time')
) stats, dba_hist_snapshot s
 where stats.instance_number=s.instance_number
  and stats.snap_id=s.snap_id
  and stats.dbid=s.dbid
  and s.instance_number=&instance_number
  and stats.snap_id between &start_snap and &end_snap
) where snap_id > min_snap_id and nvl(total_waits,1) > 0
) where event_name!='DB time' and wait_rank <= 5
order by inst_id, snap_id;

Real-Time SQL Monitoring

Onwards Oracle Database 11g

http://www.oracle.com/technetwork/database/manageability/sqlmonitor-084401.html

Read bind variable values of currently executing SQL

If you are running on Oracle 11.2+ and have the licenses for Oracle Diagnostics & Tuning Packs and when Oracle’s SQL monitoring feature actually kicks in, then you can use V$SQL_MONITOR to view the bind variable values of currently executing SQL. Normally the bind variable values live in the private memory (PGA) of a process, but when SQL Monitoring kicks in for a statement in Oracle 11.2, it will copy the current bind variable values to SGA (V$SQL_MONITOR), so these will be readable by other users (like you, who’s trying to troubleshoot a performance problem) as well.

http://tech.e2sn.com/oracle/troubleshooting/oracle-s-real-time-sql-monitoring-feature-v-sql_monitor

Best current practices against queries running slow after database upgrade

http://gavinsoorma.com/2011/02/upgrade-to-11g-and-ensure-optimizer-plan-stability-using-sql-plan-baselines/comment-page-1/

http://www.perftuning.com/wp-content/uploads/2013/10/Using-SQL-Tuning-Sets-for-Plan-Stability.pdf

  • Things to Consider Before Upgrade to 11.2.0.2 in Relation to Database Performance          [Document 1320966.1]
  • Query Performance Degradation – Upgrade Related – Recommended Actions          [Document 745216.1]
  • TROUBLESHOOTING: Server Upgrade Results in Slow Query Performance          [Document 160089.1]

INITRANS Sizing considerations

What is “INITRANS”?
Specify the initial number of concurrent transaction entries allocated within each data block allocated to the database object. This value can range from 1 to 255 and defaults to 1, with the following exceptions: (on10g MAX_TRANS ‘s ignored )

* The default INITRANS value for a cluster is 2 or the default INITRANS value of the tablespace in which the cluster resides, whichever is greater.

* The default value for an index is 2.

In general, you should not change the INITRANS value from its default.

Each transaction that updates a block requires a transaction entry in the block. The size of a transaction entry depends on your operating system. This parameter ensures that a minimum number of concurrent transactions can update the block and helps avoid the overhead of dynamically allocating a transaction entry.

If you have global indexes, a global index segment and global index blocks are shared by server processes of the same parallel DML statement. Even if the operations are not performed against the same row, the server processes can share the same index blocks. Each server transaction needs one transaction entry in the index block header before it can make changes to a block.

Therefore, in the CREATE INDEX or ALTER INDEX statements,

you should set INITRANS, the initial number of transactions allocated within each data block, to a large value, such as the maximum DOP against this index

BLOCK SIZE and INITRANS :
=========================

Each ITL entry in the block transaction variable header takes 24 bytes. Though a block can have a maximum of 255 different ITLs, the ITLs allocation is limited by block size. The database block size plays an important role when allocating the number of inital ITLs for the block. The rule is “the total size allocated for initial ITLs SHOULD be LESS THAN 50% of the database block size”.

ie : sizeof(INITIAL ITLs) < ( 50 % of the DATABASE BLOCK SIZE )

Examining ITL allocation (a brief experiment) ==========================
STEP 1 : Create a table with INITRANS 10. SQL> CREATE TABLE TEST (I NUMBER) INITRANS 10;

STEP 2: Insert 1 record for testing purpose. A blockdump can be taken later.
SQL> INSERT INTO TEST VALUES (10);
SQL> COMMIT;

Step 3: Find out the block number and the file id for dumping the block:
SQL> select dbms_rowid.rowid_relative_fno(rowid), dbms_rowid.rowid_block_number(rowid) from test;

Step 4: Dump the block:
SQL> ALTER SYSTEM DUMP DATAFILE x BLOCK y;

Step 5: Open the dump trace file located in USER_DUMP_DEST directory and check the following:

Block header dump: 0x0040ad12
Object id on Block? Y
seg/obj: 0x5881 csc: 0x5dc.33121987 itc: 10 flg: O typ: 1 – DATA
fsl: 0 fnx: 0x0 ver: 0x01

Itl Xid Uba Flag Lck Scn/Fsc
0x01 xid: 0x0003.05b.0000009b uba: 0x008005f3.005e.42 –U- 1 fsc 0x0000.33121989
0x02 xid: 0x0000.000.00000000 uba: 0x00000000.0000.00 —- 0 fsc 0x0000.00000000
0x03 xid: 0x0000.000.00000000 uba: 0x00000000.0000.00 —- 0 fsc 0x0000.00000000
0x04 xid: 0x0000.000.00000000 uba: 0x00000000.0000.00 —- 0 fsc 0x0000.00000000
0x05 xid: 0x0000.000.00000000 uba: 0x00000000.0000.00 —- 0 fsc 0x0000.00000000
0x06 xid: 0x0000.000.00000000 uba: 0x00000000.0000.00 —- 0 fsc 0x0000.00000000
0x07 xid: 0x0000.000.00000000 uba: 0x00000000.0000.00 —- 0 fsc 0x0000.00000000
0x08 xid: 0x0000.000.00000000 uba: 0x00000000.0000.00 —- 0 fsc 0x0000.00000000
0x09 xid: 0x0000.000.00000000 uba: 0x00000000.0000.00 —- 0 fsc 0x0000.00000000
0x0a xid: 0x0000.000.00000000 uba: 0x00000000.0000.00 —- 0 fsc 0x0000.00000000

Example:
SQL> CREATE TABLE TEST (I NUMBER) INITRANS 10;

Table created.

SQL> INSERT INTO TEST VALUES (10);

1 row created.

SQL> commit;

Commit complete.

SQL> select dbms_rowid.rowid_relative_fno(rowid), dbms_rowid.rowid_block_number(rowid) from test;

DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
————————————————————————
4 36477

SQL> ALTER SYSTEM DUMP DATAFILE 4 block 36477;

System altered.

Check tracefile:

Block header dump: 0x01008e7d
Object id on Block? Y
seg/obj: 0x18fec csc: 0x00.278431ed itc: 10 flg: E typ: 1 – DATA
brn: 0 bdba: 0x1008e79 ver: 0x01 opc: 0
inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0008.019.0017e02b 0x01800ce1.17d9.08 –U- 1 fsc 0x0000.278431ef
0x02 0x0000.000.00000000 0x00000000.0000.00 —- 0 fsc 0x0000.00000000
0x03 0x0000.000.00000000 0x00000000.0000.00 —- 0 fsc 0x0000.00000000
0x04 0x0000.000.00000000 0x00000000.0000.00 —- 0 fsc 0x0000.00000000
0x05 0x0000.000.00000000 0x00000000.0000.00 —- 0 fsc 0x0000.00000000
0x06 0x0000.000.00000000 0x00000000.0000.00 —- 0 fsc 0x0000.00000000
0x07 0x0000.000.00000000 0x00000000.0000.00 —- 0 fsc 0x0000.00000000
0x08 0x0000.000.00000000 0x00000000.0000.00 —- 0 fsc 0x0000.00000000
0x09 0x0000.000.00000000 0x00000000.0000.00 —- 0 fsc 0x0000.00000000
0x0a 0x0000.000.00000000 0x00000000.0000.00 —- 0 fsc 0x0000.00000000
The above dump confirms that the ITL allocation worked as specified. However this may not be the case for all the valid values for INITRANS.

Assuming that your database has 2k block size (db_block_size=2048), if you set INITRANS to 45, only 41 ITLs will be found in the dump because of the consideration of the block size. So for a 2k block size, any value greater than 41 for INITRANS would result only in allocating 41 ITL entries in the block header.

Likewise , if you have a 4k block size, the maximum number of inital ITL entries are only 83. Any value over and above 83 for INITRANS are ignored.

BLOCK SIZE NO OF ITLs allocated in block header
=============================================

2048 41
4096 83
8192 169

As I mentioned before, not more than 50 % of the block is utilized for ITLs. For a 2k block sized database, the maximum ITLs during intial allocation is 41. Each ITL takes 24 bytes of space in the header. So

For 2k Block: 41 * 24 = 984 bytes. This is 48 % of the total block size.

For 4k block: 83 * 24 = 1992 bytes.

For 8k block: 169 * 24 = 4056 bytes. (Same 48%)

Follow the above 5 steps to check the ITL allocation for different block sizes.
==================================================================================

We can modify INITRANS attribute by use ALTER

EXAMPLE
SQL> ALTER TABLE TABLE_NAME INITRANS 10;

Example: Test on 8k … initrans =255
SQL> CREATE TABLE TEST2 (I NUMBER) INITRANS 255;

Table created.

SQL> INSERT INTO TEST2 VALUES (10);

1 row created.

SQL> commit;

Commit complete.

SQL> select dbms_rowid.rowid_relative_fno(rowid), dbms_rowid.rowid_block_number(rowid) from test2;

DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
———————————— ————————————
4 36485

SQL> ALTER SYSTEM DUMP DATAFILE 4 block 36485;

System altered.

Check trace file:

Block header dump: 0x01008e85
Object id on Block? Y
seg/obj: 0x18fed csc: 0x00.2784367f itc: 169 flg: E typ: 1 – DATA
brn: 0 bdba: 0x1008e81 ver: 0x01 opc: 0
inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0004.008.0011e01c 0x01800c72.154d.05 –U- 1 fsc 0x0000.27843681
0x02 0x0000.000.00000000 0x00000000.0000.00 —- 0 fsc 0x0000.00000000
.
.
.
0xa5 0x0000.000.00000000 0x00000000.0000.00 —- 0 fsc 0x0000.00000000
0xa6 0x0000.000.00000000 0x00000000.0000.00 —- 0 fsc 0x0000.00000000
0xa7 0x0000.000.00000000 0x00000000.0000.00 —- 0 fsc 0x0000.00000000
0xa8 0x0000.000.00000000 0x00000000.0000.00 —- 0 fsc 0x0000.00000000

So, use shell command to help

$ grep fsc oradb1_ora_15394.trc | wc -l
169

So, 8k block: 169 * 24 = 4056 bytes. (Same 48%)… not 255

You should know before modify =>

Cautions on Altering Tables Physical Attributes The values you specify in this clause affect the table as follows:

* For a nonpartitioned table, the values you specify override any values specified for the table at create time.

* For a range-, list-, or hash-partitioned table, the values you specify are the default values for the table and the actual values for every existing partition, overriding any values already set for the partitions. To change default table attributes without overriding existing partition values, use the modify_table_default_attrs clause.

* For a composite-partitioned table, the values you specify are the default values for the table and all partitions of the table and the actual values for all subpartitions of the table, overriding any values already set for the subpartitions. To change default partition attributes without overriding existing subpartition values, use the modify_table_default_attrs clause with the FOR PARTITION clause.

http://www.databases-la.com/?q=node/71