Oracle Startup/Shutdown scripts

For Oracle 11gR2 you have the opportunity to use Oracle Restart, but to use it you must install Grid Infrastructure. If you don't want to use GI, you can use the scripts below:

vi /etc/init.d/dbora


#!/bin/sh
# chkconfig: 345 99 10
# description: Oracle automatic startup/shutdown script.
#
# Set ORA_OWNER to the user id of the owner of the Oracle database software.

ORA_OWNER=oracle

case "$1" in
    'start')
        # Start the Oracle databases:
        su - $ORA_OWNER -c "/home/oracle/scripts/startup.sh >> /home/oracle/scripts/startup_shutdown.log 2>&1"
        touch /var/lock/subsys/dbora
        ;;
    'stop')
        # Stop the Oracle databases:
        su - $ORA_OWNER -c "/home/oracle/scripts/shutdown.sh >> /home/oracle/scripts/startup_shutdown.log 2>&1"
        rm -f /var/lock/subsys/dbora
        ;;
esac

chmod 750 dbora

vi /home/oracle/scripts/startup.sh

#!/bin/bash

export ORACLE_SID=DB11G

export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1

export PATH=$PATH:$ORACLE_HOME/bin

# Start Listener

lsnrctl start

# Start Database

sqlplus / as sysdba << EOF

STARTUP;

EXIT;

EOF

chmod 755 startup.sh

vi /home/oracle/scripts/shutdown.sh

#!/bin/bash

export ORACLE_SID=sapcc

export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1

export PATH=$PATH:$ORACLE_HOME/bin

# Start Listener

lsnrctl stop

# Start Database

sqlplus / as sysdba << EOF

SHUTDOWN IMMEDIATE;

EXIT;

EOF

chmod 755 shutdown.sh

Running scripts as root:

/etc/init.d/dbora stop

/etc/init.d/dbora start

Running as service:

chkconfig --add dbora

chkconfig --list dbora

dbora           0:off   1:off   2:off   3:on    4:on    5:on    6:off

service dbora stop

service dbora start

HOW TO SQL - NULL values

NULL values often generate unexpected results when they appear in SQL queries. This is because not always these NULL values are understood. So, what about those NULL  values?
LNNVL
NULLIF
NVL
NVL2

It’s probably best to begin with a few words about what NULL is NOT:

·         NULL is not the number 0

·         NULL is not the empty string

NULL is a special placeholder in SQL used to indicate that the value does not exist in the database, that the value is unknown.

Now let’s explore some unknown NULL values!

·         Comparison with null values is done with special syntax IS NULL or IS NOT NULL, and doesn’t use comparison operands like =, <>, >, <.

select employee_id from employees where manager_id=NULL;

no rows selected

SQL> select employee_id from employees where manager_id IS NULL;

EMPLOYEE_ID

-----------

        100

·         Because the value of NULL values is unknown, unknown is also the result of any operation with NULL.

select 5+7+null+9 as suma from dual;

      SUMA

----------

       NULL

·         The truth table is also influenced by NULL values as follows, an interesting case being FALSE or NULL = NULL:

Bool1           Bool2           AND              OR

TRUE             TRUE             TRUE             TRUE

TRUE             FALSE           FALSE           TRUE

TRUE             NULL            NULL            TRUE

FALSE           TRUE             FALSE           TRUE

FALSE           FALSE           FALSE           FALSE          

FALSE           NULL            FALSE           NULL

NULL            TRUE             NULL            TRUE

NULL            FALSE           FALSE           NULL

NULL            NULL            NULL            NULL

 

·         To view how unknown NULL values are treated in databases, let’s take the example of DEPARTMENTS table, where there are some departments with no manager (manager_id is NULL).

SELECT * FROM DEPARTMENTS WHERE MANAGER_ID <= 200

UNION ALL

SELECT * FROM DEPARTMENTS WHERE MANAGER_ID > 200;

This SQL, which is a UNION ALL between 2 sets of data that covers all values for MANAGER_ID will not return all departments, as someone would expect, but only those with a known value for manager_id.  

10     Administration       200    1700

30     Purchasing           114    1700

50     Shipping             121    1700

60     IT                   103    1700

80     Sales                145    2700

90     Executive            100    1700

100    Finance              108    1700

20     Marketing            201    1700

40     Human Resources      203    2700

70     Public Relations     204    2700

110    Accounting           205    1700

