things to remember when working with - abis.be · about static and dynamic sql 10.utilities things...

TRAINING & CONSULTING Things to remember when working with Oracle... (for UDB specialists)

Kris Van Thillo, ABIS

ABIS Training & [email protected]

2013

Document number: DB2LUWUserMeeting2013Front.fm5 March 2013

Address comments concerning the contents of this publication to:ABIS Training & Consulting, P.O. Box 220, B-3000 Leuven, BelgiumTel.: (+32)-16-245610, Fax: (+32)-16-245639

© Copyright ABIS N.V.

ABIS Training & 3

Think

xtents

s

Consulting

about ...

• (1) Instances and Databases

• (2) Bufferpools

• (3) Tablespaces, Datafiles and e

• (4) About UNDO

• (5) About physical row structure

Think about ...

1. Instances and Databases2. Bufferpools3. Tablespaces, Datafiles, and

Extents4. UNDO - tablespaces, seg-

ments5. About physical row struc-

tures6. Tables and Indexes7. Nulls8. Security9. About static and dynamic

SQL10. Utilities

T ABIS 4

• (6) Tables and indexes

• (7) Nulls

• (8) Security

hings to think about...

• (9) About Static and Dynamic SQL

• (10) Utilities

Think about ...





SQL10. Utilities

T ABIS 5

Instances and Databases 1

An instance is a database is an instance, right?

Basically, yes, ...· an instance ‘contains’ - services - ONE database [ie. physical struc-

ture]

AC environ-

e, as the in-

cally ‘catal-

tance (db by


[a database can be serviced by multiple instances in a Rment]

· the name of the database is generally of no importancstance is the object to be managed

· you connect to the instance, not to the database· if remote access is required, the instance should be lo

oged’ - added to local name resolution facilities (basic: tnsnames.ora)

· security by default implemented on an instance-by-insdb) level

Think about ...





SQL10. Utilities

T ABIS 6

Memory structures

Background procsInstance

Datafiles Redo log file

ArchivesArchives

Archives

Physicaldatabasestructure


Controlfile

DF1

DF2

DF3

DF4 DF5

Database

Userstablespace

Systemtablespace

Sysauxtablespace

Logicaldatabasestructure

tabletable

table

Think about ...





SQL10. Utilities

T ABIS 7

Database buffercache (dflt size)

Keepbuffer cache

Recycle bufferpool

Database buffer cache (size nK)


SGA

Shared pool Reserved pool

Library cache

SharedSQL




Large pool

Java pool

Streams pool

Redo log buffer cache Fixed SGA

Datadictionary

cache

Control structures

area

PLSQL procs& packages

Software code area

PGA

Stack space

Session information

Private SQL area

Runtime SQL area

Think about ...





SQL10. Utilities

T ABIS 8

Bufferpools 2

• Basically the same idea - cache data!

• Implemented differently:· not CREATEd, but ‘activated’ or ‘enabled’

ful

long [access


· not explicitely named (cfr. z/OS)· typically one per required page/block size type

[4 k, 8 k, 16 k, 32 k][subs: default, keep, recycle]

· no prefetchers - SPs read-in big blocks if deemed help[scan, index scan]DBA can influence what stays in memory, and, for how type, object definition]

· link to storage object through tablespaces

Think about ...





SQL10. Utilities

T ABIS 9

Tablespaces, Datafiles, and Extents 3

or if you prefer: Tablespaces, Containers, and Extents

An Oracle tablespace:· is an automatically managed DMS tablespace

occurs

AN, NAS

n based ta-


· assigned [default ?] to a bufferpool· in which NO Oracle managed extent balancing/striping

if striping is important:°) do it manually - based on sizing/DBA intervention°) rely on other Oracle (ASM) or third party software (Sbased solutions) <=> bigfile tablespaces

· [system, sysaux, temp, undo, users (?) - and applicatioblespaces]

· there is no such thing as an indexspace!

Think about ...





SQL10. Utilities

T ABIS 10

Think about ...





SQL10. Utilities

T ABIS 11

UNDO - tablespaces, segments 4

- store rollback or ‘undo’ information[undo is a more correct term]

- used for locking and read consistency, transaction rollback, and re-covery - versioning!

in undo ta-

e undo seg-

gt


- [created/activated/enabled] undo segments are stored blespaces - ie. physical objects

- general idea:· data change:

-> after image (AI) to transaction log (redo log)-> before image (BI) to undo segment; this modifies thment. Consequently:

=> AI undo modification is sent to the transaction lo=> BI undo modification is sent to the undo segmen[one extra generation kept]

Think about ...





SQL10. Utilities

T ABIS 12

30

20

Tx 333

Update ...Set ...Where

Tx 555

DeleteFrom ...Where

333

555

555

----1

----2

----3

Tx# Ts

A

A

B

Ext

Select ...From ...

(SCN300)

290

290


70

60

50

50*

40

333 header update 299333 10.50.3 7 old

555 10.70.90 row to insert

Commit;

A

B

A1

A2

B1

B210.70.90

10.50.3

555 ...555 ...

280

333

200

200

restored old row

299

new7

Think about ...





SQL10. Utilities

T ABIS 13

active(uncommitted)

inactive(committed)

requirednot overwritten

optional(overwritten)

TRANSACTION Tx1


unexpired expired

˚ required for transactionrollback

˚ NOT required fortransactionrollback˚ required for readconsistency, flashback

˚ NOT required fortransactionrollback˚ NOT required forreadconsistency˚ required for flashback

Think about ...





SQL10. Utilities

T ABIS 14

UNDO - tablespaces, segments - cont.

Flashback and Rewind!

- undo based:· flashback query


