1 The Best Oracle Database 11g New Features All India Oracle Users Group 2009 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.2 Audience Knowledge /Versions•Oracle7 Experience ?•Oracle8iExperience ?•Oracle9iExperience ?•Oracle10gExperience?•Oracle Database 11gExperience?•Goals–Present NEW features in an EASY way–Focus on a few nice features of Oracle11g•Non-Goals–Learn ALL aspects of Oracle11g 3 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 SizesKnow the Oracle5 Oracle Firsts –Innovation!1979 First commercial SQL relational database management system1983 First 32-bitmode RDBMS1984 First database with read consistency1987 First client-serverdatabase1994 First commercial and multilevel secure database evaluations1995First 64-bitmode RDBMS1996 First to break the 30,000 TPC-C barrier1997First Webdatabase1998 First Database -Native JavaSupport; 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 Clusters2004 First True Grid Database2005 First FREE Oracle Database(10g Express Edition) 2006 First Oracle Support for LINUX Offering 2007 Oracle 11g Released! 2008Oracle Exadata Server Announced (Oracle buys BEA)2009Oracle buys Sun –Java; MySQL; Solaris; Hardware; OpenOffice6 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 stillthe database lead•New releases of Siebel, PeopleSoft and Oracle12 Apps.•New Oracle BI Suite &Acquisition of Hyperion •Acquisition of BEA, SUN7 Oracle gets Sun: Java, MySQL, Solaris, OpenOffice, Hardware, Storage TechTesting the FutureVersionVersion 11.1.0.6.0 of the Database9 Oracle Database 11g Release 1: Upgrade PathsSource DatabaseTarget Database9.2.0.4.0 (or higher)11.1.x10.1.0.2.0 (or higher)11.1.x10.2.0.1.0 (or higher)11.1.xDirect Upgrade PathDirect Upgrade PathSource DatabaseUpgrade Path for TargetDatabaseTarget Database7.3.3.0.0 (or lower)7.3.4.x --> 9.2.0.8 11.1.x8.0.5.0.0 (or lower)8.0.6.x --> 9.2.0.8 11.1.x8.1.7.0.0 (or lower)8.1.7.4 --> 9.2.0.8 11.1.x9.0.1.3.0 (or lower) 9.0.1.4 --> 9.2.0.8 11.1.xIn-Direct Upgrade PathIn-Direct Upgrade Path10 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 Configuration11 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.12 The New Version –Life is Good!$ sqlplus ***/***SQL*Plus: Release 11.1.0.6.0 -Production on Tue Oct 30 11:21:04 2007Copyright (c) 1982, 2007, Oracle. All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 -ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> startupORACLE instance started.Total System Global Area 422670336 bytesFixed Size 1300352 bytesVariable Size 306186368 bytesDatabase Buffers 109051904 bytesRedo Buffers 6131712 bytesDatabase mounted.Database opened.13 Database Information -UP!Monitor Database(UP)Users areDefinitelyUsing it!MEMORY_TARGET&Automatic Memory Management15 Automatic Memory Management (AMM)MEMORY_TARGET in 11g•First there was some Automatic Memory Mgmt -9i–SGA_MAX_SIZEintroduced 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 minimums16 SGA & PGA will be MEMORY_TARGET17 Automatically sizedSGAComponents thatUse SGA_TARGETComponentInitialization ParameterFixed SGANoneShared PoolSHARED_POOLLarge PoolLARGE_POOL_SIZEJava PoolJAVA_POOL_SIZEBuffer CacheDB_CACHE_SIZEStreams PoolSTREAMS_POOL_SIZE18 Manually SizedSGAComponents thatUse SGA_TARGETComponentInitialization ParameterLog bufferLOG_BUFFER (pfile only in 10g)Keep PoolDB_KEEP_CACHE_SIZERecycle PoolDB_RECYCLE_CACHE_SIZEBlock cachesDB_nK_CACHE_SIZEProgram Global Area (now in MEMORY_TARGET):Aggregate PGAPGA_AGGREGATE_TARGET19 Automatic Memory Management (AMM)MEMORY_TARGET in 11gSQL> shoparameter sga_NAME TYPE VALUE---------------------------------------------------------------------------sga_max_sizebig integer 360Msga_targetbig integer 0SQL> shoparameter memoryNAME TYPE VALUE------------------------------------------------------------------------------memory_max_targetbig integer 360Mmemory_target big integer 360M20 Moving from SGA_TARGET to:MEMORY_TARGETSQL> shoparameter targetNAME TYPE VALUE-------------------------------------------------------------------------------memory_max_targetbig integer 0memory_target big integer 0pga_aggregate_targetbig integer 110Msga_targetbig integer 250M 21 Moving from SGA_TARGET to:MEMORY_TARGETALTER 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> shoparameter targetNAME TYPE VALUE-------------------------------------------------------------------------------memory_max_targetbig integer 360Mmemory_target big integer 360Mpga_aggregate_targetbig integer 0sga_targetbig integer 0 22 Moving from SGA_TARGET to:MEMORY_TARGET (set minimums)ALTER SYSTEM SET SGA_TARGET=200; ALTER SYSTEM SET PGA_AGGREGATE_TARGET=100; SQL> shoparameter targetNAME TYPE VALUE-------------------------------------------------------------------------------memory_max_targetbig integer 360Mmemory_target big integer 360Mpga_aggregate_targetbig integer 100Msga_targetbig integer 200MBuffer Cache & Result Cache24 First, A quick review:Flush Buffer Cache•The new 10g feature allows the flush of the buffer cache. It isNOT 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 ofOracle.•To flush the buffer cache perform the following:SQL> ALTER SYSTEM FLUSH BUFFER_CACHE;25 Flush Buffer Cache Exampleselect count(*) from tab1;COUNT(*)-----------------1147Execution 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 gets7 consistent gets6 physical reads26 Flush Buffer Cache Exampleselect count(*) from tab1; (Run it again and the physical reads go away)COUNT(*)-----------------1147Execution 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 gets7 consistent gets0 physical reads27 Flush Buffer Cache ExampleALTER SYSTEM FLUSH BUFFER_CACHE;System altered.select count(*) from tab1; (Flush the cache and the physical reads are back)COUNT(*)-----------------1147Execution 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 gets7 consistent gets6 physical reads28 The Result Cache•Function Resultsof 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_ONclauses•Takes its memory from the Shared Pool–Set with RESULT_CACHE_SIZE–RESULT_CACHE_MODE=force (auto/manual)•DBMS_RESULT_CACHE.FLUSHto clear•Is NOT passed between RAC/Grid nodes•Check the docs for other Restrictions & Rules!!29 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_maxfrom empstgroup by t.country_name, t.city_name)order by a_maxdesc) where rownum< 2;30 Result Cache Example PerformanceStep 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).31 Result Cache Example PerformanceStep 3-In Session 2Executed query with the RESULT_CACHE hint and it returned an elapsed time of 3.18 seconds (cache it).Step 4-In Session 1Executed query without the RESULT_CACHE hint, but with RESULT_CACHE_MODE=force and it returned an elapsed time of0.86 seconds (cached!!).32 Result Cache Example QueryFrom the Oracle Docs•The RELIES_ON Clausespecifies tables or views that the Function Results are dependent on.--Package specification CREATE OR REPLACE PACKAGE HR IS ... type DeptInfoRecIS RECORD (avgSalNUMBER, numberEmployeesNUMBER); --Function declaration FUNCTION GetDeptInfo(dept_idNUMBER) RETURN DeptInfoRecRESULT_CACHE; ... END HR; 33 Result Cache Example QueryFrom the Oracle DocsPACKAGE BODY HR IS ... --Function definition FUNCTION GetDeptInfo(dept_idNUMBER) RETURN DeptInfoRecRESULT_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; 34 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_DEPENDENCY35 The Result Cache –FYI OnlyDigging DeeperKSPPINM KSPPSTVL KSPPDESC------------------------------------------------------------------------------_result_cache_auto_execution_threshold1 result cache auto execution threshold_result_cache_auto_size_threshold100 result cache auto max size allowed_result_cache_auto_time_threshold1000 result cache auto time threshold_result_cache_block_size1024 result cache block size_result_cache_bypassFALSE bypass the result cache_result_cache_hash_buckets1024 hash bucket count_result_cache_invalid0 post-invalidation usage allowance_result_cache_max_result100 maximum result size as percent of cache size_result_cache_remote_expiration0 maximum life time (min) for anyresult using a remote object_result_cache_timeout60 maximum time (sec) a session waits for a result36 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_handleIN VARCHAR2 := NULL, plan_nameIN VARCHAR2 := NULL, format IN VARCHAR2 := 'TYPICAL') <‘BASIC’/’ALL’>RETURN dbms_xplan_type_table;37 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 version11 rows selectedThe Virtual Column39 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 partitionon 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.40 The Virtual Columncreate table emp_rich(empnonumber(4),sal number(7,2),yearly_salgenerated 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.41 The Virtual Columnselect * from emp_rich;EMPNO SAL YEARLY_SALDEPTNO----------------------------------------------------------7369 800 9600207499 1600 19200307521 1250 15000307566 2975 35700207654 1250 15000307698 2850 3420030...The Invisible Index43 The Invisible Index•Set an index to VISIBLE or INVISIBLE–ALTER INDEX idxINVISIBLE; –ALTER INDEX idxVISIBLE;–CREATE INDEX... INVISIBLE;•Great to turn off indexesfor 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!44 The InvisibleIndexcreate index deptno_invisible_idxon dept_rich(deptno) invisible;Index created.select count(*) from dept_richwhere deptno = 30; (doesn’t see the index)COUNT(*)--------------512Execution 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 |--------------------------------------------------------------------------------45 The InvisibleIndexselect /*+ index(dept_richdept_rich_inv_idx) */count(*) from dept_richwhere deptno = 30; (forces the index with hint)COUNT(*)--------------512Execution 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 |---------------------------------------------------------------------------------------46 The Invisible Index (set visible)alter index dept_rich_inv_idxvisible;Index altered.select count(*) from dept_richwhere deptno = 30;(it does see the index)COUNT(*)--------------512Execution 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 |---------------------------------------------------------------------------------------47 The Invisible Index (set visible)select /*+ no_index(dept_richdept_rich_inv_idx) */count(*)from dept_richwhere deptno = 30; (forces notusing the index with hint)COUNT(*)----------512Execution 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 |--------------------------------------------------------------------------------48 The Invisible Index (check it)alter index dept_rich_inv_idxinvisible;Index altered.select index_name, visibilityfrom dba_indexes (or go to USER_INDEXES)where index_name = 'DEPT_RICH_INV_IDX‘;INDEX_NAME VISIBILITY--------------------------------------------------DEPT_RICH_INV_IDX INVISIBLECreate & Rebuild Index Online50 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_nameON table (col1,...)ONLINE;Index created.ALTER INDEX index_nameREBUILD ONLINE;Index altered.51 Rebuild Index or Coalesce (FYI)Coalesce Example from Oracle Doc. 52 Rebuild Index or CoalesceCoalesce•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 useRebuild:•Quickly move index to another tablespace•Requires more disk space•Creates new index tree and shrinks heights•Change storage/tblspcw/o droppingNice Developer Tools/ImprovementsDDL_LOCK_TIMEOUTPL/SQL ExpressionsSimple IntegerNew PL/SQL Packages54 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 waitfor 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 = 30Session altered.55 Allow Sequencesin 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 neededso the code is more efficient.•For big jobs –Saves MANY cursors 56 Allow Sequences in PL/SQL ExpressionsOLD WayDECLAREV_NEW_VAL NUMBER;BEGINSELECT MY_SEQ.NEXTVAL INTO V_NEW_VAL FROM DUAL;END;NEW WayDECLAREV_NEW_VAL NUMBER;BEGINV_NEW_VAL := MY_SEQ.NEXTVAL;END;57 Simple IntegerData Type•Oracle added the new SIMPLE_INTEGER data typeto 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 timesincrementing a numeric variable by one. We executed the test for each of these three times.Results:NUMBER: 1.26sPLS_INTEGER: 0.88sSIMPLE_INTEGER: 0.65s58 Additional EnhancementsNew PL/SQLPackages•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•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_CACHE59 Additional EnhancementsEnhanced PL/SQLPackages•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•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_FLASHBACKNice DBA ToolOracle Secure Files61 Oracle SecureFilesHigh-Performance Large Objects•High-performancetransactional 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 migration62 Oracle Secure FilesBetter Performance than LOBs…Read Performance Write Performance 0.010.1110100Mb/Sec0.010.1110100Mb/SecFile Size (Mb)File Size (Mb)Secure FilesLinux FilesSecure FilesLinux FilesAdding Files using New Disk Space –2x fast than LOBsAdding Files using Deleted Space –22x faster than LOBsPL/SQL Reads –6x Faster than LOBsYour mileage will vary.... ADDM Enhancements(Automatic Database Diagnostic Monitor)64 ADDM enhancements•Global ADDMso that Diagnostics are done across the entire cluster•Emergency ADDMfor 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 snapshots65 ADDM BrieflySpecific Database InstanceWe have 5ADDM FindingsCheck themHere66 ADDM BrieflyTop ADDMFindingsClick a SingleTimeframeLet’s Check the Hard ParseIssue67 ADDM BrieflyDetailedInfo&FindingsAdd’lInfo68 ADDM -Run NOW!A Big ProblemOccursRun ADDM NOW!69 ADDM –Run NOW!Done.CPUIssue70 ADDM –Run NOW!Detailon CPUIssue?SuggestedFixes71 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 timesAWR 1AWR 2AWR 3Inst 1Inst 2Inst 3Self-Diagnostic EngineDatabase-Level ADDM11gInstance-Level ADDM • Allows drill down to instances • Runs proactively every hour when taking AWR Snapshots (default)72 ADDM Considerations:•CPUBottlenecks•Undersized MemoryStructures –SGA /PGA•I/OCapacity Issues•High Load SQLstatements•High Load PL/SQL•RAC specificissues –Global hot block/interconnect•Application issuessuch as parsing, locks...etc.•Concurrency (buffer busy) or hot object issues•Configuration issues –Redo, Archive, Checkpoint.73 SQL Tuning Advisors &SQL Plan Management (SPM)74 SQL Plan Management•SQL Plan Managementis 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 Profilecontains additionalSTATISTICSfor this SQL statement for the query optimizer to generate a better execution plan. An outline/baseline contains HINTSfor this SQL statement for query optimizer to generate a better execution plan.75 SQL Plan Management•SQL ProfilestoresSTATISTICSfor a SQL statement for the query optimizer to generate a better execution plan. •A Stored Outline/SQL Plan Baseline contains HINTSfor 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.76 SQL Plan ManagementCreate a SQL Tuning SetTuningIssueCreate a TuningSet from Top 10SQL77 SQL Plan ManagementCreate a SQL Tuning SetTuning Set NameQueries78 SQL Plan ManagementViewing a SQL Tuning SetTuning Set NameQueries& Stats79 SQL Plan ManagementCreate a SQL Tuning SetRun theTuning Advisoron thisSQLTuning Set (STS)Run itNOW80 SQL Plan ManagementCreate a SQL Tuning SetResultsSelectOne queryAnd clickView81 SQL Plan ManagementClick on any SQL IDSQL TextWaits & Statistics82 SQL Plan ManagementCreate a SQL Tuning SetCompareBefore&After83 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)84 SQL Plan ManagementCapturing BaselinesDBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE( sql_id IN VARCHAR2, plan_hash_valueIN 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_handleand sql_text. 85 SQL Plan ManagementCapturing BaselinesDBMS_SPM.LOAD_PLANS_FROM_SQLSET(sqlset_name IN VARCHAR2, sqlset_ownerIN VARCHAR2 := NULL, basic_filterIN VARCHAR2 := NULL, fixed IN VARCHAR2 := 'NO', enabled IN VARCHAR2 := 'YES' commit_rowsIN 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)86 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.begindbms_spm.configure('plan_retention_weeks',100);end;/PL/SQL procedure successfully completed.•You can also purge individual planswith the purge_sql_plan_baselinefunction.•You can also query dba_sql_plan_baselinesas well as use DBMS_XPLAN.display_sql_plan_baselineto view stored plans.87 SQL Performance Analyzer88 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 Batches89 SQL Performance AnalyzerEasy 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!90 SQL Performance AnalyzerClickHere91 SQL Performance AnalyzerGuided Workflow92 SQL Performance AnalyzerOptimizer Upgrade (10g vs. 11g)93 SQL AdvisorsTuningAdvisorsRepairAdvisor(next)SQL Access AdvisorNEW Partition Advisor95 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)96 SQL Access Advisor &NEW Partition AdvisorStep One Use a SQLTuning Set97 SQL Access Advisor &NEW Partition AdvisorLook atPartitionsQuick Solution98 SQL Access Advisor &NEW Partition AdvisorStep 3Scheduleit.Run itNow!99 SQL Access Advisor &NEW Partition AdvisorFinal Review100 SQL Access Advisor &NEW Partition AdvisorJob SubmittedJob Running Now.101 SQL Access Advisor &NEW Partition AdvisorImproveI/O Improve ExecutionTimeThe SQL Repair AdvisorORA-600103 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.104 SQL Repair Advisor –Go straight from AlertsGo to theDatabaseInstance Click Alert (ORA-600) message text to see details105 SQL Repair Advisor –View Problem DetailsClick on View Problem Details to go to the SupportBench106 Support Workbench-DetailsClick onSQL RepairAdvisor 107 Resultsfrom SQL Repair AdvisorClick onView to Get theDetail finding of the Advisor Note a SQLPatch (FIX for the SQL) has been generated108 SQL Repair Advisor Recommendation /ConfirmationClick onImplement To accept the SQL Patch 109 Real Application Testing! Databaseworkloadcapture and replay110 Database workload capture and replay•Used to capture database workloadon one system and replay later on a different system. Useful to compare two different systems. •Could rival LoadRunnerin the future (may be more precise!)Brief Steps:•Captureworkload on a database even from 10gR2 •Restorethe database on a test systemto the SCN when capture begins •Perform upgradeand make changes to the test system as needed •Preprocess the captured workloadif it is not preprocessed •Configure the test systemfor replay (I don’t do this here)•Replay workloadon 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)111 Pre-Change Production SystemClientClient…App ServerApp ServerApp ServerClientBackup…Changes Unsupported11g Changes SupportedCaptured WorkloadProcessProcessProcess…Capture Workload•Database Upgrades, Patches•Schema, Parameters•RAC nodes, Interconnect•OS Platforms, OS Upgrades•CPU, Memory•Storage•Etc. 112 Post-Change Test System…Replay Driver………App ServerApp ServerApp Server…ProcessProcessProcess…ProcessProcessProcess…Can use Snapshot Standby as test systemReplay DriverClientClient…ClientPre-Change (could be 9.2.0.8 or 10g Capture) Production SystemProcessed Captured WorkloadBackupCapture Workload113 Database ReplayFYI Only –Download to view in detailReal AppTesting:Database Replay114 Database workload capture and replayOn NEW system (shortened) –FYI Only115 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 DivergencePartitioning:(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 11G117 Range Partitioning (V8)CREATE TABLE DEPT(DEPTNONUMBER(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’);118 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_qxvalues less than (maxvalue, maxvalue, maxvalue) tablespace users4);119 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_yrinteger not null)partition by hash (acct_no)partitions 4store in (users1, users2, users3, users4);120 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 BYHASH(data_item) SUBPARTITIONS 4STORE 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_1999VALUES 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_1999VALUES LESS THAN (TO_DATE('01-jan-2000', 'dd-mon-yyyy'))SUBPARTITIONS 8STORE 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')));121 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.122 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.123 Interval Partitioning –11gCREATE 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 partition124 Interval Partitioning –11gselect segment_name, partition_namefrom dba_segmentswhere segment_name = 'DEPT_NEW‘;SEGMENT_NAME PARTITION_NAME----------------------------------------------------------DEPT_NEW D1DEPT_NEW D2DEPT_NEW D3125 Interval Partitioning –11gCREATE 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.126 Interval Partitioning –11ginsert into dept_new2 values(40,null);insert into dept_new2 values(50,null);insert into dept_new2 values(99,null);select segment_name, partition_namefrom dba_segmentswhere segment_name = 'DEPT_NEW2'SEGMENT_NAME PARTITION_NAME------------------------------------------------------------DEPT_NEW2 D1DEPT_NEW2 D2DEPT_NEW2 D3DEPT_NEW2 SYS_P41DEPT_NEW2 SYS_P42DEPT_NEW2 SYS_P43127 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 t1COMPRESS FOR ALL OPERATIONSPresentation by Shyam Varan Nath –Honey I shrunk the Data Warehouse128 Partition CompressionCREATE TABLE DEPT_new3(DEPTNO NUMBER(2),DEPT_NAME VARCHAR2(30))COMPRESSPARTITION 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.129 Partition Compressioninsert 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.130 Partition Compressionselect table_name, partition_name, compressionfrom dba_tab_partitionswhere table_name = 'DEPT_NEW3‘;TABLE_NAME PARTITION_NAME COMPRESS----------------------------------------------------------------------------DEPT_NEW3 D1 ENABLEDDEPT_NEW3 D2 DISABLEDDEPT_NEW3 D3 ENABLEDDEPT_NEW3 SYS_P64 ENABLEDDEPT_NEW3 SYS_P65 ENABLEDDEPT_NEW3 SYS_P66 ENABLED6 rows selected.131 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 WindowsDBMS_SCHEDULER.CREATE_WINDOW132 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_DEPENDENTSpackage procedure.•DBMS_STATS performance has been improved.133 Automatic Diagnostic Repository (ADR)134 Automatic Diagnostic Repository (ADR)•Oracle 11g includes a Fault DiagnosabilityInfrastructure 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 135 ADR Directory Structure for aDatabase InstanceADR Basediagrdbmsalert cdumpincidenttrace(others)Alert Log: /u01/app/oracle/diag/rdbms/o11gb/O11gb/traceORACLE_HOME: /u01/app/oracle/product/11.1.0/db_1136 ADR –V$ Diagnostic Infoselect name, value from v$diag_info;NAME VALUE-----------------------------------------------------------------------------Diag Enabled TRUEADR Base /u01/app/oracleADR Home /u01/app/oracle/diag/rdbms/o11gb/O11gbDiag Trace /u01/app/oracle/diag/rdbms/o11gb/O11gb/traceDiag Alert /u01/app/oracle/diag/rdbms/o11gb/O11gb/alertDiag Incident /u01/app/oracle/diag/rdbms/o11gb/O11gb/incidentDiag Cdump/u01/app/oracle/diag/rdbms/o11gb/O11gb/cdumpHealth Monitor /u01/app/oracle/diag/rdbms/o11gb/O11gb/hmDefault Trace File/u01/app/oracle/diag/rdbms/o11gb/O11gb/trace/O11gb_ora_16676.trcActive Problem Count 0Active Incident Count 011 rows selected.Optimizer Statistics & Other Optimizer AdvancesSpecial Thanks: Maria Colgan, Penny Avril & Debbie Migliore 138 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 COMPUTEwith SPEED of a SAMPLE–Oracle’goal is to OBSOLETE the need and use of sampling.–Accuracy is comparable to COMPUTE139 Incremental Statistics Maintenance -Stats by Partitionvs. table•In 10g, if you gather stats on one partitionafter a bulk load it causes a full scan of all partitionsto 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.140 Manage New StatisticsGather Statsbut 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!141 Manage New StatisticsGather Statsbut make them PENDINGselect dbms_stats.get_prefs('PUBLISH', 'SH', 'CUST') publish from dual;PUBLISH--------------------TRUEexec 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--------------------FALSE142 Manage New StatisticsGather Statsbut make them PENDINGselect table_name, last_analyzed analyze_time, num_rows, blocks,avg_row_len from user_tableswhere table_name = 'CUST';TABLE_NAME ANALYZE_T NUM_ROWS BLOCKS AVG_ROW_LEN--------------------------------------------------CUSTexecute dbms_stats.gather_table_stats('SH', 'CUST');PL/SQL procedure successfully completed.select table_name, last_analyzed analyze_time, num_rows, blocks,avg_row_lenfrom user_tableswhere table_name = 'CUST';TABLE_NAME ANALYZE_T NUM_ROWS BLOCKS AVG_ROW_LEN--------------------------------------------------CUST143 Manage New StatisticsPUBLISH Stats after Testing Completealter 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_lenfrom user_tableswhere table_name = 'CUST';TABLE_NAME ANALYZE_T NUM_ROWS BLOCKS AVG_ROW_LEN--------------------------------------------------CUST 13-OCT-07 55500 1485 180exec dbms_stats.delete_table_stats('SH', 'CUST');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 amountsof items such as swim suitssell more in the summer and snow shoessell more in the winter.–The make of a car and colorare 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 setor not? Should it be used.•Oracle calculates correlated statistics so the optimizer will make great decisions. Single column statistics and histograms are not enough!ExampleSELECT make, price, colorFROM cars_dot_comWHERE make = ‘CORVETTE’;SILVER50,000CORVETTEBLACK60,000CORVETTERED40,000CORVETTESLIVER45,000JEEPBLACK35,000JEEPRED90,000CADILLAC SILVER50,000CORVETTEBLACK60,000CORVETTERED40,000CORVETTEColorPriceMake•Three records selected.•Single column statistics are accurate Example, cont.SELECT make, price, colorFROM cars_dot_comWHERE make = ‘CORVETTE’AND COLOR = 'RED‘;RED40,000CORVETTESLIVER45,000JEEPBLACK35,000JEEPRED90,000CADILLAC SILVER50,000CORVETTEBLACK60,000CORVETTERED40,000CORVETTEColorPriceMake•One record selected.•No correlated columns•Additional predicate reduces result set •Single column statistics are STILL sufficientExample, cont.SELECT make, price, colorFROM cars_dot_comWHERE make = ‘CORVETTE’AND PRICE = 50000;SLIVER50,000CORVETTEBLACK50,000CORVETTERED50,000CORVETTESLIVER45,000JEEPBLACK35,000JEEPRED90,000CADILLAC SILVER50,000CORVETTEBLACK50,000CORVETTERED50,000CORVETTEColorPriceMake•Three records selected.•Correlated columns•Additional predicate has no effect•Single column statistics are NOT sufficient•Must use ‘=‘and not < or >148 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 correctly149 Manage New Statistics –FYI OnlyAfter normal Statistics Creationselect column_name, num_distinct, histogramfrom user_tab_col_statistics where table_name = 'CUSTOMERS‘;COLUMN_NAME NUM_DISTINCT HISTOGRAM---------------------------------------------------------CUST_VALID 2 NONECOUNTRY_ID 19 FREQUENCYCUST_STATE_PROVINCE 145 NONECUST_CITY_ID 620 HEIGHT BALANCEDCUST_CITY 620 NONECUST_LAST_NAME 908 NONECUST_FIRST_NAME 1300 NONECUST_ID 55500 NONE...23 rows selected.150 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#4exec dbms_stats.gather_table_stats('SH','CUSTOMERS', method_opt => 'for all columns size skewonly');PL/SQL procedure successfully completed.151 Manage New Statistics –FYI OnlyNow there are Extended Statisticsselect column_name, num_distinct, histogramfrom user_tab_col_statistics where table_name = 'CUSTOMERS';COLUMN_NAME NUM_DISTINCT HISTOGRAM---------------------------------------------------------SYS_STUJGVLRVH5USVDU$XNV4_IR#4 145 FREQUENCYCUST_VALID 2 FREQUENCYCOUNTRY_ID 19 FREQUENCYCUST_STATE_PROVINCE 145 FREQUENCYCUST_CITY_ID 620 HEIGHT BALANCEDCUST_CITY 620 HEIGHT BALANCEDCUST_LAST_NAME 908 HEIGHT BALANCEDCUST_FIRST_NAME 1300 HEIGHT BALANCEDCUST_ID 55500 HEIGHT BALANCED...24 rows selected.152 Manage New Statistics –FYI OnlyDROP Extended Statisticsexec dbms_stats.drop_extended_stats('SH', 'CUSTOMERS', '(country_id, cust_state_province)');PL/SQL procedure successfully completed.select column_name, num_distinct, histogramfrom user_tab_col_statistics where table_name = 'CUSTOMERS‘;COLUMN_NAME NUM_DISTINCT HISTOGRAM---------------------------------------------------------CUST_VALID 2 NONECOUNTRY_ID 19 FREQUENCYCUST_STATE_PROVINCE 145 NONECUST_CITY_ID 620 HEIGHT BALANCEDCUST_CITY 620 NONECUST_LAST_NAME 908 NONECUST_FIRST_NAME 1300 NONECUST_ID 55500 NONE...23 rows selected.153 Adaptive Cursor Sharing •The optimizer peeks at user-defined bind valuesduring 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).154 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 peekingyou can set: alter system set "_OPTIM_PEEK_USER_BINDS"=FALSE;Note:When running tkprof "explain=username/password" argument shouldNOT be used. That will cause tkprof to issue an explain plan whose output coulddiffer from the execution plan info inside the raw 10046/sql_trace file.Consider a Telephone Company…OPERATOR7782CLARKOPERATOR7788SCOTTPRESIDENT8739KINGOPERATOR7521WARDOPERATOR7499ALLENOPERATOR6973SMITHJobEmpnoEnameSELECT Ename, Empno, JobFROM EmpWHERE Job = :B1Value 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 searchOPERATOR7782CLARKOPERATOR7788SCOTTOPERATOR7521WARDOPERATOR7499ALLENOPERATOR6973SMITHJobEmpnoEname156 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 range157 Bind Peeking Cursor Sharing (cs) Statisticsselect sql_id, peeked, executions, rows_processed, cpu_timefrom v$sql_cs_statistics;(using the peeked value on the 2nd+ execution)SQL_ID PEXECUTIONSROWS_PROCESSED CPU_TIME------------------------------------------------5wfj3qs71nd7m Y31 02rad83pp613m1 Y 3 3 0dr78c03uv97bp N 1 3 0dr78c03uv97bp N 1 3 0dr78c03uv97bp Y 1 3 09qv6tq9ag5b80 Y 3 3 0a2k4qkh681fzx Y 3 2 0413zr99jf9h72 N 1 1 0413zr99jf9h72 N 1 1 0413zr99jf9h72 Y 1 1 0fd69nfzww1mhm Y 6 0 0158 Bind Peeking –V$SQLselect sql_id, executions, is_bind_sensitive, is_bind_awarefrom v$sql;SQL_ID EXECUTIONS I I-------------------------9ugwm6xmvw06u 11 Y Nbdfrydpbzw07g 11 Y N57pfs5p8xc07w 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.159 Grid Control –10gR2; Many more Options!Security Enhancements161 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 minimum162 Security EnhancementsAUDIT_TRAIL=DB (default)•Audit Trail is ON by default(was off in 10g), •AUDIT_TRAIL=DBis 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% coston TPCC benchmark.163 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 cpuqueries –Careful!•Generate System Stats on migrate: Tune /11g Parameters164 Oracle Audit VaultOracle Database VaultDB Security Evaluation #19Transparent Data Encryption EM Configuration ScanningFine Grained Auditing (9i)Secure application rolesClient Identifier /Identity propagationOracle Label Security (2000)Proxy authenticationEnterprise User SecurityGlobal rolesVirtual Private Database (8i)Database Encryption APIStrong authentication (PKI, Kerberos, RADIUS)Native Network Encryption (Oracle7) Database Auditing Government customerOracle Database SecurityBuilt over MANY years... 20071977165 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 loginsand 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’sunattended using DBUA silent mode166 The Future: 8 ExabytesLook what fits in one 10g Database!2K –A typewritten page5M –The complete works of Shakespeare10M –One minute of high fidelity sound2T –Information generated on YouTube in one day10T –530,000,000 miles of bookshelves at the Library of Congress20P –All hard-disk drives in 1995 (or your database in 2010)700P –Data of 700,000 companies with Revenues less than $200M1E –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)167 8 Exabytes:Look what fits in one 10g Database!•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.)Compelling Technology Statistics!0510152025303540RadioTVCableInternetWirelessYears to Reach 50MUsers169 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.170 V$ Views over the yearsVersionV$ ViewsX$ Tables 6 23 ? (35) 7 72 1268.01322008.11852719.02273529.225939410.1.0.2340 (+31%)543 (+38%)10.2.0.139661311.1.0.6 484 (+22%)798 (+30%)171 Oracle is never caught from behindOracle’s 30thAnniversary in 2007•Great Sales/Marketing•Great Database•Applications Leader•BI Leader•Already in the lead•Game Over!172 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, 2001173 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 Sizes174 For More Information•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”-Churchill175 Know the DBA Mind!176 Before they were DBAs, they were Engineers177 This is How DBA View Themselves! “You must BE the change you want to see in the world.”--Mahatma Gandhi179 www.rolta.comwww.tusc.comrich@tusc.com“Success usually comes to those that are too busy to be looking for it.”-Henry David Thoreau 180 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 InformationRich Niemiec: rich@tusc.com181 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, LexDe Haan, Elaine DeMeo and Jim Gray.182 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 Reseller183 Rolta TUSC Corporate Profile•“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 Partner184 Rolta TUSC –YourPartner ….Accomplished in Oracle!1842008 Oracle Partner of the Year (Titan)Prior Winner 2002, 2004, 2007185 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