oracle database training€¦ · oracle data guard oracle flashback technology asm recovery manager...

Oracle Training

10g, 11g

Maria Mundrova

DWH Developer/DBA

Adastra Bulgaria

Oracle 9i, 10g, 11g Administrator

2

Training Agenda

Oracle Database Concepts Oracle Database Architecture

Oracle Database Features

Oracle Database Application Development SQL, PL/SQL, Java

Oracle Database Data Warehousing Partitioning in Data Warehouses

Indexes

Maintain the Data Warehouse

- Load large amount of data

- Delete large number of records

Data warehouse Performance

- Query Rewrite

- Star Transformation

- SQL for Aggregation and Analysis

Oracle 11g Database New Features

PL/SQL New Features & Enhancements in

Oracle Database 11g

Q & A

3

Oracle Database Architecture An Oracle database server consists of:

Database - set of files that store data

Database instance - memory structures and background processes

4

Oracle Database Architecture SGA –shared memory structures with control information for Oracle instance

Shared Pool

Shared SQL Area – contains parse tree with SQLs. Oracle saves memory using a

Shared SQL area for statement run multiple times ; library hit/miss

Data Dictionary Cache – tables and views with reference data about database, users

Server Result Cache – SQL query result cache and PL/SQL function result cache

Reserved Pool – allocate large chunks of memory for large objects to be loaded

Large Pool – optional area memory for I/O processes, backup/restore operations

Database Buffer Cache – holds copies of data blocks read from data files; cache hit/miss

Redo Log Buffer – holds redo entries with information about changes made to the

database

Fixed SGA – contains internal information about the state of the database, instance, locks

size is set by Oracle database and can not be altered manually.

Java Pool – memory for all session Java code

Streams Pool – if not configured , it is automatically allocated when Streams is first used

PGA – data and control information for a server process

Private SQL Area – bind information, runtime memory structures

Each session that issues SQL has private SQL area

Session Memory – holds session’s variables, logon information

For a shared server, session memory is shared and not private

5

Background Processes

DBWRn – writes dirty buffers in the buffer cache to datafiles on disk. If number of free buffers

goes too low and users need to read blocks form disk into the cache, DBWR writes dirty

buffers to disk to ensure free buffers.

CKPT:

1.DBWR writes dirty buffers to the datafile

2.Latest SCN is updated to the datafile header

3.Latest SCN is written to the controlfiles

CKPT occurs when – redo log switch, log_checkpoint_interval reached, alter system

checkpoint

LGWR – writes redo entries from the redo log buffer to a redo log file on disk when:

Commit transaction

Redo log buffers

- When the redo log buffer is one third full

ARCn – copies online redo log files to archive log; only when database is in ARCHIVELOG

RVWR – writes “before” image to the flashback database logs. Flashback log is read to

perform Flashback Database recovery.

PMON – process recovery when user process fails; cleans up the db buffer cache and free

resources the user was waiting for

SMON – performs recovery, if necessary at instance startup

MMON – manageability tasks to issue alerts when metrics violated

MMNL – light-weight manageability tasks like session history capture, compute metrics

QMNn - monitor message queues for Oracle Stream Advanced Queuing

6

Oracle database files

Datafiles – Oracle stores data logically in tablespaces and physically in datafiles

select * from v$datafile

Control Files – contains information about database needed at startup and during normal

operation:

Database name

Database creation timestamp

Tablespace inforemation

Archive log information

Checkpoint information

Oracle recommends to use multiplied control files

select * from v$controlfile

Online Redo Log files – record all changes made to data. Two or more copies of redo logs can

be maintained on different disks

7

Oracle database features

Oracle High Availability

Oracle RAC

Oracle Data Guard

Oracle Flashback Technology

ASM

Recovery Manager

Flash Recovery Area

Oracle Stream

Partitioning

Partitioned Tables

Partitioned Indexes

Partitioning Methods

8

Oracle RAC

Multiple instances linked by interconnect to share

access to Oracle database

Single database system that spans multiple hardware

