I have added a new book review to my bookshelf: Expert Oracle JDBC – J.R. Menon.

Today, I tried to get a nice, clean report about objects with reclaimable space from Segment Advisor. It is no problem to display the list in Enterprise Manager Grid|DB Control, but it is not so easy in SQL*Plus.

This is what i ended up with:

 SELECT
  segment_owner ,
  segment_name,
  round(allocated_space/1024/1024) ALLOC_MB ,
  round(used_space/1024/1024) USED_MB ,
  round(reclaimable_space/1024/1024) RECLAIM_MB    ,
  (1-ROUND((used_space/allocated_space),2))*100 AS reclaim_pct
   FROM TABLE(dbms_space.asa_recommendations('TRUE', 'TRUE', 'FALSE'))
  WHERE tablespace_name IN ('TS_DATA')
AND segment_type         = 'TABLE'
AND segment_owner LIKE '%'
AND segment_name LIKE '%'
AND (reclaimable_space >= 1000000
         OR (((1-ROUND((used_space/allocated_space),2))*100)) > 30)
ORDER BY reclaimable_space DESC

The default RAC installation does normally not set “local_listener” init.ora parameter. If the listener is running on port 1521, then the database does not need the parameter in order to find and register with the local TNS listener process. However, if you have *not* set local_listener, it means that the database registers at the listener with the physical IP address instead of the virtual (vip) address.

You can determine if this happens when you take a look at “lsnrctl serv” output from your rac nodes:

Service "S_APP" has 1 instance(s).
  Instance "MDDB1", status READY, has 2 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=ora-vm1.intra)(PORT=1521))

Instead of ora-vm1.intra, this should be ora-vm1-vip.intra.

Why should I care?

If you use the default configuration, then you are using the parameter “REMOTE_LISTENER” and therefore Server Side Connect Time Load Balancing. This means, that the listeners of all nodes receive load information from all instances of all nodes and they can redirect connections to the least loaded instance, even if the instance is on another node. But the connect string they then send back to the client contains the physical IP address instead of the virtual.

In case of node crashes or kernel panics, etc. the client has to wait for the TCP timeout until this is detected.

Solution

tnsnames.ora:

LISTENER_MDDB1 =
  (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ora-vm1-vip.intra)(PORT = 1521))
  )

LISTENER_MDDB2 =
  (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ora-vm2-vip.intra)(PORT = 1521))
  )

init.ora:
alter system set local_listener = 'LISTENER_MDDB1' sid='MDDB1';
alter system set local_listener = 'LISTENER_MDDB2' sid='MDDB2';

One of my customers is having severe RAC performance issues, which appeared a dozen times so far. Each time, the performance impact lasted around 10 minutes and caused basically a hang of the application. ASH investigation revealed that the time frame of performance issues exactly matches a DRM operation of the biggest segment of the database. During the problematic time period, there are 20-50 instead of 5-10 active sessions and they are mostly waiting for gc related events: “gc buffer busy”,”gc cr block busy”, “gc cr block 2-way”, “gc current block 2-way”, “gc current request”, “gc current grant busy”, etc.

In addition, there is one single session which has wait event “kjbdrmcvtq lmon drm quiesce: ping completion” (on instance 1) and 1-3 sessions with wait event “gc remaster“. (on instance 2) The cur_obj# of the session waiting on “gc remaster” is pointing to the segment being remastered.

Does anybody have any experience with DRM problems with 10.2.0.4 on Linux Itanium?

I know that it is possible to deactive DRM, but usually it should be beneficial to have it enabled. I could not find any reports of performance impact during DRM operation on metalink. Support is involved but clueless so far.

Regards,
Martin

http://forums.oracle.com/forums/message.jspa?messageID=3447436#3447436

We recently experienced a big issue, when the production database was hung. It turned out that the database has deadlocked, but the GES did not detect the deadlock situation, so all the sessions were waiting on “enq: TX row lock contention”.

We could provide a reproducible testcase and it turned out to be bug 7014855. The bug is platform specific to Linux Itanium port and a patch is available.

I recently had to analyze a system in respect of index utilization. The application has dozens of indexes and we have to find out, which ones are really needed and which ones are candidates for removal.

There is a nice feature, called Index Monitoring which switches a flag if the index is used.

1

ALTER INDEX schema.INDEX MONITORING USAGE;

Internally, a recursive SQL Statement is executed for every parsing of a SQL:

1

UPDATE object_usage SET flags=flags+1 WHERE obj#=:1 and bitand(flags, 1)=0;

To check whether the index was used, you can query the view v$object_usage. Unfortunately, the view definition contains a where clause to list only indexes of the current schema.

1

   WHERE    io.owner# = userenv('SCHEMAID')

