Calling Web Service with wget

Wget - "The non-interactive network downloader." (from the man page) could be used to call web service if a request XML is available. This is useful to quickly test a web service.
1. First step is to capture the SOAP request in XML format. Wireshark could be used for this. Below is a sample of a request which is used to test the failover setup for application server connection when RAC is used. The request will send the client's machine name as the input and will respond with the instance connected at the back end.

<?xml version='1.0' encoding='UTF-8'?>
  <S:Envelope xmlns:S="http://schemas.xmlsoap.org/soap/envelope/">
    <S:Body>
      <ns2:getAppAndDBServerNames xmlns:ns2="http://asanga/">
        <arg0>pc120</arg0>
      </ns2:getAppAndDBServerNames>
    </S:Body>
</S:Envelope>

2. Save the XML request in a file. This file will be used as the input to the wget.
3. Execute the wget command as shown below

wget http://192.168.0.66:8080/FailOverTest/FailOverTestPort --post-file=input.xml 
--header="Content-Type: text/xml" --output-document=soapResponse.xml

input.xml is the file with xml request created in step 2 above. Response from the WS will be saved into soapResponse.xml. 192.168.0.66:8080 is the IP and port of the server where web service is deployed and /FailOverTest/FailOverTestPort is the service called.

$ wget http://192.168.0.66:8080/FailOverTest/FailOverTestPort --post-file=input.xml --header="Content-Type: text/xml" --output-document=soapResponse.xml
--2012-09-20 11:47:08--  http://192.168.0.66:8080/FailOverTest/FailOverTestPort
Connecting to 192.168.0.66:8080... connected.
HTTP request sent, awaiting response... 200 OK
Length: 331 [text/xml]
Saving to: `soapResponse.xml'

100%[============================================================================================================>] 331         --.-K/s   in 0s

2012-09-20 11:47:08 (35.1 MB/s) - `soapResponse.xml' saved [331/331]

4. The soapResponse.xml will have the output in XML format.

<?xml version="1.0" ?>
  <S:Envelopexmlns:S="http://schemas.xmlsoap.org/soap/envelope/">
    <S:Body>
      <ns2:getAppAndDBServerNamesResponse xmlns:ns2="http://asanga/">
        <return>
          Connected to ent11g2 DB Server hpc1.domain.net Application Server on Thu Sep 20 11:34:02 BST 2012
        </return>
      </ns2:getAppAndDBServerNamesResponse>
    </S:Body>
  </S:Envelope>

enq: TX row lock contention During Inserts due to Bitmap Indexes

Bitmap indexes are not suited on tables that have frequent DML operations. It was shown on an earlier post how the bitmap index locking happens.
enq: TX row lock contention waits happen when multiple sessions try to modify the same row, but could manifest in other areas as well such as inserts on a primary key column and inserts on bitmap index columns. Since insert is effectively creating a new row seeing enq: TX row lock contention on inserts could be bit puzzling.
In the case of inserting into a primary key column subsequent sessions will wait on enq: TX row lock contention event until first session commit or rollback. This is required to identify and raise ORA-00001: unique constraint in cases where duplicate values are being inserted into primary key column. Therefore it is not uncommon to see enq: TX row lock contention waits on inserts when there's a primary key column and a highly concurrent insert rate. To reduce this wait if possible remove the primary key but may not always possible due to application logic.
Second place where enq: TX row lock contention events comes up is when inserting to table with bitmap indexes. enq: TX row lock contention waits happen since bitmap index entries must be updated based on the inserted rows. This has a far worse performance impact than insert on a primary key. Below is a simple test to illustrate the point.
Create a table with primary key

create table abc (id number, a varchar2(1), b number(1), c varchar2(1), primary key (id));

Create bitmap indexes as below

SQL> create bitmap index aidxa on abc(a);
Index created.

SQL> create bitmap index aidxb on abc(b);
Index created.

SQL> create bitmap index aidxc on abc(c);
Index created.

Open two sqlplus sessions and run the following two PL/SQL blocks. On session 1

begin
    for i in 1 .. 100000
    loop
      insert into abc values(i,dbms_random.string('i',1),round(dbms_random.value(1,5)),dbms_random.string('i',1));
      commit;
    end loop;
end;
/

On session 2

begin
    for i in 100000 .. 200000
    loop
      insert into abc values(i,dbms_random.string('i',1),round(dbms_random.value(1,5)),dbms_random.string('i',1));
      commit;
    end loop;
end;
/

Monitor the waits either through em console or active session history view. In this case APConsole has been used and output is given at the end.