· flashback version query· flashback transaction query

· flashback transaction backout· flashback table

- not undo based:· flashback drop (recycle bin)

· flashback database (flashback logs)

Think about ...





SQL10. Utilities

T ABIS 15


select count(*) from test as of timestamp(TO_TIMESTAMP('07-04-2005 06:30:00', 'DD-MM-YYYY HH24:MI:SS'));

d b.e

e


eclare CURSOR PrevVersion is SELECT * FROM test as of timestamp TO_TIMESTAMP('07-04-2005 06:25:00', 'DD-MM-YYYY HH24:MI:SS'); Data PrevVersion%ROWTYPE; RowExist number := 0;egin...nd;

xecute dbms_flashback.enable_at_time( TO_TIMESTAMP('07-04-2005 06:30:00', 'DD-MM-YYYY HH24:MI:SS'));

Think about ...





SQL10. Utilities

T ABIS 16


Remember!

- versioning - and currently_committed for that matter - is an‘acquired taste’!

versioning


advantages and disadvantages!

- developping applications against a dbms enabled forrequires a distinct development skill set!

it will in all likelyhood require application changes!

Think about ...





SQL10. Utilities

T ABIS 17

About physical row structures 5

- row length is typically dynamic· all columns are stored using ‘variable length’ semantics

· numbers are typically stored ‘in decimal format’ (not binary)· nulls values are either implied or stored using placeholder

ns last]

ited to page

, not on any !


[most accessed first, fixed first/variable last, null colum

- no ‘max # rows per page’ concept; row length not limsize· migration (growing below page size) - moved to · chaining (growing beyond page size) - pieces

- rows are assigned to pages based on page free spacekind of ‘logical’, ie. value or key based, allocation scheme[based on bitmaps - LHWM, HHWM]

Think about ...





SQL10. Utilities

T ABIS 18

Think about ...





SQL10. Utilities

T ABIS 19

Tables and Indexes 6

- Basically the same

- Tables are divided in segments, to be assigned to a specific ta-blespace (or one, if so desired)· column based [data type based]

- your call

al tables, ... list, interval,

ntly

dexes, func-


· row based - partitions [partitioned tables]· matrix structure [?]

· different data types, LOBs stored in row OR out of row

Learn about/Read up on ... IOTs, [Hash] clusters, externand on the available partitioning options: range, hash,system, reference [ie. auto], ...

- Indexes are created on tables, and stored independe

Learn about/Read up on ... reverse indexes, invisible intion based indexes

Think about ...





SQL10. Utilities

T ABIS 20

Nulls 7

- null is an empty string is null

- nulls are:· stored - or not!


· never stored in traditional B*tree indexes[dense - sparse]stored in bitmap indexes

· what about your where conditions?[not null with default?]

Think about ...





SQL10. Utilities

T ABIS 21

Security 8

• user identification and validation basically different!

- Oracle managed - eg. password reuse requirements, ...

- verified by Oracle or externally (by default ‘not verified’ ie. trusted)

•

• ntegration, ...


- no integration with default OS platform security

Oracle uses privileges and roles:

Oracle roles <=> UDB authorisationsRoles can however be added

‘Advanced’ features supported - encryption, SSO, LDAP i

Think about ...





SQL10. Utilities

T ABIS 22

About static and dynamic SQL 9

All Oracle SQL - both static and dynamic SQL - is DB2 UDB dynamic, that is: optimized at run time.

[optimized => parsed]

he (*)]

ed on as-


[QEP <=> plan; cached in pools cfr. dyn statement cac

- hard parse:· first time ever parse

- soft parse· reuse existing parse statement

- softer-the-soft· application sends ‘parsed’ statement to the server

(*) QEP stored in cursor area’s - parent area, child area bassumed and observed access path

stored outlines, profiles

Think about ...





SQL10. Utilities

T ABIS 23

Utilities 10

Oracle: RMAN, Import/Export aka Dpump, Loader, Analyze (1), db_verify, ?? (2), ??(2)

DB2 UDB: Backup/Restore, Import/Export, Load, Runstats, ??, reorg, r

(( teristics


eorgchk

1) dbms_stats.gather_<object>_stats2) use command sequences to verify multiple storage charac

Think about ...





SQL10. Utilities

T ABIS 24

So what does Oracle compatibility mode do?

- Datatypes:· DATE data type based on TIMESTAMP(0)

[supports oracle style computations, eg ‘+1’]

· NUMBER

assumed

cs


[decfloat(16), decimal(p,s)]· VARCHAR2 and NVARCHAR2 (*)

[varchar, non-padded comparison semantics](*) hardly ever used!

- SQL data access level enforcement[what to expect in a stored object - SQL, NoSQL, Write?]

- Outer join operator ==> ‘+’ (*)(*) old school most Oracle apllis should use ansi SQL‘+’ refers to column where the missing values should be ‘present’, to generate the outer rows

- Hierarchical queries - connect by, start with, prior[CTE in Oracle still have somewhat limited functionality]

- INOUT parameter used when creating Oracle stored pro

Think about ...





SQL10. Utilities

T ABIS 25

So what does Oracle compatibility mode do? (II)

- ROWNUM

- DUAL table in Oracle is typically references with schema name[eg. old style coding requires sequence referencing using access to a table]


- Oracle data dictionary-compatible views[sys.dba_... sys.all_... sys.user_...

- PL/SQL support

- truncate table

Think about ...





SQL10. Utilities

T ABIS 26

Thank you!

AKk


BIS Training & Consultingris Van [email protected]

TRAINING & CONSULTING

things to remember when working with - abis.be · about static and dynamic sql 10.utilities things...

Documents