Firman102’s Weblog

To convert from a UNIX (Epoch) date to Oracle date
TO_DATE(‘1970-01-01’, ‘YYYY-MM-DD’) + UNIX_date_in_millis / 86400000 = Oracle_date

And to convert the other way
(Oracle_date – TO_DATE(‘1970-01-01’, ‘YYYY-MM-DD’) )* 86400000 = UNIX_date_in_millis

I don’t remember why I needed this so long ago.

Tingkat: Pemula
DB Version: 10.2.0.1 above

Database yang sudah ter-create dapat kita clone menggunakan DBCA. Hasil clone ini akan disimpan sebagai template.

Pertama, jalankan dbca:

Klik tombol Next, maka kita masuk ke step 1. Pastikan Manage Templates terpilih. Kemudian klik tombol Next:

Pada step 2, Pilih Create a database template dan From an existing database (structure as well as data), kemudian klik tombol Next:

Pada step 3, Pilih Database instance yang ingin kita clone, kemudian klik tombol Next:

Tentukan Nama template pada bagian Name, kemudian tentukan pula dimana kita akan menyimpannya, isi dibagian Template datafile, setelah itu klik tombol Next:

Step 5 akan ada pilihan, apakah lokasi file yang ada sekarang akan kita simpan atau akan menggunakan lokasi file standar dari oracle. Terakhir klik tombol Finish:

Klik tombol OK untuk memulai proses clone:

Tampak pada gambar proses clone sedang berjalan:

Proses clone selesai:

In this Document

Applies to:

Oracle Warehouse Builder – Version: 11.1
Information in this document applies to any platform.

Goal

How to start the Warehouse Builder Repository Listener ?

Solution

1. To start the Warehouse Builder Browser Listener:
   1. Open a Command-box.
   2. Navigate to owb_homeowbbinwin32 (/owb_home/owb/bin/unix for Unix ).
   3. Run the startOwbbInst.bat (startOwbbInst.sh for Unix)
      The first time you invoke this listener, select and re-confirm a password for an oc4jadmin account
      D:Oracleowb11owbbinwin32>startOwbbInst.batD:Oracleowb11owbbinwin32>call setowbenv.bat
      2007-12-03 10:21:30.230 NOTIFICATION Auto-unpacking D:Oracleowb11owbj2eeowbb.war…
      2007-12-03 10:21:30.360 NOTIFICATION Unjar D:Oracleowb11owbj2eeowbb.war in D:Oracleowb11owbj2eeowbb
      2007-12-03 10:21:43.439 NOTIFICATION Finished auto-unpacking D:Oracleowb11owbj2eeowbb.war
      03-dec.-2007 10:21:43 com.evermind.server.XMLApplicationServerConfig randomizeJtaAdminPassword
      INFO: Updating JtaAdmin account
      07/12/03 10:21:44 Set OC4J administrator’s password (password text will not be displayed as it is entered)
      Enter password:
      Confirm password:
      The password for OC4J administrator “oc4jadmin” has been set.
      07/12/03 10:22:19 The OC4J administrator “oc4jadmin” account is activated.
      07/12/03 10:22:30 Oracle Containers for J2EE 10g (10.1.3.0.0) initialized

      Subsequent start will look like this :

      D:Oracleowb11owbbinwin32>startOwbbInst.batD:Oracleowb11owbbinwin32>call setowbenv.bat
      07/12/03 10:31:35 Oracle Containers for J2EE 10g (10.1.3.0.0) initialized
   4. The Command-box will remain open until the Warehouse Builder Browser Listener is stopped.
2. To stop the Warehouse Builder Browser Listener:
   1. Open a Command-box.
   2. Navigate to owb_homeowbbinwin32 (/owb_home/owb/bin/unix for Unix ).
   3. Run stopOwbbInst.bat (stopOwbbInst.sh for Unix)
      Each time you stop this listener, the password has to match what was supplied in the first start.
      D:Oracleowb11owbbinwin32>stopOwbbInst.batD:Oracleowb11owbbinwin32>call setowbenv.bat
      Enter password:
      D:Oracleowb11owbbinwin32>

