Monday, April 16, 2007

Parsing in remote databases

I was trying to decrease the number of parses for a 9.2.0.7 production database when I realized that the top parsing sql was coming from another database through a database link. This sql has a parse/execute ratio of 1 meaning it is parsed every time it is called. I have searched the bug database and found that there is a bug about parsing when you are joining a local table with a remote table, the bug number is 4913460. For those who are using db links extensively the following is a test case showing the parsing problem in remote databases.

In the remote database create a simple table:


SQL> create table remote_table
2 as select level remote_a from dual connect by level <=1000;

Table created.

SQL> create index remote_ind1 on remote_table(remote_a);

Index created.

In the local database create a simple table:

SQL> create table local_table
2 as select level local_a from dual connect by level <=5000;

Table created.

SQL> create index local_index on local_table(local_a);

Index created.

Than create a database link connecting these two databases (commands not printed here) and query the local table joining it to the remote table:

declare
v_a number;
begin
for i in 1..1000 loop
select local_a into v_a from local_table
where local_a in (select remote_a from remote_table@test.world where remote_a=i);
end loop;
end;
/

In the remote database check v$sqlarea for the part of this sql (you can first get the hash_value by querying v$sqlarea with: sql_text like '%REMOTE_TABLE%'):

SQL> COLUMN sql_text FORMAT a30
SQL> select hash_value,sql_text,executions,parse_calls from v$sqlarea where hash_value=1971931531;

HASH_VALUE SQL_TEXT EXECUTIONS PARSE_CALLS
---------- ------------------------------ ---------- -----------
1971931531 SELECT "REMOTE_A" FROM "REMOTE 1000 1000
_TABLE" "REMOTE_TABLE" WHERE "
REMOTE_A"=:1


The sql in the remote database is parsed for every execution. In the local database again, try with EXISTS:

declare
v_a number;
begin
for i in 1..1000 loop
select local_a into v_a from local_table
where local_a=i and exists (select null from remote_table@test.world where remote_a=local_table.local_a);
end loop;
end;
/

The remote database shows:

SQL> r
1* select hash_value,sql_text,executions,parse_calls from v$sqlarea where hash_value=1971931531

HASH_VALUE SQL_TEXT EXECUTIONS PARSE_CALLS
---------- ------------------------------ ---------- -----------
1971931531 SELECT "REMOTE_A" FROM "REMOTE 2000 2000
_TABLE" "REMOTE_TABLE" WHERE "
REMOTE_A"=:1

This bug will be fixed in 11.1 but has a patch on 9.2.0.8.

Tuesday, April 10, 2007

The Read Consistency Trap in Sql Tracing

There are some important things to consider when interpreting sql trace files. One of the most important ones is the read consistency trap as it is called in the documentation.

If you see different number of block reads with the same data at different times when you trace a sql statement, this can be one reason. A test case showing what this means follows:


SQL> create table t as select * from all_objects;

Table created.

SQL> alter session set sql_trace=true;

Session altered.

SQL> begin
2 for t in (select * from t) loop
3 null;
4 end loop;
5 end;
6 /

PL/SQL procedure successfully completed.

After doing a full table scan from the above session, update all rows from another session:

SQL> update t set object_name='test';

23840 rows updated.

Execute the last sql from the first session again:

SQL> /

PL/SQL procedure successfully completed.


When you get the tkprof output with aggregate=no you can see the statistics for the two executions of the same statement differently without aggregation.

tkprof test_ora_6273.trc test.txt aggregate=no


In test.txt you can see the two executions of the statement:

SELECT *
FROM
T


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 23841 0.82 0.71 331 23844 0 23840
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 23843 0.82 0.71 331 23844 0 23840

SELECT *
FROM
T


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 23841 1.19 1.00 0 48015 0 23840
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 23843 1.19 1.00 0 48015 0 23840

Why did the numbers in the "query" column approximately double when the number of rows, the number of fetches did not change? Because there is an active transaction on the table which updated all the rows. So in order to get a read consistent view of the table the query goes to the undo blocks as well as table blocks.