Therefore, you can create a new view or just omit the limitation from the where clause:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

 CREATE OR REPLACE VIEW V$ALL_OBJECT_USAGE 
         (OWNER,
         INDEX_NAME, 
         TABLE_NAME, 
         MONITORING, 
         USED, 
         START_MONITORING, 
         END_MONITORING) 
         AS 
           SELECT u.name, io.name, t.name, 
           decode(bitand(i.flags, 65536), 0, 'NO', 'YES'), 
           decode(bitand(ou.flags, 1), 0, 'NO', 'YES'), 
           ou.start_monitoring, 
           ou.end_monitoring 
         FROM sys.user$ u, sys.obj$ io, sys.obj$ t, sys.ind$ i, sys.object_usage ou 
         WHERE i.obj# = ou.obj# 
         AND io.obj# = ou.obj# 
         AND t.obj# = i.bo#
         AND u.user# = io.owner#

However, what is less known is that the DBMS_STATS can in some cases manipulate these flags. The issue is tracked with “Bug 6798910 – DBMS_STATS or EXPLAIN PLAN trigger index usage monitoring”. If you are using 10.2.0.4 and you are gathering stats manually with “CASCADE=>TRUE”, then the monitoring flag is set to “YES”. Obviously, in this case you can not identify candidate indexes for removal.

I came across a metalink note (Note 786507.1) which describes a very short way to identify the blocker of a session waiting on mutex “cursor: pin S wait on X”. Previously, I thought it could only be analyzed with system state dumps, but the note describes that parameter P2RAW of wait event “cursor: pin S wait on X” contains the blocking session id and the refcount. For 32bit environments the field contains 4 bytes (2 bytes for session id, 2 bytes for refcount). On 64 bit environments, the field contains 8 bytes (4 vs. 4).

select p2raw from v$session where event = ‘cursor: pin S wait on X’

The first 2 or 4 bytes from p2raw have to be converted to dec and point to the blocking Session ID. (SID)

On the oracle-l list, there was a post about a nasty bug in Oracle RAC 10.2.0.4 which can cause LGWR errors, instance failures and redo log corruptions.

The bug occurs if you add/remove redo log groups on a cluster database, which has some of it´s instances shut down.

More information about this Bug, which is tracked with Bug ID 6786022 can be found on MetaLink. There is a patch for 10.2.0.4 and Linux x86-64 available.

During the Hotsos Symposium 2009 Training Day, Jonathan Lewis presented a problem which appears even on current 10g/11g databases. What is especially interesting is how this issue can be diagnosed. I reproduced the problem in 11.1.0.7 and will provide the steps you can use to verify. The problem can be demonstrated best when comparing response time of an update statement for 8k blocksize and 16k blocksize.

• 8k Blocksize

SQL> DROP TABLE t1_8k purge;
 
SQL> CREATE TABLESPACE DEMO8K datafile SIZE 128M blocksize 8192;
 
TABLESPACE created.
 
Elapsed: 00:00:07.35
 
SQL> CREATE TABLE mdecker.t1_8k
(n1 NUMBER,
 n2 NUMBER)
 TABLESPACE DEMO8K;
 
TABLE created.
 
Elapsed: 00:00:00.02
 
SQL> INSERT INTO t1_8k
SELECT TRUNC(dbms_random.VALUE(10000000,100000000)) n1,
           TO_NUMBER(NULL) AS n2
    FROM dual
CONNECT BY LEVEL <= 500000
/
 
 
500000 ROWS created.
 
Elapsed: 00:00:06.89
 
SQL> BEGIN dbms_stats.gather_table_stats(
        ownname => 'MDECKER',
        tabname => 'T1_8K',
        estimate_percent => 100);
END;
/
 
PL/SQL PROCEDURE successfully completed.
 
Elapsed: 00:00:02.13
 
SQL> SELECT num_rows,blocks FROM dba_tables WHERE table_name = 'T1_8K';
 
  NUM_ROWS     BLOCKS
---------- ----------
    500000        874
 
Elapsed: 00:00:00.15
SQL> UPDATE t1_8k SET n2 = n1;
 
500000 ROWS updated.
 
Elapsed: 00:01:09.04
SQL> COMMIT;

• 16k Blocksize

SQL> CREATE TABLESPACE DEMO16K datafile SIZE 128M blocksize 16384;
 
TABLESPACE created.
 
Elapsed: 00:00:14.75
 
SQL> CREATE TABLE mdecker.t1_16k
(n1 NUMBER,
 n2 NUMBER)
 TABLESPACE DEMO16K;
 
TABLE created.
 
Elapsed: 00:00:00.03
 
SQL> INSERT INTO t1_16k
SELECT TRUNC(dbms_random.VALUE(10000000,100000000)) n1,
           TO_NUMBER(NULL) AS n2
    FROM dual
CONNECT BY LEVEL <= 500000
/  
 
 
500000 ROWS created.
 
