Oracle optimizer features enable and disable

With each version or release of Oracle Database, new features and bug fixes added in the optimizer, this is good initially but any code modification may include new bugs.

Right Decision, Wrong Decision Road SignWhen upgrading from one version of Oracle to a higher is possible that some queries run really bad, this can be solved with a workaround like (if we have migrated from 11gR2 for example):

ALTER session SET optimizer_features_enabled='';

or to make it permanent:

Continue reading

Oracle rebuild index and status

In tables where changes constantly, as a maintenance over the DB, you have to rebuild the indexes B-TREE periodically, for example (the syntax is more complex):


A B-TREE ideal has branches all perfectly balanced, a B-TREE degraded can offer linear time rather than logarithmic search because of strong imbalance.

To determine if an index should be rebuilt:

Continue reading

Oracle database statistics, gathering, speed and activity redo log

Each new version of the optimizer is increasingly dependent on the statistics, the quality of which may be sufficient for an earlier version but not the current.

As a rule be analyzed a table (in casacada, ie including indexes) significant modifications are made upon it. For example:

  • truncates
  • Insert into .. select …
  • Any insert, update and deletion bulk (bulk means that affects more than 20% of all records)

The statistics can be obtained in many ways, some faster than others (partial estimates, complete, etc …) and more or less redologs generation.

For example if we use the DBMS_STATS package ( specifically the sub-program GATHER_TABLE_STATS (, the syntax is very similar to the sub-programs:

Continue reading

Change redo logs from filesystem to RAW

There is one thing I particularly like, but it is possible that we find people who say that if the database performance is greatly improved with raw redo in front of a filesystem (ext4 for example).

To find out for sure we can work with RAW mode redologs very easily. Let’s take an example made in Red Hat 4, with a test BDD with 3 redologs of 51 MB each:

[oracle@clu01 DBU]$ ls -l
total 1502076
-rw-r-----  1 oracle oinstall   9748480 Jun 25 20:38 control01.ctl
-rw-r-----  1 oracle oinstall   9748480 Jun 25 20:38 control02.ctl
-rw-r-----  1 oracle oinstall   7061504 Jun 12 11:36 control03.ctl
-rw-r-----  1 oracle oinstall  52429312 Jun 25 11:06 redo01.log
-rw-r-----  1 oracle oinstall  52429312 Jun 25 10:39 redo02.log
-rw-r-----  1 oracle oinstall  52429312 Jun 25 10:39 redo03.log
-rw-r-----  1 oracle oinstall 545267712 Jun 25 20:38 sysaux01.dbf
-rw-r-----  1 oracle oinstall 744497152 Jun 25 20:38 system01.dbf
-rw-r-----  1 oracle oinstall  20979712 Jun 23 21:22 temp01.dbf
-rw-r-----  1 oracle oinstall  36708352 Jun 25 20:38 undotbs01.dbf
-rw-r-----  1 oracle oinstall   5251072 Jun 25 20:38 users01.dbf

Continue reading

MySQL tuning variables, parameters for InnoDB

Continuing the tuning MySQL will now play specifically InnoDB engine.

This post is linked to the “MySQL tuning parameters for any engine“, we will work from states and according a result see that variables can be modified to improve results.

In the post “MySQL variables states” already worked with variables and states.

Documentation of all the variables can be found in:

Continue reading

Memory management for Oracle 10g and >= Oracle 11g

In 10g and 11g versions of Oracle Database, it has been simplified configuration memory structures for both the SGA and the PGA significantly.

Oracle version >=10g

From 10g, 2 new memory management parameters that enormously simplify this task are introduced.

SGA_TARGET, simply set a value and resizes demand values (if they are = 0):

  • Buffer cache (DB_CACHE_SIZE)
  • Shared pool (SHARED_POOL_SIZE)
  • Large pool (LARGE_POOL_SIZE)
  • Java pool (JAVA_POOL_SIZE)
  • Streams pool (STREAMS_POOL_SIZE)

Continue reading

MySQL tuning parameters for any engine

Many people make MySQL installations and do not care about the parameterization. The parameterization is important, one BD can work well (for now) with the default parameters, problems arise when the database grows or increases their workload.

In this post we will discuss the parameters that can affect the performance of any motor (the most used are InnoDB and MyISAM).

The variables are used by the server to size structures important memory for good engine performance of BD, states server will indicate if the variables we’ve defined are actually causing a positive effect or otherwise not suitable for absolutely nothing.

Continue reading

MySQL InnoDB file per table

During a default installation of MySQL, a common mistake is not changing the settings as stored data in InnoDB engine. By default, all information is stored in one datafile, which is usually named:


Continue reading

MySQL variables states

To perform a tuning of MySQL, you must first become familiar with a few concepts, MySQL has variables and states:

  • A variable has an associated value can be set in my.cnf (my.ini in Windows) or just set a default implicitly, although it is possible to modify some variables at runtime (care that is not the change is reflected in the configuration files). According to the version that we are using can have more than 300 (as with version 5.5). It is possible to know the value of all the variables:

Continue reading

Oracle fake index

Recently following the document ID 456468.1, I made a verification of the integrity of the catalog. Anyway, they have appeared a couple of errors indicating a problem with an object identifier:


This object exist in dba_objects, the query:

Continue reading