systems as unified database system to an application

Shared Storage can use:

ASM

Raw devices(prior to 11g R2)

CFS

OCFS

GPFS

OCR and Voting disk

9

Oracle Data Guard

Provides services to create,

maintain, monitor one or more

standby databases to enable

production Oracle database to

survive disasters, failures

Database Types

Logical Standby

Physical Standby

Protection Modes

Maximum protection

Maximum availability

Maximum performance

10

Maximum Availability Architecture

MAA has 2 identical sites:

Primary site with RAC primary

database

Secondary site with RAC

standby database

Why is it MAA?

Computer failures

RAC(no downtime)

Data Guard(sec to mins)

Oracle Streams(no downtime)

Storage failures

ASM(no downtime)

RMAN(mins to hours)

Data Guard(secs to mins)

Oracle Streams(no downtime)

Site failures

RMAN(hours to days)

Data Guard(secsto mins)

Oracle Streams(secsto mins)

11

Oracle flashback technology

Oracle Flashback Query

Ability to view data as it existed in the past, undo data is persistent and will survive

database malfunction or shutdown

Recovering lost data, rows deleted/updated even committed

Oracle Flashback Table

Recover table(s) to a previous point in time

Does not restore DML actions

Oracle Flashback Database

Efficient alternative to database point-in-time recovery

Oracle Flashback Drop

Restore dropped table( when a table is dropped, Oracle places it into the Recycle Bin)

Oracle Flashback Restore Points

12

Partitioning

Partition table

Any table can be partitioned

except with LONG or LONG RAW

columns

Partitioned index-organized table

CREATE TABLE labor_hour (

WORK_DATE DATE,

EMPLOYEE_NO VARCHAR2(8),

CONSTRAINT pk_labor_hour

PRIMARY KEY (work_date,

employee_no))

ORGANIZATION INDEX

PARTITION BY RANGE

(<partitioning_column>)

(<partition definitions>);

IOT is organized like B*Tree Indexes

Partitioning Methods:

Range

List

Hash

Composite

13

Oracle database application development SQL

DDL,DML,Session, System, Transaction Control

PL/SQL

Variables, Constructs, Cursors, Exceptions, Dynamic SQL, Functions/Procedures/Packages

Java

Java stored procedures

PL/SQL and Oracle Integration

JDBC

JMS

Bind variables – key to application performance

14

Partitioning in DWH

Partition for improved performance

http://www.oracle.com/technology/oramag/oracle/06-sep/o56partition.html

Consider what you expect to get out of partitioning:

Performance – when queries directly reference partition key in the where clause, you

may use it to reduce the number of partitions to scan in range scans, FTS. In many

cases situations partitioning can reduce performance

Management – significant benefit is from management perspective - swap, truncate,

merge, exchange partitions; make life easier when loading/deleting data at the partition

level

Range Partition – most common

Maps rows to partitions based on ranges of columns values( usually dates); historical data

CREATE TABLE sales_range

(salesman_id NUMBER(5),

salesman_name VARCHAR2(30),

sales_amount NUMBER(10),

sales_date DATE)

COMPRESS

PARTITION BY RANGE(sales_date)

(PARTITION sales_jan2000 VALUES LESS THAN(TO_DATE('02/01/2000','DD/MM/YYYY')),

PARTITION sales_feb2000 VALUES LESS THAN(TO_DATE('03/01/2000','DD/MM/YYYY')),

PARTITION sales_mar2000 VALUES LESS THAN(TO_DATE('04/01/2000','DD/MM/YYYY')),

PARTITION sales_apr2000 VALUES LESS THAN(TO_DATE('05/01/2000','DD/MM/YYYY')));

http://www.oracle.com/technology/oramag/oracle/06-sep/o56partition.html

15

When to partition

Table

Table > 2GB

Table with historical data

Contents of table need to be distributed on different types of storage device

Index

Avoid rebuilding the entire index

Maintain on parts without invalidating the entire index

16

Partitioning in DWH

Partition for improved performance Hash Partition

