Random notes on database features, code, practice, optimization and techniques.
As said before, I decided to take the (beta) exam "
Oracle 10g upgrade for OCP 8i candidates". Though, I am both 8i and 9i certified - I chose to upgrade from 8i because -
(a) the exam was cheaper ($50 - compared to a $125 for upgrade from 9i)
(b) it's a beta exam (still!) and therefore, much more questions are expected. Since the "cram guides" are not out on market - it will be a bit of challenge.
This is a nice, formal way of learning. I am in a regular job - so could not really assign more than 3-4 days to learn the topics I am expected to cover.
The exam is on Sunday and on this nice, sunny Friday afternoon I feel well-prepared. My preparation strategy was based on the fact that I actually wanted to learn the new product rather than just 'mug' some cramsheets and get a paper certificate.
So, I chose to follow the *excellent* hands-on courses by Oracle University. My present employer is an Oracle partner - so all these courses were free to me. They not only cover the real meat, but I could see some excellent 'demo' of the new features. I don't know what software they were using to create those excellent self-contained demos - but it rocked.
The courses I covered were - you can also search for them on http://ilearning.oracle.com -
(I've added my study notes alongwith) ..
1) Oracle 10g - The Self-managing DatabaseA great introduction to Oracle 10g by Anuj Goel. Talks about the design decisions behind 10g (minimize the maintenance, administration, installation and troubleshooting time) and how they have actually been tackled.
Without going through the live presentation, you can also download the powerpoint presentation and go through it.
2) Oracle Database 10g Administration in 2 DaysVery basic material. Nothing really 'conceptual' but on 'how to do using different Oracle given tools'. Don't spend more than 15 mins here.
This is totally SQL free zone - as all administration and management tasks have been illustrated with Enterprise Manager
The 'Backup and Recovery' section talks about 'Oracle suggested Backup Strategy' - which is
"The Oracle-suggested backup strategy is based on creating an image copy of your database which is rolled forward using incrementally updated backups. Oracle Enterprise Manager schedules RMAN backups jobs for you to run during the overnight hours.
For each datafile, the strategy calls for backups to be made as follows:
* At the beginning of day 1 of the strategy (the time the first scheduled job actually runs), an incremental level 0 datafile copy backup. It contains the datafile contents at the beginning of day
1. In a restore-and-recovery scenario, the redo logs from day 1 can be used to recover to any point during day 1.
* At the beginning of day 2, an incremental level 1 backup is created, containing the blocks changed during day 1. In a restore-and-recovery scenario, this incremental level 1 can be applied to quickly recover the rolled-forward level 0 backup to the beginning of day 2, and redo logs can be used to recover to any point during day 2.
* At the beginning of each day n for days 3 and later, the level 1 backup from the beginning of day n-1 is applied to the level 0 backup. This brings the datafile copy to its state at the beginning of day n-1. Then, a new level 1 is created, containing the blocks changed during day n-1. In a restore-and-recovery scenario, this incremental level 1 can be applied to quickly recover a restored backup to the beginning of day n, and redo logs can be used to recover the database to any point during day n.
The datafile copies used in the Oracle-suggested backup strategy are tagged with the tag ORA$OEM_LEVEL_0. The level 1 incremental backups for use in this strategy are created for use with datafile copies that are so labeled. You can safely implement other backup strategies without concern for interference from the backups for the Oracle suggested strategy."
3)
Oracle Database 10G: Manageability OverviewMostly the framework for this 'self-managing' database. Same presentation as #1 - but much more detailed and has an informative question-answer session.
Download the presentation (click on "Reference") from link #1 above. This one doesn't work.
4) Oracle Database 10g: Self-Managing Database - Space ManagementDeals with
(a) Proactive Tablespace Monitoring every 10 mins done by new background process MMON - supposed to be in-memory check and therefore fast (Critical Alert : 97%, Warning Alert : 85%). Threshold value - can be emailed or paged. Works only on locally managed tablespace though. DBMS_SERVER_ALERTS is the PL/SQL API.
(b) Segment Management : Has a very cool demo of new 'online Shrink' operation. Remember,
even though it's 'online' - there're both shared (when compacting unused free space) as well as exclusive locks (when reclaiming the freed space) obtained on the segment. It will block a DML on the segment that's going on in parallel while shrinking is on.
Takes place through, delete-insert mechanism by Oracle and therefore, chained rows may or may not be impacted. Though it's a DML operation, triggers are NOT fired by Oracle.
Earlier, High water mark can only be brought down by rebuilding (or, moving) the table. The tables need to have 'enable row movement' on. Indexes are usable after a shrink (unlike rebuild / move).
Very useful feature if you remember 'online' doesn't mean 'can do anytime'.
However, following structures cannot be shrunk -
cluster tables, tables with LONG, LOB, tables with function-based indexes.
Temporary and UNDO segments, since handles different way, cannot of course be shrunk.
Syntax -
ALTER TABLE
SHRINK SPACE [COMPACT] [CASCADE];
If the COMPACT clause is not specified, Oracle resets the HWM by reclaiming the freed up space. If COMPACT is specified, reclaimable space is reclaimed, but the HWM is not set-back (and thus, no exclusive lock on the segment is obtained).
EM Advisor Central -> Segment Advisor can give an idea of which segments are ripe for SHRINK operation.
(c) Undo Management : Now even the undo_retention is automatic and default. Only, if you set statistics_level to typical (collects 30 statistics on a frequent basis and dumps it on AWR).
AUM = Automatic Undo Management.
Init params like MAX_ROLLBACK_SEGMENTS, UNDO_SUPPRESS_ERRORS (always TRUE), ROW_LOCKING, SERIALIZABLE, and TRANSACTION_AUDITING (always TRUE) no longer work with 10g.
Resumable Space Allocation can be altered in session and system level.
(d) Redo Logfile Advisor : Another advisor in 10g advisor network. Lowly useful, but not a killer feature.
If FAST_START_MTTR_TARGET is set the smallest optimal size of REDO file is calculated.
OPTIMAL_LOGFILE_SIZE (in MB) is a new column in V$INSTANCE_RECOVERY.
5)Oracle Database 10g: Self-Managing Database - Integrated Storage Services
Basically 'Space Management -2'. Talks about little but important things. Like, the obsolescense of MAXTRANS parameter. BIGFILE tablespace. I liked the easy description of big-endian and small-endian architecture.
SYSAUX - Now SYSAUX tablespace stores everything that should be installed with database ('auxilliary database metadata) but does not belong to CORE kernel (i.e., SYS objects). It has to be locally managed, ASSM, Extent Management Local. The data it contains - Text, UltraSearch, InterMedia, OLAP, Scheduler, AWR, XMLDB, Workspace Manager, Logminer, Statspack etc. Query V$SYSAUX_OCCUPANTS to know about it.
Just like SYSTEM tablespace, SYSAUX tablespace cannot be dropped or renamed.
TABLESPACE -
(a) with 10g, a 'temporary tablespace group' - basically a bunch of TEMP tablespaces - can be configured and be defaulted to a user's temporary tablespace. The benefit - user can use different temp tablespaces in different sessions or, a single SQL can use multiple temp tablespaces for sorting / hashing.
A temporary tablespace group - just like a single temporary tablespace - can be made the 'default temporary tablespace' of the database. Then none of the tablespaces in that temp. tablespace group can be dropped.
New DD view - DBA_TABLESPACE_GROUPS.
(b) Now tablespaces can be renamed. Could be useful in transporting tablespaces between two databases. Remember, RENAME does not rename the data files.
(c) A simple but very useful feature - Like 'default temp tablespace' -a 'default permanent tablespace' can be made in database level. Thus, any unqualified object does not end up being in SYSTEM anymore. Can be suggested during 'create database' or later.
BIGFILE Tablespace -
A BFT has a really big datafile. For all practical purpose, the concept of datafile can now be forgotten and whole architecture can be looked at a logical level (i.e., tablespace stores table). Only bitmaps can contain the huge extent information for BFT - thus BFT is possible only with Locally managed tablespace. Oracle can now store upto 8 Exa Bytes (8 Million Tera Bytes) of data thanks to BFT.
Max data in Database = D *F * B (D -> max. data files, F -> Max blocks per file, B-> max block size).
1 PB (petabyte) = 1024 TB (terabyte); 1EB (exabyte) = 1024 PB = 1,048,576 TB = 2**60 bytes!
"You should avoid creating a BFT on a system that does not support striping, because of negative implications for parallel query execution and RMAN backup parallelization."
There's something interesting on the 'it's useless to think how many extents a segment has' -
"You should try to avoid having more than 100,000 extents (based on a 2 K block size). When more extents are created, there may be a performance penalty under concurrency and during DDL operations that involve high space management activity. However, the above number of extents is not an absolute limit and this number varies proportionally higher to the block size used. For example, with an 8 K block size, you can have 400,000 extents; with a 16 K block size, you can have 800,000 extents. This calculation is based on the number of bitmap blocks (and their size) that will be required to represent the file."
Exotic Info - ROWID for normal (small file) tablespaces is of following format -
OOOOOOFFFBBBBBBRRR
- Where OOOOOO -> segment number
FFF -> relative file number
BBBBBB-> data block address (relative to the tablespace, not datafile. Thus two rows of the same table can have same data block address, and reside on two separate data files).
RRR -> slot # inside a block
Since FFF becomes irrelevant with BIGFILE tablespace (relative file number is always 1024!), the rowid FFF part is used to denote the data block address as well.
Thus, DBMS_ROWID now takes a new input parameter called TS_TYPE_IN (BIGFILE or SMALLFILE).
Transportable Tablespace - 10g allows tablespaces to be transported across different platforms (not doable in 9i).
Steps ->
1. Make tablespace READ ONLY
2. Extract METADATA using data pump (expdp)
3. If the target has different 'endianness' - convert byte ordering in files using RMAN
4. Copy the datafiles in target, Load metadata using data pump (impdp)
5. Make tablespace READ WRITE
To determine endianness -
select endian_format
from
v$transportable_format a, v$database b
where a.platform_name = b.platform_name;
ASM -
Where Oracle treats the entire disk subsystem as a single RAW device. No more partitioning, I/O tuning, data movement - Oracle does it for you. Extension of the earlier coined SAME (Stripe and Mirror Everything) concept. No more need to buy "volume manager" or "cluster manager" software.
A separate Oracle ASM instance (database) is started, when used. Both this and the working database have access to a common set of disks (DISK GROUPS). ASM instance has two new background processes - (a) RBAL (rebalancing DISK GROUPS) and (b) ARBn (does data extent movements).
Databases using ASM also has two new background processes namely, (a) RBAL (global opens of the disks in DISK GROUPS) and (b) ASMB (talks to ASM instance). ASM instances can be clustered as well.
6) Oracle Database 10g: Self-Managing Database - General Enhancements
(a) SYS statistics should be collected (Oracle recommends so). GATHER_DATABASE_STATS can collect stats on dictionary tables and so is the new GATHER_DICTIONARY_STATS.
(b) FIXED tables should also be analyzed once in the instance lifetime using GATHER_FIXED_OBJECTS_STATS. However, fixed tables do not have I/O cost since they reside on memory and thus only CPU costing is used, if at all, by CBO.
(c) Table MONITORING no longer relevant since the monitoring is done by STATISTICS_LEVEL anyways.
(d) V$FAST_START_TRANSACTIONS keep record of recovering and recovered transactions.
(e) MAXTRANS is gone. No longer works now. Oracle does MAXTRANS management itself.
(f) MTS_ parameters are now SHARED_ parameters. Just setting SHARED_SERVERS parameter is sufficient for multi-threading.
(g) TRCSESS command line tool collates all trace files from a session. You still have to perform TKPROF on it though.
(h) ALTER SYSTEM FLUSH BUFFER_CACHE;
7) Oracle Database 10g: Self-Managing Database - Common Infrastructure
Talks about the whole ADDM-AWR-Server Alert paradigm. Good to know the framework of the new features. I am not yet done testing the claims.
AWR -
Manageability infrastructure -
- Application and SQL
- Storage
- Space
- System Resource
- Backup & Recovery
These tasks have been automated with new 10g infrastructure where automated tasks, server alerts and advisory framework work upon AWR (Automatic Workload Repository).
Not all 'base statistics' (those on memory) are converted into AWR (on disk). The ones that go to AWR are -
- Access and usage stats for objects
- time model stats (V$SYS_TIME_MODEL)
- V$SESSTAT and V$SYSSTAT
- Optimizer Stats (V$SQL)
- ASH (active session history)
ASH - secondly data sampled from v$session. MMON flushes this data to AWR every hour and MMNL whenever the buffer is full. ASH == 2MB per CPU memory and max upto 5% of shared_pool_size.
Base Statistics = Raw Data Collected (total physical reads since startup)
Metrics = secondary stats derived from above (avg. physical reads in an hour) and thus, used by management framework.
10g supports metrics for system, session, files and wait-events stats.
AWR == Base Stats, SQL Stats, Metrics, ASH, Advisor Results in DBA_HIST_* tables. DBMS_WORKLOAD_REPOSITORY is the PL/SQL API to control AWR data and baseline.
$ORACLE_HOME/rdbms/admin/awrrpt creates a statspack type (much more detailed) report.
Database Feature Usage Stats - DBA_FEATURE_USAGE_STATISTICS & DBA_HIGH_WATER_MARK_STATISTICS
Server Generated Alerts --
V$ALERT_TYPES - DBA_THRESHOLDS
Threshold Alerts = Stateful Alerts (e.g., tablespace 97% full. Appear in DBA_OUTSTANDING_ALERTS, when cleared DBA_ALERT_HISTORY)
Stateless (Binary. Like, snapshot_too_old - goes directly to the history table)
V$SYSMETRIC & V$SYSMETRIC_HISTORY keep metrics for 1 hr.
PL/SQL API - DBMS_SERVER_ALERT
Automated Jobs -
By default, GATHER_STATS_JOB.
DBMS_STATS.LOCK_TABLE/SCHEMA_STATS locks a table/schema from getting analyzed.
DBA_OPTSTAT_OPERATIONS - history of DBMS_STATS operations.
A very very useful feature - when index is created, the stats are auto-generated.
Advisory Framework -
Advisors -
- ADDM
- SQL tuning
- SQL access
- Memory (PGA, SGA, Buffer Cache, Library Cache)
- Segment
- Undo
Advisor attributes can be LIMITED or COMPREHENSIVE. Can be specified a max. TIME LIMIT. Can be interruptible. Can take user directives.
PL/SQL api - DBMS_ADVISOR.
DBA_ADVISOR_ tables.
8) Oracle Database 10g: Self-Managing Database - Performance Management
The new SGA advisor, Automated SGA, Redo advisor.
Optimizer can now analyze a statement for, say 30 mins and come out with the best possible plan and store it internally for all future executions (only if you want it!). Thus tuning SQL in packaged application becomes easier (no need to touch code anymore).
Some issues not captured by Statspack, but by ADDM -
- excessive logon / logoff
- memory undersizing
- hot blocks and objects
- RAC service issues
- Locks and ITL contention
- Checkpointing issues
- PL/SQL, Java time
Has an *excellent* demo viewlet on Proactive Monitoring -> ADDM recommendations. Really helpful to understand the new philosophy on ohhlistic performance management.
For Reactive Tuning, EM provides
- CPU & Wait classes (reports holistic CPU usage including that by extra-Oracle stuff)
- Top SQL Statements
- Top sessions hurting
Just set SGA_TARGET for automated SGA sizing. Setting it to 0 disables automatic SGA sizing.
It can be increased up to SGA_MAX_SIZE. New background process MMAN serves as SGA memory broker.
SQL Tuning Advisor -
Calls the CBO in a 'special internal mode' to make it do four analyses -
- stale / missing stats analysis
- Plan Tuning Analysis (SQL Profile) - stored in data dictionary and thus does not need a hard code change in packaged apps
- Access Path Analysis
- Restructure SQL
Things done in SQL Profile which is not doable when CBO 'normally' analyzes a SQL (usually under fraction of a second) to find the access plan -
- runs partial execution
- dynamic sampling of statistics
- advanced predicate selectivity - correlation between columns, join skews and complex predicates (like function)
PL/SQL API for SQL Advisor: DBMS_SQLTUNE
9) Oracle Database 10g: Maximize Performance - General Enhancements
Talks of the general performance enhancements (as opposed to the new performance management and infrastructure in the previous two lessons).
A) Partitioning Enhancements - - List-partitioned table operations are permissible on list-partitioned IOT
- Index status = USABLE throughout partition management (DROP, TRUNCATE..)
- Bitmap index on list-partitioned IOT
- LOB columns supported on partitioned IOT
- Partitioned table can be DDLd with 'UPDATE INDEXES' so Oracle rebuilds all indexe segments
- SKIP_UNUSABLE_INDEXES (true) can be set in session or stsrem level
- NEW hash-partitioned global indexes are introduced (hash funcion = f(partitioning key, no. of partitions)
- Hash-partitioned index saves from right-hand contention (during insert of incremental sequenced columns). The contention is then spread across different partitions from the 'right hand' partition (as happens in list-partitioned indexes)
- According to Oracle -For queries involving equality or in-list predicates, performance of hash-partitioned indexes and non-partitioned indexes are comparable
- ALTER INDEX ADD PARTITION..COALESCE PARTITION
- Hash-partitioned indexes must be built partition wise (RENAME, SPLIT, MERGE are unsupported)
- Hash-partitioned indexes work better with parallel queries