Additional steps are described in the Warehouse Builder Installation and Administration Guide 11g Release 1 (11.1) : Chapter 1 Installation Overview and Requirements – Launching Warehouse Builder Components – page 1-19 .

James Koopmann, jkoopmann@pinehorse.com

Introduction

Do you know how your disk subsystem is actually performing? This article looks at extracting various I/O statistics so that you can monitor and determine just how well your disks are doing.

How can I separate Oracle I/O to maximize performance?

Should I separate data files from index files?

Should I separate redo logs”

These question(s), AND many more, seem to flood our minds as database administrators. They are easy to answer with generalities but in practice can be very difficult to come to a conclusion on unless we look at how our disk subsystem is actually performing.

Many of us might stop reading this article right now, saying to ourselves that these questions and level of detail is only available to our system administrators or those that control the disk farm. All too often, I have seen two different methodologies when configuring Oracle on storage. The first is to use Oracle’s Flexible Architecture (OFA) approach where architects will separate Oracle object types (data, index, redo, archive, etc.) across a storage array. The second approach is to architect a JBOD (Just a Bunch Of Disks) configuration and throw everything on it. Both of these approaches lack the planning and configuration that ultimately produces a well-tuned database system. They are just taking a shot in the dark, hoping everything is going to work well because they followed a predefined methodology. Well, methodologies may not work in your case. Don’t look to your system administrators as they might not even know how to extract information themselves, and when they do it is usually at a higher level since they too can not relate the information to the Oracle stack.

So the DBA must begin to understand the application from a purely I/O perspective, relay that information to the Storage Administrator and then, together, develop a plan for configuring or altering a storage subsystem that will be able to service the application mix. For the storage, a key performance indicator of an OLTP environment is based on I/Os per second (IOPS) and latencies (I/O turn-around time). OLAP databases are your data warehouses or reporting systems and are categorized by moving large amounts of data that is mostly read only. For the storage array, the performance of an OLAP environment is based on Mega-bytes per second (MBPS). A database workload is often descriptive of its application mix. Understanding and translating an application mix into a database workload is critical for optimizing a storage system. The workload of an OLTP database is categorized by small random I/O while OLAP is categorized by large sequential or random I/O.

For the database administrator it is now time to get dirty and look at the internals of your database. Somewhere and somehow, you must extract some form of statistics that allow you to categorize the type of SQL and I/O requests at the database level. Oracle for instance has quite a few internal tables that allow for the interrogation of this information. For instance we can query the gv$sysstat view for (‘physical read total IO requests’ – ‘physical read total multi block requests’) to get the number of small reads in the system. Do this over a period of time, subtract the beginning value from the ending value and you quickly get IOPS for small reads over that period. This MUST be done for each statistic available to get a view of total IOPS and MBPS being requested by your particular database. These are the pertinent statistics you will need to extract. Basically, large reads and writes are used to calculate MBPS and small reads and writes are used for IOPS calculations.

gv$sysstat (name, value) 

Total Reads :'physical read total IO requests' 
Total Writes:'physical write total IO requests' 

Large Reads :'physical read total multi block requests' 
Large Writes:'physical write total multi block requests' 

Total Bytes Read    :'physical read total bytes' 
Total Bytes Written :'physical write total bytes' 

To calculate small reads: 
Small Reads  = Total Reads - Large Reads 
Small Writes = Total Writes - Large Writes 

Coming up with these numbers allows us to make intelligent decisions in regards to our current database performance and storage requirements. Granted, the database may be experiencing contention and actual I/O requests may be lower then optimal so tuning may be in order. Regardless, it is the DBAs responsibility to take these IOPS and MBPS to the Storage Administrator, evaluate if the storage solution is being taxed, and if a reconfiguration is required. These reconfigurations may require moving data files around to use more disks, adding more disks for higher throughput, or reducing IOPS and MBPS through the application. Well, we have come full circle to the application again. I know you know what to do.

