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.

Turning off archive log generation for some tables

We have a client that are creating 250GB of archive on dailly basis,

And he want to decrease this volume of archive.

Well, the solution is turn off the archive at specifics tables, the catch is that Oracle doesn´t allow us to do it. Below I´ll show how I did it:

SQL> SELECT NAME,VALUE FROM V$STATNAME T1,V$MYSTAT T2
WHERE T1.STATISTIC#=T2.STATISTIC#
AND UPPER (NAME) LIKE ‘%REDO%’
ORDER BY 1;

NAME                                          VALUE
—————————————- ———-
IMU Redo allocation size                          0
redo blocks read for recovery                     0
redo blocks written                               0
redo buffer allocation retries                    0
redo entries                                      1
redo log space requests                           0
redo log space wait time                          0
redo log switch interrupts                        0
redo ordering marks                               0
redo size                                       400
redo subscn max counts                            0
redo synch time                                   0
redo synch writes                                 0
redo wastage                                      0
redo write time                                   0
redo writer latching time                         0
redo writes                                       0

17 rows selected.

 /* As one may see, no previous redo at all*/

SQL> INSERT INTO REDO_OFF (SELECT ROWNUM FROM DBA_OBJECTS) ;

107692 rows created.

SQL> COMMIT;
Commit complete.

/*Now let´s see how redo had been generate*/

SQL> SELECT NAME,VALUE FROM V$STATNAME T1,V$MYSTAT T2
WHERE T1.STATISTIC#=T2.STATISTIC#
AND UPPER (NAME) LIKE ‘%REDO%’
ORDER BY 1;

NAME                                          VALUE
—————————————- ———-
IMU Redo allocation size                          0
redo blocks read for recovery                     0
redo blocks written                               0
redo buffer allocation retries                    0
redo entries                                      2
redo log space requests                           0
redo log space wait time                          0
redo log switch interrupts                        0
redo ordering marks                               0
redo size                                       540
redo subscn max counts                            0
redo synch time                                   0
redo synch writes                                 1
redo wastage                                      0
redo write time                                   0
redo writer latching time                         0
redo writes                                       0

17 rows selected.

/* Without redo, of course we don´t have archive either */

/* Now let´s do it again, but at a archived on table */

SQL> INSERT INTO L3_REDO_ON (SELECT ROWNUM FROM DBA_OBJECTS) ;

107693 rows created.

SQL> COMMIT;

Commit complete.

SQL> SELECT NAME,VALUE FROM V$STATNAME T1,V$MYSTAT T2
WHERE T1.STATISTIC#=T2.STATISTIC#
AND UPPER (NAME) LIKE ‘%REDO%’
ORDER BY 1;

NAME                                          VALUE
—————————————- ———-
IMU Redo allocation size                      11512
redo blocks read for recovery                     0
redo blocks written                               0
redo buffer allocation retries                    0
redo entries                                   1752
redo log space requests                           0
redo log space wait time                          0
redo log switch interrupts                        0
redo ordering marks                               0
redo size                                   1752392
redo subscn max counts                            0
redo synch time                                   0
redo synch writes                                 4
redo wastage                                      0
redo write time                                   0
redo writer latching time                         0
redo writes                                       0

17 rows selected.

SQL>

/*Lot of redo and archive as we expected*/

No there isn´t any hidden parameter to turn the archive off.

The trick is that the “turned off archives tables” were at a second instance, with the ALL ARCHIVE LOG DISABLED, and I just created synonyms point out to the second instance.

So for the applications , and users it will be transparent , and you could “turn off” the archive generation at the desired tables.

Of course at the second instance you still have the REDO generation, that´s why I didn´t claim that the REDO was being turning off, instead I said THE ARCHIVE would be off.

Weird pitfall at Oracle index monitoring usage.

Oracle 10g and below doesn´t detect correctly recursive usage of index at v$object_usage.

Scenario:

I deleted a row from a parent table (which made a recurse query at the index of the child table), and then I checked at v$object_usage to if the detection would be made. I found out that it didn´t as you may see below.

QL> create table l3_ajuste_b (id number , constraint l3_fk1 foreign key(id) references l3_ajuste (id))
  2  ;

SQL> create index l3_ajuste_b_ind on l3_ajuste_b(id) ;

Index created.

SQL> alter index l3_ajuste_b_ind monitoring usage;

Index altered.
SQL> delete l3_ajuste where id=2;

1 row deleted.

SQL> commit;
SQL> select USED from v$object_usage;

USE
—
NO

Then after a second attempt to delete, but this time getting a violation  of integrity constraint, somehow Oracle managed to log correctly.

SQL> delete l3_ajuste where id=1;
delete l3_ajuste where id=1
*
ERROR at line 1:
ORA-02292: integrity constraint (SYS.L3_FK1) violated – child record found
SQL> select USED from v$object_usage;

USE
—
YES

The bottom line, never drop indexes just because they aren´t showing at v$object_usage,

Check by yourself if they are helping any foreign key (which was my case).

Furthermore the Execution Optimizer often use the statistical information of index to create a better plan.

So you might even change by accident your executions plan, after  dropping a index that “is not in use”

My advice is use some Oracle tools to guarantee your plan stability before any drop of your indexes.

How to overcome outer joins boundaries.

Once in a while, you need to use outer joins and you face some limitations regarding semi-joins or something else.

The solution is not elegant, but is quite simple indeed.

Create your own “outer join”,  which means instead of join straight to the desired table, create a union between the desirable table and the original table (discarding the duplicate by using an anti-join (not exists /not in)

select t1.id,texto from l3_tb1 t1,
(
select id,texto from l3_tb2
union all
select t3.id,” from l3_tb1 t3 where
t3.id not in (select id from l3_tb2)
)
t2 where t1.id=t2.id;

Just remember that this solution is more resource intensive than the “real” outer join.