Biggest difference between analyze and dbms_stats

Analyze collect statistics from the indexes associated within a table and dbms_stats don´t.

Using dbms_stats you should add the parameter cascade=>true to accomplish the same result.

Small list of bugs that lead Oracle to choose the wrong path

At OTN I saw this, and it´s quite usefull to check over time if your Oracle version still suffer from these bugs.

CBO does not consider cheaper NL-Plan without hints

Improving performance with foreign key

Oracle statistics are single table  only (may be version 12 will come with relationship statistic)

When you have a subquery with FKs between then, you may know that for each query at the child table, it will have just one row on the parent table,but Oracle is blind in this matter.

Then Oracle make some math to guess how it could be the selectivity on the main table. And usually it goes wrong and sometimes very wrong.

So the only way to give to Oracle a perfect picture, is creating the foreign key (yes that foreign key that you avoided creating due to “never will be violated”) Than Oracle will give you a much better plan.

I´d decide write about it after answer this post at OTN Different Execution paths for similar query.

How to control the speed of archive generation during an Oracle import

The scenario is an Oracle 10g, importing a table from 35GB dump file inside a test instance, with small archive area and running a simultaneous delete at the same table.(therefore we should use commit=y)

Another huge constraint is that the archive backup run each other hour and can´t be changed.

The bottom line is that I need something like a parameter MAXIMUM_ROWS_IMP_HOUR=2000000 , the issue is that such parameter doesn´t exist.

The solution:

Encompass the lock table, delete , sleep(big), commit and another sleep (small) inside a PL/SQL block

So the “sleep” will give to import some rows and the lock will slow down the pace of the whole process

Best way to add not null columns at a million rows table

I saw this question today at OTN:

Massive updates of new columns on tables

DBMS_REDEFINITION is usually the best way to go,

You will benefit from direct path technology, plus having a controlled operation, avoiding all that worries about disable triggers, FK, Indexes, and so on.

 

 

 

Why tables without indexes should have their PCTFREE set to zero or one

If your table don’t have any indexes, it’s most likely that it is sort of audit or log table, therefore no further update will happen there, turning any value above one on a complete waste of room.

When dropping a primary key index could lead us to performance improvement in Oracle

Many people forget that Oracle doesn´t need a unique index to reinforce the uniqueness of the primary key.

So if you have another index , that among other columns have also the columns of your primary key, this new index

is a strong candidate to replace the “original” primary key index.

The query below intend to spot the primary keys that can have your index dropped.

SELECT T1.OWNER,T1.TABLE_NAME,T1.CONSTRAINT_NAME,T2.INDEX_NAME
FROM DBA_CONSTRAINTS T1,DBA_INDEXES T2
WHERE CONSTRAINT_TYPE=’P’
AND T1.INDEX_NAME<>T2.INDEX_NAME
AND T1.STATUS=’ENABLED’
AND T2.STATUS=’VALID’
AND T1.OWNER=T2.OWNER
AND T1.TABLE_NAME=T2.TABLE_NAME
AND T1.OWNER NOT IN (‘SYS’,’SYSTEM’,’WKSYS’)
AND NOT EXISTS
   (SELECT NULL FROM DBA_CONS_COLUMNS T3 WHERE
    T1.OWNER=T3.OWNER
    AND T1.TABLE_NAME=T3.TABLE_NAME
    AND T1.CONSTRAINT_NAME=T3.CONSTRAINT_NAME
    AND NOT EXISTS (
             SELECT NULL FROM DBA_IND_COLUMNS T4 WHERE
   T3.OWNER=T4.INDEX_OWNER AND T3.TABLE_NAME=T4.TABLE_NAME
       AND T3.COLUMN_NAME=T4.COLUMN_NAME
       AND T4.INDEX_NAME=T2.INDEX_NAME))
group by T1.OWNER,T1.TABLE_NAME,T1.CONSTRAINT_NAME,T2.INDEX_NAME
order by 1,2,3;

IMPORTANT: In order to drop your primary key index, you should first recreate the primary key constraint with the keyword (USING INDEX) and the new index.

Which index does my Oracle need?

Oracle 10g and below have performance issues regarding lack of indexes at foreign key table. You can even get a dead lock, due to a sort of lock scalation (Oracle actually doesn´t scalate locks, but since it lock the whole table instead of a single row, in this case I rather call it lock scalation)

This query will show you a list of most likelly in need indexes for your environment

SELECT DISTINCT T1.OWNER,T1.TABLE_NAME,T1.CONSTRAINT_NAME
FROM DBA_CONSTRAINTS T1,DBA_INDEXES T2
WHERE CONSTRAINT_TYPE=’R’
AND T1.STATUS=’ENABLED’
AND T2.STATUS=’VALID’
AND T1.OWNER=T2.OWNER
AND T1.TABLE_NAME=T2.TABLE_NAME
AND T1.OWNER NOT IN (‘SYS’,’SYSTEM’,’WKSYS’)
AND NOT EXISTS
   (SELECT NULL FROM DBA_CONS_COLUMNS T3 WHERE
    T1.OWNER=T3.OWNER
    AND T1.TABLE_NAME=T3.TABLE_NAME
    AND T1.CONSTRAINT_NAME=T3.CONSTRAINT_NAME
    AND EXISTS (
             SELECT NULL FROM DBA_IND_COLUMNS T4 WHERE
   T3.OWNER=T4.INDEX_OWNER AND T3.TABLE_NAME=T4.TABLE_NAME
       AND T3.COLUMN_NAME=T4.COLUMN_NAME
       AND T4.INDEX_NAME=T2.INDEX_NAME))
order by 1,2,3;

TimesTen it´s all about performance

Even using memory to speed up the whole operation, the ordinary Oracle have a primary goal to guarantee the recovery of the data, putting your information at the disk as quick as possible.

On the other hand TimesTen have a primary goal to make things faster, then they second thought is the security.

The simple and easy way to benefit from TimesTen is use the Cache group to put one or many Oracle tables running at TimesTen.

With TimesTen  you can achieve the remarkable 8.7 Millions Transactions Per Second.

First commercial inmemory database In 2010 full integration with Oracle product.

Last but not least, TimesTen have a concern regarding high availability with it replication memory to memory, at zero downtime using another timesTen node called reader farm.

So if you reach Oracle Enterprise Edition edge, with all it´s features like Reference Partitions/OLAP/Compression/Flash Cache among others, and you still need to improve your performance, TimesTen might be your solution.

Just keep in mind, that often redesign a little piece of your table, might solve lot of issues.

SQLTXPLAIN Tool that helps to diagnose a SQL statement performing poorly

SQLTXPLAIN AKA SQLT, is a tool that help dig deeper to improve a query response time.

After SQLT received a SQL statement it checks GV$ tables for further informations.
Then it record all information regarding a specific query.
It´s output set of diagnostics reports, including a test case , in HTML format

It´s help check out why different performance are happening it similar systems.

The heathly report , shows altogether things that we gonna need to investigate this query,
it even show the 10053 informations, and cross it with the 10046

It´s free (download at my Oracle Support Doc ID 215187.1)
And the installation are pretty straightforward, Just execute some sqls as SYSDBA

The XPLAIN doesn´t work for BIND, but the XTRACT and XCUTE, might work well.