Oracle Database 10g ? First Experience

| 4 March 2004 − DOAG SIG Database − Hamburg | Oracle Database 10g − First Experiences Christian Antognini christian.antognini@trivadis.com © © Copyright Trivadis AG 1 Oracle 10g Database by Trivadis Where do the experience come from? – Trivadis participated to the Oracle Database 10g Beta program Rollout – April 2003: Beta 1 available as hosted resource by Oracle – Mai 2003: Beta 1 available for internal installations – October 2003: Beta 2 available for internal installations – February 2003: 10g production available – April 2004: 10g TechnoCircle goes live - 27-28 April 2004 Stuttgart - 6-7 Mai 2004 Frankfurt - 25-26 Mai 2004 München - For more information see www.trivadis.com © © Copyright Trivadis AG 2 Agenda Automatic SGA Management Automatic Statistics Gathering End-to-End Tracing Data Pump Conclusion © © Copyright Trivadis AG 3 Automatic SGA Management − Why? To configure the size of the different SGA components we are used to take the following steps 1. Initial setting (experience, rule of thumb) 2. Let the instance run some time… 3. Check SGA ratios and statistics, modify the configuration if necessary 4. Go back to point 2 from time to time Since Oracle has the necessary information to do the same job, why we should do it? © © Copyright Trivadis AG 4 Dynamic SGA Enhancements In Oracle9i not all SGA pools are dynamically reconfigurable, in fact to change the following parameters a database restart is necessary – LARGE_POOL_SIZE – JAVA_POOL_SIZE These limitations no more exists in Oracle 10g A new pool, named Streams pool, has been introduced – STREAMS_POOL_SIZE – It is dynamic as well – If the size of the Streams pool is zero, then the memory used by Streams is allocated from the shared pool © © Copyright Trivadis AG 5 V$SGAINFO This new dynamic performance view gives the most important information about the SGA components SQL> SELECT * FROM v$sgainfo; NAME BYTES RESIZEABLE -------------------------------- ---------- ---------- Fixed SGA Size 778240 No Redo Buffers 262144 No Buffer Cache Size 109051904 Yes Shared Pool Size 50331648 Yes Large Pool Size 4194304 Yes Java Pool Size 4194304 Yes Streams Pool Size 0 Yes Granule Size 4194304 No Maximum SGA Size 268435456 No Startup overhead in Shared Pool 25165824 No Free SGA Memory Available 96468992 © © Copyright Trivadis AG 6 Automatic SGA Management Since Oracle 10g the DBA can delegate to the database the tuning of the pools’ size, i.e. Oracle dynamically resize the pools according to the system workload This feature is activated if – SGA_TARGET is greater than 0 – STATISTICS_LEVEL is set to TYPICAL (default) or ALL The new background process MMAN is responsible for the resizes © © Copyright Trivadis AG 7 Automatic SGA Management The following pools are not automatically tuned – Keep/Recycle buffer caches – Buffer caches for non-standard block sizes – Streams pool The DBA is responsible to specify and tune them The amount of memory needed for these pools reduces the memory available for automatic tuning fixed SGA SGA_MAX_SIZE shared pool redo log buffer java pool free SGA_TARGET non def. buffer caches large pool space Static def. buffer cache streams pool Automatically managed © © Copyright Trivadis AG 8 Automatic SGA Management To reuse the tuned values across database restart Oracle stores them in the SPFILE The “older” parameters are set to 0 if no value has been specified SQL> SELECT name, value 2 FROM v$parameter 3 WHERE name LIKE '%pool_size' OR name LIKE '%db_cache_size'; NAME VALUE ------------------------------ ------------------------------ __shared_pool_size 50331648 shared_pool_size 0 __large_pool_size 4194304 large_pool_size 0 __java_pool_size 8388608 java_pool_size 0 streams_pool_size 0 __db_cache_size 96468992 db_cache_size 0 © © Copyright Trivadis AG 9 Agenda Automatic SGA Management Automatic Statistics Gathering End-to-End Tracing Data Pump Conclusion © © Copyright Trivadis AG 10 Automatic Statistics Gathering Since the RBO is no longer supported Oracle absolutely needs optimizer statistics to generate correct execution plans For this reason the gathering is completely automatic, i.e. the statistics are automatically gathered for all segments and kept up-to-date The default configuration is good for many installations, but of course it can be changed according to special needs © © Copyright Trivadis AG 11 Automatic Statistics Gathering − Job During the installation a job (based on the new scheduler) named GATHER_STATS_JOB is automatically configured and enabled Its scheduling window is – Monday-Friday: between 22:00 and 06:00 – Saturday-Sunday It calls a program named GATHER_STATS_PROG, which in turn calls without parameters the procedure DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC It can be disabled with the following call dbms_scheduler.disable('GATHER_STATS_JOB') © © Copyright Trivadis AG 12 Automatic Statistics Gathering − Parameters All procedures that gathers optimizer statistics no longer have hardcoded default values Default values are stored in the data dictionary They can be get and set with the DBMS_STATS procedures – GET_PARAM – SET_PARAM PARAMETER DEFAULT VALUE ---------------- ------------------------------ CASCADE DBMS_STATS.AUTO_CASCADE DEGREE NULL ESTIMATE_PERCENT DBMS_STATS.AUTO_SAMPLE_SIZE METHOD_OPT FOR ALL COLUMNS SIZE AUTO NO_INVALIDATE DBMS_STATS.AUTO_INVALIDATE GRANULARITY AUTO AUTOSTATS_TARGET AUTO © © Copyright Trivadis AG 13 Automatic Statistics Gathering − MONITORING In early releases, to know how many modifications were performed on tables, a monitoring feature had to be explicitly enabled at table level These statistics were used by DBMS_STATS to gather statistics only for objects with stale statistics Since Oracle 10g the MONITORING clause is deprecated; the same feature is enabled at database level by setting the parameter STATISTICS_LEVEL at TYPICAL (default) or ALL Also the procedures ALTER_DATABASE_TAB_MONITORING and ALTER_SCHEMA_TAB_MONITORING of the package DBMS_STATS are deprecated © © Copyright Trivadis AG 14 Data Dictionary Statistics Since the RBO is not longer supported the data dictionary should be analyzed as well Two new procedures are available in DBMS_STATS – GATHER_DICTIONARY_STATS - Gathers statistics on all Oracle owned schemas (i.e. SYS, SYSTEM, OUTLN, DBSNMP, XDB, WMSYS, CTXSYS, WKSYS, …) - SYSDBA or both ANALYZE ANY DICTIONARY and ANALYZE ANY system privileges are needed – GATHER_FIXED_OBJECTS_STATS - Gathers statistics on dynamic performance tables (see V$FIXED_TABLE) - SYSDBA or ANALYZE ANY DICTIONARY system privilege is needed A new parameter GATHER_FIXED has been added to – GATHER_SCHEMA_STATS – GATHER_DATABASE_STATS © © Copyright Trivadis AG 15 Locking Statistics If you want to guarantee that statistics for a given table or schema are not changed, you can lock them When statistics on a table are locked, all statistics (i.e. table, column and index statistics) are locked All procedures that modify statistics of an individual table, index or column will raise an error if statistics of the object is locked ORA-20005: object statistics are locked (stattype = ALL) All procedures that operate on multiple tables (e.g.