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.
Example:
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?

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)
  

needs


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
  

example

• 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
  

goal

• 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'
PASSING JCOL
RETURNING NUMBER
NULL ON ERROR) from t1;


select JSON_VALUE('$.geo.lat' 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)
columns
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


cons.

• 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 11.2.0.2
  
• 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
  

references

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
  

configuration

• 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 11.2.0.3 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

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


Memory

• 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

example:

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

Partitioning

• 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

Parallelization

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, odp.net


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.create_profile(profile_name);
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 (asktom.oracle.com)
• 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

http://apex.oracle.com/oow

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)

ODP.NET

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

Networking

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

DRCP

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

JDBC

• 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

OCI

Performance

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

Database

• 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

PL/SQL

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

references:

www.mhprofessional.com/dbsec

Compressing LOB segments with securefiles

After Oracle Database 11G we have a new storage option for the LOB segments which is securefiles. By enabling securefiles LOB segments can be compressed significantly. After I have faced an unusually extending tablespace (approximately 30G per day) which may halt the database because of this unplanned extend, I have start digging into the problem and find out this LOB column which hold the unstructured HTML data inside it, its size was more than 350Gb lying inside a total of 600Gb database which means more than half of the database is only this one column. This is just unacceptible. In the following example you can find the same scenerio on the test system. Here the uncomressed LOB column holds 22Gb of space.

select 
  OWNER, 
  TABLE_NAME, 
  COLUMN_NAME, 
  SEGMENT_NAME, 
  INDEX_NAME 
from 
  DBA_LOBS 
where 
  TABLE_NAME='AUD_PRODUCT_BIG_DATA';

/*
OWNER       TABLE_NAME            COLUMN_NAME   SEGMENT_NAME                INDEX_NAME
--------    --------------------  -----------   -------------------------   ------------------------
SCH_DMALL   AUD_PRODUCT_BIG_DATA  DESCRIPTION   AUD_PRODUCT_BIG_DATA_CLOB   SYS_IL0000126287C00004$$
*/

select bytes/1024/1024 from dba_segments where segment_name='SYS_LOB0000126287C00004$$';  
--22GB

After moving the LOB column with the storage option of "SECUREFILE" and "MEDIUM COMPRESS" option the size of the LOB segment decreased to approx. 833Mb. More than 1/25 compression which is as valuable as gold in this production database.

ALTER TABLE sch_dmall.aud_product_big_data move lob (description) store
AS
  SECUREFILE aud_product_big_data_clob (TABLESPACE TS_AUD_LOB_SEGMENT COMPRESS MEDIUM) parallel 4;

select bytes/1024/1024 from dba_segments where segment_name='AUD_PRODUCT_BIG_DATA_CLOB';  
--833.125

There are three levels of compression which is LOW MEDIUM and HIGH which defines the compression level. Of course the higher the level gets, the more CPU power you need to spend. here I used MEDIUM to see the result and it is pretty convincing to not to change the setting.


References:

http://blog.trivadis.com/b/mathiaszarick/archive/2012/03/05/lob-compression-with-oracle-strange-multiple-physical-reads.aspx

http://www.oracle-base.com/articles/11g/secure-files-11gr1.php