Oracle Logbook

May 23, 2012

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

Filed under: Uncategorized — heliodias @ 8:19 pm

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.

Advertisements

May 18, 2012

Which index does my Oracle need?

Filed under: Uncategorized — heliodias @ 8:46 pm

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;

May 17, 2012

TimesTen it´s all about performance

Filed under: Uncategorized — heliodias @ 3:39 pm

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.

May 15, 2012

SQLTXPLAIN Tool that helps to diagnose a SQL statement performing poorly

Filed under: Uncategorized — heliodias @ 6:15 pm

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.

May 11, 2012

Turning off archive log generation for some tables

Filed under: Uncategorized — heliodias @ 10:10 pm

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.

May 10, 2012

Weird pitfall at Oracle index monitoring usage.

Filed under: Uncategorized — heliodias @ 9:47 pm

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.

May 1, 2012

How to overcome outer joins boundaries.

Filed under: Uncategorized — heliodias @ 6:49 am

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.

 

April 30, 2012

Oracle Heap Tables or SQL Server Clustered Indexes?

Filed under: Uncategorized — heliodias @ 4:51 pm

Jonathan Lewis and Grant Fritchey Live Debate

Thurs 7th June 2012 17:00 CEST 16:00 BST 11.00 EDT 10.00 CDT 08.00 PDT

Register your place

Oracle and SQL Server may both share a common language, but certain things are handled quite differently. Jonathan Lewis (OakTable Network, Oracle Ace Director) is used to seeing heap tables (almost) everywhere, but Grant Fritchey (Microsoft SQL Server MVP) is used to seeing clustered indexes (almost) everywhere. But which arrangement performs better? And is comparative performance even the right thing to measure?

In this live discussion, these two heavyweights in their respective technology areas will debate the pros and cons of Oracle Heap Tables and SQL Clustered Indexes. Jonathan and Grant may even play a few unexpected cards during the discussion. Be prepared for a lively exchange which will not only entertain, but will teach you key concepts on Oracle and SQL Server.

Please note: this webinar is offered free of charge, and places are strictly limited and offered on a first come, first serve basis. Register your place now.

April 27, 2012

Avoiding redolog delays using commit_wait and commit_logging

Filed under: Uncategorized — heliodias @ 11:44 pm

Abstract:

This post shows how to get a lot of benefits regarding improve the redolog written,  having as tradeoff  a tiny little chance to lost commited transactions in a event of crash.

Those who can´t afford lose a commited transaction e.g. (BANKs, Financial corporations, Hospitals) should disregard what I´m going to present.

 

Oracle have the hability to group together redo operations instead of writting as fast as it could, by changing parameters

For the experimentation , I advice to open two sessions, one for control the other for the operations undergoing.

session 1
create table l3_t1 (id number);
session 2
select name,value from v$statname t1,v$mystat t2 where t1.statistic#=t2.statistic#
and value>0
and (name like ‘redo%’ or name =’messages sent’)
order by 1
/
set linesize 28
set pagesize 0
spool insert1.sql
select ‘insert into l3_t1 values(1); commit;’ from dba_objects
where rownum <10001;

@insert1
select name,value from v$statname t1,v$mystat t2 where t1.statistic#=t2.statistic#
and value>0
and (name like ‘redo%’ or name =’messages sent’)
order by 1
/

/*After 10 thousands of inserts you should keep your eyes the the around 10000 messages and 500 sync time  */

NAME                                                                  VALUE
—————————————————————- ———-
messages sent                                                         10002
redo entries                                                          10079
redo size                                                           5150552
redo synch time                                                         552
redo synch writes                                                     10002

@insert1
select name,value from v$statname t1,v$mystat t2 where t1.statistic#=t2.statistic#
and value>0
and (name like ‘redo%’ or name =’messages sent’)
order by 1
/

/*After the second round of more 10 thousands of inserts you should keep your eyes the the around 20000 messages and 920 sync time  */
NAME                                                                  VALUE
—————————————————————- ———-
messages sent                                                         20003
redo entries                                                          20166
redo size                                                          10299892
redo synch time                                                         920
redo synch writes                                                     20003

==============
Session 1

SQL> alter system set commit_logging=batch scope=both;

System altered.

SQL> alter system set commit_wait=nowait scope=both;

System altered.
===============

Session 2

@insert1

select name,value from v$statname t1,v$mystat t2 where t1.statistic#=t2.statistic#
and value>0
and (name like ‘redo%’ or name =’messages sent’)
order by 1
/

/*Now the magic , you almost didn´t have messages nor sync time, even doing once again more 10.000 inserts & commits  */
NAME                                                                  VALUE
—————————————————————- ———-
messages sent                                                         20008
redo entries                                                          34497
redo size                                                          15107148
redo synch time                                                         921
redo synch writes                                                     20005

 

I stated before that was “tiny,little chance” due to the fact that in the event of a crash , to actually loose some information, Oracle had buffered grouped redo, which is unlikelly, but not impossible.

Once again, don´t  use it, unless the loss of transaction is not a big deal for you.

Just to make myself clear, the stacks are lose in the worse case the last few seconds of transaction. If this will cost you nothing, and you are suffering from redo sync wait event , this could be a solution.

April 20, 2012

IOT Candidates – Which tables should be consider to Index Organizer Tables

Filed under: Uncategorized — heliodias @ 9:32 pm

Abstract: This post is an introduction to where the investigation should start.

The very begin is check tables with just one index:

SELECT COUNT(*) FROM (
select OWNER,TABLE_NAME,count(*) from dba_indexes
WHERE OWNER NOT IN (‘SYS’, ‘SYSMAN’,’XDB’,’SYSTEM’,’CTXSYS’,’OLAPSYS’)
group by OWNER,TABLE_NAME
HAVING COUNT(*)=1);

  COUNT(*)
———-
      2008

In this sample we could see 2008 tables that  deserve a further investigation.

The next step is check the  v$segment_statistics to see which among them have less DML , those will be the perfect candidate.

Important advice, before change the structure to IOT, test it, and be sure that you won´t gonna need lot´s of  indexes in the future.

« Previous PageNext Page »

Create a free website or blog at WordPress.com.