Oracle Corner

What I like about Oracle 10g is that it has finally made advances in self-diagnosis and self-monitoring. Oracle has been promissing a self-tuning database for ages, and this is the first version that steps considerably closer to this goal.

Interesting Oracle 10g features:

***) Cloning Oracle software installations
You can use Oracle Universal Installer to clone the whole Oracle software installation to another servers.

• ./runInstaller -clone ORACLE_HOME="" ORACLE_HOME_NAME="" [-responseFile ]

***) Data Pump
With Data Pump, jobs can be paused, monitored, resumed, restarted or left to run in the background. R/W external tables (they used to be r/o only) can be created with the ORACLE_DATAPUMP access driver; this is mostly convenient for very large tables.
Unfortunately, data pump files are not compatible with imp/exp dump files, which would be nice to have.

***) Advisors
There are plenty of new advisors in 10g, some of them (like SQL Tuning Advisor) are still not giving much useful information, but this is the beginning in fulfilling a dream of self-tuned database

• SQL Tuning Advisor
• Input: ADDM's SQLs, top SQL (AWR and SGA) and SQL sets
• Output: stale/missing statistics, Access Patchs, Restructured SQLs
• SQL Access Advisor
• Input: the same as for tuning advisor + filtered SQLs, hypothetical workload and tables full of your own SQL
• Output: recommendations for new indexes, new MV/MVL
• PGA Advisor
• Defined with SGA_TARGET parameter
• Segment Advisor
• Input: ADDM
• Output: Growth trends and shrink recommendations
• Undo Advisor
• Oracle auto-tunes UNDO_RETENTION
• Output: longest running query, average undo Kb/minute, maximum undo Kb/min, how big UNDI tablespace must be to support x hours of undo retention based on historical data
  

***) Easy installation
More information in one of my other notes on this BLOG.

***) New scheduling system
Finally a more sophisticated scheduling system: Job Scheduler. In short: a job specifies what is going to be executed (program) and when (schedule). Both programs and schedules can be reused by jobs. Jobs can be exported/imported from one system to another and they can be managed in classes (groups). Even better, classes can be prioritised, so that high priority jobs will get run first. The DBA can create a Window (a time period) and assign a job class to a resource group.

***) Lots of small features
Recycle bin, SYSAUX being used instead of SYSTEM tablespace, new global default tablespace, shrinking objects and resetting HWM at the same time, bigfile data files (up to 8 exabytes), ...
Flashback Transaction Query feature. This can be used instead of Log Miner and uses undo_retention parameter.

***) Self-Monitoring
MMON checks tablespaces' free space every 10 minutes and logs data into AWR. The DBA can define critical and warning thresholds and when breached, a dbsm_server_alert can alert the DBA (by e-mail or shown on the console's home).

***) Backup/recovery
The Block Tracking feature tracks all changed data file blocks, and "indexes" those changed blocks into the binary file. RMAN does not need to read the whole data file to perform an incremental backup, only those changed blocks.
RMAN can compress backups. I couldn't find more details about what type of compression is done, so I can only assume that UTL_COMPRESS is used for the compression.
Flashback database is great for speedy recovery in time.

***) Security
10g didn't introduce many new security features, probably because 9i was hailed as "unbreakable" and had quite a good security.
Auditing can be extended to add SQL statements and bind variables for audited operations
VPD policies can fire now on specific columns

Everybody likes something for free, especially if it is a good product from a big, proven company. Oracle Express Edition is a free database that companies can seriously consider for small-scale databases. The Express Edition has some limits: the server must have only 1 CPU server, up to 1Gb of memory, maximum database size of 4Gb, and only one instance per server.

I see this as Oracle's attempt to compete with some of the open-source databases like PostgreSQL and MySQL, therefore availability only for 32-bit Linux and Windows.

Overall, a good move from Oracle!

The Oracle 10g installation is much more sleek than the installations in previous Oracle versions. For a start, there is only 1 CD required to install the basic Oracle software. It takes about 15 minutes to install Oracle Standard Edition and a bit more than 1Gb in disk space. Another CD, called "a companion CD" is then used to add additional features like HTML DB with HTTP server, JDBC drivers, SQLJ, Examples, JPublisher, Oracle Text, etc.

I also prefer adding SQL Developer, which is an excellent free tool for general data browsing and running SQL.

After the installation you will notice couple of new services:

• OracleOraDB10giSQL*Plus for web-based SQL*Plus
  
• Manual iSQL*Plus is with
  
• >isqlplusctl start
• iSQL*Plus can be accessed at http://server:5560/isqlplus
  
