Positive on 11g : v$active_session_history column TEMP_SPACE_ALLOCATED

Tired of analysing “ORA-1652: unable to extend temp segment by 128 in tablespace TEMP” ?

The very last column of v$active_session_history is TEMP_SPACE_ALLOCATED in 11g.

dba_hist_active_sess_history has it too.

 

 

Posted in Uncategorized | Leave a comment

Upgrade to 11g: 11g raises error but Oracle 10g not for ambiguous ANSI joins

Beware:

Metalink note: Bug 5368296  ANSI join SQL may not report ORA-918 for ambiguous column

Long story short: Oracle 10g does not raise error for ambiguous columns missing aliases. Oracle 11g catches the ambiguity meaning that for good or for bad your application won’t work in 11g.

 

Posted in Uncategorized | Leave a comment

Upgrade to 11g: XML function ExtractValue: Additional Spaces and Line Feed

Metalink note:

“Additional Spaces and Line Feed Added When Extracting Element With ExtractValue() [ID 369934.1] “

The XML function ExtractValue in 10g Oracle strips spaces and line feeds but in Oracle 11g preserves. Oracle 11g behavior is according to the standard. Oracle 10g is wrong. However the pl/sql code written for 10g fails on 11g.

One of possible workarrounds is to  modify the source code to trim spaces and line feeds:

RTRIM(TRIM(RTRIM(TRIM(extractValue(c, xpath, namespace)),chr(10))),chr(10))

Another possible solution would be to overload extractValue. However extractValue seems to be inbuilt SQL function meaning overloading failed (for me.)

 

 

Posted in Uncategorized | Leave a comment

RMAN restore slow: 128K write buffer size

Oracle version 10.2 (10.2.0.4)

According to “RMAN Restore Performance from Tape is Very Poor (Doc ID 850988.1)” the alter system set “_BACKUP_KSFQ_BUFSZ”=1048576; should solve the issue of small write chunks.

Remains to be tested but it looks promising.

Posted in Uncategorized | Leave a comment

OMON: analysis of one incident

Storage syncronization to mirror took too long at 11′th Apr.

Omon “physical reads” graphs indicated a clear peek of physical reads.

24 hours graph:

1

 

A few weeks graph:

 

 

2

A few weeks graph aggregated into 200 bars:

3

AWR report indicates outstanding SQL:

4The sql itself is easy to tune by forcing index only acccess (not table reads) and a better index.

 

 

Posted in Uncategorized | Leave a comment

Bugs series: Skip using IGNORE_ROW_ON_DUPKEY_INDEX Hint in 11.2. Wait until 12c

Skip using IGNORE_ROW_ON_DUPKEY_INDEX Hint in 11.2. Wait until 12c. Your data can be corrupted by letting in duplicate rows.

Patch which might help is 11865420. However if PSU’s are installed on top of 11.2 then patch merge request must be issued to Oracle.

The IGNORE_ROW_ON_DUPKEY_INDEX hint stands on itself – it changes execution semantics. Looks like it is a last minute change in Oracle 11.

 

 

Posted in Uncategorized | Leave a comment

OMON – a browser based tool to browse AWR/PERFSTAT

Omon is a tool to output in graphical form AWR/PERFSTAT data.

All it needs is a running Oracle HTTP server in any form (as long as it supports mod_plsql) and 10g database to hold OMON PL/SQL stored procedures and few tables.

Installation zip file is under Google docs:

https://docs.google.com/open?id=0BwJA-n3_efqsWjZJejhRblBJdUE

( it wasn’t tested on RAC. I think it will aggregate statistics over all instances… )

TOP Events graph screenshot

TOP Events screenshot

Events graph screenshot:

OMON events screenshot

Statistics screenshot:

Statistics screenshot

Add database page:

Add database page screenshot

Upcomming:

Peek search module. Peek search module searches all statistics for peeks in a given interval:

Statistics peek search page

In the plans:

* drill-down possibility in graph pages. For example, pressing on “db block reads” graph column will open SQL statements ordered by db block reads.

* Collecting additional data to that of Perfstat/AWR. For example, 10g does not snapshot v$events_histogram. Snapshots of particular session level statistics like IO.  Snapshots of v$plan_statistics.  OMON repository was in the first versions of OMON but for popular use the repository must be “reimagined”.

Posted in Uncategorized | 3 Comments