Maps rows to partitions based on linear hashing algorithm Oracle applies to the partitioning key.

Useful when there is no obvious range key or when range partitioning will cause uneven distribution.

CREATE TABLE invoices

(invoice_no NUMBER NOT NULL,

invoice_date DATE NOT NULL,

comments VARCHAR2(500))

PARTITION BY HASH (invoice_no)

PARTITIONS 4

STORE IN (users, users, users, users);

List Partition

Can group unordered and unrelated set of data; multi-column partition key not supported

CREATE TABLE sales_list

(salesman_id NUMBER(5),

salesman_name VARCHAR2(30),

sales_state VARCHAR2(20),

sales_amount NUMBER(10),

sales_date DATE)

PARTITION BY LIST(sales_state)

(PARTITION sales_west VALUES('California', 'Hawaii') COMPRESS,

PARTITION sales_east VALUES('New York', 'Virginia', 'Florida'),

PARTITION sales_central VALUES('Texas', 'Illinois'));

17

Partition existing table

Exiting table test_table is not partitioned

Create new test_table2 with the same constraints and indexes

CREATE TABLE test_table2 (

id NUMBER(10),

created_date DATE,

data VARCHAR2(50)

)

PARTITION BY RANGE (created_date)

(PARTITION test_table_2010 VALUES LESS THAN (MAXVALUE));

-- Add new keys, FKs and triggers.

ALTER TABLE test_table2 ADD (

CONSTRAINT test_table_pk2 PRIMARY KEY (id)

);

CREATE INDEX bita_created_date_i2 ON test_table2(created_date) LOCAL;

Exchange partition

ALTER TABLE test_table2

EXCHANGE PARTITION test_table_2010

WITH TABLE test_table

WITHOUT VALIDATION

UPDATE GLOBAL INDEXES;

Drop old table and rename the new table and its constraints

18

Indexes

B-Tree Indexes

Bitmap Indexes

Bitmap Join index

Function-based Indexes

Partitioned Indexes

Local - All index entries in a single partition will correspond to a single table partition

(equipartitioned). They are created with the LOCAL keyword and support partition

independance.

Global - Index in a single partition may correspond to multiple table partitions. They are

created with the GLOBAL keyword, do not support partition independance.

Cost of an Index

Each index maintained by a DML of an indexed key requires 3 times more resources

than actual DML on a table without indexes

Do not use indexes as panacea:

Remove nonselective indexes to speed DML

Consider reordering columns in concatenated indexes

Order column with most selectivity first

19


Partitioning to improve DWH refresh

Merge operation

MERGE USING Product_Changes S -- Source/Delta table

INTO Products P -- Destination table

ON (P.PROD_ID = S.PROD_ID) -- Search/Join condition

WHEN MATCHED THEN UPDATE -- update if join

SET P.PROD_LIST_PRICE = S.PROD_NEW_PRICE

WHEN NOT MATCHED THEN

INSERT (PROD_ID, PROD_STATUS, PROD_LIST_PRICE) -- insert if not join

VALUES (S.PROD_ID, S.PROD_NEW_STATUS, S.PROD_NEW_PRICE);

MERGE USING Product_Changes S

INTO Products D ON (D.PROD_ID = S.PROD_ID)

WHEN MATCHED THEN

UPDATE SET D.PROD_LIST_PRICE =S.PROD_NEW_PRICE, D.PROD_STATUS =

S.PROD_NEWSTATUS

DELETE WHERE (D.PROD_STATUS = "OBSOLETE")

WHEN NOT MATCHED THEN

INSERT (PROD_ID, PROD_LIST_PRICE, PROD_STATUS)

VALUES (S.PROD_ID, S.PROD_NEW_PRICE, S.PROD_NEW_STATUS);

20


Load large data

1. create temp table with data to be loaded

use create table as select * from ( the table partitioned )

2.load new data into temp

2.1 can use direct-path or append

3. gather stats on temp table

4. create indexes on temp table for each of the local indexes on the partitioned table

