Sunday, February 20, 2011

Redo Log Switches at their Peak

One of our customers was having extreme performance issues with their Oracle database. During my investigations I found they had 215 redo log switches per hour. This means a 3.65 log switches per minute or a redo log switch every 16.7 seconds.

Although this was not the only reason of performance issues but this was certainly one of the causes. The redo log files were sized pretty small and the poor database was spending most of its time switch the redo log files.

Here’s a screenshot for you guys:

Below is the query I use to find redo log switches. This query lists redo log switches per hour for the last seven days.

column day format a10
column Switches_per_day format 9999
column 00 format 999
column 01 format 999
column 02 format 999
column 03 format 999
column 04 format 999
column 05 format 999
column 06 format 999
column 07 format 999
column 08 format 999
column 09 format 999
column 10 format 999
column 11 format 999
column 12 format 999
column 13 format 999
column 14 format 999
column 15 format 999
column 16 format 999
column 17 format 999
column 18 format 999
column 19 format 999
column 20 format 999
column 21 format 999
column 22 format 999
column 23 format 999

select to_char(first_time,'DD-MON') day,
sum(decode(to_char(first_time,'hh24'),'00',1,0)) "00",
sum(decode(to_char(first_time,'hh24'),'01',1,0)) "01",
sum(decode(to_char(first_time,'hh24'),'02',1,0)) "02",
sum(decode(to_char(first_time,'hh24'),'03',1,0)) "03",
sum(decode(to_char(first_time,'hh24'),'04',1,0)) "04",
sum(decode(to_char(first_time,'hh24'),'05',1,0)) "05",
sum(decode(to_char(first_time,'hh24'),'06',1,0)) "06",
sum(decode(to_char(first_time,'hh24'),'07',1,0)) "07",
sum(decode(to_char(first_time,'hh24'),'08',1,0)) "08",
sum(decode(to_char(first_time,'hh24'),'09',1,0)) "09",
sum(decode(to_char(first_time,'hh24'),'10',1,0)) "10",
sum(decode(to_char(first_time,'hh24'),'11',1,0)) "11",
sum(decode(to_char(first_time,'hh24'),'12',1,0)) "12",
sum(decode(to_char(first_time,'hh24'),'13',1,0)) "13",
sum(decode(to_char(first_time,'hh24'),'14',1,0)) "14",
sum(decode(to_char(first_time,'hh24'),'15',1,0)) "15",
sum(decode(to_char(first_time,'hh24'),'16',1,0)) "16",
sum(decode(to_char(first_time,'hh24'),'17',1,0)) "17",
sum(decode(to_char(first_time,'hh24'),'18',1,0)) "18",
sum(decode(to_char(first_time,'hh24'),'19',1,0)) "19",
sum(decode(to_char(first_time,'hh24'),'20',1,0)) "20",
sum(decode(to_char(first_time,'hh24'),'21',1,0)) "21",
sum(decode(to_char(first_time,'hh24'),'22',1,0)) "22",
sum(decode(to_char(first_time,'hh24'),'23',1,0)) "23",
count(to_char(first_time,'MM-DD')) Switches_per_day
from v$log_history
where trunc(first_time) between trunc(sysdate) - 6 and trunc(sysdate)
group by to_char(first_time,'DD-MON') 
order by to_char(first_time,'DD-MON') ;

Updated: The redo log files were sized at 25 MB. To relieve the database from this pain, I increased the size of the redo log files to 1 GB each.

Updated-2: Here is the post related to this.

Wednesday, February 09, 2011

Shared SQL and Cursors

Recently I had a discussion on “Shared SQLs and Cursors” with one of my colleagues and during the discussion it came out that “shared SQL” and “cursors” are misunderstood. I thought of blogging on this topic to help those who still misunderstand these topics.

As per his understanding “Shared SQL” and “Cursors” are the same and shared among all user sessions. But in fact:

Shared SQL is the SQL residing in the shared pool. This SQL statement can be shared among all the database sessions. The shared SQL is at the database level, all sessions can see and use them.

A Cursor points to some shared SQL residing in the shared pool. You may have more than one cursor pointing to the same shared SQL. A cursor is at the session level. So, many database sessions may point to the same shared SQL.

The below diagram should help understand this concept:

Below is a demonstration of the same. I have four database connections, one as “SYS” user and other three connected as user “TEST”. I make a note of SIDs of each database connection as user “TEST”. My session ids are listed below:

“TEST” User ---------> SID

TEST Session – 1 ---------> 30

TEST Session – 2 ---------> 27

TEST Session – 3 ---------> 26

I shall be using table “T1” for this test which is residing in “TEST” schema.

SQL> desc t1
 Name             Null?    Type
 ---------------- -------- -------------
 ID               NOT NULL NUMBER
 NAME             NOT NULL VARCHAR2(30)

SQL>
Below is the query that will help us with our experiment. Make sure that you use the same query in all the sessions. For simplicity you could save this query in a file.
select /*momen*/ * from t1 where id = 1;