Below are two scripts that will get you started. I have spent quite a bit of time getting them easy to use and giving you the information quickly to understand how your disk subsystem is performing over time.

set echo     off 
set feedback off 
set heading  off 
set linesize 40 
set pagesize 55 
set verify   off 

set termout off 
column rpt new_value rpt 
select instance_name||'_'||to_char(sysdate,'YYYYMMDDHH24MISS')||'_vsysstat_ioworkload.LST' rpt from v$instance; 
set termout on 
prompt 
prompt 
prompt ^^^^^^^^^^^^^ 
prompt Report Name : ../LST/&&rpt 
prompt ^^^^^^^^^^^^^ 
spool ../LST/&&rpt 

column sr1  new_value sr1 
column sw1  new_value sw1 
column lr1  new_value lr1 
column lw1  new_value lw1 
column tbr1 new_value tbr1 
column tbw1 new_value tbw1 
set termout off 
SELECT 
sum(decode(name,'physical read total IO requests',value,0)- decode(name,'physical read total multi block requests',value,0)) sr1, 
sum(decode(name,'physical write total IO requests',value,0)- decode(name,'physical write total multi block requests',value,0)) sw1, 
sum(decode(name,'physical read total multi block requests',value,0)) lr1, 
sum(decode(name,'physical write total multi block requests',value,0)) lw1, 
sum(decode(name,'physical read total bytes',value,0)) tbr1, 
sum(decode(name,'physical write total bytes',value,0)) tbw1 
FROM v$sysstat; 
set termout on 

prompt 
prompt 
prompt ^^^^^^^^^^^^ 
prompt First Sample 
prompt ^^^^^^^^^^^^ 
prompt Number of Small Reads : &&sr1 
prompt Number of Small Writes: &&sw1 
prompt Number of Large Reads : &&lr1 
prompt Number of Large Writes: &&lw1 
prompt Total Bytes Read      : &&tbr1 
prompt Total Bytes Written   : &&tbw1 
prompt 
prompt 
prompt Enter the amount of time (in seconds) you would like this process to sleep for sampling data 
prompt ^^^^^^^^^^^^^^^^^^ 
prompt Sleep Time (secs): &&sleeptime 
prompt ^^^^^^^^^^^^^^^^^^ 
exec DBMS_LOCK.SLEEP (&&sleeptime); 

column sr2  new_value sr2 
column sw2  new_value sw2 
column lr2  new_value lr2 
column lw2  new_value lw2 
column tbr2 new_value tbr2 
column tbw2 new_value tbw2 
set termout off 
SELECT 
sum(decode(name,'physical read total IO requests',value,0)- decode(name,'physical read total multi block requests',value,0)) sr2, 
sum(decode(name,'physical write total IO requests',value,0)- decode(name,'physical write total multi block requests',value,0)) sw2, 
sum(decode(name,'physical read total multi block requests',value,0)) lr2, 
sum(decode(name,'physical write total multi block requests',value,0)) lw2, 
sum(decode(name,'physical read total bytes',value,0)) tbr2, 
sum(decode(name,'physical write total bytes',value,0)) tbw2 
FROM v$sysstat; 
set termout on 

prompt 
prompt 
prompt ^^^^^^^^^^^^^ 
prompt Second Sample 
prompt ^^^^^^^^^^^^^ 
prompt Number of Small Reads : &&sr2 
prompt Number of Small Writes: &&sw2 
prompt Number of Large Reads : &&lr2 
prompt Number of Large Writes: &&lw2 
prompt Total Bytes Read      : &&tbr2 
prompt Total Bytes Written   : &&tbw2 
prompt 
prompt 
prompt ^^^^^^^^^ 
prompt Results : 
prompt ^^^^^^^^^ 

