Slide 1 :
Slide 2 : 2 For assistance and queries, contact us atsangam09@aioug.org
or visit www.aioug.org
Next AIOUG Web cast on 19th October 2009 Keep watching www.aioug.org for details : 3 Next AIOUG Web cast on 19th October 2009 Keep watching www.aioug.org for details
Slide 4 : 4 Rich Niemiec, Rolta TUSC (www.rolta.com www.tusc.com)
(Thanks: Murali Vallath, Satyendra Kumar, Ken Jacobs, Debbie Migliore, Maria Colgan)
Oracle Disclaimer: The following is intended to outline Oracle's general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, and timing of any features or functionality described for Oracle's products remains at the sole discretion of Oracle. The Best Oracle Database 11g New Features
All India Oracle Users Group 2009
Audience Knowledge / Versions : 5 Audience Knowledge / Versions Oracle7 Experience ?
Oracle8i Experience ?
Oracle9i Experience ?
Oracle10g Experience?
Oracle Database 11g Experience?
Goals
Present NEW features in an EASY way
Focus on a few nice features of Oracle11g
Non-Goals
Learn ALL aspects of Oracle11g
Overview : 6 Overview Know the Oracle
Start Me Up – Using Memory Target
The Buffer Cache & The Result Cache
Virtual Columns
Invisible Indexes & Online Index Rebuilds
Creating & Rebuilding Indexes Online
DDL Lock Timeout, PL/SQL Expressions/Simple Integer
Secure Files
ADDM Enhancements
SQL Plan Management (SPM) and capturing SQL Plan Baselines
SQL Performance Analyzer, Access Advisor & Query Repair Advisor
Real Application Testing (Database Capture and Replay)
Interval Partitioning & Partition Compression
Automatic Diagnostic Repository (ADR)
Auto Sample, Creating Pending Statistics
Adaptive Cursor Sharing and Bind Peeking
EM, Grid Control, Security Enhancements & the Future Sizes
Know the Oracle : Know the Oracle
Oracle Firsts – Innovation! : 8 Oracle Firsts – Innovation! 1979 First commercial SQL relational database management system1983 First 32-bit mode RDBMS1984 First database with read consistency1987 First client-server database1994 First commercial and multilevel secure database evaluations1995 First 64-bit mode RDBMS1996 First to break the 30,000 TPC-C barrier1997 First Web database1998 First Database - Native Java Support; Breaks 100,000 TPC-C1998 First Commercial RDBMS ported to Linux2000 First database with XML2001 First middle-tier database cache2001 First RDBMS with Real Application Clusters 2004 First True Grid Database2005 First FREE Oracle Database (10g Express Edition) 2006 First Oracle Support for LINUX Offering 2007 Oracle 11g Released!
2008 Oracle Exadata Server Announced (Oracle buys BEA)
2009 Oracle buys Sun – Java; MySQL; Solaris; Hardware; OpenOffice
2007: Version 11g : 9 2007: Version 11g The Focus has been Acquisitions and gaining Market Share
Oracle 11g Database extends an already large lead
Easier to Manage the Database – Better Grid Control
Self Tuning through a variety of tools (Makes 1 person equal 10)
Better Security/Encryption & Recoverability via Flashback
Better Testing Tools (Real Application Testing)
Andy Mendelsohn is still the database lead
New releases of Siebel, PeopleSoft and Oracle12 Apps.
New Oracle BI Suite & Acquisition of Hyperion
Acquisition of BEA, SUN
Oracle gets Sun: Java, MySQL, Solaris, OpenOffice, Hardware, Storage Tech : 10 Oracle gets Sun: Java, MySQL, Solaris, OpenOffice, Hardware, Storage Tech
Slide 11 : Testing the Future Version
Version 11.1.0.6.0 of the Database
Oracle Database 11g Release 1: Upgrade Paths : 12 Oracle Database 11g Release 1: Upgrade Paths Direct Upgrade Path In-Direct Upgrade Path
Database Upgrade Assistant (DBUA) : 13 Database Upgrade Assistant (DBUA) Command Line Option to Auto Extend System Files
Express Edition Upgrade to others
Integration with Oracle Database 11g Pre-upgrade Tool
Moving Data Files into ASM, SAN, and Other File Systems
Oracle Base and Diagnostic Destination Configuration
Database Upgrade Assistant (DBUA) : 14 Database Upgrade Assistant (DBUA) DBUA checks before the upgrade:
Invalid user accounts or roles
Invalid data types or invalid objects
De-supported character sets
Adequate resources (rollback segments, tablespaces, and free disk space)
Missing SQL scripts needed for the upgrade
Listener running (if Oracle Enterprise Manager Database Control upgrade or configuration is requested)
Oracle Database software linked with Database Vault option. If Database Vault is enabled, Disable Database Vault before upgrade.
The New Version – Life is Good! : 15 The New Version – Life is Good! $ sqlplus ***/***
SQL*Plus: Release 11.1.0.6.0 - Production on Tue Oct 30 11:21:04 2007
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> startup
ORACLE instance started.
Total System Global Area 422670336 bytes
Fixed Size 1300352 bytes
Variable Size 306186368 bytes
Database Buffers 109051904 bytes
Redo Buffers 6131712 bytes
Database mounted.
Database opened.
Or... Use 11g EM... : 16 Or... Use 11g EM...
Or... Use 11g EM...Status... : 17 Or... Use 11g EM...Status...
Database Information – Back UP! : 18 Database Information – Back UP! Monitor Database
We have an alert – we logged on as SYS
Database Information - UP! : 19 Database Information - UP! Monitor Database
(UP)
Users are
Definitely
Using it!
Slide 20 : MEMORY_TARGET
&
Automatic Memory Management
Automatic Memory Management (AMM)MEMORY_TARGET in 11g : 21 Automatic Memory Management (AMM)MEMORY_TARGET in 11g First there was some Automatic Memory Mgmt - 9i
SGA_MAX_SIZE introduced in 9i – Dynamic Memory
No more Buffers – DB_CACHE_SIZE
Granule sizes introduced - _ksm_granule_size
Then came SGA_TARGET – 10g
Oracle Applications recommends setting this for SGA
Set minimums for key values (Data Cache / Shared Pool)
Now there is MEMORY_TARGET – 11g
SGA + PGA all in one setting; Still set minimums
SGA & PGA will be MEMORY_TARGET : 22 SGA & PGA will be MEMORY_TARGET
Automatically sized SGA Components thatUse SGA_TARGET : 23 Automatically sized SGA Components thatUse SGA_TARGET Component Initialization Parameter
Fixed SGA None
Shared Pool SHARED_POOL
Large Pool LARGE_POOL_SIZE
Java Pool JAVA_POOL_SIZE
Buffer Cache DB_CACHE_SIZE
Streams Pool STREAMS_POOL_SIZE
Manually Sized SGA Components thatUse SGA_TARGET : 24 Manually Sized SGA Components thatUse SGA_TARGET Component Initialization Parameter
Log buffer LOG_BUFFER (pfile only in 10g)
Keep Pool DB_KEEP_CACHE_SIZE
Recycle Pool DB_RECYCLE_CACHE_SIZE
Block caches DB_nK_CACHE_SIZE
Program Global Area (now in MEMORY_TARGET):
Aggregate PGA PGA_AGGREGATE_TARGET
Automatic Memory Management (AMM)MEMORY_TARGET in 11g : 25 Automatic Memory Management (AMM)MEMORY_TARGET in 11g SQL> sho parameter sga_
NAME TYPE VALUE
------------------------------------ ------------- --------------------------
sga_max_size big integer 360M
sga_target big integer 0
SQL> sho parameter memory
NAME TYPE VALUE
------------------------------------ ------------ ------------------------------
memory_max_target big integer 360M
memory_target big integer 360M
Moving from SGA_TARGET to:MEMORY_TARGET : 26 Moving from SGA_TARGET to:MEMORY_TARGET SQL> sho parameter target
NAME TYPE VALUE
------------------------------------ ------------- ------------------------------
memory_max_target big integer 0
memory_target big integer 0
pga_aggregate_target big integer 110M
sga_target big integer 250M
Moving from SGA_TARGET to:MEMORY_TARGET : 27 Moving from SGA_TARGET to:MEMORY_TARGET ALTER SYSTEM SET MEMORY_MAX_TARGET=360M SCOPE=SPFILE;
(shutdown/startup)
ALTER SYSTEM SET MEMORY_TARGET=360M SCOPE=SPFILE;
ALTER SYSTEM SET SGA_TARGET=0; (or set a minimum)
ALTER SYSTEM SET PGA_AGGREGATE_TARGET=0; (or set a minimum)
SQL> sho parameter target
NAME TYPE VALUE
------------------------------------ ------------- ------------------------------
memory_max_target big integer 360M
memory_target big integer 360M
pga_aggregate_target big integer 0
sga_target big integer 0
Moving from SGA_TARGET to:MEMORY_TARGET (set minimums) : 28 Moving from SGA_TARGET to:MEMORY_TARGET (set minimums) ALTER SYSTEM SET SGA_TARGET=200;
ALTER SYSTEM SET PGA_AGGREGATE_TARGET=100;
SQL> sho parameter target
NAME TYPE VALUE
------------------------------------ ------------- ------------------------------
memory_max_target big integer 360M
memory_target big integer 360M
pga_aggregate_target big integer 100M
sga_target big integer 200M
Moving from SGA_TARGET to:MEMORY_TARGET - EM : 29 Moving from SGA_TARGET to:MEMORY_TARGET - EM
Buffer Cache & Result Cache : Buffer Cache & Result Cache
First, A quick review:Flush Buffer Cache : 31 First, A quick review:Flush Buffer Cache The new 10g feature allows the flush of the buffer cache. It is NOT intended for production use, but rather for system testing purposes.
This can help you in your tuning needs or as a band-aid if you have ‘free buffer’ waits (there are better ways to fix this like writing more often or increasing the DB_CACHE_SIZE)
Note that any Oracle I/O not done in the SGA counts as a physical I/O. If your system has O/S caching or disk caching, the actual I/O that shows up as physical may indeed be a memory read outside of Oracle.
To flush the buffer cache perform the following:
SQL> ALTER SYSTEM FLUSH BUFFER_CACHE;
Flush Buffer Cache Example : 32 Flush Buffer Cache Example select count(*) from tab1;
COUNT(*)
-----------------
1147
Execution Plan
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'TAB1' (TABLE) (Cost=4 Card=1147)
Statistics
----------------------------------------------------------
0 db block gets
7 consistent gets
6 physical reads
Flush Buffer Cache Example : 33 Flush Buffer Cache Example select count(*) from tab1; (Run it again and the physical reads go away)
COUNT(*)
-----------------
1147
Execution Plan
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'TAB1' (TABLE) (Cost=4 Card=1147)
Statistics
----------------------------------------------------------
0 db block gets
7 consistent gets
0 physical reads
Flush Buffer Cache Example : 34 Flush Buffer Cache Example ALTER SYSTEM FLUSH BUFFER_CACHE;
System altered.
select count(*) from tab1; (Flush the cache and the physical reads are back)
COUNT(*)
-----------------
1147
Execution Plan
-----------------------------------------------------------------------------------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'TAB1' (TABLE) (Cost=4 Card=1147)
Statistics
----------------------------------------------------------
0 db block gets
7 consistent gets
6 physical reads
The Result Cache : 35 The Result Cache Function Results of queries and query fragments can be cached in memory for future executions.
Choose calculations that frequently run
Choose data that does NOT frequently change
RESULT_CACHE & RELIES_ON clauses
Takes its memory from the Shared Pool
Set with RESULT_CACHE_SIZE
RESULT_CACHE_MODE=force (auto/manual)
DBMS_RESULT_CACHE.FLUSH to clear
Is NOT passed between RAC/Grid nodes
Check the docs for other Restrictions & Rules!!
Result Cache Performance Example Query (1M Row Test) : 36 Result Cache Performance Example Query (1M Row Test) select *
from (select *
from (select t.country_name, t.city_name,
sum(t.salary) a_sum, max(t.salary) a_max
from emps t
group by t.country_name, t.city_name)
order by a_max desc)
where rownum < 2;
Result Cache Example Performance : 37 Result Cache Example Performance Step 1 - In Session 1-
Executed query without hint and it returned an elapsed time of 3.80 seconds (not cached).
Step 2 - In Session 2 –
Executed query without hint and it returned an elapsed time of 3.20 seconds (not cached).
Result Cache Example Performance : 38 Result Cache Example Performance Step 3 - In Session 2
Executed query with the RESULT_CACHE hint and it returned an elapsed time of 3.18 seconds (cache it).
Step 4 - In Session 1
Executed query without the RESULT_CACHE hint, but with RESULT_CACHE_MODE=force and it returned an elapsed time of 0.86 seconds (cached!!).
Result Cache Example QueryFrom the Oracle Docs : 39 Result Cache Example QueryFrom the Oracle Docs The RELIES_ON Clause specifies tables or views that the Function Results are dependent on.
-- Package specification
CREATE OR REPLACE PACKAGE HR IS
...
type DeptInfoRec IS RECORD (avgSal NUMBER,
numberEmployees NUMBER);
-- Function declaration
FUNCTION GetDeptInfo (dept_id NUMBER) RETURN DeptInfoRec
RESULT_CACHE;
...
END HR;
Result Cache Example QueryFrom the Oracle Docs : 40 Result Cache Example QueryFrom the Oracle Docs PACKAGE BODY HR IS
...
-- Function definition
FUNCTION GetDeptInfo (dept_id NUMBER) RETURN DeptInfoRec
RESULT_CACHE RELIES_ON (EMP);
IS
result DeptInfoRec;
BEGIN SELECT AVG(sal), count(*) INTO result
FROM EMP
WHERE deptno = dept_id;
RETURN result;
END;
...
END HR;
The Result Cache – V$ Views : 41 The Result Cache – V$ Views V$RESULT_CACHE_STATISTICS – Displays the amount of memory to help you determine memory currently allocated to the result cache.
Other V$ views:
V$RESULT_CACHE_MEMORY
V$RESULT_CACHE_OBJECTS
V$RESULT_CACHE_DEPENDENCY
The Result Cache – FYI OnlyDigging Deeper : 42 The Result Cache – FYI OnlyDigging Deeper KSPPINM KSPPSTVL KSPPDESC
-------------------------------------- -------- --------------------------------
_result_cache_auto_execution_threshold 1 result cache auto execution
threshold
_result_cache_auto_size_threshold 100 result cache auto max size allowed
_result_cache_auto_time_threshold 1000 result cache auto time threshold
_result_cache_block_size 1024 result cache block size
_result_cache_bypass FALSE bypass the result cache
_result_cache_hash_buckets 1024 hash bucket count
_result_cache_invalid 0 post-invalidation usage allowance
_result_cache_max_result 100 maximum result size as percent
of cache size
_result_cache_remote_expiration 0 maximum life time (min) for any
result using a remote object
_result_cache_timeout 60 maximum time (sec) a session
waits for a result
Tuning Tools – FYI OnlyDBMS_XPLAN : 43 Tuning Tools – FYI OnlyDBMS_XPLAN Use DBMS_XPLAN to query the execution plan
Automatically queries the last plan in PLAN_TABLE
uses a TABLE() function with another pipelined function
Operation text truncation might be a problem
Will give additional information after plan
Highlight filter vs join conditions, if plan table is current
Displays warning message of old version plan table is being used
In 11g, a procedure for SQL Plan Baselines (we’ll cover these later).
DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE (
sql_handle IN VARCHAR2 := NULL,
plan_name IN VARCHAR2 := NULL,
format IN VARCHAR2 := 'TYPICAL') <‘BASIC’/’ALL’>
RETURN dbms_xplan_type_table;
Tuning Tools – FYI OnlyDBMS_XPLAN : 44 Tuning Tools – FYI OnlyDBMS_XPLAN DBMS_XPLAN Example:
Select *
from table (dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Pstart| Pstop |
--------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 328 | 2296 | 2 | | |
| 1 | UPDATE | JOURNAL_LINE | | | | | |
| 2 | PARTITION RANGE ALL| | | | | 1 | 4 |
| 3 | TABLE ACCESS FULL | JOURNAL_LINE | 328 | 2296 | 2 | 1 | 4 |
--------------------------------------------------------------------------------------
Note: cpu costing is off, 'PLAN_TABLE' is old version
11 rows selected
The Virtual Column : The Virtual Column
The Virtual Column : 46 The Virtual Column The value of the virtual column is a derived expression.
Can be derived from columns of the same table or from constants
Can include SQL or user-defined PL/SQL functions
The virtual column is NOT PHYSICALLY STORED.
You CAN NOT explicitly write to a virtual column
You CAN create an index (result it function-based index) or partition on a virtual column
If you UPDATE columns of a virtual column and it has an index, then it will be computed on the UPDATE vs. on the SELECT (very important from a tuning standpoint).
Index Organized and External Tables can NOT have virtual columns.
The Virtual Column : 47 The Virtual Column create table emp_rich
(empno number(4),
sal number(7,2),
yearly_sal generated always as (sal*12),
deptno number(2));
Table created.
insert into emp_rich(empno, sal, deptno)
select empno, sal, deptno from scott.emp;
14 rows created.
The Virtual Column : 48 The Virtual Column select * from emp_rich;
EMPNO SAL YEARLY_SAL DEPTNO
------------ ----------- ------------------- ----------------
7369 800 9600 20
7499 1600 19200 30
7521 1250 15000 30
7566 2975 35700 20
7654 1250 15000 30
7698 2850 34200 30
...
The Invisible Index : The Invisible Index
The Invisible Index : 50 The Invisible Index Set an index to VISIBLE or INVISIBLE
ALTER INDEX idx INVISIBLE;
ALTER INDEX idx VISIBLE;
CREATE INDEX... INVISIBLE;
Great to turn off indexes for a while when you think they’re not being used, but BEFORE you drop them.
Can use INDEX (to override invisibility) or NO_INDEX (to override visibility) hints to override either setting.
The index IS MAINTAINED during DML
Great for testing!
The Invisible Index : 51 The Invisible Index create index deptno_invisible_idx on dept_rich(deptno) invisible;
Index created.
select count(*) from dept_rich where deptno = 30; (doesn’t see the index)
COUNT(*)
--------------
512
Execution Plan
----------------------------------------------------------
Plan hash value: 3024595593
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 | 4 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 2 | | |
|* 2 | TABLE ACCESS FULL| DEPT_RICH | 512 | 1024 | 4 (0)| 0:00:01 |
--------------------------------------------------------------------------------
The Invisible Index : 52 The Invisible Index select /*+ index(dept_rich dept_rich_inv_idx) */ count(*)
from dept_rich where deptno = 30; (forces the index with hint)
COUNT(*)
--------------
512
Execution Plan
----------------------------------------------------------
Plan hash value: 3699452051
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 2 | | |
|* 2 | INDEX RANGE SCAN| DEPT_RICH_INV_IDX | 512 | 1024 |1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
The Invisible Index (set visible) : 53 The Invisible Index (set visible) alter index dept_rich_inv_idx visible;
Index altered.
select count(*) from dept_rich where deptno = 30;
(it does see the index)
COUNT(*)
--------------
512
Execution Plan
----------------------------------------------------------
Plan hash value: 3699452051
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 2 | | |
|* 2 | INDEX RANGE SCAN| DEPT_RICH_INV_IDX | 512 | 1024 |1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
The Invisible Index (set visible) : 54 The Invisible Index (set visible) select /*+ no_index(dept_rich dept_rich_inv_idx) */ count(*)
from dept_rich
where deptno = 30; (forces not using the index with hint)
COUNT(*)
----------
512
Execution Plan
----------------------------------------------------------
Plan hash value: 3024595593
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 | 4 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 2 | | |
|* 2 | TABLE ACCESS FULL| DEPT_RICH | 512 | 1024 | 4 (0)| 0:00:01 |
--------------------------------------------------------------------------------
The Invisible Index (check it) : 55 The Invisible Index (check it) alter index dept_rich_inv_idx invisible;
Index altered.
select index_name, visibility
from dba_indexes (or go to USER_INDEXES)
where index_name = 'DEPT_RICH_INV_IDX‘;
INDEX_NAME VISIBILITY
-------------------------------- ------------------
DEPT_RICH_INV_IDX INVISIBLE
Create & Rebuild Index Online : Create & Rebuild Index Online
Create & Rebuild Index Online : 57 Create & Rebuild Index Online You can create/rebuild indexes even when doing DML on the base table, but it’s better to do during low DML activity.
Prior to Oracle 11g, this required an exclusive lock at the beginning and end of the rebuild. This lock could cause DML delays and performance spike. This lock is no longer required for this operation.
Rebuild is faster than a DROP and CREATE
Basic Syntax:
CREATE INDEX index_name ON table (col1,...) ONLINE;
Index created.
ALTER INDEX index_name REBUILD ONLINE;
Index altered.
Rebuild Index or Coalesce (FYI)Coalesce Example from Oracle Doc. : 58 Rebuild Index or Coalesce (FYI)Coalesce Example from Oracle Doc.
Rebuild Index or Coalesce : 59 Rebuild Index or Coalesce Rebuild:
Quickly move index to another tablespace
Requires more disk space
Creates new index tree and shrinks heights
Change storage/tblspc w/o dropping Coalesce
Can’t move to another tablespace
Requires much less space than rebuild
Coalesces leaf blocks that are in the same branch
Quickly frees index leaf blocks for use
Nice Developer Tools/ImprovementsDDL_LOCK_TIMEOUTPL/SQL ExpressionsSimple IntegerNew PL/SQL Packages : Nice Developer Tools/ImprovementsDDL_LOCK_TIMEOUTPL/SQL ExpressionsSimple IntegerNew PL/SQL Packages
The DDL Lock Timeout : 61 The DDL Lock Timeout DDL Statements (Create/Alter/Drop) require exclusive locks and thus sometimes fail due to bad timing.
The parameter DDL_LOCK_TIMEOUT specifies the amount of time (in seconds) the DDL statement will wait for the lock before timing out and failing.
The default value is 0, the max value is 100000 (27.77 hours).
Example:
alter session set DDL_LOCK_TIMEOUT = 30
Session altered.
Allow Sequences in PL/SQL Expressions : 62 Allow Sequences in PL/SQL Expressions In Previous Versions needed to retrieve the value of a sequence (CURRVAL / NEXTVAL) by invoking a cursor (explicit or implicit).
In 11g:
No cursor is needed so the code is more efficient.
For big jobs – Saves MANY cursors
Allow Sequences in PL/SQL Expressions : 63 Allow Sequences in PL/SQL Expressions OLD Way
DECLARE
V_NEW_VAL NUMBER;
BEGIN
SELECT MY_SEQ.NEXTVAL INTO V_NEW_VAL FROM DUAL;
END;
NEW Way
DECLARE
V_NEW_VAL NUMBER;
BEGIN
V_NEW_VAL := MY_SEQ.NEXTVAL;
END;
Simple Integer Data Type : 64 Simple Integer Data Type Oracle added the new SIMPLE_INTEGER data type to be more efficient than PLS_INTEGER since the operations are done directly at the hardware level. There is also a built-in NOT NULL condition for SIMPLE_INTEGER.
The performance is larger when the PLS_CODE_TYPE=‘NATIVE’ vs. INTERPRETED
We used a PL/SQL Block to loop through 1 million times incrementing a numeric variable by one. We executed the test for each of these three times.
Results:
NUMBER: 1.26s
PLS_INTEGER: 0.88s
SIMPLE_INTEGER: 0.65s
Additional EnhancementsNew PL/SQL Packages : 65 Additional EnhancementsNew PL/SQL Packages DBMS_AUTO_TASK_ADMIN
DBMS_COMPARISON
DBMS_DG
DBMS_EDITIONS_UTILITIES
DBMS_HM (Health Monitor)
DBMS_HPROF
DBMS_MGD_ID_UTL
DBMS_NETWORK_ACL_ADMIN
DBMS_RESCONFIG
DBMS_RESULT_CACHE DBMS_SQLDIAG (SQL Repair)
DBMS_WORKLOAD_CAPTURE
DBMS_WORKLOAD_REPLAY
DBMS_XA
DBMS_XDBADMIN
DBMS_XEVENT
DBMS_XMLDTD
DBMS_XMLINDEX
DBMS_XMLTRANSLATIONS
SDO_RDF
SDO_RDF_INFERENCE
Additional EnhancementsEnhanced PL/SQL Packages : 66 Additional EnhancementsEnhanced PL/SQL Packages DBMS_ADVISOR
DBMS_APPLY_ADM
DBMS_AQ
DBMS_AQADM
DBMS_CAPTURE_ADM
DBMS_CDC_PUBLISH
DBMS_CDC_SUBSCRIBE
DBMS_CQ_NOTIFICATION
DBMS_DATA_MINING
DBMS_DATA_MINING_TRANSFORM
DBMS_DATAPUMP
DBMS_EXPFIL
DBMS_FLASHBACK DBMS_HS_PASSTHROUGH
DBMS_LOB
DBMS_LOGSTDBY
DBMS_MGWADM
DBMS_MVIEW
DBMS_PREDICTIVE_ANALYTICS
DBMS_RESOURCE_MANAGER
DBMS_RLMGR
DBMS_RULE_ADM
DBMS_SCHEDULER
DBMS_SERVER_ALERT
DBMS_SESSION
DBMS_SPACE DBMS_SQL
DBMS_SQLTUNE
DBMS_STATS
DBMS_STREAMS_ADM
DBMS_TRACE
DBMS_UTILITY
DBMS_WORKLOAD_REPOSITORY
DBMS_XDB
DBMS_XMLSCHEMA
DBMS_XPLAN
UTL_INADDR
UTL_RECOMP
UTL_SMTP
UTL_TCP
Nice DBA ToolOracle Secure Files : Nice DBA ToolOracle Secure Files
Oracle SecureFilesHigh-Performance Large Objects : 68 Oracle SecureFilesHigh-Performance Large Objects High-performance transactional access to large object data
RFID, DICOM medical, CAD, images, 3D spacial
low-latency, high throughput, concurrent access
space-optimized storage
Protect your valuable data ... Keep large objects in the database!
transactions
transparent encryption
compression and de-duplication
database-quality security, reliability, and scalability
Better security, single view and management of data
Superset of LOB interfaces – easy migration
Oracle Secure FilesBetter Performance than LOBs… : 69 Oracle Secure FilesBetter Performance than LOBs… Adding Files using New Disk Space – 2x fast than LOBs
Adding Files using Deleted Space – 22x faster than LOBs
PL/SQL Reads – 6x Faster than LOBs
Your mileage will vary....
ADDM Enhancements(Automatic Database Diagnostic Monitor) : ADDM Enhancements(Automatic Database Diagnostic Monitor)
ADDM enhancements : 71 ADDM enhancements Global ADDM so that Diagnostics are done across the entire cluster
Emergency ADDM for use when database is hung
On any granularity
Database Cluster
Database Instance
Specific Target (such as host, ASM...etc.)
Over a specified time NOT tied to a pair of snapshots
ADDM Briefly : 72 ADDM Briefly Specific
Database
Instance
We have 5
ADDM
Findings
Check them
Here
ADDM Briefly : 73 ADDM Briefly Top ADDM
Findings
Click a
Single
Timeframe
Let’s
Check the
Hard Parse
Issue
ADDM Briefly : 74 ADDM Briefly Detailed
Info
&
Findings
Add’l
Info
ADDM - Run NOW! : 75 ADDM - Run NOW! A Big
Problem
Occurs
Run
ADDM
NOW!
ADDM - Run NOW! : 76 ADDM - Run NOW! Are you
Sure?
Running
ADDM – Run NOW! : 77 ADDM – Run NOW! Done.
CPU
Issue
ADDM – Run NOW! : 78 ADDM – Run NOW! Detail
on CPU
Issue?
Suggested
Fixes
ADDM – Run NOW! : 79 ADDM – Run NOW! View
The
Report
ADDM for RAC : 80 ADDM for RAC Performance expert in a box
Now RAC specialist too!
Identifies the most “Globally Significant” performance issues for the entire RAC database
Database-wide and instance-level analysis
Database-wide analysis of:
Global cache interconnect issues
Lock manager congestion issues
Global resource contention, e.g. IO bandwidth, hot blocks
Globally high-load SQL
Skew in instance response times
Allows drill down to instances
Runs proactively every hour when taking AWR Snapshots (default)
ADDM Considerations: : 81 ADDM Considerations: CPU Bottlenecks
Undersized Memory Structures – SGA / PGA
I/O Capacity Issues
High Load SQL statements
High Load PL/SQL
RAC specific issues – Global hot block/interconnect
Application issues such as parsing, locks...etc.
Concurrency (buffer busy) or hot object issues
Configuration issues – Redo, Archive, Checkpoint.
SQL Tuning Advisors &SQL Plan Management (SPM) : 82 SQL Tuning Advisors &SQL Plan Management (SPM)
SQL Plan Management : 83 SQL Plan Management SQL Plan Management is a mechanism that records/evaluates execution plan of SQL statements (good & bad) over time and builds SQL Plan baselines (replaces stored outlines) of existing plans known to be efficient.
Events that cause the need for SQL Plan baselines:
New version of Oracle (New optimizer version – Use capture replay to test effect)
Changes to optimizer statistics or data changes
Schema, application or metadata changes (use SQL Advisor to get suggestions)
System settings changes (Use SQL Replay to find what works)
SQL Profile (statistics – data skews & correlated columns) creation
Stored outlines are deprecated (discouraged) in Oracle Database 11g. Oracle highly recommends migrating existing stored outlines to SQL plan baselines. A SQL Profile contains additional STATISTICS for this SQL statement for the query optimizer to generate a better execution plan. An outline/baseline contains HINTS for this SQL statement for query optimizer to generate a better execution plan.
SQL Plan Management : 84 SQL Plan Management SQL Profile stores STATISTICS for a SQL statement for the query optimizer to generate a better execution plan.
A Stored Outline/SQL Plan Baseline contains HINTS for this SQL statement for query optimizer to generate a better execution plan.
A SQL Plan Baseline should evolve with changes in the system to analyze good/bad plans over time.
View these in DBA_PLAN_BASELINES
You can also export a SQL Tuning Set and import it to new system. Capture baselines for Tuning Set with DBMS_SPM (see later slide on entire syntax). Can also use a pack/unpack function to pack/unpack all plans in a system for transporting.
SQL Plan ManagementCreate a SQL Tuning Set : 85 SQL Plan ManagementCreate a SQL Tuning Set Tuning
Issue
Create a
Tuning
Set from
Top 10
SQL
SQL Plan ManagementCreate a SQL Tuning Set : 86 SQL Plan ManagementCreate a SQL Tuning Set Tuning
Set Name
Queries
SQL Plan ManagementViewing a SQL Tuning Set : 87 SQL Plan ManagementViewing a SQL Tuning Set Tuning
Set Name
Queries
& Stats
SQL Plan ManagementCreate a SQL Tuning Set : 88 SQL Plan ManagementCreate a SQL Tuning Set Run the
Tuning
Advisor
on this
SQL
Tuning
Set (STS)
Run it
NOW
SQL Plan ManagementCreate a SQL Tuning Set : 89 SQL Plan ManagementCreate a SQL Tuning Set Results
Select
One query
And click
View
SQL Plan ManagementClick on any SQL ID : 90 SQL Plan ManagementClick on any SQL ID SQL
Text
Waits
&
Statistics
SQL Plan ManagementCreate a SQL Tuning Set : 91 SQL Plan ManagementCreate a SQL Tuning Set SQL
Profile
Will
Help 99%
SQL Plan ManagementCreate a SQL Tuning Set : 92 SQL Plan ManagementCreate a SQL Tuning Set Compare
Before
&
After
SQL Plan ControlSQL Profiles stored in the system : 93 SQL Plan ControlSQL Profiles stored in the system SQL
Profiles
SQL
Plan
Baselines
SQL Plan ManagementCapturing Baselines : 94 SQL Plan ManagementCapturing Baselines Capturing baselines (migrate stored outlines - hints)
Plan history is only tracked for a SQL statement that executes more than once (no ad-hoc queries)
Automatic Plan Capture:
OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES
(set to TRUE – the default is FALSE)
Enable the use of SQL Plan Baselines (could be session level of a tuning set & without the capture):
OPTIMIZER_USE_SQL_PLAN_BASELINES
(set to TRUE – the default is TRUE)
SQL Plan ManagementCapturing Baselines : 95 SQL Plan ManagementCapturing Baselines DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (
sql_id IN VARCHAR2,
plan_hash_value IN NUMBER := NULL,
sql_text IN CLOB,
fixed IN VARCHAR2 := 'NO',
enabled IN VARCHAR2 := 'YES')
RETURN PLS_INTEGER;
Load one or more plans present in the cursor cache for a SQL statement. You can also do this using the plan_handle and sql_text.
SQL Plan ManagementCapturing Baselines : 96 SQL Plan ManagementCapturing Baselines DBMS_SPM.LOAD_PLANS_FROM_SQLSET (
sqlset_name IN VARCHAR2,
sqlset_owner IN VARCHAR2 := NULL,
basic_filter IN VARCHAR2 := NULL,
fixed IN VARCHAR2 := 'NO',
enabled IN VARCHAR2 := 'YES'
commit_rows IN NUMBER := 1000)
RETURN PLS_INTEGER;
Manually load plans stored in SQL Tuning Sets (STS) into plan baselines.
Note that plan history is only tracked for a SQL statement that executes more than once (no ad-hoc queries)
SQL Plan ManagementCapturing Baselines : 97 SQL Plan ManagementCapturing Baselines Set the retention to 100 weeks to retain unused plans before they are purged (53 is the default). Shown for example, 100 is not recommended, 53 is better since it will include monthly/yearly runs.
begin
dbms_spm.configure('plan_retention_weeks',100);
end;
/
PL/SQL procedure successfully completed.
You can also purge individual plans with the purge_sql_plan_baseline function.
You can also query dba_sql_plan_baselines as well as use DBMS_XPLAN.display_sql_plan_baseline to view stored plans.
SQL Performance Analyzer : 98 SQL Performance Analyzer
SQL Performance Analyzer : 99 SQL Performance Analyzer Measure and report on performance before and after a change! DBMS_SQLTUNE package.
Great for:
Database Upgrades
Application Upgrades
Hardware Changes
Database or Schema Changes
Best for SQL Tuning – Especially Batches
SQL Performance Analyzer : 100 SQL Performance Analyzer Easy to run – SQL Focus (Test SGA settings):
Capture SQL
Transport SQL
Create a Replay Task
Set up the environment to Test
Make any changes to Test (such as SGA/Optimizer)
Compare before and after performance
Tune the problems!
SQL Performance Analyzer : 101 SQL Performance Analyzer Click
Here
SQL Performance AnalyzerGuided Workflow : 102 SQL Performance AnalyzerGuided Workflow
SQL Performance AnalyzerOptimizer Upgrade (10g vs. 11g) : 103 SQL Performance AnalyzerOptimizer Upgrade (10g vs. 11g)
SQL Advisors : 104 SQL Advisors TuningAdvisors
Repair
Advisor
(next)
SQL Access AdvisorNEW Partition Advisor : SQL Access AdvisorNEW Partition Advisor
SQL Access Advisor &NEW Partition Advisor : 106 SQL Access Advisor &NEW Partition Advisor The SQL Advisor now combines the functionality of the SQL Tuning Advisor, SQL Access Advisor and the new Partition Advisor.
Recommends Partitioning Needs
Utilize a previous SQL Tuning Set
Take SQL straight from what’s currently in the CACHE.
Create a hypothetical workload
SQL Access Advisor checks Indexes, Partitions or Materialized Views (schema related issues)
SQL Access Advisor &NEW Partition Advisor : 107 SQL Access Advisor &NEW Partition Advisor Step One
Use a SQL
Tuning Set
SQL Access Advisor &NEW Partition Advisor : 108 SQL Access Advisor &NEW Partition Advisor Look at
Partitions
Quick
Solution
SQL Access Advisor &NEW Partition Advisor : 109 SQL Access Advisor &NEW Partition Advisor Step 3
Schedule
it.
Run it
Now!
SQL Access Advisor &NEW Partition Advisor : 110 SQL Access Advisor &NEW Partition Advisor Final
Review
SQL Access Advisor &NEW Partition Advisor : 111 SQL Access Advisor &NEW Partition Advisor Job
Submitted
Job
Running
Now.
SQL Access Advisor &NEW Partition Advisor : 112 SQL Access Advisor &NEW Partition Advisor Improve
I/O
Improve Execution
Time
The SQL Repair AdvisorORA-600 : The SQL Repair AdvisorORA-600
SQL Repair Advisor : 114 SQL Repair Advisor Used to Repair Problem SQL – Oracle Errors
Reloads and recompiles SQL statements to gather diagnostics information to fix.
Uses the diagnostic information to repair the problem SQL statement (DBMS_SQLDIAG)
Will fix error going through compilation, execution and trying different routes (could be a slower route for now) to come up with a temporary SQL Patch without error until fixed.
SQL Repair Advisor – Go straight from Alerts : 115 SQL Repair Advisor – Go straight from Alerts Go to the
Database
Instance
Click Alert (ORA-600) message text to see details
SQL Repair Advisor – View Problem Details : 116 SQL Repair Advisor – View Problem Details Click on View Problem Details to go to the Support
Bench
Support Workbench - Details : 117 Support Workbench - Details Click on
SQL
Repair
Advisor
Results from SQL Repair Advisor : 118 Results from SQL Repair Advisor Click on
View to
Get the
Detail finding of the Advisor Note a SQL
Patch (FIX for the SQL) has been generated
SQL Repair Advisor Recommendation / Confirmation : 119 SQL Repair Advisor Recommendation / Confirmation Click on
Implement
To accept the SQL Patch
Real Application Testing! Database workload capture and replay : 120 Real Application Testing! Database workload capture and replay
Database workload capture and replay : 121 Database workload capture and replay Used to capture database workload on one system and replay later on a different system. Useful to compare two different systems.
Could rival LoadRunner in the future (may be more precise!)
Brief Steps:
Capture workload on a database even from 10gR2
Restore the database on a test system to the SCN when capture begins
Perform upgrade and make changes to the test system as needed
Preprocess the captured workload if it is not preprocessed
Configure the test system for replay (I don’t do this here)
Replay workload on the restored database (I don’t have this in this presentation, but will show some of the screens to do it)
Great to test upgrade to 11g (Capture 10gR2 then test against 11g)
Pre-Change Production System : 122 Pre-Change Production System … Backup … Captured Workload Process Process … Capture Workload Changes Unsupported 11g Changes Supported Database Upgrades, Patches
Schema, Parameters
RAC nodes, Interconnect
OS Platforms, OS Upgrades
CPU, Memory
Storage
Etc.
Post-Change Test System : 123 Post-Change Test System … … … … … Process Process … Process Process … Can use Snapshot Standby as test system Pre-Change (could be 9.2.0.8 or 10g Capture) Production System Processed Captured Workload Backup Capture Workload
Database ReplayFYI Only – Download to view in detail : 124 Database ReplayFYI Only – Download to view in detail Real App
Testing:
Database
Replay
Capture Workload – FYI Only : 125 Capture Workload – FYI Only
Capture Workload – FYI Only : 126 Capture Workload – FYI Only
Database workload – PreprocessFYI Only : 127 Database workload – PreprocessFYI Only
Database workload capture and replayOn NEW system (shortened) – FYI Only : 128 Database workload capture and replayOn NEW system (shortened) – FYI Only
Replay Options... : 129 Replay Options... Synchronized Replay
Exact Concurrency, commits & data divergence minimal
Unsynchronized Replay
Not the same concurrency or commits
Data divergence can be large depending on load test performed
Creates Report
Data Divergence
Error Divergence
Performance Divergence
Partitioning: (FYI Only) - Tables can be split into many pieces (10g).- Only a subset of the data is queried- All of the data COULD be queried- Leads to enhanced performance of large tables- Re-orgs & backups can be done on a partition level- 4 quick examples follow (many many rules for each)- WHAT’S NEW IN ORACLE 11G : Partitioning: (FYI Only) - Tables can be split into many pieces (10g).- Only a subset of the data is queried- All of the data COULD be queried- Leads to enhanced performance of large tables- Re-orgs & backups can be done on a partition level- 4 quick examples follow (many many rules for each)- WHAT’S NEW IN ORACLE 11G
The Rules – See Partitioning Guide : 131 The Rules – See Partitioning Guide
Range Partitioning (V8) : 132 Range Partitioning (V8) CREATE TABLE DEPT
(DEPTNO NUMBER(2),
DEPT_NAME VARCHAR2(30))
PARTITION BY RANGE(DEPTNO)
(PARTITION D1 VALUES LESS THAN (10) TABLESPACE DEPT1,
PARTITION D2 VALUES LESS THAN (20) TABLESPACE DEPT2,
PARTITION D3 VALUES LESS THAN (MAXVALUE) TABLESPACE DEPT3);
INSERT INTO DEPT VALUES (1, ‘DEPT 1’);
INSERT INTO DEPT VALUES (7, ‘DEPT 7’);
INSERT INTO DEPT VALUES (10, ‘DEPT 10’);
INSERT INTO DEPT VALUES (15, ‘DEPT 15’);
INSERT INTO DEPT VALUES (22, ‘DEPT 22’);
Range Partitioning (8i) (Multi-Column) : 133 Range Partitioning (8i) (Multi-Column) create table cust_sales (
acct_no number(5),
cust_name char(30),
sale_day integer not null,
sale_mth integer not null,
sale_yr integer not null)
partition by range (sale_yr, sale_mth, sale_day)
(partition cust_sales_q1 values less than (1998, 04, 01) tablespace users1,
partition cust_sales_q2 values less than (1998, 07, 01) tablespace users2,
partition cust_sales_q3 values less than (1998, 10, 01) tablespace users3,
partition cust_sales_q4 values less than (1999, 01, 01) tablespace users4,
partition cust_sales_qx values less than (maxvalue, maxvalue, maxvalue) tablespace users4);
Hash Partitioning (8i) (Multi-Column) : 134 Hash Partitioning (8i) (Multi-Column) create table cust_sales_hash (
acct_no number(5),
cust_name char(30),
sale_day integer not null,
sale_mth integer not null,
sale_yr integer not null)
partition by hash (acct_no)
partitions 4
store in (users1, users2, users3, users4);
Composite Partitioning v (8i) : 135 Composite Partitioning v (8i) CREATE TABLE test5 (data_item INTEGER, length_of_item INTEGER, storage_type VARCHAR(30), owning_dept NUMBER, storage_date DATE) PARTITION BY RANGE (storage_date) SUBPARTITION BY HASH(data_item) SUBPARTITIONS 4
STORE IN (data_tbs1, data_tbs2,
data_tbs3, data_tbs4) (PARTITION q1_1999 VALUES LESS THAN (TO_DATE('01-apr-1999', 'dd-mon-yyyy')), PARTITION q2_1999 VALUES LESS THAN (TO_DATE('01-jul-1999', 'dd-mon-yyyy')),
PARTITION q3_1999
VALUES LESS THAN (TO_DATE('01-oct-1999', 'dd-mon-yyyy'))
(SUBPARTITION q3_1999_s1 TABLESPACE data_tbs1,
SUBPARTITION q3_1999_s2 TABLESPACE data_tbs2),
PARTITION q4_1999
VALUES LESS THAN (TO_DATE('01-jan-2000', 'dd-mon-yyyy'))
SUBPARTITIONS 8
STORE IN (q4_tbs1, q4_tbs2, q4_tbs3, q4_tbs4,
q4_tbs5, q4_tbs6, q4_tbs7, q4_tbs8), PARTITION q1_2000 VALUES LESS THAN (TO_DATE('01-apr-2000', 'dd-mon-yyyy')));
List Partitioning (Allowed since 9i) : 136 List Partitioning (Allowed since 9i) create table dept_part
(deptno number(2),
dname varchar2(14),
loc varchar2(13))
partition by list (dname)
(partition d1_east values ('BOSTON', 'NEW YORK'),
partition d2_west values ('SAN FRANCISCO', 'LOS ANGELES'),
partition d3_south values ('ATLANTA', 'DALLAS'),
partition d4_north values ('CHICAGO', 'DETROIT'));
Table created.
Interval Partitioning – 11g : 137 Interval Partitioning – 11g This is a helpful addition to range partitioning where Oracle automatically creates a partition when the inserted value exceeds all other partition ranges. 11g aslo has Ref & Virtual Column Partitioning (not covered here).
There are the following restrictions:
You can only specify one partitioning key column, and it must be of NUMBER or DATE type.
Interval partitioning is NOT supported for index-organized tables.
You can NOT create a domain index on an interval-partitioned table.
Interval Partitioning – 11g : 138 Interval Partitioning – 11g CREATE TABLE DEPT_new
(DEPTNO NUMBER(2),
DEPT_NAME VARCHAR2(30))
PARTITION BY RANGE(DEPTNO)
(PARTITION D1 VALUES LESS THAN (10),
PARTITION D2 VALUES LESS THAN (20),
PARTITION D3 VALUES LESS THAN (30));
Table created.
SQL> insert into dept_new values(40, 'test2');
insert into dept_new values(40, 'test2')
*
ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition
Interval Partitioning – 11g : 139 Interval Partitioning – 11g select segment_name, partition_name
from dba_segments
where segment_name = 'DEPT_NEW‘;
SEGMENT_NAME PARTITION_NAME
------------------------------ ----------------------------
DEPT_NEW D1
DEPT_NEW D2
DEPT_NEW D3
Interval Partitioning – 11g : 140 Interval Partitioning – 11g CREATE TABLE DEPT_NEW2
(DEPTNO NUMBER(2),
DEPT_NAME VARCHAR2(30))
PARTITION BY RANGE(DEPTNO)
INTERVAL(10)
(PARTITION D1 VALUES LESS THAN (10),
PARTITION D2 VALUES LESS THAN (20),
PARTITION D3 VALUES LESS THAN (30))
Table created.
SQL> insert into dept_new2 values(40, 'test2');
1 row created.
Interval Partitioning – 11g : 141 Interval Partitioning – 11g insert into dept_new2 values(40,null);
insert into dept_new2 values(50,null);
insert into dept_new2 values(99,null);
select segment_name, partition_name
from dba_segments
where segment_name = 'DEPT_NEW2'
SEGMENT_NAME PARTITION_NAME
------------------------------ ------------------------------
DEPT_NEW2 D1
DEPT_NEW2 D2
DEPT_NEW2 D3
DEPT_NEW2 SYS_P41
DEPT_NEW2 SYS_P42
DEPT_NEW2 SYS_P43
Partition Compression : 142 Partition Compression You can now COMPRESS individual partitions
Compression as high as 3.5 to 1 is possible
Compressed Tables now support
DML Statements
Add and Drop Column
Partition level COMPRESS or NOCOMPRESS
ALTER TABLE... COMPRESS (old compress)
ALTER TABLE... NOCOMPRESS
Table compression now supported for OLTP
New Advanced Compression Option (chargeable):
CREATE TABLE t1 COMPRESS FOR ALL OPERATIONS
Presentation by Shyam Varan Nath – Honey I shrunk the Data Warehouse
Partition Compression : 143 Partition Compression CREATE TABLE DEPT_new3
(DEPTNO NUMBER(2),
DEPT_NAME VARCHAR2(30))
COMPRESS
PARTITION BY RANGE(DEPTNO)
interval(10)
(PARTITION D1 VALUES LESS THAN (10),
PARTITION D2 VALUES LESS THAN (20) NOCOMPRESS,
PARTITION D3 VALUES LESS THAN (30))
Table created.
Partition Compression : 144 Partition Compression insert into dept_new3 values(10,null);
1 row created.
insert into dept_new3 values(20,null);
1 row created.
insert into dept_new3 values(30,null);
1 row created.
insert into dept_new3 values(60,null);
1 row created.
insert into dept_new3 values(90,null);
1 row created.
Partition Compression : 145 Partition Compression select table_name, partition_name, compression
from dba_tab_partitions
where table_name = 'DEPT_NEW3‘;
TABLE_NAME PARTITION_NAME COMPRESS
------------------------------ ------------------------------ ----------------
DEPT_NEW3 D1 ENABLED
DEPT_NEW3 D2 DISABLED
DEPT_NEW3 D3 ENABLED
DEPT_NEW3 SYS_P64 ENABLED
DEPT_NEW3 SYS_P65 ENABLED
DEPT_NEW3 SYS_P66 ENABLED
6 rows selected.
Object Maintenance : 146 Object Maintenance
Object Maintenance – Reorganize : 147 Object Maintenance – Reorganize
Automated Maintenance Tasks : 148 Automated Maintenance Tasks Automatic Optimizer Statistics Collection
Automatic Segment Advisor
Automatic SQL Tuning Advisor (DBMS_SQLTUNE)
Disable/Enable Automated Tasks:
DBMS_AUTO_TASK_ADMIN.DISABLE (ENABLE)
Setting up Maintenance Windows
DBMS_SCHEDULER.CREATE_WINDOW
Additional Enhancements : 149 Additional Enhancements Ability to online redefine tables that have materialized view logs:
Tables with materialized view logs can now be redefined online.
Materialized view logs are now one of the dependent objects that can be copied to the interim table with the DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS package procedure.
DBMS_STATS performance has been improved.
Large-Scale Data Warehouses*Feature Usage : 150 Large-Scale Data Warehouses*Feature Usage Source: Oracle ST Survey * Oracle Survey
Automatic Diagnostic Repository (ADR) : 151 Automatic Diagnostic Repository (ADR)
Automatic Diagnostic Repository (ADR) : 152 Automatic Diagnostic Repository (ADR) Oracle 11g includes a Fault Diagnosability Infrastructure to prevent, detect, diagnose, resolve issues related to bugs, corruption, etc.
When a critical error occurs it is assigned an incident number and all diagnostic data tagged with this in ADR.
ADR is a file based repository outside of the database
ADR helps detect problems proactively
ADR helps limit the damage of interruptions
ADR helps reduce problem diagnostic time
ADR simplifies Oracle Support / Customer interaction
The ADR also contains Health Reports, Trace Files, Dump Files, SQL Test Cases and Data Repair Records
ADR Directory Structure for aDatabase Instance : 153 ADR Directory Structure for aDatabase Instance ADR Base
diag
rdbms
alert cdump incident trace (others) Alert Log: /u01/app/oracle/diag/rdbms/o11gb/O11gb/trace
ORACLE_HOME: /u01/app/oracle/product/11.1.0/db_1
ADR – V$ Diagnostic Info : 154 ADR – V$ Diagnostic Info select name, value
from v$diag_info;
NAME VALUE
----------------------------- ------------------------------------------------
Diag Enabled TRUE
ADR Base /u01/app/oracle
ADR Home /u01/app/oracle/diag/rdbms/o11gb/O11gb
Diag Trace /u01/app/oracle/diag/rdbms/o11gb/O11gb/trace
Diag Alert /u01/app/oracle/diag/rdbms/o11gb/O11gb/alert
Diag Incident /u01/app/oracle/diag/rdbms/o11gb/O11gb/incident
Diag Cdump /u01/app/oracle/diag/rdbms/o11gb/O11gb/cdump
Health Monitor /u01/app/oracle/diag/rdbms/o11gb/O11gb/hm
Default Trace File /u01/app/oracle/diag/rdbms/o11gb/O11gb/trace/O11gb_ora_16676.trc
Active Problem Count 0
Active Incident Count 0
11 rows selected.
Optimizer Statistics & Other Optimizer Advances : Optimizer Statistics & Other Optimizer Advances Special Thanks: Maria Colgan, Penny Avril & Debbie Migliore
Improved SPEED and QualityGathering Stats – AUTO-SAMPLING : 156 Improved SPEED and QualityGathering Stats – AUTO-SAMPLING Manually gather stats: Impossible to find sample size that works for ALL tables - need COMPUTE
Especially hard to find a good sample size when the data distribution is very skewed.
NEW Auto-sampling: “Discovers” the best sample size for every table in your system for you.
Get the Quality of a COMPUTE with SPEED of a SAMPLE
Oracle’ goal is to OBSOLETE the need and use of sampling.
Accuracy is comparable to COMPUTE
Incremental Statistics Maintenance - Stats by Partition vs. table : 157 Incremental Statistics Maintenance - Stats by Partition vs. table In 10g, if you gather stats on one partition after a bulk load it causes a full scan of all partitions to gather global table statistics with is extremely time consuming
In 10g, you have to manual copy statistics to new partition
In 11g Gather stats for TOUCHED PARTITIONS only!
Table stats are refreshed WITHOUT scanning the un-touched partitions.
Manage New StatisticsGather Stats but make PENDING : 158 Manage New StatisticsGather Stats but make PENDING Currently DBAs are scared to gather stats on a table that is changing for fear of unpredictable execution plans.
You have to ‘FREEZE’ critical plans or stats.
In 11g, gather stats and save as PENDING.
Verify the new stats won’t adversely affect things by checking them with a single user using an alter session or try them out on a different system.
When everything looks good – then, PUBLISH them for all to use!
Manage New StatisticsGather Stats but make them PENDING : 159 Manage New StatisticsGather Stats but make them PENDING select dbms_stats.get_prefs('PUBLISH', 'SH', 'CUST') publish from dual;
PUBLISH
--------------------
TRUE
exec dbms_stats.set_table_prefs('SH', 'CUST', 'PUBLISH', 'false');
PL/SQL procedure successfully completed.
select dbms_stats.get_prefs('PUBLISH', 'SH', 'CUST') publish from dual;
PUBLISH
--------------------
FALSE
Manage New StatisticsGather Stats but make them PENDING : 160 Manage New StatisticsGather Stats but make them PENDING select table_name, last_analyzed analyze_time, num_rows, blocks, avg_row_len
from user_tables
where table_name = 'CUST';
TABLE_NAME ANALYZE_T NUM_ROWS BLOCKS AVG_ROW_LEN
---------- --------- ---------- ---------- -----------
CUST
execute dbms_stats.gather_table_stats('SH', 'CUST');
PL/SQL procedure successfully completed.
select table_name, last_analyzed analyze_time, num_rows, blocks, avg_row_len
from user_tables
where table_name = 'CUST';
TABLE_NAME ANALYZE_T NUM_ROWS BLOCKS AVG_ROW_LEN
---------- --------- ---------- ---------- -----------
CUST
Manage New StatisticsPUBLISH Stats after Testing Complete : 161 Manage New StatisticsPUBLISH Stats after Testing Complete alter session set optimizer_use_pending_statistics = true;
(Then run your query – If ready/better – publish the new stats)
exec dbms_stats.publish_pending_stats('SH', 'CUST');
PL/SQL procedure successfully completed.
select table_name, last_analyzed analyze_time, num_rows, blocks, avg_row_len
from user_tables
where table_name = 'CUST';
TABLE_NAME ANALYZE_T NUM_ROWS BLOCKS AVG_ROW_LEN
---------- --------- ---------- ---------- -----------
CUST 13-OCT-07 55500 1485 180
exec dbms_stats.delete_table_stats('SH', 'CUST');
Extended Optimizer Statistics:New Multi-Column Statistics : Extended Optimizer Statistics:New Multi-Column Statistics Corporate data often has correlations between different columns of a table. For example:
A job title is correlated to the salary.
The season affects the sold amounts of items such as swim suits sell more in the summer and snow shoes sell more in the winter.
The make of a car and color are often used together but are not really correlated well so the filter doesn’t reduce the result set.
Optimizer has to estimate the correct cardinality
Will the additional column condition reduce the result set or not? Should it be used.
Oracle calculates correlated statistics so the optimizer will make great decisions. Single column statistics and histograms are not enough!
Example : Example SELECT make, price, color
FROM cars_dot_com
WHERE make = ‘CORVETTE’; Three records selected.
Single column statistics are accurate
Example, cont. : Example, cont. SELECT make, price, color
FROM cars_dot_com
WHERE make = ‘CORVETTE’
AND COLOR = 'RED‘; One record selected.
No correlated columns
Additional predicate reduces result set
Single column statistics are STILL sufficient
Example, cont. : Example, cont. Three records selected.
Correlated columns
Additional predicate has no effect
Single column statistics are NOT sufficient
Must use ‘=‘ and not < or > SELECT make, price, color
FROM cars_dot_com
WHERE make = ‘CORVETTE’
AND PRICE = 50000;
Manage New Statistics – FYI OnlyEXTENDED Statistic Group : 166 Manage New Statistics – FYI OnlyEXTENDED Statistic Group Provides a way to collect stats on a group of columns
Full integration into existing statistics framework
Automatically maintained with column statistics
Instantaneous and transparent benefit for any application
Accurate cardinalities for inter-related columns
Multiple predicates on the same table are estimated correctly
Manage New Statistics – FYI OnlyAfter normal Statistics Creation : 167 Manage New Statistics – FYI OnlyAfter normal Statistics Creation select column_name, num_distinct, histogram
from user_tab_col_statistics where table_name = 'CUSTOMERS‘;
COLUMN_NAME NUM_DISTINCT HISTOGRAM
------------------------------ ------------ ---------------
CUST_VALID 2 NONE
COUNTRY_ID 19 FREQUENCY
CUST_STATE_PROVINCE 145 NONE
CUST_CITY_ID 620 HEIGHT BALANCED
CUST_CITY 620 NONE
CUST_LAST_NAME 908 NONE
CUST_FIRST_NAME 1300 NONE
CUST_ID 55500 NONE
...
23 rows selected.
Manage New Statistics – FYI OnlyCreate EXTENDED Statistic Group : 168 Manage New Statistics – FYI OnlyCreate EXTENDED Statistic Group Now lets create the extended statistics group & re-gather statistics on the CUSTOMER table (query user_tab_col_statistics to see new column):
select dbms_stats.create_extended_stats('SH','CUSTOMERS', '(country_id, cust_state_province)') from dual;
DBMS_STATS.CREATE_EXTENDED_STATS('SH','CUSTOMERS','(CO
--------------------------------------------------------------------------------
SYS_STUJGVLRVH5USVDU$XNV4_IR#4
exec dbms_stats.gather_table_stats('SH','CUSTOMERS', method_opt => 'for all columns size skewonly');
PL/SQL procedure successfully completed.
Manage New Statistics – FYI OnlyNow there are Extended Statistics : 169 Manage New Statistics – FYI OnlyNow there are Extended Statistics select column_name, num_distinct, histogram
from user_tab_col_statistics where table_name = 'CUSTOMERS';
COLUMN_NAME NUM_DISTINCT HISTOGRAM
------------------------------ ------------ ---------------
SYS_STUJGVLRVH5USVDU$XNV4_IR#4 145 FREQUENCY
CUST_VALID 2 FREQUENCY
COUNTRY_ID 19 FREQUENCY
CUST_STATE_PROVINCE 145 FREQUENCY
CUST_CITY_ID 620 HEIGHT BALANCED
CUST_CITY 620 HEIGHT BALANCED
CUST_LAST_NAME 908 HEIGHT BALANCED
CUST_FIRST_NAME 1300 HEIGHT BALANCED
CUST_ID 55500 HEIGHT BALANCED
...
24 rows selected.
Manage New Statistics – FYI OnlyDROP Extended Statistics : 170 Manage New Statistics – FYI OnlyDROP Extended Statistics exec dbms_stats.drop_extended_stats('SH', 'CUSTOMERS', '(country_id, cust_state_province)');
PL/SQL procedure successfully completed.
select column_name, num_distinct, histogram
from user_tab_col_statistics where table_name = 'CUSTOMERS‘;
COLUMN_NAME NUM_DISTINCT HISTOGRAM
------------------------------ ------------ ---------------
CUST_VALID 2 NONE
COUNTRY_ID 19 FREQUENCY
CUST_STATE_PROVINCE 145 NONE
CUST_CITY_ID 620 HEIGHT BALANCED
CUST_CITY 620 NONE
CUST_LAST_NAME 908 NONE
CUST_FIRST_NAME 1300 NONE
CUST_ID 55500 NONE
...
23 rows selected.
Adaptive Cursor Sharing : 171 Adaptive Cursor Sharing The optimizer peeks at user-defined bind values during plan selection on the hard parse.
Initial value of the binds determines the plan for all future binds (hopefully the first peek covers most queries)
Same execution plan shared regardless of future bind values
One plan is not always appropriate for all bind values for a given SQL statement
Where job= ‘PRESIDENT’ (use an index – only one row)
Where job = ‘OPERATOR’ (don’t use an index – 90% of the table)
If Oracle “peeks” and sees the President, it will use the index. Future queries also use the index without peeking after that (bad for the OPERATOR query).
Bind Peeking – Pre-11g : 172 Bind Peeking – Pre-11g If you need to tune a query that you suspect has issues related to bind peeking, use v$sql_plan or tkprof output using different values for bind variables and compare execution plans in both cases.
If you wish to deactivate bind peeking you can set:
alter system set "_OPTIM_PEEK_USER_BINDS"=FALSE;
Note: When running tkprof "explain=username/password" argument should NOT be used. That will cause tkprof to issue an explain plan whose output could differ from the execution plan info inside the raw 10046/sql_trace file.
Consider a Telephone Company… : Consider a Telephone Company… SELECT Ename, Empno, Job
FROM Emp
WHERE Job = :B1
Value of B1 = ‘OPERATOR’; If ‘OPERATOR’ is the bind value at hard parse, most records will be selected. Execution plan will be a full table scan
If ‘PRESIDENT’ is the bind value at hard parse, few records will be selected. Execution plan will be an index search
Adaptive Cursor Sharing : 174 Adaptive Cursor Sharing Solution:
In 11g, Oracle uses bind-aware cursor matching.
Share the plan when binds values are “equivalent”
Plans are marked with selectivity range
If current bind values fall within range they use the same plan
Create a new plan if binds are not equivalent
Generating a new plan with a different selectivity range
Bind Peeking Cursor Sharing (cs) Statistics : 175 Bind Peeking Cursor Sharing (cs) Statistics select sql_id, peeked, executions, rows_processed, cpu_time
from v$sql_cs_statistics; (using the peeked value on the 2nd+ execution)
SQL_ID P EXECUTIONS ROWS_PROCESSED CPU_TIME
------------- - ---------- -------------- ----------
5wfj3qs71nd7m Y 3 1 0
2rad83pp613m1 Y 3 3 0
dr78c03uv97bp N 1 3 0
dr78c03uv97bp N 1 3 0
dr78c03uv97bp Y 1 3 0
9qv6tq9ag5b80 Y 3 3 0
a2k4qkh681fzx Y 3 2 0
413zr99jf9h72 N 1 1 0
413zr99jf9h72 N 1 1 0
413zr99jf9h72 Y 1 1 0
fd69nfzww1mhm Y 6 0 0
Bind Peeking – V$SQL : 176 Bind Peeking – V$SQL select sql_id, executions, is_bind_sensitive, is_bind_aware
from v$sql;
SQL_ID EXECUTIONS I I
------------- ---------- - -
9ugwm6xmvw06u 11 Y N
bdfrydpbzw07g 11 Y N
57pfs5p8xc07w 20 N N
...
is_bind_sensitive – If ‘Y’, then Oracle is using multiple plans depending on bind variable.
is_bind_aware – Oracle knows that the different data patterns may result depending on bind value. Oracle switches to a bind-aware cursor and may hard parse the statement.
Enterprise Manager for the Grid : 177 Host and Hardware Enterprise Manager for the Grid Database Oracle9iAS Storage Network and Load Balancer Applications Administration
Monitoring
Provisioning
Security Enterprise
Manager
Grid Control – 10gR2; Many more Options! : 178 Grid Control – 10gR2; Many more Options!
Enterprise Manager 11g Just a bit of changes... : 179 Enterprise Manager 11g Just a bit of changes... Specific
Database
Instance
We have 5
ADDM
Findings
Check them
Here
Slide 180 : Security Enhancements
Security Enhancements : 181 Security Enhancements 11g is more restrictive
Password lock time (1), password grace time (7) and password life time (180) all more restrictive; Failed login attempts stays the same (10).
Passwords will be case sensitive now! (on by default)
Enhanced hashing algorithm for passwords / DES still available.
Strong passwords (set via password complexity verification in EM or SQL):
Minimum 8 characters
At least one letter and one digit
Not servername or servername(1-100)
Not a common password (i.e. welcome1)
Must differ from previous password by 3 characters minimum
Security EnhancementsAUDIT_TRAIL=DB (default) : 182 Security EnhancementsAUDIT_TRAIL=DB (default) Audit Trail is ON by default (was off in 10g),
AUDIT_TRAIL=DB is now the default.
Things that will be audited by default include:
CREATE USER, CREATE SESSION, CREATE ANY TABLLE, CREATE ANY PROCEDURE, CREATE ANY JOB, CREATE EXTERNAL JOB, CREATE ANY LIBRARY, CREATE PUBLIC DB LINK
ALTER USER, ALTER ANY TABLE, ALTER ANY PROCEDURE, ALTER PROFILE, ALTER DATABASE, ALTER SYSTEM, AUDIT SYSTEM
DROP USER, DROP ANY TABLE, DROP ANY PROCEDURE, DROP PROFILE
GRANT ANY PRIVILEGE, GRANT ANY OBJECT PRIVILEGE
EXEMPT ACCESS POLICY
AUDIT SYSTEM
Cost of Auditing improved to be 1-2% cost on TPCC benchmark.
All the Rest worth noting… : 183 All the Rest worth noting… SEC_CASE_SENSITIVE_LOGON=FALSE
CONNECT Role only Create Session (vs. Tbl/View…)
Consider: _NEW_INITIAL_JOIN_ORDERS=FALSE
(CBO more join orders – higher parse times possible)
GATHER_STATS_JOB on for all DML:
DBMS_STATS.LOCK_TABLE_STATS(‘SH’,’T1’);
Auto PROFILES if 3x better; Oracle Always Tuning…
Statspack STILL works in 11g
Real Time stats generated for high cpu queries – Careful!
Generate System Stats on migrate: Tune / 11g Parameters
Slide 184 : 184 Oracle Audit Vault
Oracle Database Vault
DB Security Evaluation #19
Transparent Data Encryption
EM Configuration Scanning
Fine Grained Auditing (9i)
Secure application roles
Client Identifier / Identity propagation
Oracle Label Security (2000)
Proxy authentication
Enterprise User Security
Global roles
Virtual Private Database (8i)
Database Encryption API
Strong authentication (PKI, Kerberos, RADIUS)
Native Network Encryption (Oracle7)
Database Auditing
Government customer Oracle Database Security
Built over MANY years... 2007 1977
Oracle Upgrade Case Studies(Thanks Mike Dietrich, Carol Tagliaferri, Roy Swonger: 11g Upgrade Paper – Oracle Germany) : 185 Oracle Upgrade Case Studies(Thanks Mike Dietrich, Carol Tagliaferri, Roy Swonger: 11g Upgrade Paper – Oracle Germany) University with about 20,000 users on Sun Solaris
Moved 10 databases from 9.2.0.8 to 11.1.0.6
Used SQL Tuning Advisor and SQL Performance Analyzer (SPA) to fix 94 queries
Also moved to RAC, ASM & Data Guard
30% more logins and yet Response Time is 50% LOWER!
International Customer with 400+ databases on IBM AIX & EMC DMX disks
Moved from 9.2.0.8 to 11.1.0.6 – 54% slower
Used SPM, SPA, DB Replay to tune things…
Changed parameters to 11g – 15% improvement
Gathered system stats – 7% improvement
Used SPA – 18% improvement
Turn on SQL Profiling (SPM) – 8% improvement
11g is now 11% FASTER than 9.2.0.8
Data Warehouse customer on RH Linux 64-Bit
Moving from 10.2 to 11.1.0.7 with 50 databases each at around 10T
Over 200,000 partitions in the database
Silent Upgrade of 50 other DWHS’s unattended using DBUA silent mode
Slide 186 : 186 2K – A typewritten page
5M – The complete works of Shakespeare
10M – One minute of high fidelity sound
2T – Information generated on YouTube in one day
10T – 530,000,000 miles of bookshelves at the Library of Congress
20P – All hard-disk drives in 1995 (or your database in 2010)
700P –Data of 700,000 companies with Revenues less than $200M
1E – Combined Fortune 1000 company databases (average 1P each)
1E –Next 9000 world company databases (average 100T each)
8E – Capacity of ONE Oracle10g Database (CURRENT)
12E to 16E – Info generated before 1999 (memory resident in 64-bit)
16E – Addressable memory with 64-bit (CURRENT)
161E – New information in 2006 (mostly images not stored in DB)
1Z – 1000E (Zettabyte - Grains of sand on beaches -125 Oracle DBs)
100TY - 100T-Yottabytes – Addressable memory 128-bit (FUTURE) The Future: 8 Exabytes
Look what fits in one 10g Database!
Slide 187 : 187 All databases of the largest 1,000,000 companies
in the world (3E).
or
All Information generated in the world in 1999 (2E)
or
All Information generated in the world in 2003 (5E)
or
All Email generated in the world in 2006 (6E)
or
1 Mount Everest filled with Documents (approx.) 8 Exabytes:
Look what fits in one 10g Database!
Compelling Technology Statistics! : Compelling Technology Statistics!
Friedman’s 6 Dimensions of Understanding Globalization* : 189 Friedman’s 6 Dimensions of Understanding Globalization* Politics (Merging)
Culture (Still disparate)
Technology (Merging/Merged)
Finance (Merging/Merged)
National security (Disparate)
Ecology (Merging)
* Sited from Mark Hasson, PSU, Global Pricing and International Marketing.
Slide 190 : 190 Rich Niemiec, Rolta TUSC (www.roltatusc.com.au)
Oracle ??: ?????Oracle????,????,???????,???????????????,???????????Oracle????????????????????????? 11g?????
Open World 2007 (??11g Beta)
Slide 191 : 191
V$ Views over the years : 192 V$ Views over the years Version V$ Views X$ Tables
6 23 ? (35)
7 72 126
8.0 132 200
8.1 185 271
9.0 227 352
9.2 259 394
10.1.0.2 340 (+31%) 543 (+38%)
10.2.0.1 396 613
11.1.0.6 484 (+22%) 798 (+30%)
Oracle is never caught from behind Oracle’s 30th Anniversary in 2007 : 193 Oracle is never caught from behind Oracle’s 30th Anniversary in 2007 Great Sales/Marketing
Great Database
Applications Leader
BI Leader
Already in the lead
Game Over!
A Diverse Team is Oracle’s Secret! : 194 A Diverse Team is Oracle’s Secret! “Larry Ellison is the genius behind Oracle, the company, Bob Miner was the genius behind Oracle, the product. The combination of the diverse team Oracle has had over the years is the secret of their success!”
- Rich Niemiec, Select Magazine, 2001
Summary : 195 Summary Know the Oracle
Start Me Up – Using Memory Target
The Buffer Cache & The Result Cache
Virtual Columns
Invisible Indexes & Online Index Rebuilds
Creating & Rebuilding Indexes Online
DDL Lock Timeout, PL/SQL Expressions/Simple Integer
Secure Files
ADDM Enhancements
SQL Plan Management (SPM) and capturing SQL Plan Baselines
SQL Performance Analyzer, Access Advisor & Query Repair Advisor
Real Application Testing (Database Capture and Replay)
Interval Partitioning & Partition Compression
Automatic Diagnostic Repository (ADR)
Auto Sample, Creating Pending Statistics
Adaptive Cursor Sharing and Bind Peeking
EM, Grid Control, Security Enhancements & the Future Sizes
For More Information : 196 www.tusc.com
Oracle9i Performance Tuning Tips & Techniques; Richard J. Niemiec; Oracle Press (May 2003)
Oracle 10g Tuning (June 11, 2007) “If you are going through hell, keep going” - Churchill For More Information
???? : 197 www.tusc.com
Oracle9i Performance Tuning Tips & Techniques; Richard J. Niemiec; Oracle Press (May 2003)
Oracle 10g Tuning (June 11, 2007) “???????????????”
- Henry David Thoreau ????
Know the DBA Mind! : 198 Know the DBA Mind!
This is How DBA View Themselves! : 199 This is How DBA View Themselves!
Be a Better DBA to the Business! : 200 Be a Better DBA to the Business! Do Developers think of this when they think of their DBA?
Slide 201 : “You must BE the change you want to see in the world.”
--Mahatma Gandhi
www.rolta.com www.tusc.comrich@tusc.com : 202 www.rolta.com www.tusc.comrich@tusc.com “Success usually comes to those that are too busy to be looking for it.”
- Henry David Thoreau
Copyright Information : 203 Copyright Information Neither Rolta. Rolta TUSC, Oracle nor the author guarantee this document to be error-free. Please provide comments/questions to rich@tusc.com.
Rolta TUSC©2009. This document cannot be reproduced without expressed written consent from an officer of Rolta or Rolta TUSC, but the audience may reproduce or copy this for conference use.
Contact Information
Rich Niemiec: rich@tusc.com
References : 204 References www.tusc.com. www.rolta.com
Oracle10g Performance Tuning Tips & Techniques; Richard J. Niemiec; Oracle Press
Database Secure Configuration Initiative: Enhancements with Oracle Database 11g, www.oracle.com
All Oracle11g Documentation from Oracle Beta Site
Introduction to Oracle Database 11g, Ken Jacobs
Oracle Database 11g New Features, Linda Smith
New Optimizer Features in 11g, Maria Colgan
www.ioug.org, www.oracle.com & technet.oracle.com
Thanks Dan M., Bob T., Brad, Joe, Heidi, Mike K., Debbie, Maria, Linda
All companies and product names are trademarks or registered trademarks of the respective owners.
Dedicated to the memory of Stan Yellott, Mark Beaton, Ray Mansfield, Lex De Haan, Elaine DeMeo and Jim Gray.
Rolta TUSC Services : 205 Rolta TUSC Services Rolta Software
Oracle Technical Solutions
Full-Life Cycle Development Projects
Enterprise Architecture
Fusion Middleware, Database & OBIEE Services
Oracle Application Solutions
Oracle Applications & EPM Implementations/Upgrades
Oracle Applications & Hyperion Tuning
Managed Services
24x7x365 Remote Monitoring & Management
Functional & Technical Support (Oracle, SQL Server, Unix)
Training / Mentoring & Oracle Reseller
Slide 206 : 206 “The Oracle Experts” since 1988
Oracle Partner of the Year, 2002, 2004, 2007 & 2008
Editors Choice – Consultant of the Year 2002 & 2004
Authorship, User Groups and Various Awards
One of the first 6 Oracle Masters in the World
Certified Advantage Partner Rolta TUSC Corporate Profile
Rolta TUSC – Your Partner ….Accomplished in Oracle! : 207 Rolta TUSC – Your Partner ….Accomplished in Oracle! 207 2008 Oracle Partner of the Year (Titan) Prior Winner 2002, 2004, 2007
Rich’s Overview(rich@tusc.com) : 208 Rich’s Overview(rich@tusc.com) President Rolta EICT International
President of TUSC – A Rolta Company:
Inc. 500 Company (Fastest Growing 500 Private Companies)
10 Offices in the United States (U.S.); Based in Chicago
Oracle Advantage Partner in Tech & Applications
Author (3 Oracle Best Sellers – #1 Tuning Book for a Decade):
Oracle Performing Tips & Techniques (Covers Oracle7 & 8i)
Oracle9i Performance Tips & Techniques
Oracle Database 10g Performance Tips & Techniques
Former President of the International Oracle Users Group
Current President of the Midwest Oracle Users Group
Chicago Entrepreneur Hall of Fame - 1998
Entrepreneur of the Year & National Hall of Fame - 2001
IOUG Top Speaker in 1991, 1994, 1997, 2001, 2006, 2007
MOUG Top Speaker Twelve Times
National Trio Achiever award - 2006
Oracle Certified Master & Oracle Ace Director
Purdue Outstanding Electrical & Computer and Engineer - 2007