·         Another thing that is related to the unknown character of NULL values is that NULL values aren’t stored in INDEXES. This is why queries having condition IS NULL don’t use index, even if there is one defined for the column specified in condition. On the contrary, the index is used in queries having conditions IS NOT NULL.

select * from employees where manager_id is null;

-------------------------------------------------------------------------------

| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |

-------------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |           |     1 |    69 |     3   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| EMPLOYEES |     1 |    69 |     3   (0)| 00:00:01 |

-------------------------------------------------------------------------------

select * from employees where manager_id is not null;

--------------------------------------------------------------------------------

| Id  | Operation                   | Name           | Rows  | Bytes | Cost

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |                |   107 |  7383 |     9  

|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES      |   107 |  7383 |     9  

|*  2 |   INDEX FULL SCAN           | EMP_MANAGER_IX |   107 |       |     1  

--------------------------------------------------------------------------------

·         In sorting operation, NULL values appear LAST in ASC order and FIRST in DESC order.

select * from departments order by manager_id ASC;

           90 Executive                             100        1700

           60 IT                                    103        1700

          …….

          110 Accounting                            205        1700

          230 IT Helpdesk                           NULL       1700

          240 Government Sales                      NULL       1700

          ……

          140 Control And Credit                    NULL       1700

select * from departments order by manager_id DESC;

          140 Control And Credit                    NULL       1700

          ………

          160 Benefits                              NULL       1700

          110 Accounting                            205        1700

          ……

           90 Executive                             100        1700

·         All aggregate functions except COUNT(*) and GROUPING ignore nulls. You can use the NVL function in the argument to an aggregate function to substitute a value for a null. COUNT never returns null, but returns either a number or zero. For all the remaining aggregate functions, if the data set contains no rows, or contains only rows with nulls as arguments to the aggregate function, then the function returns null.

SELECT COUNT(*) FROM DEPARTMENTS;

  COUNT(*)

----------

        27

SQL> select count(manager_id) from departments;

COUNT(MANAGER_ID)

-----------------

               11

select min(manager_id) from departments;

MIN(MANAGER_ID)

---------------

            100

SQL> select max(manager_id) from departments;

MAX(MANAGER_ID)

---------------

            205

·         NULL does not equal NULL or other value

select * from a;

        C1         C2

---------- ----------

         1          1

         1          2

         1      NULL

      NULL      NULL

select * from a where c1=c2;

        C1         C2

---------- ----------

1             1

·         The NULL-related functions facilitate null handling. The NULL-related functions are:

COALESCE

·         Null have different behavior in NOT  IN and NOT EXISTS.

Take, for example, the case of returning all employees that has no managers:

SELECT * FROM HR.EMPLOYEES WHERE EMPLOYEE_ID NOT IN ( SELECT MANAGER_ID FROM HR.EMPLOYEES );

no rows selected

SELECT * FROM HR.EMPLOYEES E1 WHERE NOT EXISTS ( SELECT 1 FROM HR.EMPLOYEES E2 WHERE E2.EMPLOYEE_ID = E1.MANAGER_ID);

        100 Steven               King

SKING                     515.123.4567         17-JUN-98 AD_PRES         24000

                                     90

                or departments with no employees:

SELECT department_id FROM hr.departments

WHERE department_id

NOT IN (SELECT department_id FROM HR.EMPLOYEES);

            no rows selected

           

SELECT DEPARTMENT_ID FROM HR.DEPARTMENTS d

WHERE NOT EXISTS (SELECT 1

FROM HR.EMPLOYEES e where e.department_id = d.department_id);

DEPARTMENT_ID

-------------

          120

          130

          140

………

As you can see, this happens because one or more values in NOT IN clause are NULL (UNKNOWN), and the predicate  department_id NOT IN (x,y,NULL) evaluates to neither TRUE, nor FALSE, but to UNKNOWN (NULL).

This is why, NOT IN can be used if all the values that must be evaluated differ from NULL value.

As a conclusion of this short intro, the existence of NULL values into a database introduces a new degree of uncertainty. If not understood, a lot of guessing must be done by an SQL programmer to counter for erroneous results of NULL values in a database.

How to check Oracle 11g is Standard or Enterprise Edition

You can select the v$version:

Output for SE:


BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

Output for EE

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

PL/SQL Release 11.2.0.3.0 - Production

CORE    11.2.0.3.0      Production

TNS for Linux: Version 11.2.0.3.0 - Production