4.1 Use the COMPUTE STATISTICS clause on the CREATE INDEX command to gather

cost-based optimizer (CBO)

statistics during the index build. This is a "free" operation, which saves having to scan

the indexes after they are built

5. Exchange temp table with partiton

5.1 Use the UPDATE GLOBAL INDEXES clause to maintain any GLOBAL indexes

Note: When creating an index, Oracle 10g and above automatically compute optimizer

statistics. And even before that, 9i had a COMPUTE STATISTICS clause to accomplish

the same thing. Unfortunately not true for CREATE TABLE AS SELECT yet.

http://download.oracle.com/docs/cd/E11882_01/server.112/e10819/changes.htm#BEHBDHHG

21


Delete large amount of data

Partitioned table by period

Instead of deleting rows , drop the old partition

Non-Partitioned table when data to be deleted is large that the data to keep

1. create table <newTable> as select * from <oldTable> where ...

2. drop old table

3. rename <newTable> to <oldTable>

4. create the index.

22

Data Warehouse Performance

Query Rewrite

Queries with aggregates that require computations over large number of rows are expensive.

Query rewrite transparently rewrites such queries using MV.CREATE MATERIALIZED VIEW sum_sales_pscat_month_city_mv

ENABLE QUERY REWRITE AS

SELECT p.prod_subcategory, t.calendar_month_desc, c.cust_city,

SUM(s.amount_sold) AS sum_amount_sold,

COUNT(s.amount_sold) AS count_amount_sold

FROM sales s, products p, times t, customers c

WHERE s.time_id=t.time_id

AND s.prod_id=p.prod_id

AND s.cust_id=c.cust_id

GROUP BY p.prod_subcategory, t.calendar_month_desc, c.cust_city;

SELECT p.prod_subcategory, t.calendar_month_desc, c.cust_city,

SUM(s.amount_sold) AS sum_amount_sold,

COUNT(s.amount_sold) AS count_amount_sold

FROM sales s, products p, times t, customers c

WHERE s.time_id=t.time_id

AND s.prod_id=p.prod_id

AND s.cust_id=c.cust_id

GROUP BY p.prod_subcategory, t.calendar_month_desc, c.cust_city;

SELECT mv.prod_subcategory, mv.calendar_month_desc, mv.cust_city,

mv.sum_amount_sold, mv.count_amount_sold

FROM sum_sales_pscat_month_city_mv;

23

Star transformation

Star Schema, Query

Tune Star Query(*)

Bitmap index on each FK column of the fact

Enable optimizer feature for star-queries STAR_TRANSFORMATION_ENABLED=TRUE

Star transformation

Query performance for star queries implicitly rewriting SQL of the original star query

Oracle automatically chooses star transformation when appropriate(conditions in*)

Oracle processes using 2 phases:

1.Retrieves exact result set from fact table(utilizes bitmap indexes)

2.Join this result set to the dimension tables

Note:

If the query accesses a large percentage of the rows in the fact, it might

be better to use a full table scan and not use the transformations. However, if the

constraining predicates on the dimensions are sufficiently selective and only a

small portion of the fact table needs to be retrieved, the plan based on the

transformation will probably be superior.

Query with hint, bind variables, fact with no bitmap indexes does not support star transformation

Optimizer will not choose star transformation for small tables, or tables with good single table-

access path

24

Aggregation ROLLUP extension to GROUP BY- calculate multiple levels of subtotals across dimensions.

SELECT channels.channel_desc, calendar_month_desc,

countries.country_iso_code,

TO_CHAR(SUM(amount_sold), '9,999,999,999') SALES$

FROM sales, customers, times, channels, countries

WHERE sales.time_id=times.time_id

AND sales.cust_id=customers.cust_id

AND customers.country_id = countries.country_id

AND sales.channel_id = channels.channel_id

AND channels.channel_desc IN ('Direct Sales', 'Internet')

AND times.calendar_month_desc IN ('2000-09', '2000-10')

AND countries.country_iso_code IN ('GB', 'US')

GROUP BY

