Oracle User Group Scotland Conference 2015

Oracle User Group Scotland Conference 2015

1) SQLcl (SQL command line) new enhanced SQL*Plus, easy to output index info, DDL, output/load data in csv, xml. Early adopter (beta) version available for download now, production version expected soon and to be bundled by default into Oracle 12cR2.

More at Kris Rice.

2) Relax and Recover Martin Nash

A free tool to make a bootable .iso file for fast bare metal recovering of any server, onto which the normal backup files could then be restored. Free GPL licence. Linux only.

3) Who changed my Plan? Presanth Kothuri

dbms_sqldiag.dump_trace – to generate a 10053 trace for any SQL in the shared pool


  • 9i – bind variable peeking
  • 10g – dynamic sampling
  • 11gR1 – adaptive cursor sharing (bind aware cursor, multiple explain plans based on bind variables) , extended statistics
  • 11gR2 – cardinality feedback
  • 12c – Adaptive Query Optimisation

Problem causes:

  • statistics
  • bind variable peeking
  • cardinality feedback
  • Bad SQL code
  • Missing indexes
  • Oracle Bugs, especially with cardinality feedback – e.g. partition pruning not used, global index used instead.

11gR1+ Adaptive Cursor Sharing limits bind variable numbers to 14. Runs slowly once, makes as bind aware.

12c Adaptive Plans – fix bad plans on the fly. Adaptive statistics – gather and persist statistics as statement is running.

Outlines – deprecated since 11g.

SQL Profiles – opt_estimate can auto generate and apply. Dbms_sqltune or OEM GUI, part of SQL tuning advisor. See in v$sqlplan.other_xml. Can use dbms_sqltune import_sql_profile (see blog).
SQL Plan Baselines
SQL Baselines

SQL Patches – undocumented, used by SQL repair advisor but can be used to create hints and inject them into SQL.

SQL Plan Baselines (11g) – dbms_spm
optimizer_capture_sql_plan_baselines = true – to gather
optimizer_use_sql_plan_baselines = true – to apply them
Plans are either accepted=used or unaccepted=evolution
Can move plans from e.g. test > production and from outlines > SQL plan baselines.
You can manually mark a plan as fixed in place.
Plan evolution – in 11g manually run dbms_spm.evolve, in 12c is an automatic nightly task.
12c has sqlobj$plan.
Dbms_sqltune.create_sqlset / .load_sqlset
Very powerful, do for every upgrade.
dbms_spm.configure ( plan_retention_weeks , 26 ) to change how long SQL plan baselines are kept for.

Signature column in sqlobj$ is used to match SQL = sql_text with no whitespace.

dbms_sqltune = SQL Profiles = needs diagnatics + tuning pack licence. Use force_match=yes for query different in literals
dbms_spm = SQL Plan Baselines = free with Enterprise Edition
dbms_sqldiag_internal = SQL Patches = undocumented, so licence free.

4) Oracle Enterprise Manager Cloud Control 12c High Availability Failover Zahid Anwar and Clive Archibald

Needed because Cloud Control could be running critical jobs, backups, alerting.

Create symbolic links on both servers, ‘/em’, for OMS install. This will be shared network storage.
vi /etc/hosts – set up shared OMS host name
vi /etc/oraInst_oms.loc – point to share storage with ‘inventory_loc=/em/…’
Install middleware and agent home both into /em
Change host name to shared host name
’emctl status oms -details’ – shows full config of servers etc.
’emctl status agent’
OMS lock file location – for performance change this to local storage. Everything else uses shared storage.
In DNS set TTL60 – time to live 60 seconds before DNS entry points to DR server. DNS is faster than specifying two addresses in tnsnames.ora
OMS + one EE database is licence free, all additional RAC or dataguard servers need to pay for a licence.

If running OMS and database together on server, need two inventories:
Shared inventory /etc/orainst_oms.loc – for main OMS, floats.
Local inventory /etc/orainst.loc – on both boxes, monitors local database.

