Infrastructure at your Service

All Posts By

Lazhar Felahi

Lazhar Felahi

How to use DBMS_SCHEDULER to improve performance ?

By | Database Administration & Monitoring, Development & Performance, Oracle | 3 Comments

From an application point of view, the oracle scheduler DBMS_SCHEDULER allows to reach best performance by parallelizing your process. Let’s start with the following PL/SQL code inserting in serial several rows from a metadata table to a target table. In my example, the metadata table does not contain “directly” the data but a set a of sql statement to be executed and for which the rows returned must be inserted into the target table My_Target_Table_Serial…

Read More
Lazhar Felahi

Oracle Text : Using and Indexing – the CONTEXT Index

By | Database Administration & Monitoring, Development & Performance, Oracle | 2 Comments

Everybody has already faced performance problem with oracle CLOB columns. The aim of this blog is to show you (always from a real user case) how to use one of Oracle Text Indexes (CONTEXT index) to solve performance problem with CLOB column. The oracle text complete documentation is here : Text Application Developer’s Guide Let’s start with the following SQL query which take more than 6.18 minutes to execute : SQL> set timing on SQL>…

Read More
Lazhar Felahi

Oracle Materialized View Refresh : Fast or Complete ?

By | Database Administration & Monitoring, Oracle | 2 Comments

In contrary of views, materialized views avoid executing the SQL query for every access by storing the result set of the query. When a master table is modified, the related materialized view becomes stale and a refresh is necessary to have the materialized view up to date. I will not show you the materialized view concepts, the Oracle Datawarehouse Guide is perfect for that. I will show you, from a user real case,  all steps…

Read More
Lazhar Felahi

SQL Tuning – Mix NULL / NOT NULL Values

By | Development & Performance, Oracle | No Comments

One of the difficulty when writing a SQL query (static SQL) is to have in the same Where Clause different conditions handling Null Values and Not Null Values for a predica. Let’s me explain you by an example : Users can entered different values for a user field from an OBI report: – If no value entered then all rows must be returned. – If 1 value entered then only row(s) related to the filter…

Read More
Lazhar Felahi

Where come from Oracle CMP$ tables and how to delete them ?

By | Database Administration & Monitoring | No Comments

Regarding the following “MOS Note Is Table SCHEMA.CMP4$222224 Or Similar Related To Compression Advisor? (Doc ID 1606356.1)”, we know that since Oracle 11.2.0.4 BP1 or Higher, due to the failure of Compression Advisor some tables with names that include “CMP”, created “temporary – the time the process is running” by Compression Advisor process (ie CMP4$23590) are not removed from the database as that should be the case. How theses tables are created ? How to…

Read More