Group items tagged

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.

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).

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:

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.

The client load balancing feature enables clients to randomize connection requests among the listeners. Tnsnames Parameter: LOAD_BALANCE

The connect-time failover enables clients to connect to another listener if the initial connection to the first listener fails. The number of listener protocol addresses determines how many listeners are tried. Without connect-time failover, Oracle Net attempts a connection with only one listener. The default is on. Tnsnames Parameter: FAILOVER

The listener connection load balancing feature improves connection performance by balancing the number of active connections among multiple dispatchers and instances. In a single-instance environment, the listener selects the least loaded dispatcher to handle the incoming client requests. In an Oracle Real Application Clusters (RAC) environment, connection load balancing also has the capability to balance the number of active connections among multiple instances. 1. Least-loaded node 2. Least-loaded instance 3. Least-loaded dispatcher for that instance (in case of Shared server configuration)

Doc ID: 578524.1

Note: It is very important to use entire address string in order to configure dispatchers on RAC and host name used in it should be a VIP hostname, otherwise connection may suffer intermittent ORA-12545 errors. Refer metalink note <333159.1>

alter system set dispatchers='(address=(protocol=tcp)(host=node1-vip))(dispatchers=2)' scope=both   sid='racdb1';

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.

Oracle Database Availability Configure Install Patching Performance Security

Enterprise Manager Configure Patching

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.

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)

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

This instance relocation uses a new featured dubbed Oracle Omotion.

This instance relocation uses a new featured dubbed Oracle Omotion.

This is a similar approach to instance relocation that was first introduced by Savantis Systems with their DB-Switch invention, an offshoot of the Database Area Network (DAN) approach.