column sri new_value sri 
column swi new_value swi 
column tsi new_value tsi 
column srp new_value srp 
column swp new_value swp 
column lri new_value lri 
column lwi new_value lwi 
column tli new_value tli 
column lrp new_value lrp 
column lwp new_value lwp 
column tr  new_value tr 
column tw  new_value tw 
column tm  new_value tm 
SELECT 
ROUND((&&sr2-&&sr1)/&&sleeptime,3) sri, 
ROUND((&&sw2-&&sw1)/&&sleeptime,3) swi, 
ROUND(((&&sr2-&&sr1)+(&&sw2-&&sw1))/&&sleeptime,3) tsi, 
ROUND((&&sr2-&&sr1)/DECODE(((&&sr2-&&sr1)+(&&sw2-&&sw1)),0,1,((&&sr2-&&sr1)+(&&sw2-&&sw1)))*100,3) srp, 
ROUND((&&sw2-&&sw1)/DECODE(((&&sr2-&&sr1)+(&&sw2-&&sw1)),0,1,((&&sr2-&&sr1)+(&&sw2-&&sw1)))*100,3) swp, 
ROUND((&&lr2-&&lr1)/&&sleeptime,3) lri, 
ROUND((&&lw2-&&lw1)/&&sleeptime,3) lwi, 
ROUND(((&&lr2-&&lr1)+(&&lw2-&&lw1))/&&sleeptime,3) tli, 
ROUND((&&lr2-&&lr1)/DECODE(((&&lr2-&&lr1)+(&&lw2-&&lw1)),0,1,((&&lr2-&&lr1)+(&&lw2-&&lw1)))*100,3) lrp, 
ROUND((&&lw2-&&lw1)/DECODE(((&&lr2-&&lr1)+(&&lw2-&&lw1)),0,1,((&&lr2-&&lr1)+(&&lw2-&&lw1)))*100,3) lwp, 
ROUND(((&&tbr2-&&tbr1)/&&sleeptime)/1048576,3) tr, 
ROUND(((&&tbw2-&&tbw1)/&&sleeptime)/1048576,3) tw, 
ROUND((((&&tbr2-&&tbr1)+(&&tbw2-&&tbw1))/&&sleeptime)/1048576,3) tm 
FROM dual; 

SELECT 
'Small Read  IOPS  = '||ROUND((&&sr2-&&sr1)/&&sleeptime,3)||' IOPS', 
'Small Write IOPS  = '||ROUND((&&sw2-&&sw1)/&&sleeptime,3)||' IOPS', 
'Total Small IOPS  = '||ROUND(((&&sr2-&&sr1)+(&&sw2-&&sw1))/&&sleeptime,3)||' IOPS', 
'Small Read  I/O % = '||ROUND((&&sr2-&&sr1)/DECODE(((&&sr2-&&sr1)+(&&sw2-&&sw1)),0,1,((&&sr2-&&sr1)+(&&sw2-&&sw1)))*100,3)||' %', 
'Small Write I/O % = '||ROUND((&&sw2-&&sw1)/DECODE(((&&sr2-&&sr1)+(&&sw2-&&sw1)),0,1,((&&sr2-&&sr1)+(&&sw2-&&sw1)))*100,3)||' %', 
'Large Read  IOPS  = '||ROUND((&&lr2-&&lr1)/&&sleeptime,3)||' IOPS', 
'Large Write IOPS  = '||ROUND((&&lw2-&&lw1)/&&sleeptime,3)||' IOPS', 
'Total Large IOPS  = '||ROUND(((&&lr2-&&lr1)+(&&lw2-&&lw1))/&&sleeptime,3)||' IOPS', 
'Large Read  I/O % = '||ROUND((&&lr2-&&lr1)/DECODE(((&&lr2-&&lr1)+(&&lw2-&&lw1)),0,1,((&&lr2-&&lr1)+(&&lw2-&&lw1)))*100,3)||' %', 
'Large Write I/O % = '||ROUND((&&lw2-&&lw1)/DECODE(((&&lr2-&&lr1)+(&&lw2-&&lw1)),0,1,((&&lr2-&&lr1)+(&&lw2-&&lw1)))*100,3)||' %', 
'Total Read        = '||ROUND(((&&tbr2-&&tbr1)/&&sleeptime)/1048576,3)||' MBPS', 
'Total Written     = '||ROUND(((&&tbw2-&&tbw1)/&&sleeptime)/1048576,3)||' MBPS', 
'Total MBPS        = '||ROUND((((&&tbr2-&&tbr1)+(&&tbw2-&&tbw1))/&&sleeptime)/1048576,3)||' MBPS' 
FROM dual 
; 

