3zecisi3: October 2012

Monday, October 22, 2012

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

Wednesday, October 17, 2012

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

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

POS_DATA POS_DATA_P3 NO

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

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

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

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

POS_DATA SYS_P283 NO

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

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

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

POS_DATA SYS_P284 NO

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

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

POS_DATA SYS_P284 NO

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

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')

Tuesday, October 16, 2012

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

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

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

| 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:

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);