Drop the bitmap indexes and truncate the table and run the test again. The image below shows the comparison of the two test cases.

It is apparent that amount of cpu time is high during the test with bitmap index in-place but the point is the enq: TX row lock contention which is none when test was run without bitmap index. Therefore when there are high enq: TX row lock contention waits seen for insert statements it is worth investigating whether the table in question has bitmap indexes.

Related post
Bitmap Index Locking

Metalink Notes
TX Transaction and Enq: Tx - Row Lock Contention - Example wait scenarios [ID 62354.1]

PCTFREE and Rows per Block

For tablespaces with ASSM only the pctfree parameter would have any affect on the data storage. From Oracle database concept document "The PCTFREE storage parameter is essential to how the database manages free space. This SQL parameter sets the minimum percentage of a data reserved as free space for updates to existing rows. Thus, PCTFREE is important for preventing row migration and avoiding wasted space."
Amount of rows that could be stored in a block is affected by the value set for pctfree. If the pctfree is high the row count in a block goes down (useful in reducing hot blocks) and when pctfree is low the row count inside the block goes high (useful for full table scans). Size of the block and the length of the row also plays a part in the row count inside the block.
Following is a simple test to illustrate this point. Two tables are created identical in all aspects except the pctfree value. Both reside in a tablespace which has a block size of 2K.

create table pct0 (id varchar2(100)) tablespace tbs2k pctfree 0;
create table pct99 (id varchar2(100)) tablespace tbs2k pctfree 99;

SQL> select table_name,pct_free from user_tables where table_name in ('PCT0','PCT99');

TABLE_NAME                       PCT_FREE
------------------------------ ----------
PCT99                                  99
PCT0                                    0

Following PL/SQL code block will insert 100 rows to each table

declare
x varchar2(40);
begin
  for i in 1 .. 100
    loop
    x := dbms_random.string('x',36);
    insert into pct0 values (x);
    insert into pct99 values (x);
    commit;
  end loop;
end;
/

There are two ways to get the row count inside a block either by using dbms_rowid package or decoding the rowid.
Using dbms_rowid package on pct0 table

SQL> select DBMS_ROWID.ROWID_OBJECT(rowid) "OBJECT",
  2            DBMS_ROWID.ROWID_RELATIVE_FNO(rowid) "FILE",
  3             DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid) "BLOCK",count(*) from pct0
  4             group by DBMS_ROWID.ROWID_OBJECT(rowid), DBMS_ROWID.ROWID_RELATIVE_FNO(rowid), DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid);

    OBJECT       FILE      BLOCK   COUNT(*)
---------- ---------- ---------- ----------
    103409         14     206983        100

which tells that all the rows are in one block. Querying the pct99 table

SQL> select DBMS_ROWID.ROWID_OBJECT(rowid) "OBJECT",
  2            DBMS_ROWID.ROWID_RELATIVE_FNO(rowid) "FILE",
  3             DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid) "BLOCK",count(*) from pct99
  4             group by DBMS_ROWID.ROWID_OBJECT(rowid), DBMS_ROWID.ROWID_RELATIVE_FNO(rowid), DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid);

    OBJECT       FILE      BLOCK   COUNT(*)
---------- ---------- ---------- ----------
    103411         14     206998          1
    103411         14     207007          1
    103411         14     207014          1
    103411         14     207017          1
    103411         14     207041          1
    103411         14     207049          1
..
..
..
    OBJECT       FILE      BLOCK   COUNT(*)
---------- ---------- ---------- ----------
    103411         14     207098          1

100 rows selected.

which tells that each row is stored in single block and 100 blocks are needed to store all the rows.

select rows_per_block,count(*) from (select substr(rowid,10,6) as block,count(*) as rows_per_block 
from pct0 group by substr(rowid,10,6)) group by rows_per_block order by 1;

ROWS_PER_BLOCK   COUNT(*)
-------------- ----------
           100          1


select rows_per_block,count(*) from (select substr(rowid,10,6) as block,count(*) as rows_per_block 
from pct99 group by substr(rowid,10,6)) group by rows_per_block order by 1;

ROWS_PER_BLOCK   COUNT(*)
-------------- ----------
             1        100

As more blocks are used the space usage will also increase compared to pct0 table above.

SQL> select segment_name as table_name, bytes/1024 as "Size KB" from user_segments where segment_name IN ('PCT0','PCT99');

TABLE_NAME    Size KB
---------- ----------
PCT0               64
PCT99             896

Multiple factors must be considered when setting the pctfree value such as the trade-offs between increase in storage vs reduction in IO (in FTS), the nature of access (FTS vs single block access) to the segment.