Create Oracle SQL Profile For Tuning

Looking for how to tune a SQL statement by creating a SQL Profile? The query optimizer can sometimes produce inaccurate estimates about an attribute of a statement due to lack of information, leading to poor execution plans. Automatic SQL Tuning deals with this problem with its SQL Profiling capability. The Automatic Tuning Optimizer creates a profile of the SQL statement called a SQL Profile, consisting of auxiliary statistics specific to that statement. During SQL Profiling, the Automatic Tuning Optimizer also uses execution history information of the SQL statement to appropriately set optimizer parameter settings, such as changing the OPTIMIZER_MODE initialization Read More

Nested table

In PL SQL exists an efficient and adaptable collection of data: Nested table. Nested table is similar to one dimensional array but with some differences : An array has declare number of element – Nested table not. The size of nested table can increase using extend method. An array has always consecutive elements – Nested table has consecutive elements initially but it can become sparse when elements are deleted. To create a nested table you have to use this syntax : type type_name is table of element_type[size element]; table_name type_name; Here below an example to creation and use of a Read More

Binary Search

One of the most interesting algorithms of research is binary search algorithm. This is the fastest algorithm (algorithm of binary trees apart) to search a data inside a group of elements. In fact it runs in a worst log2(x) comparisions before to find (or not) the data. The only prerequisite is : the set of elements where to search the data must be sorted. Below an example of this algorithm. int binary_search( int arr[], int tot_el, int data ) { /* Declaration and initialization of variables */ int el = 0; int me = 0; int count = 0; /* Read More

Using Invisible Indexes

Table created. SQL> create index ind_TEST.index_tables on TEST.tables_empty (a) Index created. OWNER                          OBJECT_NAME                    OBJECT_TYPE —————————— —————————— ———– TEST                           TABLES_EMPTY                   TABLE IND_TEST                       INDEX_TABLES                  INDEX   SQL> exec dbms_stats.gather_table_stats (ownname=>’TEST’,tabname=>’TABLES_EMPTY’,cascade=>TRUE); PL/SQL procedure successfully completed. SQL>  exec dbms_stats.gather_index_stats (ownname=>’IND_TEST’,indname=>’INDEX_TABLES’); PL/SQL procedure successfully completed. SQL> conn ind_TEST Enter password: SQL>  explain plan for 2  select * from TEST.TABLES_EMPTY; SQL> @?/rdbms/admin/utlxpls   PLAN_TABLE_OUTPUT ———————————————————————– Plan hash value: 932631555   ———————————————————————————- | Id  | Operation    | Name         | Rows  | Bytes | Cost (%CPU)| Time     | ———————————————————————————- |   0 | SELECT STATEMENT  |       |     1 |    13 |     2   (0)| 00:00:01 | |   1 |  TABLE Read More

EXPLICIT CURSORS – modify tables

In the previous article (cursors overview) we have seen simple examples with SQL statements. You can also use cursor to modify database tables. If we want to use explicit cursor to update or delete data, we need to: -declare the cursor with FOR UPDATE clause (to avoid unwanted changes from others). -use clause WHERE CURRENT OF to modify FETCH returned row. FOR UPDATE clause determines a specific row level lock on your data, so you are sure that data don’t change before your delete or update statement. FOR UPDATE syntax is the follow: DECLARE CURSOR cur_name IS SELECT […your cols…] Read More

Create Database Link

Use the CREATE DATABASE LINK statement to create a database link. A database link is a schema object in one database that enables you to access objects on another database. The other database need not be an Oracle Database system. However, to access non-Oracle systems you must use Oracle Heterogeneous Services. After you have created a database link, you can use it to refer to tables and views on the other database. In SQL statements, you can refer to a table or view on the other database by appending @dblink to the table or view name. You can query a Read More