eDBA Services - Oracle & MySQL

One obvious method of reducing pings between instances is to isolate the transactions that use a specific data set to a specific server in the RAC cluster.

Some of the good RAC practices, to put it quite frankly, waste disk and memory space to improve data sharing and dispersal characteristics.

An example of an efficient RAC object is one that is used by only a single instance at a time. To achieve this singularity of use, the rows-per-block (RPB) of the data object must be reduced.

- instead of having a 100 gig tablespace to backup, you have 100, 1 gig tablespaces. (each tablespace spends less time in backup mode, reduces the amount of potential extra redo, reduces the amount of manual recovery you need to do if the instance failes during backup). same with restores.

every single admin option you do to a partition applies to a hash partition

Say you do a join on the hash partition key -- we can do parallel partition wise elimination on the join

PARALLEL PROCESSING Ensure the system has enough shared memory. GoldenGate runs as an Oracle process. Each Extract or Replicat process requires upwards of 25-50 MB of system shared memory. This means less memory for the Oracle DBMS, especially the SGA. Use parallel Replicat groups on the target system to reduce latency thru parallelism. Consider parallel Extract groups for tables that are fetch intensive (e.g., those that trigger SQL procedures). Group tables that have R.I. to each other in the same Extract-Replicat pair. Pair each Replicat with its own trail and corresponding Extract process. When using parallel Replicats, configure each one to process a different portion of the overall data.

PASSTHRU PARAMETER Consider using this parameter if there is no filtering, conversion or mapping required and you’re using DATAPUMP. In pass-through mode, the Extract process does not look up table definitions, either from the database or from a data definitions file. Pass-through mode increases the throughput of the data pump, because all of the functionality that looks up object definitions is bypassed. This saves database fetches to improve performance.

INSERTAPPEND A new GoldenGate 10.4 feature. Use for large transactions . Puts records at end of table rather than doing a more costly insert into other areas of table.

In a distribute transaction -- one in which "DATABASE A" calls "DATABASE B", only "DATABASE A" may commit -- the reason -- "database B" has no mechanism for co-ordinating with "database a" on the commit. We need to do a 2 phase commit to ensure that when the transaction commits any work performed on DATABASE A is committed *as well as* any work on database b.

This is the reason will do no allow for ANY node other then the "parent" node of the transaction to issue a commit or rollback in a distributed transaction. The other nodes simply do not have control over the other possible nodes in the transaction.

Here’s a handy little query I made up the other day to quickly digest any of the segment statistics from the AWR and grab the top objects for the cluster, reporting on each instance.

Any time you see heavy concurrency problems during inserts on table data blocks there should always be one first place to look: space management. Since ancient versions of OPS it has been a well-known fact that freelists are the enemy of concurrency.

Cons of ASSM: §         Slow for full-table scans: Several studies have shown that large-table full-table scans (FTS) will run longer with ASSM than standard bitmaps. ASSM FTS tablespaces are consistently slower than freelist FTS operations. This implies that ASSM may not be appropriate for decision support systems and warehouse applications unless partitioning is used with Oracle Parallel Query. §        Slower for high-volume concurrent inserts: Numerous experts have conducted studies that show that tables with high volume bulk loads perform faster with traditional multiple freelists. §         ASSM will influence index clustering: For row ordered tables, ASSM can adversely affect the clustering_factor for indexes. Bitmap freelists are less likely to place adjacent tows on physically adjacent data blocks, and this can lower the clustering_factor and the cost-based optimizer's propensity to favor an index range scan.

Pros of ASSM: §          Varying row sizes: ASSM is better than a static pctused. The bitmaps make ASSM tablespaces better at handling rows with wide variations in row length. §         Reducing buffer busy waits: ASSM will remove buffer busy waits better than using multiple freelists. When a table has multiple freelists, all purges must be parallelized to reload the freelists evenly, and ASSM has no such limitation. §         Great for Real Application Clusters: The bitmap freelists remove the need to define multiple freelists groups for RAC and provide overall improved freelist management over traditional freelists.