NLSRTL Version 11.2.0.3.0 - Production

Compare AWR periods

First list available AWR snapshots

select snap_id, end_interval_time
from dba_hist_snapshot
where end_interval_time > trunc(sysdate-10)
order by snap_id;


Then run this sql to generate HTML report with differences

select * from table(dbms_workload_repository.awr_diff_report_html(3663464033,1,8249,8250,3663464033,1,8417,8418));

Oracle INTERVAL partitioning tips

Oracle INTERVAL partitioning is an extension to the existing RANGE partitioning, and can be used to partition tables for DATE and NUMBER columns.
Below are some examples of working with interval partitioning.

Create table:


create table
pos_data (
   start_date        DATE,
   store_id          NUMBER,
   inventory_id      NUMBER(6),
   qty_sold          NUMBER(3)
)
PARTITION BY RANGE (start_date)
INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
(
   PARTITION pos_data_p2 VALUES LESS THAN (TO_DATE('1-7-2007', 'DD-MM-YYYY')),
   PARTITION pos_data_p3 VALUES LESS THAN (TO_DATE('1-8-2007', 'DD-MM-YYYY'))
);  

Table created.

View partitions for the table:

SELECT

   TABLE_NAME,

   PARTITION_NAME,

   PARTITION_POSITION,

   HIGH_VALUE

FROM

   USER_TAB_PARTITIONS

WHERE

   TABLE_NAME='POS_DATA'

ORDER BY

   PARTITION_NAME; 

TABLE_NAME                     PARTITION_NAME                 PARTITION_POSITION

------------------------------ ------------------------------ ------------------

HIGH_VALUE

--------------------------------------------------------------------------------

POS_DATA                       POS_DATA_P2                                     1