ROLLUP(channels.channel_desc, calendar_month_desc, countries.country_iso_code);

CHANNEL_DESC CALENDAR CO SALES$

Internet 2000-09 GB 16,569

Internet 2000-09 US 124,224

Internet 2000-09 140,793

Internet 2000-10 GB 14,539

Internet 2000-10 US 137,054

Internet 2000-10 151,593

Internet 292,387

Direct Sales 2000-09 GB 85,223

Direct Sales 2000-09 US 638,201

Direct Sales 2000-09 723,424

Direct Sales 2000-10 GB 91,925

Direct Sales 2000-10 US 682,297

Direct Sales 2000-10 774,222

Direct Sales 1,497,646

1,790,032

25

Analytical functions

RANK, DENSE_RANK

LAG/LEAD

FIRST/LAST

SELECT LAG (BUSINESS_DATE, 1) OVER (PARTITION BY LOCATION_CODE ORDER BY

BUSINESS_DATE ASC) AS PREV_ONE_BD,

BUSINESS_DATE AS BUSINESS_DATE,

LEAD (BUSINESS_DATE, 2) OVER (PARTITION BY LOCATION_CODE ORDER BY

BUSINESS_DATE ASC) AS NEXT_TWO_BD,

LOCATION_CODE AS LOCATION_CODE

FROM FCL_BUSINESS_DAYS

WHERE LOCATION_CODE = 'LDN‘

PREV_ONE_BD|BUSINESS_DATE|NEXT_TWO_BD|LOCATION_CODE

|30.12.2004|01.09.2005|LDN|

30.12.2004|31.12.2004|02.09.2005|LDN|

31.12.2004|01.09.2005|05.09.2005|LDN|

01.09.2005|02.09.2005|06.09.2005|LDN|

02.09.2005|05.09.2005|07.09.2005|LDN|

05.09.2005|06.09.2005|08.09.2005|LDN|

06.09.2005|07.09.2005|09.09.2005|LDN|

26

Oracle 11g new features Virtual Columns(VC)

Not physically stored in the table, but derived from data

Indexes on VC functional indexes

No DML operations on them

Expression of 1 VC can not refer to 2 VC

It can only refer to column form the same tableCREATE TABLE adastra_empl (

id NUMBER,

first_name VARCHAR2(50),

last_name VARCHAR2(50),

salary NUMBER(36,2),

comm NUMBER(3),

salary1 AS (ROUND(salary*(1+comm1/100),2)), ABRREVIATED SYNTAX

salary2 NUMBER GENERATED ALWAYS AS (ROUND(salary*(1+comm1/100),3)) VIRTUAL, VERBOSE SYNTAX

CONSTRAINT adastra_emp_pk PRIMARY KEY (id) );

SELECT column_name, data_default

FROM user_tab_columns

WHERE table_name = ‘ADASTRA_EMPL';

COLUMN_NAME DATA_DEFAULT

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

ID

FIRST_NAME

LAST_NAME

SALARY

COMM1

SALARY1 ROUND("SALARY"*(1+"COMM1"/100),2)

SALARY2 ROUND("SALARY"*(1+"COMM1"/100),3)

27

Oracle 11g new features

Partitioning just got better

New Interval Partitioning

Automatically creates a new partition when data outside the existing range is first

inserted(monthly partitions, automatic new partition first day of the month)

Reference Partitioning

Table ORDERS

Jan 2006

... ...

Feb 2006

Table LINEITEMS

... ...

• Partitioning key inherited through PK-FK relationship

• Avoids redundant storage, maintenance of order_date

RANGE(order_date)

Primary key order_id

• RANGE(order_date)

• Foreign key order_id

28


Virtual Column-Based Partitioning

Partition key can be defined by expression using one or more columns

Before Oracle 11g, table could only be partitioned if the partition key physically exists

Virtual columns remove this restriction and match business requirements to logically

partition objects does not match existing columns

Partition key columns defined on virtual columns of a table

Partition key is defined by an expression stored as metadata only