Network response times recommended:
1ms – for full 12c Cloud Control
10ms – for older 10g OEM or with less options running.

Recommend physical server for database, but VMS ok for OMS.

Underlying RAC database also needs service:
srvctl add service .. -d ..
Create service on primary
Database trigger on startup on both databases to check and startup service if is primary currently.

5) Database Security in the Cloud Oded Raz

Oracle Cloud either:
1) Schema edition – get a schema, 50gb max.
2) Database Cloud Virtual Image – get root on a server, same as Azure and Amazon.
3) (coming soon) Database Cloud Automated – same as (2) but Oracle handle install and patching.
4) (coming soon) Fully managed database instance – Oracle handle almost all DBA tasks.

Schema edition 50gb max size, others unlimited.

Three prices:
1) SE1 – up to 16 OCPUs
2) EE (includes transparent data encryption)
3) EE High Performance – includes partitioning etc. options.
Pay per month or per hour.

Dbms_crypto string + key -> returns encrypted data. Needs very good key management.
Better (for encrypted data at rest) = Transparent Data Encryption. Uses a database wallet, which needs managed. Very low overhead on hardware ~% if you buy hardware with dedicated crypto CPU, 2-3% overhead otherwise.

Data Redaction (dynamic masking) e.g. call centre users get ‘xxxx-xxxx-4898’ while billing department gets full credit number. Dbms_redact.add_policy.
Only hides the result set, not the where clause, so can be hacked by e.g. a PL/SQL loop.

Data Masking (physical masking) – a grid control option when copying e.g. live -> test.

Database Vault privileged user control – has a new user, database vault owner, which is a super-DBA.
Then everyone else, even root, sysdba, dba, can only do what DVO grants them.
But is a very expensive extra cost option, so not used in practice – except is included with Oracle Cloud.

Oracle Label Security – grant permissions on rows.

Oracle Database Firewall – firewall for SQL statements + can alert.
Oracle Audit Vault – huge data warehouse for storing all the ODF generated info.
Practical alternative to database auditing.
Both available for non oracle databases.

6) MySQL Database Administration Mario Beck

Storage engines:
InnoDB – storage engine, owned by Oracle, has ACID, crash recovery, row locking, referential integrity, full text, etc. Is the default storage, used 99% time now, but some of the system objects still not moved to this.
MyISAM – old junk original storage engine. Still used for system objects.
Cluster etc. storage engines – rarely used.

MySQL config file = my.cnf (linux) aka my.ini (traditionally named windows).
Is kept in /etc or /etc/mysql or BASEDIR, check location with ‘lsof -p <' (linux) or windows system service definition -defaults_file= (often c:\windows, c:\, BASEDIR). Crucial parameter - innodb_buffer_pool_size=Xg = the buffer cache size. MySQL has an 'oracle' mode for SQL syntax. 'MySQL Workbench' is a separate download, free, has: Design - E-R, reverse engineer, compare. Development - stored procedures etc. Administration - edit my.cnf file, start/stop, monitor performance. Migration - wizard MySQL>MySQL, Oracle>MySQL.

1) MySQL dump – very slow, small databases only. Often people put database into read only mode for duration.
2) Snapshot filesystem – non MySQL solution, needs read only mode for the duration.
3) Replication – and backup slave system = best option.

Error log in DATADIR/hostname-err.log
General logfile – optional, for full auditing, not often used.
Slow query log – DATADIR/hostname-slow.log – read with mysqldumpslow. By default has all queries > 10s.
SQL – show variables like ‘%’ , show status like ‘%’.
Performance schema in MySQL5.6
SYS schema (like v$)
Workbench GUI for performance.

MySQL Enterprise Edition (costs) works with Oracle Enterprise Manager for MySQL. Also has MySQL Enterprise Monitor.
MySQL Enterprise Backup = full backup solution, although need to use InnoDB (the default) for online backup.