• OraceJobScheduler10g service is disabled by default. If you want to enable it, run it with a low-privleged OS account to secure the external jobs
  
• OracleCSService, a clustering server, which can be safely stopped if you don't plan to use clustering
• OracleDBConsole is an Enterprize Manager console
  

I personally like installing the software first, checking the installation and then creating a new database. If you use DBCA to create a new database, the whole process takes less than half an hour and a basic database takes 1Gb of space.
Oracle 10g has a list of instance parameters that you can set up and off you go. The following list of initial instance parameters will give you a reasonable database to start with:

• Memory parameters:
• sga_target (the value depends on memory availability and application reqs)
• pga_aggregate_target (the same as above)
• sga_max_size (sga_target+100M, if you can afford it)
  
• processes (as required)
  
• Management parameters:
• auti_Trail=db_extended (get the SQL statements and bind variables in auditing)
• statistics_level=TYPICAL (Enable ADDM and automatic statistics)
• resumable_timeout=3600 (1 hour wait for DBA to resume imports)
• General bdump, udump, cdump, control files, undo
  
• timed_os_statistics. This is a bit expensive parameter, so you can switch it off if you can't afford any extra CPU consumption.
  
• Recovery parameters:
• db_flashback_retention_target=1440
  
• don't forget to mount and 'ALTER DATABASE FLASHBACK ON;'
  
• db_recovery_file_dest and db_recovery_file_dest_size
  
• archive_lag_target=1800 (force a switch every 30 minutes)
• log_archive_dest_2= 'location=/mounted_remote_server_dir optional reopen=300'
  
• The above 2 parameters give the cheapest DR strategy
  

Make sure your database has the default tablespace defined and you can also do some of the following:

• Enable row movement for relevant tables to be able to shrink space
• Set up alerts' e-mailing with DBMS_SERVER_ALERT
• Switch your older scripts from using imp/exp to Data Pump
  

To manually start your EM console, type

• > emctl start dbconsole
• The default URL is http://server:1158/em
• Don't forget that all EM packs have to be paid for, but you can use the EM console for basic work.

Touch count algorithm was introduced in Oracle 8i, to resolve buffer cache disturbances by large scans. This algorithm makes the blocks earn their right to be at and to stay at the MRU end.

New concept of "touch-count" is introduced, where each buffer has a counter. When the buffer is touched, the counter goes up. Oracle tries to increase the counter only once in every 3 seconds

• New buffer is placed at the middle of the LRU list
  
• _db_percent_hot_default % (50 by default)
  
• When a touch-count>2 (_db_aging_hot_criteria), it is moved to the MRU end
• Server process can move it while looking for a free buffer or
• DBWR can move it while looking for the dirty blocks
• Once moved to the MRU end, the touch-count is reset back to zero
• If the buffer doesn't get used again, it will be moved to the LRU end
• When this happens, one buffer in the middle will switch from the cold to hot zone
• Touch-count for this buffer will be set to 1

B-TREEs

• Oracle indexes are built using B*-trees
• They are always balanced
• Update is done through delete, then insert
• Each leaf node has a pointer to the next leaf node
• Each leaf node contains indexed value and corresponding ROWIDs
• Deleted blocks are re-used for inserts or eventually cleaned out and put back to the free list
  

CLUSTERING FACTOR

• CF show you how many table reads have to be done for a full index scan
• The worst possible scenario would be #of rows
• The best scenario would be #of table blocks
• Rebuilding a table to be sorted like a index is the solution (if possible)
• Index pre-fetching is helpfull (_index_prefetch_factor and _db_file_noncontig_mblock_read_count hidden parameters)
• Oracle batches single-block reads
  

INDEX SCANS

• Index full scan will scan all leaf blocks
• Sequential read is used
  
• Corresponding wait is "db file sequential read", single block waits (p3=1)
  
• Index fast full scan will skip the B-tree and just scan the index segment
• Can use multiblock reads
• If FFS is slow, it is worth considering an index rebuild

INDEX FRAGMENTATION

• Happens for sparse deletes
• If PCTFREE is too high
• Sometimes for indexes with random inserts
  
• Index rebuilds give the best performance gains for FFS

REGULAR DBA CHECKS

• Indexes larger than tables
• De-fragmented indexes (some of them might be worth rebuilding)
• Indexes with low selectivity (selectivity: Distinct values#/all values#)
  
• Foreign keys without indexes
• Tables with no indexes
• Overlapping indexes
• ANALYZE INDEX VALIDATE STRUCTURE;
• Check INDEX_STATS view
  

PRO-ACTIVE ACTIONS:

• Monitoring indexes.
• ALTER INDEX MONITORING USAGE ;
• Check v$object_usage afterwards