Cons of ASSM: §         Slow for full-table scans: Several studies have shown that large-table full-table scans (FTS) will run longer with ASSM than standard bitmaps. ASSM FTS tablespaces are consistently slower than freelist FTS operations. This implies that ASSM may not be appropriate for decision support systems and warehouse applications unless partitioning is used with Oracle Parallel Query. §          Slower for high-volume concurrent inserts: Numerous experts have conducted studies that show that tables with high volume bulk loads perform faster with traditional multiple freelists. §          ASSM will influence index clustering: For row ordered tables, ASSM can adversely affect the clustering_factor for indexes. Bitmap freelists are less likely to place adjacent tows on physically adjacent data blocks, and this can lower the clustering_factor and the cost-based optimizer's propensity to favor an index range scan.

Unfortunately data modeling is not yet covered by the Unified Modeling Language (UML), even though persistence-related issues are clearly an important aspect of object-oriented software project.

The good news is that the Object Management Group (OMG) issued an RFP for an official UML Data Modeling Profile in December 2005.

This page summarizes the data modeling profile for UML Class Diagrams, that I apply in Agile Database Techniques, The Object Primer 3rd Edition, and Refactoring Databases.  First some important definitions:

Statistics are somewhat fallible in that they are seldom 100 percent accurate, but in most cases they do sufficiently indicate what was intended. Be sure you understand what each statistic represents and the units used (there is a big difference between microseconds and centiseconds).

Time-breakdown statistics (Time Model) make it significantly easier to determine the type of operations that are consuming resources in the database.

DB time: Time spent by all user processes in the database (that is,. non-idle wait time + CPU time).

Adaptive Cursor Sharing is a new feature starting from Oracle version 11g release 1. The idea behind is to improve the execution plans for statements with bind variables. CBO has been enhanced to allow multiple execution plans to be used for a single statement with bind variables, without hard parsing the SQL.

There is no special way to configure ACS. It is on by default and of course there is a hidden initialization parameter to turn it off if needed. Key role in the process of decision whether ACS will be used for a particular statement are: Two new columns in V$SQL view (IS_BIND_SENSITIVE and IS_BIND_AWARE) Three new views (V$SQL_CS_HISTOGRAM, V$SQL_CS_SELECTIVITY, V$SQL_CS_STATISTICS)

Each time you execute this statement; Oracle will convert it to an ASCII function and apply a hashing algorithm over it; than it will check if this SQL statement is already present in the SHARED POOL.

If the statement is in the SHARED POOL, Oracle will reuse (soft parse) it together with its execution plan. If the statement is not in the SHARED POOL, Oracle will have to do a hard parse.

Oracle’ CBO can generate more optimized execution plan if the he knows upfront the values of the filter predicated, meaning if the values are literals and not bind variables.

1. Turn on block checking

2. Turn on block tracking when using RMAN backups (if running 10g)

3. Duplex log groups and members and have more than one archive log dest

Enterprise Manager A graphical all-purpose tool that can be used to identify when a spike occurred, drill down to the cause, and examine ADDM recommendations. The benefit of a graphical representation of performance data is visible (pun intended). Data visualizations display any skew directly.

Automatic Database Diagnostic Monitor (ADDM) An expert system that automatically identifies and recommends solutions for many instance-wide performance problems. Best used for longer-duration performance problems (that is, problems that are continuous or ongoing for a large proportion of the snapshot interval). The symptoms and problems are available by running the ADDM report, and through Enterprise Manager.

Active Session History (ASH) An all-purpose tool providing data that is useful when investigating system-wide problems, shorter-duration spikes, or smaller-scoped problems (for example, for a specific user, or SQL, or a module/action).The advantage of using ASH data when compared to other diagnostic information is that the data is of a finer granularity. This allows you to look at a problem to identify how the symptoms "build up," or allows you to determine exactly which resources are involved and who is using them. The ASH data can be queried directly or accessed via a targeted ASH report.

failed to start a managed process after the maximum retry limit Log: /home/oracle/product/10.2.0/oms10g/opmn/logs/WebCache~WebCache~1

failed to start a managed process after the maximum retry limit Log: /home/oracle/product/10.2.0/oms10g/opmn/logs/WebCache~WebCacheAdmin~1

b.Login as root user and execute following command OMS_HOME/webcache/bin/webcache_setuser.sh setroot <username> Note: In place of the <username> you should give the user name which you use to start oms.

Select Query

Insert Delete Update

Subquery

Register the plan in the SQL Plan Baselines

the registered SQL Plan Baseline prevents an other plan to be used

Evolve the plan