High Availability:
1) Replication = Master (read-write) -> Slave (read only)
2) MySQL Fabric = dgmgrl style monitoring and transparent application failover for (1)
3) OS or VMWare failover
4) MySQL Cluster – stores data in NDB which shards data across a set of active-passive pairs, e.g.:
Pair 1: Active-Passive = 1/3 data.
Pair 2: Active-Passive = 1/3 data.
Pair 3: Active-Passive = 1/3 data.

7) Oracle DB In-Memory Zohar Elkayam

Available from 12c only.
Is an extra cost option.
Uses more RAM.
No LRU mechanism.
Is compressed ~15:1.
Not (yet) available with active dataguard.

Enable with:
Parameter inmemory_size=xxxGB = this is part of SGA, but not part of buffer cache.
Alter table|partition inmemory ;
Drop unused indexes (used for analytics) because not needed now.

SGA will comprise:
-Buffer Cache
-Shared Pool
-Column Store In Memory Buffer = new

Useful SQL: select inmemory , inmemory_duplicate /* if not want to send around RAC */ from dba_tables ;

Can specify which columns of table are ‘in memory’.

8) Oracle License Sibaji Choudhury

If Oracle Corporation identify a violation, generally give you 30 days to either buy licence or deinstall. And they will backdate your charges = a ‘term licence’ plus support cost for that period.

Risk of audit:
Organisational (profit) growth,
Hardware refresh,
Merger / divestiture / change corporate structure.

Inclusions – e.g. Weblogic covers Oracle Application Server Enterprise Edition which in turn covers forms and reports stand alone – so don’t pay for both!

License agreement says compliance ‘sole responsibility of customer’.

Diagnostic pack is a pre requisite of tuning pack.

Processor or Named User Plus
Golden ratio: 1 processor licence = 50 NUP licenses, same price.
Min NUP cost is 25 NUPs per processor.
So for a 4 processor server, NUP min = 4*25 = 100 NUP min
= 1/2 the cost of a processor based licence for the same server
= save £80k pa (~£40k processor licence cost per year)

Named user is anyone authorised to use the software (i.e. dba_users not v$session).

Automated batch processing = users.

Multiplexing (pooled connections) = count all users. If can’t count/control users at all then must go for processor licence only.

SE has RAC free of cost from 10g onwards.
4 sockets max in cluster, e.g. 2 socket + 2 socket RAC.
SE1 has a 2 socket max overall.

Custom scripts killer – customer doesn’t really use an EB Suite module, except custom SQL queries its tables = need to pay up.

DR licensing
Backup tape = no licence
Failover = 10 days per calendar year ok to run it iff on same SAN.
Standby = pay full licence for both.
Mirroring = pay full licence for both.

So failover/standby if on same SAN (almost never) = 10 days ok.
If on different SANs (almost always) = pay up.

If primary is RAC, but failover/standby is non-RAC stand alone = need to pay for RAC option for all servers.

10 days is for 10 days in a calendar year, with any part of a day counted, so e.g. 10am-1am = 2 days.

Even if oracle is not installed on standby server, just a SAN replication/mirroring of dbfiles for DR, then pay up for both servers.

Only one free 10 day failover server allowed per live server – you can pick the server with the most CPUs as the free one.

Need electrical wired partition, to avoid paying licenses for all processors, between
Sun domain
HP node partition
IBM logical partition – and then only if capped, uncapped allowed to grow freely would need licensed.

Oracle matching service levels on e.g. EE/SE/SE1 database = can’t downgrade once bought a licence – at least not without HQAPP level approval negotiated.

June 11, 2015

  • Thank you for sharing my slides, I hope you enjoyed the sessions despite the fire alarm going off… 🙂

    Please note that I will be releasing the full demo document for the SQLcl presentation in my blog shortly. I would appreciate if you could link that as well.


  • Leave a Reply

    Your email address will not be published. Required fields are marked *