Friday, September 27, 2013

OOW Notes - ASM New Features on 12c

Thursday 12:30 - ASM New features

  • simple idea
  • 1to1 ASM to server
  • shared disk groups
  • wide file striping
  • 10years old in oracle database

grid computing .. cloud computing
  • more consolidation → more database instances per node
  • more clustering → more nodes in clusters
  • larger storage configurations

ASM evolves
  • maximize ASM robustness because increased number of database instances running on large servers
  • minimize ASM overhead because of large clusters
  • minimize cluster reconfiguration overhead because of large clusters

engineered systems (EXADATA and database appliance)
  • complete solutions
  • optimized for a particular objective
  • storage management provided by ASM
  • storage cells provides management offloading

flex ASM (NEW)
  • eliminate requirement for an ASM instance on every cluster server
  • database instances connect any ASM instance in the clustering database instances can failover to a secondary ASM instance
  • admins specify the cardinality of the ASM instances (default 3)

ASM network
in the previous version an oracle cluster required:
  • public network for client applications
  • one or more private networks for interconnect communication within the cluster

remote access (NEW)
in the previous versions database instances use os authentication to connect ASM
ASM clients and the server are always on the same server so this should change

12c database instance and ASM servers can be on different servers
  • flex ASM users password file authentication
  • password file in the ASM diskgroup
  • default config. is configured with the ASM installation

other flex features
  • increased mac number of disks to 511 (previous 63)
  • command for renaming ASM disk
  • ASM instance patch-level verification
  • replicated physical metadata
  • improves reliability
  • virtual metadata has always been replicated with ASM mirroring

admins can now specify a failure group repair time (NEW)
  • similar to existing disk repair time
  • new diskgroup attributes; failgroup_repair_time
  • default time 24 hours
  • power limit can be set for disk resync. operations
  • Conceptually similar to power limit setting for disk group rebalance
  • rebalance: 1 (least resource) to 1024 most resources

disk rescync now check points (NEW)
interrupted resync operations are automatic restart

sometimes rebalance operations are required to restore redundancy.
a disk fails and no replacement available
HBA containing a failure group goes offline

  • with oracle database flex ASM optimized reorganization
  • critical files such as control files log files are restored before datafiles
  • secondary failure is less likely result in critical file loss

  • admins can now specify the content type for each disk group:
  • new disk group attribute; content.type (possible values data, recovery or system)

  • disk group primary/secondary partnering changes with content.type
  • decreases likelihood that multiple failures cause data loss

silent data corruption is a fact in todays storage world
  • database checks for logical consistency when reading data
  • if a logical corruption is detected then automatic recovery can be performed using ASM mirror copies
  • for seldom accessed data, over time all mirror copies of the data could be corrupted

  • with oracle db 12c data can be proactively scrubbed (NEW)
  • scrubbing occurs automatically during rebalance operations
  • with flex ASM, most rebalance tasks offloaded to EXADATA storage
  • each offload request can replace numerous IO

IO distribution in 12c (NEW)
  • each read request is sent to at least loaded available disk
  • even read is transparent to apps and enabled default
  • users on IO bound systems should notice a performance improvement

managing flex ASM (nothing changes)

  • srvctl status asm
  • srvctl modifiy asm -count 4
  • srvctl start asm -n node_name
  • srvctl stop asm -n nodename

ASM Trivia's

why power limit setting value to 11. Why 11?

what was it is called before ASM?

all asm file extent pointers are protected from corruption by XORing to a constant.. what is that constant? 42

The acronym describing ASM data allocation and protection policy is LIKE what?

Thursday, September 26, 2013

OOW Notes - JSON data in Oracle Database

Wednesday 17:00 – Storing and Querying JSON Data in Oracle Database 12c

Understanding schema-less data management

growing trend: semi-structured data without string schema requirements
data first → schema later

growing trend adopting semistructured data tinto the database

  • xml
  • json (nosql - mongodb)


structural and nonstructural coexist
  • using single RDBMS to store both
  • using one declarative set oriented SQL
  • parallel replication security transaction in one infrastructure

data can be stored without schema
  • store semi structural data in its native aggregated form without shredding

data can be queried without schema
  • use sql extended with semi structured query languages

  • SQL/XML embedding XQUERY/XPATH
  • SQL/SJON embedding JSON path language

data can be indexed without schema
  • indexing XML using structural XML index based on XMLTABLE expression
  • indexing XML using XML full text index

road map of managing JSON data using oracle XML/JSON db
  • JSON can be persisted without schema-less storing JSON data in varchar/raw/clob/blob columns
  • JSON data can be queried without schema (SQL/JSON embedding)
  • Function based indexes