prompt Small Read  IOPS  = &&sri IOPS 
prompt Small Write IOPS  = &&swi IOPS 
prompt Total Small IOPS  = &&tsi IOPS 
prompt Small Read  I/O % = &&srp % 
prompt Small Write I/O % = &&swp % 
prompt Large Read  IOPS  = &&lri IOPS 
prompt Large Write IOPS  = &&lwi IOPS 
prompt Total Large IOPS  = &&tli IOPS 
prompt Large Read  I/O % = &&lrp % 
prompt Large Write I/O % = &&lwp % 
prompt Total Read        = &&tr MBPS 
prompt Total Written     = &&tw MBPS 
prompt Total MBPS        = &&tm MBPS 

spool off 
undefine sleeptime 

Get a complete history of IOPS & MBPS from workload repository history and graph it for you management. This allows you to see total database disk activity. Compare this against what your disk capacity is. Just remember these numbers are for ALL disks. You can get average IOPS/MBPS by dividing by your total number of disks used in servicing database requests. This is great information and once you graph the results you will really see how your I/O, and application performance, might be suffering during the day or at least determine where peak periods are.

set echo     off 
set feedback off 
set linesize 300 
set pagesize 55 
set verify   off 

set termout off 
column rpt new_value rpt 
select instance_name||'_wrh_sysstat_ioworkload_'||'.LST' rpt from v$instance; 
set termout on 
prompt 
prompt 
prompt ^^^^^^^^^^^^^ 
prompt Report Name : ../LST/&&rpt 
prompt ^^^^^^^^^^^^^ 
spool ../LST/&&rpt 

column sri head "Small|Read|IOPS" 
column swi head "Small|Write|IOPS" 
column tsi head "Total|Small|IOPS" 
column srp head "Small|Read|I/O%" 
column swp head "Small|Write|I/O%" 
column lri head "Large|Read|IOPS" 
column lwi head "Large|Write|IOPS" 
column tli head "Total|Large|IOPS" 
column lrp head "Large|Read|I/O%" 
column lwp head "Large|Write|I/O%" 
column tr  head "Total|Read|MBPS" 
column tw  head "Total|Written|MBPS" 
column tm  head "Total|MBPS" 
column begin_time for a25 
column end_time for a25 