ACCOUNT_ID AMS1000000 physical column

ACCOUNT_BRANCH AMS derived virtual column used for partitioning key

CREATE TABLE accounts (

id NUMBER,

account_id VARCHAR2(10),

account_branch VARCHAR2(3) GENERATED ALWAYS AS ( UPPER(SUBSTR(TRIM(account_id), 1, 3))

) VIRTUAL

) PARTITION BY LIST (accoutn_branch)

( PARTITION part_a VALUES ('AMS',‘ALB',‘ANG‘,),

PARTITION part_b VALUES (‘BGL',‘BEL’),

PARTITION part_c VALUES (‘CYP',..),

PARTITION part_d VALUES (‘DEN‘,..) );

29


Pivot

ORA11GR1> select deptno,

sum( decode( job, 'CLERK', sal ) ) clerk,

sum( decode( job, 'SALESMAN', sal ) ) salesman,

sum( decode( job, 'MANAGER', sal ) ) manager,

sum( decode( job, 'ANALYST', sal ) ) analyst,

sum( decode( job, 'PRESIDENT', sal ) ) president

from emp

group by deptno

order by deptno

DEPTNO CLERK SALESMAN MANAGER ANALYST PRESIDENT

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

10 1300 2450 5000

20 1900 2975 6000

30 950 5600 2850

30


Pivot

ORA11GR1> select *

from (select deptno, job, sal

from emp ) e

pivot( sum(sal) for job in

( 'CLERK', 'SALESMAN', 'MANAGER',

'ANALYST', 'PRESIDENT' ) )

order by deptno

DEPTNO 'CLERK' 'SALESMAN' 'MANAGER' 'ANALYST' 'PRESIDENT'

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

10 1300 2450 5000

20 1900 2975 6000

30 950 5600 2850

31


Example 2 for PIVOT

CREATE TABLE pivot_test (

id NUMBER,

customer_id NUMBER,

product_code VARCHAR2(5),

quantity NUMBER

);

INSERT INTO pivot_test VALUES (1, 1, 'A', 10);

INSERT INTO pivot_test VALUES (2, 1, 'B', 20);

INSERT INTO pivot_test VALUES (3, 1, 'C', 30);




INSERT INTO pivot_test VALUES (7, 3, 'B', 70);


INSERT INTO pivot_test VALUES (9, 3, 'D', 90);


COMMIT;

select *

from pivot_test

ID CUSTOMER_ID PRODUCT_CODE QUANTITY

1 1 A 10

2 1 B 20

3 1 C 30

4 2 A 40

5 2 C 50

6 3 A 60

7 3 B 70

8 3 C 80

9 3 D 90

10 4 A 100

32


Example 2 for PIVOT

SELECT *

FROM (SELECT product_code, quantity

FROM pivot_test)

PIVOT (SUM(quantity) AS sum_quantity FOR (product_code) IN ('A' as A, 'B' as B , 'C' as C ));

is equivalent to

select sum(decode(product_code, 'A', quantity)) A_SUM_QUANTITY,

sum(decode(product_code, 'B', quantity)) B_SUM_QUANTITY,

sum(decode(product_code, 'C', quantity)) C_SUM_QUANTITY

from pivot_test

A_SUM_QUANTITY B_SUM_QUANTITY C_SUM_QUANTITY

210 90 160

33


Example 3 for UNPIVOT

SELECT * FROM unpivot_test;

ID CUST_ID

PROD_CODE

_APROD_CODE

_B

PROD_CODE

_C

PROD_CODE

_D

1 101 10 20 30

2 102 40 50

3 103 60 70 80 90

4 104 100

34


Example 3 for UNPIVOT

SELECT * FROM unpivot_test UNPIVOT (quantity FOR product_code IN (product_code_a AS 'A', product_code_b AS 'B', product_code_c

AS 'C', product_code_d AS 'D'));

ID CUSTOMER_ID PRODUCT_CODE QUANTITY

1 101 A 10

1 101 B 20

1 101 C 30

2 102 A 40

2 102 C 50

3 103 A 60

3 103 B 70

3 103 C 80

3 103 D 90

4 104 A 100

35


ENCRYPTED TABLESPACES

Oracle 10.2 column encryption

- Could not range scan

- PK/FK issues

Tablespace encryption removes those limitations

ORA11GR1> create tablespace userencrypted

datafile '/…/userencrypted.dbf' size 10m

ENCRYPTION default storage( encrypt );

Tablespace created.

36

PL/SQL New Features & Enhancements

simple_integer subtype of pls_integer increases performance with integer arithmeticCREATE OR REPLACE PROCEDURE simple_pls_integer AS

v_start NUMBER;

v_simple_integer SIMPLE_INTEGER := 0;

v_simple_integer_inc SIMPLE_INTEGER := 1;

v_pls_integer PLS_INTEGER := 0;

v_pls_integer_inc PLS_INTEGER := 1;

BEGIN

v_start := DBMS_UTILITY.get_time;

FOR i IN 1 .. 10000000 LOOP

v_pls_integer := v_pls_integer + v_pls_integer_inc;

END LOOP;

DBMS_OUTPUT.put_line('PLS_INTEGER: ' || (DBMS_UTILITY.get_time - v_start) || ' hsecs');


FOR i IN 1 .. 10000000 LOOP

v_simple_integer := v_simple_integer + v_simple_integer_inc;

END LOOP;

DBMS_OUTPUT.put_line('SIMPLE_INTEGER: ' || (DBMS_UTILITY.get_time - v_start) || ' hsecs');


END simple_pls_integer;

37


Performance Test Results for simple_integer and pls_integer

oracle@BGLORA01:/home/oracle$ sqlplus /nolog

SQL*Plus: Release 11.1.0.7.0 - Production

Copyright (c) 1982, 2008, Oracle. All rights reserved.

SQL> conn sh/welcome@hera;

Connected.

SQL> SET SERVEROUTPUT ON

SQL> EXEC simple_pls_integer

PLS_INTEGER: 23 hsecs

SIMPLE_INTEGER: 22 hsecs

PL/SQL procedure successfully completed.

SQL> ALTER SESSION SET PLSQL_CODE_TYPE=NATIVE;

Session altered.

SQL> ALTER PROCEDURE simple_pls_integer COMPILE;

Procedure altered.

SQL> SET SERVEROUTPUT ON

SQL> EXEC simple_pls_integer

PLS_INTEGER: 9 hsecs

SIMPLE_INTEGER: 2 hsecs


SQL>

38


Performance Test Results for simple_integer and pls_integer

CREATE TABLE forall_example (

id NUMBER,

description VARCHAR2(100)

);

INSERT INTO forall_example VALUES (1, 'ONE');

INSERT INTO forall_example VALUES (2, 'TWO');

INSERT INTO forall_example VALUES (3, 'THREE');

INSERT INTO forall_example VALUES (4, 'FOUR');

INSERT INTO forall_example VALUES (5, 'FIVE');

INSERT INTO forall_example VALUES (6, 'SIX');

INSERT INTO forall_example VALUES (7, 'SEVEN');

INSERT INTO forall_example VALUES (8, 'EIGHT');

INSERT INTO forall_example VALUES (9, 'NINE');

INSERT INTO forall_example VALUES (10, 'TEN');

COMMIT;

39


PLS-00436

Before Oracle 11g

40


PLS-00436 restriction has been removed

Oracle 11g

DECLARE

CURSOR c IS

SELECT * FROM forall_example;

TYPE c_table IS TABLE OF forall_example%ROWTYPE;

g_c c_table;

BEGIN

SELECT *

BULK COLLECT INTO g_c

FROM forall_example;

FORALL i IN g_c.first .. g_c.last

UPDATE forall_example

SET description = g_c(i).description

WHERE id = g_c(i).id;

COMMIT;

END;

/


41

Thank you!

Questions and Answers

AQ&

oracle database training€¦ · oracle data guard oracle flashback technology asm recovery manager...

Documents