overview of JSON

growing JSON adoption
  • strong usage in web client programming
  • rest interfaces (Facebook API)
  • starts to influence server side coding
  • data model for nosql stores like couchDB, mongodb
  • very well suited for rapid application development

  • make rdbms JSON aware storage queries indexing
  • leverage relational features sql indexed joins transactions
  • enable schema-flexiable app development (data first schema later)

storing JSON data
  • no new datatype use existing: varchar2, raw, blob, clob, bfile
  • existing OCI, .NET and JDBC api works on JSON documents

check constraint to make ensure only valid JSON gets inserted

create table jtab (col1 varchar2(4000)) constraint jcons check (col1 is JSON);
insert into jtab values '{“name”:”oracle”}';

querying with JSON data with JSON/SQL

SQL/JSON emerging standard by Oracle+IBM
native JSON processing no rewrite to Xquery etc..

Oracle: streaming evaluation (no need to load all data into the memory )

path expression similar to javascript
  • starts with symbol $
  • . to native inside an object
  • [] to navigate inside an array
  • $.phoneNumber[1].type
  • wildcards $.phonenumbers[*]?(type=”home”).number

JSON functionalities

use a path expression to select a value from the JSON data and return it as a SQL value

JSON_VALUE operator

select JSON_VALUE ('$.creditHistory[1].creditScore'
NULL ON ERROR) from t1;

select JSON_VALUE('$' passing col DEFAULT 'this is not a value' ON ERROR;

JSON_QUERY operator

select JSON_QUERY ('$.creditHistory[1]' PASSING col) from table_name;

  • return JSON object '{“year”:2011, “creditScore”:650}'
  • can return multiple values

JSON_EXISTS operator

select count(*) from table_name where JSON_EXISTS('$.creditScore' PASSING col);

JSON_TABLE rowsource

  • project JSON data as relational table
  • items inside array becomes rows
  • JSON tables can be chained

select jtab.*
from table_name, JSON_table ('$.phoneNumbers[*]' passing col)
ph_type varcahar2(10) path '$.type',
ph_num … path '$.number') jtab;

OOW Notes - Database Cloning and Direct NFS

Wednesday 11:45 - Cloning Oracle DATABASE

snapshot PDB – pluggable database cloning

underlying filesystem must support storage snapshots
  • asm and asm cluster file system (acfs)
  • nfs accessed with direct nfs
  • integrated with zfs, netapp, emc

user must have privileges to create and destroy snapshots
snapshots automatically mounted in the database node
snapshots automatically deleted when clone PDB is dropped
storage credentials saved securely in transparent data encryption keystore

  • source PDB cannot be remote pdb
  • source PDB nedds to be read only while cloning
  • source PDB cannot be unplugged/dropped while clones exist

v$dba_pdb_history used to monitor pdb clones (clonetag field indicated if PDB was cloned using snapshot copy)

pdb snapshot cloning on ZFS storage app.

Step by step guide on creating thin provisioned clones
describes how storage credentials are saved in the database store

pluggable database cloning

full copy creation time is proportional to PDB size
  • over 9 hours to clone a 1.3Tb PDB
  • snapshot copy clones a 1.3Tb in under 6 minutes.

Thin provisioned PDBs result huge space savings
Over 99%space saving compared to the source pdb size

direct nfs clone db

  • first introduced
  • clone production databases with minimal impact
  • prod data is safe and secure
  • uses a simple RMAN backup
  • refresh test instances with RMAN incremental backups
  • based on copy on write technology
  • huge storage space saving with thin provisioning
  • works with single instance and rac databases

  • easy to setup
  • works on all platforms
  • instantaneous cloning
  • no copies of the data
  • create multiple clones from a single backup
  • integrated with zfssa, netapp, emc sanpshots
  • v$clonedfile provides info on cloned files

MOS note 1210656.1

clone db used cases

  • hw/sw upgrades
  • app/OS patching
  • backup verification
  • app development and testing
  • recover oracle objects
  • run readonly report queries

%10 performance downgrade on test systems

direct nfs client

  • first introduced 11gR1
  • supports NFSv3, NFSv4, NFSv4.1 (except parallel NFS)
  • massively parallel IO architecture (each oracle process creates its own connection to NFS server)
  • simplifies client management and eliminates configuration errors
  • consistent configuration and performance across different platforms (even on Microsoft Windows)
  • significant cost savings for database storage (direct NFS is a free oracle option )

improved NAS storage performance
  • optimized NFS client for database workloads
  • support for direct IO and Async. IO

optimized scalability of NAS storage
  • supports up to 4 parallel network paths to storage

improves high availability of NAS storage
  • automatically load balances
  • automatic failover