SELECT end_time, 
       ROUND(sr/inttime,3) sri, 
       ROUND(sw/inttime,3) swi, 
       ROUND((sr+sw)/inttime,3) tsi, 
       ROUND(sr/DECODE((sr+sw),0,1,(sr+sw))*100,3) srp, 
       ROUND(sw/DECODE((sr+sw),0,1,(sr+sw))*100,3) swp, 
       ROUND(lr/inttime,3) lri, 
       ROUND(lw/inttime,3) lwi, 
       ROUND((lr+lw)/inttime,3) tli, 
       ROUND(lr/DECODE((lr+lw),0,1,(lr+lw))*100,3) lrp, 
       ROUND(lw/DECODE((lr+lw),0,1,(lr+lw))*100,3) lwp, 
       ROUND((tbr/inttime)/1048576,3) tr, 
       ROUND((tbw/inttime)/1048576,3) tw, 
       ROUND(((tbr+tbw)/inttime)/1048576,3) tm 
 FROM ( 
SELECT beg.snap_id beg_id, end.snap_id end_id, 
       beg.begin_interval_time, beg.end_interval_time, 
       end.begin_interval_time begin_time, end.end_interval_time end_time, 
       (extract(day    from (end.end_interval_time - end.begin_interval_time))*86400)+ 
       (extract(hour   from (end.end_interval_time - end.begin_interval_time))*3600)+ 
       (extract(minute from (end.end_interval_time - end.begin_interval_time))*60)+ 
       (extract(second from (end.end_interval_time - end.begin_interval_time))*01) inttime, 
       decode(end.startup_time,end.begin_interval_time,end.sr,(end.sr-beg.sr))    sr, 
       decode(end.startup_time,end.begin_interval_time,end.sw,(end.sw-beg.sw))    sw, 
       decode(end.startup_time,end.begin_interval_time,end.lr,(end.lr-beg.lr))    lr, 
       decode(end.startup_time,end.begin_interval_time,end.lw,(end.lw-beg.lw))    lw, 
       decode(end.startup_time,end.begin_interval_time,end.tbr,(end.tbr-beg.tbr)) tbr, 
       decode(end.startup_time,end.begin_interval_time,end.tbw,(end.tbw-beg.tbw)) tbw 
  FROM 
(SELECT dba_hist_snapshot.snap_id, startup_time, begin_interval_time, end_interval_time, 
 sum(decode(stat_name,'physical read total IO requests',value,0)- 
 	decode(stat_name,'physical read total multi block requests',value,0)) sr, 
 sum(decode(stat_name,'physical write total IO requests',value,0)- 
 	decode(stat_name,'physical write total multi block requests',value,0)) sw, 
 sum(decode(stat_name,'physical read total multi block requests',value,0)) lr, 
 sum(decode(stat_name,'physical write total multi block requests',value,0)) lw, 
 sum(decode(stat_name,'physical read total bytes',value,0)) tbr, 
 sum(decode(stat_name,'physical write total bytes',value,0)) tbw 
   FROM wrh$_sysstat, wrh$_stat_name, dba_hist_snapshot 
  WHERE wrh$_sysstat.stat_id = wrh$_stat_name.stat_id 
    AND wrh$_sysstat.snap_id = dba_hist_snapshot.snap_id 
  group by dba_hist_snapshot.snap_id, startup_time, begin_interval_time, end_interval_time) beg, 
(SELECT dba_hist_snapshot.snap_id, startup_time, begin_interval_time, end_interval_time, 
 sum(decode(stat_name,'physical read total IO requests',value,0)- 
 	decode(stat_name,'physical read total multi block requests',value,0)) sr, 
 sum(decode(stat_name,'physical write total IO requests',value,0)- 
 	decode(stat_name,'physical write total multi block requests',value,0)) sw, 
 sum(decode(stat_name,'physical read total multi block requests',value,0)) lr, 
 sum(decode(stat_name,'physical write total multi block requests',value,0)) lw, 
 sum(decode(stat_name,'physical read total bytes',value,0)) tbr, 
 sum(decode(stat_name,'physical write total bytes',value,0)) tbw 
   FROM wrh$_sysstat, wrh$_stat_name, dba_hist_snapshot 
  WHERE wrh$_sysstat.stat_id = wrh$_stat_name.stat_id 
    AND wrh$_sysstat.snap_id = dba_hist_snapshot.snap_id 
  group by dba_hist_snapshot.snap_id, startup_time, begin_interval_time, end_interval_time) end 
 WHERE beg.snap_id + 1 = end.snap_id 
) 
order by 1 
/ 

spool off 

Understanding an application from a purely I/O perspective is a key aspect of configuring storage. Oracle has a variety of I/O types that ultimately need to be mapped, sampled, and related to storage. In Oracle’s case, there is I/O generated by server processes on behalf of users, multiple database writers, checkpoint activity, logging facilities that not only write as updates are being done but also the reading from online logs and writing to archive logs by the archive process, plus a few more and some internals that determine size and frequency of the I/Os. Oracle is a very complex system of processes that without understanding your disk I/O patterns is nearly impossible to configure properly.