What this suggests is; it is important when to trace an application. If the application you are trying to tune is running when there are transactions on the tables it accesses then you need to trace the application in that context. If you trace it when there are no transactions on its tables, you can come to the conclusion that the number of block reads is appropriate. Or you can focus on an sql taking much of the execution time when the sql you need to tune is actually another one.

Saturday, April 07, 2007

An effect of sql_trace=true

Tom Kyte has a recent post about explain plan.

In that there is something I have not realized before that I want to share. When you execute a sql statement after you set sql_trace=true it is hard parsed again even if it is already in the shared pool.

A simple test is below.


SQL> select 'test' from dual;

'TES
----
test

SQL> alter session set sql_trace=true;

Session altered.

SQL> select 'test' from dual;

'TES
----
test

SQL> exit


The tkprof output shows that the same statement run after setting sql_trace=true is hard parsed again.


select 'test'
from
dual


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 1 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 1 0 1

Misses in library cache during parse: 1




Compare this with the same sql run two times after setting sql_trace=true.


SQL> alter session set sql_trace=true;

Session altered.

SQL> select 'test2' from dual;

'TEST
-----
test2

SQL> r
1* select 'test2' from dual

'TEST
-----
test2


SQL> exit



select 'test2'
from
dual


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 4 0.00 0.00 0 2 0 2
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 8 0.00 0.00 0 2 0 2

Misses in library cache during parse: 1


Sql trace has the side effect of hard parsing every statement executed after it is enabled. If you have your sql statements already in the shared pool before enabling sql trace, be careful that you will be seeing the execution plans for your particular execution in that particular time, not the ones from the shared pool.

Wednesday, April 04, 2007

Client-side Load Balancing

A new case study has been published in Metalink about how to configure a high connect load environment. If you have an environment where bursts of logon activity appears sometimes and your tns listener is unable to cope with the demand then you can take a look at the note to see possible solutions. It is a simple note that basically describes how the tns listener works and what to when the listener cannot respond to connection requests.

There are mainly two solutions suggested:

1. Increasing the number of listeners
2. Using shared server

Increasing the number of listeners is the first logical solution that comes up. Because a single listener process can handle a limited number of requests at one time, multiple listener processes can handle many more requests. When you configure multiple listeners you also need to change the tns entries in your clients. The sample tns entry provided in that note is this:

GEORGE=
(DESCRIPTION=
(LOAD_BALANCE=ON)
(FAILOVER=ON)
(ADDRESS=(PROTOCOL=TCP)(HOST=george.us.oracle.com)(PORT=1521)
(ADDRESS=(PROTOCOL=TCP)(HOST=george.us.oracle.com)(PORT=1522)
(ADDRESS=(PROTOCOL=TCP)(HOST=george.us.oracle.com)(PORT=1523)
(ADDRESS=(PROTOCOL=TCP)(HOST=george.us.oracle.com)(PORT=1524)
(CONNECT_DATA=(SERVICE_NAME=V10R2.us.oracle.com)))

This ensures that connections are balanced between the specified ports, meaning different listener processes.

But there is one restriction that is not mentioned in the case study. If you have clients using Net8 (not higher versions) like Forms6i applications, the LOAD_BALANCE parameter does not exist, so you cannot load balance client connections using this entry. If you have such clients you need to use description lists in your tns entry as described in Metalink note 67137.1. The tns entry for load balancing in Net8 is like this:

TEST=
(DESCRIPTION_LIST=
(DESCRIPTION=
(ADDRESS=
(PROTOCOL=TCP)(HOST=host1)(PORT=1521)
)
(CONNECT_DATA=(SID=TEST))
)
(DESCRIPTION=
(ADDRESS=
(PROTOCOL=TCP)(HOST=host1)(PORT=1522)
)
(CONNECT_DATA=(SID=TEST))
)
)

Sure it is better to fix the application to correct the logon burst problem but in case you have a situation where you cannot fix the application for any reason it is good to be aware of these solutions.