MOS Notes
  • 1496040.1
  • 1495104.1
  • 1495709.1

direct NFS 12c enhancements

NFSV4 AND NFSV4.1 support is new in 12c

unified protocol for MOUNT, Port Mapper, NFS and NLM
  • simplifies client code and configuration

compound RPCs
  • improves latency by reducing the round trips to NFS server

session management
  • add flow control to the NFS protocol
  • creates bounded reply cache

  • nfs_version parameter in oranfstab
  • supported values NFSv3, NFSv4 NFSv4
  • default NFSv3

Oracle intelligent storage protocol

direct nfs use cases

  • RMAN backups
  • NON OISP (without tuning) 420Mbps
  • OISP 720Mbps

Customer Case – YAHOO

data marts running RAC and NFS storage
3 x 1GigE active-passive bonded interface
running into network throughput bottleneck with NFS
enabled dNFS over 320Mbps (without enabling 100Mbps)

%84 improvement comparing to kNFS
%19 improvement comparing to dNFSv1

Customer Case - Thompson Reuters

  • 65 TB on exadata datawarehouse
  • risk & Fraud people and company datamarts on this DWH
  • one datamart 32 TB on individual box (details below)
  • 6 x 10Gbit connection to private switch and then goes to storage BOX (all flash)
  • 4 controller and 3 direct path channel each (12 channels in total)
  • HP DL980 (80 Cores and 2Tb Memory)
  • About 2Gbps over 4 hour time period
  • Within this time period total of 10Tb IO read and write
  • Peaks as high as 5Gb pre second

Wednesday, September 25, 2013

OOW Notes - Some new features in SQL Developer 4.0

cursor on * brings all columns

select * from t_asset_brand;

explain plan button

explain plan brings drop down for the executed plans if the sql is executed

connection properties

connection color in the connection properties to change the border colors of the inner windows

cart option

menu -> view -> cart

drag and drop the tables to the cart window
create scripts for both ddl and dml
diff reports between carts
export cart data and ddl

advanced chart reports

you can test it before saving
while developing you can see the changes immediately

pdf exports are enhanced

allowed to generate template
allowed to add header and footer
create a detail report and generate into a secure pdf
report properties->pdf->security in order to add password protection to the pdf exports

OOW Notes - High Volume Text Indexing

Tuesday 12:00 - HighVolume text indexing with Oracle Text - Roger Ford

create index <index_name> on <table_name> (column_name) index type is ctxsys.context;

select from table_name
contains (product_name, 'monitor NEAR full hd', 99) > 0
order by score(99) desc;

index types:

  • context (primary)
  • ctxcat
  • ctxrule
  • Xquery Full Text

context index is inverted
consists list of words each with an associated posting list
posting lists can be very long (requires IO)
are stored in $I table
SGA works as is on this object always pin this table to SAG or flash cache if possible

Common Pitfalls


  • when indexing, postings lists are built in memory then flushed to disk when memory is exhausted
  • frequent flushes = small fragments

in memory controlled by MEMORY parameter

create index index_name
on table_name(col)
index type ctxsys.context parameters ('memory 1G');

exec ctxsys.ctx_adm.set_parameter('MAX_INDEX_MEMORY','10G');

Substring index / UTF8

context indexes are token (word) based

using leading wildcards are slow

need to turn on SUBSTRING_INDEX wordlist option

increases index build time
    1. : 5x long
    2. : %20 long

AL32UTF8 has significant indexing overhead (consider single byte character set)

Frequent syncs

  • context indexes are asynchronous
  • sync(on commit) will cause index fragmentation
  • can be cured by ctx_ddl.optimize_index

transactional will require unindexed documents to be scanned by all queries
use transactional only with sync(on commit)

if syncing frequently, optimize frequently
use 12c feature (near real time indexes)

Big IO

  • previously $I index were stored inline BLOBs
  • new storage attribute BIG_IO uses secure file lobs

Seperate offsets

posting lists consists of document Ids and word position in the document

some queries does not need word position

new separate_offset storage attribute will hugely reduce IO for these queries
will make proximity (NEAR), phase and zone section searcher will be a bit slower

Near Real Time Indexes

new storage attribute STAGE_ITAB

  • creates two level index
  • small enough to keep in memory
  • large main index stored in the disk
  • updates are moved from staging index to main index and optimized as they move
  • requires BIG_IO storage parameter in order to use

Query Filter Cache

some expressions are common to many queries
security restriction: .. “(sales or marketing) WITHIN dept”
postings entries for these queries may be very large
postings will likely be cached in sga but still need to be decompressed and merges

query filter cache, caches the results of subquery in memory
query operator ctxFilterCache(subquery)