Author: Burleson

It is very frustrating when you find your database hang. It is even more frustrating when there are no messages in the alert log and still you are unable to connect to the database. We hear complains when one tries to connect to Oracle with Enterprise Manager and it just hangs. Sometimes one is unable to cannot connect via SQL*Plus either. Oracle can hang for many reasons. In this article I will give some effective tips that can help you fix the hung databases.

Logs and Files:

Alert Log:

First of all analyze the alert log for any errors or messages.

Server-side Logs:

In addition to the alert log, you need to check server-side logs as well.

/etc/syslog, /var/adm/syslog

Listener Log:

Check the listener log files.

Trace Files:

Check the bdump, cdump and pfile directories for trace files.

Instance Availability:

You might not see an entry in the alert log if the instance is unavailable due to a crash. You can check that the instance is running and hence available by using below statement.

ps -ef|grep ora|grep pmon

Server Resources:

Databases mostly hang if server resources are over allocated and there is not enough RAM to spawn another connection to Oracle.

External issues:

There are some external issues that can make your database hang. The network being down, Kerberos security issues, SSO or a firewall issue can cause an Oracle connection to hang. You can test this by setting

sqlnet.authentication_services=(none)

in your sqlnet.ora file and then retry connecting.

Listener:

Sometimes Oracle gets hang if listener is not running. Make sure that the listener is running. Check the lsnrctl statistics.

Oracle Hanging:

Oracle sets locks in order to manage concurrent updates and ensure that the database maintains its internal integrity. In some cases Oracle use hanging and no users can connect to the database. In such scenarios you have no choice other than bouncing the instance.

Shared Data:

Mostly the end-user session hangs when a shared data resource held by another end-user is accessed. The information about when Oracle has a session waiting on a resource can be found in the v$session view in the row_wait_file# and row_wait_block#. The file number and block number can then be cross-referenced into the dba_extents view to see the name of the table where the session is waiting on a block.

Column host format a6;
Column username format a10;
Column os_user format a8;
Column program format a30;
Column tsname format a12;

select
b.machine host,
b.username username,
b.server,
b.osuser os_user,
b.program program,
a.tablespace_name ts_name,
row_wait_file# file_nbr,
row_wait_block# block_nbr,
c.owner,
c.segment_name,
c.segment_type
from
dba_data_files a,
v$session b,
dba_extents c
where
b.row_wait_file# = a.file_id
and
c.file_id = row_wait_file#
and
row_wait_block# between c.block_id and c.block_id + c.blocks – 1
and
row_wait_file# <> 0
and
type=’USER’
;

ANALYZE command hanging:

The ANALYZE command may hang if there is not enough space in the Temp Tablespace. Create a big Temporary Tablespace to avoid this problem.

The ANALYZE command may also hang if the table is partitioned and a utility (imp or loader) is inserting rows in the table, and at the same time ANALYZE command starts working on the table. You can fix this problem by analyze be done in night when there is a low DML activities.

Author: Burleson

TNS or Transparent Network Substrate is Oracle’s networking architecture. Oracle typically relies on TNS to provide generic network connectivity to and between Oracle databases. TNS provides a uniform application interface to enable network applications to access the underlying network protocols transparently.

It is very critical for Oracle professional to understand the affect of network configuration on database performance. The network administrator is able to control much of the network performance tuning and hence Oracle administrator has little control over the network settings that can affect overall database performance.

Performance Improvement by Setting Parameters:

The parameters within the sqlnet.ora , tnsnames.ora , and protocol.ora files can be set to improve the performance of distributed transactions. These parameters can be used to change the configuration and size of TCP packets. Adjusting these parameters can have a profound impact on the underlying network transport layer to improve the throughput of all Oracle transactions.

Oracle Net:

Oracle Net is a layer in the OSI model that resides above the network-specific protocol stack. Oracle Net Services provides methods for understanding and resolving network problems through the use of log and trace files. Since Oracle Net does not allow to tune the underlying network layer and therefore majority of network traffic cannot be tuned from within the Oracle environment.

