Top 60 Oracle Blogs

Recent comments

November 2017

Unstructured vs. structured

The title of this blog post was: “Tracing DBMS_RCVMAN for reclaimable archivelogs” until I started to write the conclusion…

Great Britain and Northern Ireland February 2018 Dates: “Oracle Indexing Internals and Best Practices” Seminar (Battle For Britain)

UPDATE: ALL TICKETS ARE NOW AVAILABLE FOR PURCHASE !! Attention Oracle Professionals in the United Kingdom !! I have now finalised all the dates and venues for a series of my popular and critically acclaimed “Oracle Indexing Internals and Best Practices” seminar I’ll be running in the UK in February 2018. I’m extremely excited as […]

CBO, FIRST_ROWS and VIEW misestimate

There are several bugs with the optimizer in FIRST_ROWS mode. Here is one I encountered during a to migration when a view had an ‘order by’ in its definition.

Here is the test case that reproduces the problem.

A big table:

SQL> create table DEMO1 (n constraint DEMO1_N primary key,x,y) as select 1/rownum,'x','y' from xmltable('1 to 1000000');
Table DEMO1 created.

with a view on it, and that view has an order by:

SQL> create view DEMOV as select * from DEMO1 order by n desc;
View DEMOV created.

and another table to join to:

SQL> create table DEMO2 (x constraint DEMO2_X primary key) as select dummy from dual;
Table DEMO2 created.

My query reads the view in a subquery, adds a call to a PL/SQL function, and joins the result with the other table:

Considerations When Using SQL Server 2016 Dynamic Data Masking

SQL Server 2016 introduced a new security feature called Dynamic Data Masking.  With the General Data Protection Regulations, (GDPR) breathing heavy down most IT in America’s neck, its good timing.

Dynamic Sampling vs. Extended Statistics

On datawarehouse databases, I frequently recommend increasing the level of dynamic sampling because:

nVision Performance Tuning: 7 Analysis of Tree Usage with the Selector Log

This blog post is part of a series that discusses how to get optimal performance from PeopleSoft nVision reporting as used in General Ledger.

Over time, the selector log will build up a picture of how each tree is used in a system. Here are two examples of how it can be used.

You may look at a piece of SQL generated by nVision, it will have a literal value for the selector number, and you want to know about that particular selector.

#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 85%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">REM treeanal.sql
WITH t as (
SELECT DISTINCT d.tree_name, s.dtl_fieldname, d.tree_acc_method, d.tree_acc_Selector, d.tree_acc_sel_opt

So good … it’s scary

Well…this isn’t a horror movie, it’s something awesome

#Accenture #Enkitec Group at #DOAG2017

Yes, it’s that time of the year again when DOAG (Deutsche Oracle Anwender Gruppe) is about to host its annual conference – probably the largest Oracle event in Europe!

Friday Philosophy – What Makes a Community?

Earlier this week Daniel Westermann asked a question on twitter: “What is community?”

Azure SQL Database- Automatic Index Management

As I begin my education in SQL Server 2017, as well as Azure SQL Database, my attention was caught by the optimizer improvements and especially automatic tuning.