Pattern stop class

specify regular expression exclusion list for index
large indexes can be %75 junk

  • reference codes
  • sequential/numeric data
  • wrongly indexed binary data

  • exclude all words longer than 20 chars
  • exclude all words which are mixed alpha and numeric


  • partitioned index can be created on partitioned base tables
  • use local keyword for indexes
  • currently only range partitioning is supported
  • partitioned indexes looks like separate indexes on partitions

  • better query performance on subset
  • less interference between indexing and querying
  • caching of more common target positions


parallel index creation is supported by using parallel n clause
can mix local and parallel but max 1 process per partition (db restriction)

to do intra-partition parallelization

  • create index using unusable keyword
  • run dbms_pclxutil.build_part_index and specify degrees on parallelization

index is unavailable until complete
no option for incremental builds

manual partition builds

create index using nopopulate parameter
build individual partitions using

alter index index_name rebuild partition partn parallel n;

allows complete control over parallelization
index is usable as soon as one operation is built

unlike unusable option; can build incremental changes by loading new partition and rebuilding

no sync required for non changing data
faster use partition swapping

distributed index builds

  • indexes can be built on farm of machines
  • partitioned base table on master with local indexes
  • non partitioned table on remote instance
  • build index on remote table, then transfer to master using transportable tablespace

swap in index using exchange partitioning
alter table table_name
exchange partition main_table_p10
with table remote_table
including indexes
without validation;

  • almost instant ($K IOT must be copied)
  • new data immediately searchable

OOW Notes - What is new in Oracle DB App. Development.

Monday 12:15 - Whats new in Oracle Database App. Development – Thomas Kyte

Database improvements

Application client continuity called TAF

problem: if you click an application button twice, did it really committed twice or if the node failure happened and my transaction rolled back (or finished successfully)

Transaction guard – first rdbms to preserve commit outcome.. at most one transaction execution. Used by jdbc-thin, oci, occi,

now able to ask if the transaction is rollback or commited to the transaction guard.

Logs are info held in the client side application server.

Sql translation framework

by creating profiles now it is able to transform transact-sql to oracle pl-sql.

--create a profile and translation
dbms_sql_translator.register_sql_translator(profile_name, 'select * from scott.emp','select* from scott.dept');

--in order to use in a transaction
alter session set sql_translation_profile=profile_name;
alter session set events '10601 trace name context forever, level 32';

APEX improvements

michael fitcher → web db → html db → apex

web browser → web listener / or web server with mod_plsql / or apex listener → database

4.2.3 is available

html5 supported charts and page items

packed applications
  • ask us (
  • group calendar
  • project tracking
  • issue tracker
  • checklist manager
  • meeting minutes

full support for apex in oracle multi-tenant architecture (apex installed in a container, patched and upgraded across all pluggable databases)

full support for 32K varchar2 columns
integration with real application security coming in apex 5.0

SQL Developer technology

sql command line extended reporting and charting.
Cart is enhanced.
Reports are greatly enhanced..
UI enhancements → border colors now able to change on connections
includes data modeler version 4.0
jdk 7 support and above

10012 → what is new in sqldeveloper 4.0 (I am already registered)


transaction guard support guarantees that a commit should be done at most once.
Support for PLSQL type (boolean)

Support for all 12c types.
Multitenant support
Impcilicit ref cursor support
Array binding

Apply keyword in SQL for enhanced LINQ support

global data services support
  • region based workload routing
  • connect time load balancing → global data services (gds)
  • distributed replicas

Data compression levels
  • connection level (connection string)
  • service level (tnsnames.ora)
  • database level (sqlnet.ora)
Large buffers
  • default sdu is 8k (was 2k before 11g)
  • max values was 64k
  • max values is now 2m (clobs and blobs)
Multitenant data source


Database resident connection pool vs shared server (serious differences and advantages)


  • Jpublisher maps packages to jdbc in 12.1
  • In database map reduce !?!
  • Drop in hadoop mappers and reducers (run as is)
  • Support for hadoop ecosystem components
  • SQL support for hadoop


  • xml based client config file
  • auto tune of statement cache size
  • array dml returns affected row count in an array


  • full pluggable database support

SQL and Datatypes

  • long string support
  • row count byte is 8byte integer from now on
  • bind directly to plsql packages
  • implicit result set supported


query plsql table type directly (no more to create sql types)

less namespace clutter

grant roles to code
  • invokers rights routines
  • code will run with current set of privileges of the invoker plus that roles
  • white list (that sp can only be runned from other piece code but never from sqldeveloper)
  • fine grained privilege to protect code

improved introspection
utl_call_stack → provides procedural, structured access to the information