Controlling Packet Frequency and Size:

Oracle provides a number of tools to change packet frequency and size. This enables Oracle database administrators to control the frequency and size of network packets. For example you can change the refresh interval for a snapshot to ship larger amounts at less frequent intervals.

Tuning Oracle Net connections:

Oracle Net connections between servers can be tuned using several parameters however only qualified network administrator should be consulted for tuning the network. The frequency and size of packet shipping across the network can be affected by using settings contained in the following below files. These tuning parameters will affect only the performance of the Oracle Net layer.

Controlling Buffer Flushing Delays:

The requests are not always sent immediately to their destinations as by default Oracle Net waits until the buffer is filled before transmitting data. This is most common when large amounts of data are streamed from one end to another, and Oracle Net does not transmit the packet until the buffer is full. However this problem can be solved if you add a protocol.ora file and specify a tcp.nodelay to stop buffer flushing delays.

tcp.nodelay parameter:

• Setting tcp.nodelay can cause a huge improvement in performance when there is high-volume traffic between database servers.

• tcp.nodelay parameter can be used on both the client and server.

• The tcp.nodelay parameter should be used only if TCP timeouts are encountered.

• You can specify tcp.nodelay = yes to indicate no data buffering for all TCP/IP implementations.

• Specifying tcp.nodelay = yes causes TCP buffering to be skipped so that every request is sent immediately. However network traffic can increase due to smaller and more frequent packet transmission causing slowdowns in the network.

Bypassing the Network Layer:

The automatic_ipc parameter speeds local connections to the database by bypassing the network layer.

automatic_ipc parameter:

• When automatic_ipc=on , Oracle Net checks to see if a local database is defined by the same alias. If so, network layers are bypassed as the connection is translated directly to the local IPC connections. This is useful on database servers, but it’s absolutely useless for Oracle Net clients.

• The automatic_ipc parameter should be used only on the database server when an Oracle Net connection must be made to the local database.

• If local connections are not needed or required then all Oracle Net clients can improve performance by setting automatic_ipc= off;

Data Units:

The session data unit (SDU) and transport date unit (TDU) parameters are located in the tnsnames.ora and listener.ora files.

Increasing SDU for Streams propagation:

Include DEFAULT_SDU_SIZE parameter in the receiving side of sqlnet.ora file in order to take advantage of an increased SDU for Streams propagation. The receiving side listener.ora file must indicate the SDU change for the system identifier (SID). The sending side tnsnames.ora file connect string must also include the SDU modification for the particular service.

Increasing Propagation Performance:

The performance of propagation on your system can be increased by SEND_BUF_SIZE and RECV_BUF_SIZE parameters in the listener.ora file. Network throughput can be significantly improved by using the SQLNET.SEND_BUF_SIZE and SQLNET.RECV_BUF_SIZE parameters to increase the size of the network TCP send and receive I/O buffers.

Determining number of requests:

You can determine the number of requests the listener can store while Oracle is working to establish a connection. This is determined by the undocumented queuesize parameter.

queuesize parameter:

• The queuesize parameter is used only for very high-volume databases, where the listener spawns thousands of connections per hour.

• The number of expected simultaneous connections should be equal to the size of the queuesize parameter.

LISTENER =
(ADDRESS_LIST =
(ADDRESS =
(PROTOCOL = TCP)
(HOST = marvin)
(PORT = 1521)
(QUEUESIZE = 32)
)
)

• A disadvantage of queuesize parameter is that it uses more system memory and resources by pre-allocating resources for anticipated requests.

• You can use MTS and pre-spawned Oracle connections if you have high-volume connections into a dedicated listener. Some versions of UNIX do not allow queues greater than five and there are some restrictions of the MTS queue size.

Conclusion:

To conclude I would say that an Oracle professional must fully understand and optimize Oracle Net parameters as they can have a great impact on the performance of distributed systems.