Session – SYS:

As user SYS, flush the shared pool and query V$SQL and V$OPEN_CURSOR views for our query:

SQL> alter system flush shared_pool;

System altered.

SQL> select parse_calls, executions, invalidations, loads, sql_id, substr(sql_text, 1, 20) from v$sql where sql_text like '%momen%';

PARSE_CALLS EXECUTIONS INVALIDATIONS      LOADS SQL_ID        SUBSTR(SQL_TEXT,1,20
----------- ---------- ------------- ---------- ------------- --------------------
          1          1             0          1 fnndahay4xbgv select parse_calls,

SQL>
SQL> select sid, sql_id, sql_text from v$open_cursor where user_name='TEST' and sql_text like '%momen%';

no rows selected

SQL>

Rightly, this query is not found in the shared pool.

TEST Session – 1:

Execute the query in session – 1

SQL> select /*momen*/ * from t1 where id = 1;

        ID NAME
---------- ------------------------------
         1 ICOL$a

SQL>

Session – SYS:

As the user SYS again query the V$SQL and V$OPEN_CURSOR view for our query.

SQL> select parse_calls, executions, invalidations, loads, sql_id, substr(sql_text, 1, 20) from v$sql where sql_text like '%momen%';

PARSE_CALLS EXECUTIONS INVALIDATIONS      LOADS SQL_ID        SUBSTR(SQL_TEXT,1,20
----------- ---------- ------------- ---------- ------------- --------------------
          1          1             0          1 fvnwsdghq898n select /*momen*/ * f
          2          2             0          1 fnndahay4xbgv select parse_calls,
          1          1             0          1 6wcgj9dxb3yvx select sid, sql_id,

SQL>
SQL> select sid, sql_id, sql_text from v$open_cursor where user_name='TEST' and sql_text like '%momen%';

       SID SQL_ID        SQL_TEXT
---------- ------------- ------------------------------------------------------------
        30 fvnwsdghq898n select /*momen*/ * from t1 where id = 1

SQL>

Our query now appears in both V$SQL & V$OPEN_CURSOR views. There’s one cursor now pointing to the shared SQL residing in the shared pool.

TEST Session – 2:

Now run the same query in another session.

SQL> select /*momen*/ * from t1 where id = 1;

        ID NAME
---------- ------------------------------
         1 ICOL$a

SQL>

Session – SYS:

Go back to the SQL*Plus session connection as user SYS and execute the same set of queries against V$SQL & V$OPEN_CURSOR.

 SQL> select parse_calls, executions, invalidations, loads, sql_id, substr(sql_text, 1, 20) from v$sql where sql_text like '%momen%';

PARSE_CALLS EXECUTIONS INVALIDATIONS      LOADS SQL_ID        SUBSTR(SQL_TEXT,1,20
----------- ---------- ------------- ---------- ------------- --------------------
          2          2             0          1 fvnwsdghq898n select /*momen*/ * f
          3          3             0          1 fnndahay4xbgv select parse_calls,
          2          2             0          1 6wcgj9dxb3yvx select sid, sql_id,

SQL>
SQL> select sid, sql_id, sql_text from v$open_cursor where user_name='TEST' and sql_text like '%momen%';

       SID SQL_ID        SQL_TEXT
---------- ------------- ------------------------------------------------------------
        30 fvnwsdghq898n select /*momen*/ * from t1 where id = 1
        27 fvnwsdghq898n select /*momen*/ * from t1 where id = 1

SQL>

As seen above, we now have two cursors pointing to the same shared SQL.

TEST Session – 3:

You may stop here or proceed to see how one more cursor is created to point to the same shared SQL in the shared pool.

SQL> select /*momen*/ * from t1 where id = 1;

        ID NAME
---------- ------------------------------
         1 ICOL$a

SQL>

Session – SYS:

SQL> select parse_calls, executions, invalidations, loads, sql_id, substr(sql_text, 1, 20) from v$sql where sql_text like '%momen%';

PARSE_CALLS EXECUTIONS INVALIDATIONS      LOADS SQL_ID        SUBSTR(SQL_TEXT,1,20
----------- ---------- ------------- ---------- ------------- --------------------
          3          3             0          1 fvnwsdghq898n select /*momen*/ * f
          4          4             0          1 fnndahay4xbgv select parse_calls,
          3          3             0          1 6wcgj9dxb3yvx select sid, sql_id,

SQL>
SQL> select sid, sql_id, sql_text from v$open_cursor where user_name='TEST' and sql_text like '%momen%';

       SID SQL_ID        SQL_TEXT
---------- ------------- ------------------------------------------------------------
        30 fvnwsdghq898n select /*momen*/ * from t1 where id = 1
        27 fvnwsdghq898n select /*momen*/ * from t1 where id = 1
        26 fvnwsdghq898n select /*momen*/ * from t1 where id = 1

SQL>

We now have three cursors pointing to the same shared SQL.

So, a cursor and shared SQL are two different things. The same shared SQL can have more than one cursor pointing to it.