TO_DATE(' 2007-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

POS_DATA                       POS_DATA_P3                                     2

TO_DATE(' 2007-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

Insert rows in table to generate interval partitions:


select sysdate from dual;

SYSDATE
---------
17-OCT-12



insert into pos_data values (sysdate,1,1,1);

1 row created.


insert into pos_data values (sysdate-30,1,1,1);

1 row created.

SELECT
   TABLE_NAME,
   PARTITION_NAME,
   PARTITION_POSITION,
   HIGH_VALUE
FROM
   USER_TAB_PARTITIONS
WHERE
   TABLE_NAME='POS_DATA'
ORDER BY
   PARTITION_NAME;

TABLE_NAME                     PARTITION_NAME                 PARTITION_POSITION
------------------------------ ------------------------------ ------------------
HIGH_VALUE
--------------------------------------------------------------------------------
POS_DATA                       POS_DATA_P2                                     1
TO_DATE(' 2007-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

POS_DATA                       POS_DATA_P3                                     2
TO_DATE(' 2007-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

POS_DATA                       SYS_P281                                        4
TO_DATE(' 2012-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA


TABLE_NAME                     PARTITION_NAME                 PARTITION_POSITION
------------------------------ ------------------------------ ------------------
HIGH_VALUE
--------------------------------------------------------------------------------
POS_DATA                       SYS_P282                                        3
TO_DATE(' 2012-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

Select data from interval partition:

select * from pos_data partition for (to_date('15-10-2012','dd-mm-yyyy'));

START_DAT   STORE_ID INVENTORY_ID   QTY_SOLD

--------- ---------- ------------ ----------

17-OCT-12          1            1          1

Drop interval partition

alter table pos_data drop partition for (to_date('15-09-2012','dd-mm-yyyy'));

Table altered.

SELECT

   TABLE_NAME,

   PARTITION_NAME,

   INTERVAL,

   PARTITION_POSITION,

   HIGH_VALUE

FROM

   USER_TAB_PARTITIONS

WHERE

   TABLE_NAME='POS_DATA'

ORDER BY

   PARTITION_NAME;  

TABLE_NAME                     PARTITION_NAME                 INT

------------------------------ ------------------------------ ---

PARTITION_POSITION

------------------

HIGH_VALUE

--------------------------------------------------------------------------------

POS_DATA                       POS_DATA_P2                    NO

                 1

TO_DATE(' 2007-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

POS_DATA                       POS_DATA_P3                    NO

                 2

TO_DATE(' 2007-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

TABLE_NAME                     PARTITION_NAME                 INT

------------------------------ ------------------------------ ---

PARTITION_POSITION

------------------

HIGH_VALUE

--------------------------------------------------------------------------------

POS_DATA                       SYS_P281                       YES

                 3

TO_DATE(' 2012-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

alter table pos_data drop partition for (to_date('15-10-2012','dd-mm-yyyy'));

Table altered.

alter table pos_data drop partition pos_data_p2;

Table altered.

SELECT

   TABLE_NAME,

   PARTITION_NAME,

   INTERVAL,

   PARTITION_POSITION,

   HIGH_VALUE

FROM

   USER_TAB_PARTITIONS

WHERE

   TABLE_NAME='POS_DATA'

ORDER BY

   PARTITION_NAME;  

TABLE_NAME                     PARTITION_NAME                 INT

------------------------------ ------------------------------ ---

PARTITION_POSITION

------------------

HIGH_VALUE

--------------------------------------------------------------------------------

POS_DATA                       POS_DATA_P3                    NO

                 1

TO_DATE(' 2007-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

alter table pos_data drop partition pos_data_p3;

alter table pos_data drop partition pos_data_p3

                                    *

ERROR at line 1:

ORA-14758: Last partition in the range section cannot be dropped

Change interval partitioning range:

 alter table pos_data set INTERVAL(NUMTOYMINTERVAL(3, 'MONTH'));

Table altered.

insert into pos_data values (sysdate - 90,1,1,1);

1 row created.

SQL> SELECT

   TABLE_NAME,

   PARTITION_NAME,

   INTERVAL,

   PARTITION_POSITION,

   HIGH_VALUE

FROM

   USER_TAB_PARTITIONS

WHERE

   TABLE_NAME='POS_DATA'

ORDER BY

   PARTITION_NAME;  

TABLE_NAME                     PARTITION_NAME                 INT

------------------------------ ------------------------------ ---

PARTITION_POSITION

------------------

HIGH_VALUE

--------------------------------------------------------------------------------

POS_DATA                       POS_DATA_P3                    NO

                 1

TO_DATE(' 2007-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

POS_DATA                       SYS_P283                       NO

                 3

TO_DATE(' 2012-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

TABLE_NAME                     PARTITION_NAME                 INT

------------------------------ ------------------------------ ---

PARTITION_POSITION

------------------

HIGH_VALUE

--------------------------------------------------------------------------------

POS_DATA                       SYS_P284                       NO

                 2

TO_DATE(' 2012-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

select * from pos_data partition for (to_date('15-09-2012','dd-mm-yyyy'));

START_DAT   STORE_ID INVENTORY_ID   QTY_SOLD

--------- ---------- ------------ ----------

17-SEP-12          1            1          1

19-JUL-12          1            1          1

Deactivate interval partitioning, table remains only with range partitioning:

alter table pos_data drop partition for (to_date('15-10-2012','dd-mm-yyyy'));

Table altered.

 alter table pos_data set interval();

Table altered.

SELECT

   TABLE_NAME,

   PARTITION_NAME,

   INTERVAL,

   PARTITION_POSITION,

   HIGH_VALUE

FROM

   USER_TAB_PARTITIONS

WHERE

   TABLE_NAME='POS_DATA'

ORDER BY

   PARTITION_NAME;  

TABLE_NAME                     PARTITION_NAME                 INT

------------------------------ ------------------------------ ---

PARTITION_POSITION

------------------

HIGH_VALUE

--------------------------------------------------------------------------------

POS_DATA                       POS_DATA_P3                    NO

                 1

TO_DATE(' 2007-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

POS_DATA                       SYS_P284                       NO

                 2

TO_DATE(' 2012-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

TABLE_NAME                     PARTITION_NAME                 INT

------------------------------ ------------------------------ ---

PARTITION_POSITION

------------------

HIGH_VALUE

--------------------------------------------------------------------------------

insert into pos_data values (sysdate,1,1,1);

insert into pos_data values (sysdate,1,1,1)

            *

ERROR at line 1:

ORA-14400: inserted partition key does not map to any partition

Reactivate interval partitioning:

alter table pos_data set INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'));

Table altered.

insert into pos_data values (sysdate,1,1,1);

1 row created.

SELECT

   TABLE_NAME,

   PARTITION_NAME,

   INTERVAL,

   PARTITION_POSITION,

   HIGH_VALUE

FROM

   USER_TAB_PARTITIONS

WHERE

   TABLE_NAME='POS_DATA'

ORDER BY

   PARTITION_NAME;  

TABLE_NAME                     PARTITION_NAME                 INT

------------------------------ ------------------------------ ---

PARTITION_POSITION

------------------

HIGH_VALUE

--------------------------------------------------------------------------------

POS_DATA                       POS_DATA_P3                    NO

                 1

TO_DATE(' 2007-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

POS_DATA                       SYS_P284                       NO

                 2

TO_DATE(' 2012-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

TABLE_NAME                     PARTITION_NAME                 INT

------------------------------ ------------------------------ ---

PARTITION_POSITION

------------------

HIGH_VALUE

--------------------------------------------------------------------------------

POS_DATA                       SYS_P285                       YES

                 3

TO_DATE(' 2012-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

View interval size for table:

SELECT TABLE_NAME,PARTITIONING_TYPE,PARTITION_COUNT,

PARTITIONING_KEY_COUNT,STATUS,INTERVAL

from USER_PART_TABLES

where table_name='POS_DATA';

TABLE_NAME                     PARTITION PARTITION_COUNT PARTITIONING_KEY_COUNT

------------------------------ --------- --------------- ----------------------

STATUS

--------

INTERVAL

--------------------------------------------------------------------------------

POS_DATA                       RANGE             1048575                      1

VALID

NUMTOYMINTERVAL(1,'MONTH')

HOW TO SQL 2

Explain plans

During my previous post I’ve showed you that the main “action zone” in which one RDBMS keeps all the needed resources to access the data in best and fastest way, is the memory and the core of RDBMS thinking to achieve the same objectives of best and fastest response, is the optimizer.

The SQL language used to query and modify data in a database has the following characteristics: it is supported by all major commercial database systems, it’s standardized, it’s interactive via graphical user interfaces or prompt, or it’s embedded in programs, and is declarative, based on relational algebra, meaning that in SQL you write pretty simple queries where you say exactly what you want to get from database and the query doesn’t need to describe how to get the data from the database. This declarative characteristic of the SQL lead to the importance of the optimizer in a database system, because the optimizer has to translate the SQL queries in exact steps used to access the data. 

When choosing the best and fastest way to get the data from database, the optimizer generates all the possible plans to access data queried and chooses the best one. This plan, called explain plan, is the main tool in seeing and understanding how the optimizer will retrieve the data from the database.

In Oracle database server there are some ways to access this explaining plan depending the time when the SQL statement was executed.

For the statements that will be run, if the query is run from SQL Plus tool, there are the following options: set autotrace on to see the rows, the explain plan and the statistics of the query, set autotrace traceonly to get only the explain plan and the statistics after the query has ran and set autotrace traceonly explain to get only the explain plan without executing the query. The last option is useful in getting only the explain plan in the situations of a long running query.

For a specific sql explain plan can be generated using SQL command EXPLAIN PLAN FOR <statement>, and can be viewed using the dbsm_xplan package with the following query:

SELECT * FROM TABLE (dbms_xplan.display);

When using SQL Developer as a tool for writing SQL, the explain plan can be viewed with F10 key and the autotrace output can be viewed with F6 key.

For a series of statements, explain plans can be generated using the DBMS_MONITOR package to enable trace running for statements between DBMS_MONITOR.SESSION_TRACE_ENABLE and DBMS_MONITOR.SESSION_TRACE_DISABLE statements. The trace files with explain plans will be generated in path specified by background_dump_dest internal parameter, and can be formatted to be human readable using TKPROF tool.

To get the explain plan of the currently running SQLs, you have to query internal dictionary to identify this SQL, by its session identifier or by its text, get its SQL identifier (SQL_ID) and then use the package DBMS_XPLAN to get the explain plan for its SQL_ID.

SELECT * FROM TABLE(dbms_xplan.display_cursor('&SQLID'));

For the SQL that were executed previously and now are stored in history dictionary tables of Automatic Workload Repository (AWR), explain plan can be viewed also by SQL_ID using

     SELECT * FROM TABLE(dbms_xplan.display_awr('&SQLID'));

To get the SQL_IDs from the internal dictionaries, the user has to have corresponding privileges to access those structures.

This is a sample execution plan:

Plan hash value: 529144872

------------------------------------------------------------------------------------------------

| Id  | Operation                      | Name          | Rows  | Bytes | Cost (%CPU)| Time     |

------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT               |               |     3 |   189 |     8   (0)| 00:00:01 |

|   1 |  NESTED LOOPS                  |               |       |       |            |          |

|   2 |   NESTED LOOPS                 |               |     3 |   189 |     8   (0)| 00:00:01 |

|   3 |    NESTED LOOPS                |               |     3 |   141 |     5   (0)| 00:00:01 |

|   4 |     TABLE ACCESS BY INDEX ROWID| EMPLOYEES     |     3 |    60 |     2   (0)| 00:00:01 |

|*  5 |      INDEX RANGE SCAN          | EMP_EMP_ID_PK |     3 |       |     1   (0)| 00:00:01 |

|   6 |     TABLE ACCESS BY INDEX ROWID| JOBS          |     1 |    27 |     1   (0)| 00:00:01 |

|*  7 |      INDEX UNIQUE SCAN         | JOB_ID_PK     |     1 |       |     0   (0)| 00:00:01 |

|*  8 |    INDEX UNIQUE SCAN           | DEPT_ID_PK    |     1 |       |     0   (0)| 00:00:01 |

|   9 |   TABLE ACCESS BY INDEX ROWID  | DEPARTMENTS   |     1 |    16 |     1   (0)| 00:00:01 |

------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   5 - access("E"."EMPLOYEE_ID"<103)

   7 - access("E"."JOB_ID"="J"."JOB_ID")

   8 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")

The reading of the explain plan have the following general guidelines: the first statement is the one that has the most indentation and if two statements appear at the same level of indentation, the top statement is executed first.

Each explain plan generated by the optimizer has an estimated cost and the optimizer will choose in execution of a specific statement the plan with the smallest cost. But this will not guarantee that this plan is the fastest way to return the rows. So, in practice all statements should have explained plans verified and understood.

In MySQL and PostgreSQL, there is also a similar command EXPLAIN that, if precedes a SQL statement, will display information from the optimizer about the query execution plan.

Each database server has its own tools and commands to get and view the explain plans, but the main goal is to understand how the server chose to interpret our query and how it translates in steps to get data from databases. This is why the explain plan of a statement is the first step in SQL tuning even from the development phase.

In my next post I intend to show you some interesting things about NULL values. So, stay close.

SQL Access Advisor Quick Task

1. Create and run quick task

BEGIN

  dbms_advisor.quick_tune(dbms_advisor.sqlaccess_advisor,

                                   'SAA:a3hvjhf57w6qw',

                                   'SELECT * from TABLE where COLUMN < 0');

   END;

   /

PL/SQL procedure successfully completed.

2. View results

SQL> select dbms_advisor.get_task_script('SAA:a3hvjhf57w6qw') FROM dual; 

DBMS_ADVISOR.GET_TASK_SCRIPT('SAA:A3HVJHF57W6QW')

--------------------------------------------------------------------------------

Rem  SQL Access Advisor: Version 10.2.0.5.0 - Production

Rem

Rem  Username:        SYS

Rem  Task:            SAA:a3hvjhf57w6qw

Rem  Execution date:  16/10/2012 20:02

Rem

CREATE INDEX "USER"."TABLE_IDX$$_413F0001"

    ON "USER"."TABLE"

    ("COLUMN")

    COMPUTE STATISTICS;

SQL Tuning Advisor for SQL_ID

1. Create SQL Tuning Advisor task

DECLARE

  my_task_name VARCHAR2(30);

begin

my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => '7a6b4442j5pcz',scope => 'COMPREHENSIVE',time_limit => 60,task_name => 'STA:7a6b4442j5pcz',description => '7a6b4442j5pcz');

end;

/

2. Run Task 

EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'STA:7a6b4442j5pcz');

3. View results 

SET LONG 10000

SET PAGESIZE 1000

SET LINESIZE 200

SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('STA:7a6b4442j5pcz') from dual;

DBMS_SQLTUNE.REPORT_TUNING_TASK('STA:7A6B4442J5PCZ')

--------------------------------------------------------------------------------

GENERAL INFORMATION SECTION

----------------------------------------------------------------------------

---

Tuning Task Name                  : STA:7a6b4442j5pcz

Tuning Task Owner                 : SYS

Scope                             : COMPREHENSIVE

Time Limit(seconds)               : 60

Completion Status                 : COMPLETED

Started at                        : 10/16/2012 19:47:

27

Completed at                      : 10/16/2012 19:47:54

Number of SQL Profile Findings    : 1

----------------------------------------------------------

FINDINGS SECTION (1 finding)

--------------------------------------------------------------------

-----------

1- SQL Profile Finding (see explain plans section below)

--------------------------------------------------------

  A potentially better execution plan was found for thi

s statement.

  Recommendation (estimated benefit: 99.94%)

  ------------------------------------------

  - Consider accepting the recommended SQL profile.

    execute dbms_sqltune.accept_sql_profile(task_nam

e => 'STA:7a6b4442j5pcz',

            replace => TRUE);

HOW TO SQL - 1

This is the first post from a series of posts during which I intend show you some interesting things and also make you aware of the importance of not just writing SQL, but understanding how SQL is processed.

Here I will try to explain some basic internal details in every relational database management server (RDBMS), with emphasis on Oracle server arcitecture. 

Databases are the main core in every business, because they are the base for record keeping. There are a multitude implementation of RDBMS: MySQL, DB2, PostgreSQL, SQL Server, Oracle, Informix, GreenPlum (this is new to me) etc., each with its specificity, advantages and , in some cases, limitations.

Most RDBMS, if not all, use a client-server implementation, where first, client interacts with user and takes input from user, then passes it to server. Server then will query the database to get data related to the query and will send data back to client, that will then use the data retrieved from database. From the implementation point of view, a RDBMS is made of a server and a database, the server or instance has the processes and internal memory structures, and database has only the files with data (datafiles).

The final scope of all databases, beyond the storage of data, is to maximize the throughput and speed of retrieving data to the client. This is achieved through caching data blocks and internal structures that are accessed frequently.

The best example in importance of cache usage, is the database implementation behind Facebook, that uses a large MySQL cluster for data storage and a large implementation of MemCached servers for caching requested data.

In Oracle’s implementation of caching, it’s used the main memory of server (SGA – system global area) that is structured in specific zones to hold:

·        copies of data blocks read from datafiles (Buffer Cache)

·        information about changes made to the database (Redo log buffer)

·        runtime information (Shared Pool), that id distributed in several areas:

o   one for the library cache that contains:

§  shared SQL areas to hold the parse tree and execution plan for a given SQL statement, for reuse,

§  private SQL areas (in the case of a shared server configuration) to hold specific information about each SQL run

§  PL/SQL procedures and packages, 

§  control structures such as locks and library cache handles

o   one for the dictionary cache, that holds a collection of database tables and views containing reference information about the database, its structures, and its users

o   one for the result cache, that is composed of the SQL query result cache and PL/SQL function result cache,

o   one for buffers for parallel execution messages, and control structures

·        data needed mainly in I/O intensive operations (Large Pool) 

·        information related to all session-specific Java code and data within the JVM (Java pool)

·        structures specific to Oracle Streams (Streams Pool)

In addition to this memory structure, Oracle Server allocates a program global area (PGA) to each server process, and it is used to processing SQL statements and to hold logon and other session information.

That is why in accessing a database server through SQL language is crucial to use to the maximum these memory structures:

·        Buffer cache to hold frequently accessed data, through caching

·        Shared Pool to store frequently accessed SQLs in an analyzed and optimized form, by reusing frequently used statements.

In complete processing of an SQL, there are several stages: parse, compile, optimize, execute and fetch. When the analyzed form of an SQL is already in memory (Shared Pool), its processing will have only execute and fetch stages. This is the case for SQLs with bind variables and prepared statements.

The heart of the Oracle server, when it comes to optimize an SQL, is the CBO (Cost Based Optimizer) that is used to analyze all the available information regarding data, in order to find the best execution plan. To achieve this, the optimizer access information related to access paths to data, join methods and order, data statistics, hardware performance details (CPU, I/O, memory, RAID) and internal parameters.  

Thus, the optimizer is influenced globally in his decision, mainly by instance parameters like optimizer_mode. This can be set to be optimized for throughput for all data (ALL_ROWS), usually used in DWH databases, or to be optimized for quick retrieval of first n rows from a query  (FIRST_ROWS(n)), usually used in OLTP systems.

This is one of many parameters that can be set at instance (ALTER SYSTEM), session (ALTER SESSION) or statement level (using hints). The result of the CBO activity will be an execution plan with the smallest cost associated to its execution.

Any databases that I mentioned above have a specific implementation of optimize engine that is used in choosing the best way to execute a SQL.

As a conclusion rule, when writing a query to a specific RDBMS, try to understand what the server expects to receive, and what the basic rules in taking a decision are, because more of the times the optimizer has a very different approach regarding our queries.

During following posts I will show you some internal thinking from the server point of view, step by step.