Search

Top 60 Oracle Blogs

Recent comments

September 2007

Dictionary long application

Two versions of an application to encapsulate the data dictionary views that have LONG columns. LONGs are converted to CLOBs in pipelined functions, enabling us to search, copy and manipulate these columns with string functions. A static version supports 9.2 and 10.1 and a dynamic ANYDATASET version supports 10.2 onwards. August 2007 (updated July 2010)

How to resolve SQL object and column names all the way to base tables and columns in Oracle?

Update: As this original article is from 2007, there are better options available in the modern times – for example DBMS_UTILITY.EXPAND_SQL_TEXT as explained by Maria Colgan’s blog entry here.

If you have been involved in tuning SQL code which you have never seen before, you are probably familiar with the challenges of understanding what the code is trying to do. This can be especially time consuming when the SQL references lots of views, which reference views, which reference more views etc. So there may be a large information gap between the SQL statement (like select * from some_crazy_10_level_view) and the actual execution plan (referencing 10s of tables, with evidence of query transformations).

How to resolve SQL object and column names all the way to base tables and columns in Oracle?

Update: As this original article is from 2007, there are better options available in the modern times – for example DBMS_UTILITY.EXPAND_SQL_TEXT as explained by Maria Colgan’s blog entry here.

If you have been involved in tuning SQL code which you have never seen before, you are probably familiar with the challenges of understanding what the code is trying to do. This can be especially time consuming when the SQL references lots of views, which reference views, which reference more views etc. So there may be a large information gap between the SQL statement (like select * from some_crazy_10_level_view) and the actual execution plan (referencing 10s of tables, with evidence of query transformations).

How to resolve SQL object and column names all the way to base tables and columns in Oracle?

Update: As this original article is from 2007, there are better options available in the modern times – for example DBMS_UTILITY.EXPAND_SQL_TEXT as explained by Maria Colgan’s blog entry here.

If you have been involved in tuning SQL code which you have never seen before, you are probably familiar with the challenges of understanding what the code is trying to do. This can be especially time consuming when the SQL references lots of views, which reference views, which reference more views etc. So there may be a large information gap between the SQL statement (like select * from some_crazy_10_level_view) and the actual execution plan (referencing 10s of tables, with evidence of query transformations).

How to resolve SQL object and column names all the way to base tables and columns in Oracle?

Update: As this original article is from 2007, there are better options available in the modern times – for example DBMS_UTILITY.EXPAND_SQL_TEXT as explained by Maria Colgan’s blog entry here.

If you have been involved in tuning SQL code which you have never seen before, you are probably familiar with the challenges of understanding what the code is trying to do. This can be especially time consuming when the SQL references lots of views, which reference views, which reference more views etc. So there may be a large information gap between the SQL statement (like select * from some_crazy_10_level_view) and the actual execution plan (referencing 10s of tables, with evidence of query transformations).

Dml error logging performance in 10g release 2

Performance characteristics of DML error logging. August 2005

Advanced Oracle Troubleshooting Guide, Part 3: More adventures in process stack

…or rather thread stack as nowadays decent operating systems execute threads (or tasks as they’re called in Linux kernel).

Anyway, stack trace gives you the ultimate truth on what your program is doing, exactly right now. There are couple of but’s like stack corruptions and missing symbol information which may make the traces less useful for us, but for detailed hang & performance troubleshooting the stack traces are a goldmine.

So, I present another case study – how to diagnose a complete database hang when you can’t even log on to the database.

Advanced Oracle Troubleshooting Guide, Part 3: More adventures in process stack

…or rather thread stack as nowadays decent operating systems execute threads (or tasks as they’re called in Linux kernel).

Anyway, stack trace gives you the ultimate truth on what your program is doing, exactly right now. There are couple of but’s like stack corruptions and missing symbol information which may make the traces less useful for us, but for detailed hang & performance troubleshooting the stack traces are a goldmine.

So, I present another case study – how to diagnose a complete database hang when you can’t even log on to the database.

Advanced Oracle Troubleshooting Guide, Part 3: More adventures in process stack

…or rather thread stack as nowadays decent operating systems execute threads (or tasks as they’re called in Linux kernel).

Anyway, stack trace gives you the ultimate truth on what your program is doing, exactly right now. There are couple of but’s like stack corruptions and missing symbol information which may make the traces less useful for us, but for detailed hang & performance troubleshooting the stack traces are a goldmine.

So, I present another case study – how to diagnose a complete database hang when you can’t even log on to the database.

Advanced Oracle Troubleshooting Guide, Part 3: More adventures in process stack

…or rather thread stack as nowadays decent operating systems execute threads (or tasks as they’re called in Linux kernel).

Anyway, stack trace gives you the ultimate truth on what your program is doing, exactly right now. There are couple of but’s like stack corruptions and missing symbol information which may make the traces less useful for us, but for detailed hang & performance troubleshooting the stack traces are a goldmine.

So, I present another case study – how to diagnose a complete database hang when you can’t even log on to the database.