Oracle 7.3 Tuning Information

Best Parallel load when have a separate database file for each load pro cess
- Use the "file" clause of parallel load
Unrecoverable option on Index Create, 2x Faster
Boost spin_count way up, values up to 100,000 are not unreasonable.
User OS striping instead of Oracle Striping
Veritas, Solstice,...
2 GB database files or smaller can use asynch IO
Solaris 2.5 runs on files > 2 GB
Use temporary option on temp tablespace
Speeds hash joins and sorts
"create tablespace" or change with "alter tablespace"

Create Tablespace from 2 GB database files or smaller
- Allows use of asynchronous IO
- Easier Unix backup
Set initial extent to the size of two db_blocks
- Next extent maybe be 100=D5s of Mbytes
- Don't have more than roughly 10,000 extents per object
- Don't want to overload data dictionary
- Load Processes at 1-2 GB per hour, on CS6400
- Don't want extent allocation more than 2-3 per minute
- Suggest 100M-750M extents, have more extents than CPUs
Create in parallel (equal to number of CPUs):
- Using scripts, create one then background in parallel
- alter tablespace TStbl add datafile "/dev/D3" size 2048M reuse &

Hints: (64k interlace)
- Stripe across highest bandwidth first (across controllers)
- Spread out disk activity, Round Robin
- Scans start at beginning of tablespace, by database file
- Have the initial database block be the same as the interlace, next should be a multiple fo the interlace size.

compatible 7.3.3
- Guarantees full benefit of all new performance features
- Default is 7.0.12, which misses important new features
- Examples
- Direct read for table scans and sorts, cartesian products
- Temporary tablespaces improve speed of sort and hash joins.
- Unrecoverable create option
parallel_max_servers 2*CPUs*#_of_concurrent users
- Most queries use twice the number of query server processes
- To support concurrent users, add more query servers.
- If run out of query servers, it executes sequentially
db_block_buffers #of db_block_size blocks
- Size of SGA, specify number of blocks, 2 GB limit

db_block_size8K or 16K
- Set on database creation
- 2k or 4K for OLTP, 16K for DSS
- Set on database creation
- If new database, use largest block size
db_file_multiblock_read_count 16
- set so can do 1MByte IOs (1Mdb_block_size*db_file_multiblock_read_count)
- Number of database blocks are read with a single operating system READ call
- Current limit is 1M
sort_direct_writes auto
- Bypasses the buffer cache for sorting, much faster

hash_join_enabled true
- Enable hash joins, much faster than sort merge joins
- Especially faster when one input is much smaller
hash_area_size 64M or more
- Hash joins always benefit from more physical memory
- A parallel hash join uses hash_area_size in each process.
hash_multiblock_io_count 8
- Larger I/O buffers mean fewer hash buckets.
- For 100+ GB tables ,have more hash buckets and less efficient I/Os.
- If I/O bound on temp space, consider increasing this value
always_anti_join hash
- NOT IN can only be parallelized with hash anti-join, Need to set this!

sort_area_size 4M
- If too small, causes excessive amount of I/O
- Cumulative sort area is this parameter times number of parallel query servers
- Huge size only best when disk sort is completely avoided
- Over 10M do not perform much better than 1 M, unless a disk sort is avoided altogether.
sort_area_retained_size 4M
- For DSS that often requires sorting, set this to same as sort_area_size

shared_pool_size
- Increase by (6k*CPUs+2)*parallel_max_servers
- Need additional space for a pool of message buffer
- Used by parallel query servers to communicate
partition_view_enabled true
- Enable optimization of =D2union all=D3 views
- Helps optimizer treat view like a real table
- Provide key range partitioning and partition skipping
optimizer_percent_parallel 100/#_of_simult_users
- Bias optimizer toward low parallel response time plans
- Default is 0, which parallelize the best sequential plan
- Value of 100 causes the optimizer to choose a parallel plan
- Intermediate value trades off throughput and response time

If can't allocate shared memory

Make total memory req=D5d less than systems physical memory (SGA+ QS*(sort or hash))

Remember to Use bstat/estat for general info
- Can use /opt/bin/GOODies/dsh to help analyze output
Views into Internal Oracle7 Data Structures
Statistics that can be Queried Periodically
- Gives time varying picture of long-running operation
Many of the Performance Tables Require timed_statistics be set to true, to get more info
timed_statistics can Set Dynamically
- Use alter system command

V$parameter
- name, current & default value of all system parameters
- indicates if value can be dynamically altered
V$filestat
- Sums of IO requests, # of blocks, and service time for each datafile
- Can help diagnose I/O and data distribution problems.
- Monitoring progress of a long running operation, correlate I/O activity to the explain plan output
V$pq_sesstat
- only valid only when executing parallel SQL statements.
- summary statistics about the parallel statements in session
- number of messages exchanged between processes
- actual degree of parallelism used.

V$pq_slave
- CPU time and # of messages for each query server
- Should be little variance among processes in CPU usage and number of messages processed.
- Big variance among CPUs indicates a load imbalance
- Extreme imbalance can indicate that the number of distinct values in a join column is much less than the degree of parallelism
V$pq_sysstat
- Aggregates session statistics from all query server processes
- sums the total query server message traffic
- gives the status of the pool of query servers