Elapsed: 00:00:05.51
SQL> BEGIN dbms_stats.gather_table_stats(
        ownname => 'MDECKER',
        tabname => 'T1_16K',
        estimate_percent => 100);
END;
/  
 
PL/SQL PROCEDURE successfully completed.
 
Elapsed: 00:00:01.78
SQL> SELECT num_rows,blocks FROM dba_tables WHERE table_name = 'T1_16K';
 
  NUM_ROWS     BLOCKS
---------- ----------
    500000        436
 
Elapsed: 00:00:00.07
 
SQL>  UPDATE t1_16k SET n2 = n1;
 
500000 ROWS updated.
 
Elapsed: 00:20:20.89

As you can see, the update statement for the 8k blocksize table took around 69 seconds whereas the same update for the table in the 16k tablespace took more than 20 minutes. When executing oradebug short_stack, you can see that for the 16k update, the stacktrace is similar for many executions. So, a lot of time is spent in the kernel functions ktspfsrch() and ktspscan_bmb().


SQL> oradebug setospid 23668
Oracle pid: 18, Unix process pid: 23668, image: oracle@ora-vm1.intra (TNS V1-V3)
SQL> oradebug short_stack
.....ktspfsrch()+559<-ktspscan_bmb()+315 .....
SQL> oradebug short_stack
.....ktspfsrch()+559<-ktspscan_bmb()+315 .....
SQL> oradebug short_stack
.....ktspfsrch()+559<-ktspscan_bmb()+315 .....


It is important to understand that the problem is not necessarily related to the blocksize, but to the PCTFREE value. More information about this topic can be found here:

http://structureddata.org/files/jl_test_case.html
http://structureddata.org/2008/09/08/understanding-performance/
http://www.oraclealchemist.com/oracle/hey-guys-does-size-matter/

This question came up during the excellent presentation of Dave Abercrombie – A Tour of the AWR Tables at the Hotsos Symposium 2009.

Nobody knew the answer but curious as I am, I wanted to know:

Where is the data stored?

SQL>  select owner, object_name, object_type from dba_objects where object_name = ‘DBA_HIST_SQLTEXT’;

OWNER      OBJECT_NAME                    OBJECT_TYPE
———- —————————— ——————-
SYS        DBA_HIST_SQLTEXT               VIEW
PUBLIC     DBA_HIST_SQLTEXT               SYNONYM

SQL> select text from dba_views where view_name = ‘DBA_HIST_SQLTEXT’;

TEXT
——————————————————————————–
select dbid, sql_id, sql_text, command_type
from WRH$_SQLTEXT

SQL> select owner, object_name, object_type, object_id, data_object_id  from dba_objects where object_name = ‘WRH$_SQLTEXT’;

OWNER      OBJECT_NAME                    OBJECT_TYPE          OBJECT_ID DATA_OBJECT_ID
———- —————————— ——————- ———- ————–
SYS        WRH$_SQLTEXT                   TABLE                     8996           8996
=> Not partitioned.

SQL> select min(snap_id), max(snap_id) from wrh$_SQLTEXT;

MIN(SNAP_ID) MAX(SNAP_ID)
———— ————
3040         3274

We could guess that snap_ids before 3040 were removed but I want to know. (BAAG)  I could monitor the table over a period of time and check min(snap_id) but I can also query the backed up object statistics, specifically the minimum value for column snap_id:

select to_char(savtime,’DD.MM.YYYY’), minimum, maximum, distcnt,  sample_size
from WRI$_OPTSTAT_HISTHEAD_HISTORY where obj# = 8996 and intcol# = 1;
2
TO_CHAR(SA    MINIMUM    MAXIMUM    DISTCNT SAMPLE_SIZE
———- ———- ———- ———- ———–
16.02.2009       2229       2461        114         455
17.02.2009       2296       2535        122         471
18.02.2009       2326       2559        114         469
19.02.2009       2346       2584        119         476
20.02.2009       2370       2607        108         448
21.02.2009       2394       2632        112         437
23.02.2009       2417       2640        104         412
24.02.2009       2471       2703        113         400
25.02.2009       2495       2728         94         394
26.02.2009       2516       2750        101         396
27.02.2009       2536       2776        103         322
28.02.2009       2560       2799         93         310
02.03.2009       2585       2807         88         303
03.03.2009       2637       2871         92         289
04.03.2009       2693       2895         85         287
05.03.2009       2693       2919         91         298
06.03.2009       2704       2943         86         294
09.03.2009       2730       2967         92         303
10.03.2009       2801       3039        104         303
11.03.2009       2836       3063         94         304
12.03.2009       2856       3087         99         303
13.03.2009       2872       3111        101         301
14.03.2009       2896       3135         99         299
16.03.2009       2921       3144         95         292
17.03.2009       2976       3206         94         292
18.03.2009       3003       3232         94         305

26 rows selected.

We can see that at the stats gathering during the last 26 days, every day, the min value increased. So, the answer is yes.