|
||||
|
Connection Workload management is one of the key aspects when you have RAC instances as you want to distribute the connections to specific nodes/instance or those have less load. Oracle from 10g onwards (9i as well) has two features to provide the connection load balancing Client Side load balancing (also called as connect time load balancing) Server side load balancing (also called as Listener connection load balancing) Continue reading RAC: Client & Server connection Load balancing in Oracle Why do we have a Virtual IP (VIP) in Oracle RAC 10g or 11g? Why does it just return a dead connection when its primary node fails?The goal is application availability with the shortest disruption time possible. When a node fails, the VIP associated with it is automatically failed over to some other node. When this occurs, the following things happen. Vip resources in CRS (ora.node.vip) (1) VIP detects public network failure which generates a FAN event. ( A FAN event is an fast application notification event and ONS captures and publishes to the subscriber in RAC the subscriber can be listener) (2) the new node re-arps (see arpa) the world indicating a new MAC address for the IP. (3) connected clients subscribing to FAN immediately receive ORA-3113 error or equivalent. Those not subscribing to FAN will eventually time out. (4) New connection requests rapidly traverse the tnsnames.ora address list skipping over the dead nodes, instead of having to wait on TCP-IP timeouts (default 10 mins) Without using VIPs or FAN, clients connected to a node that died will often wait for a TCP timeout period (which can be up to 10 min) before getting an error. As a result, you don't really have a good HA solution without using VIPs and FAN. The easiest way to use FAN is to use an integrated client with Fast Connection Failover (FCF) such as JDBC, OCI, or ODP.NET. One of my colleague struggling from two days for a Oracle client 11g 64 bit installation, where he is expecting a lib32 folder in Oracle_HOME as usual but he didn’t. Thanks to him first for sharing before to proceed further. He didn’t succeeded to get lib32 folder instead he found this note, which may save your time too. So What’s the Oracle’s Answer is about missing Lib32 folder in Oracle home, According to Note:- 883702.1.
This is expected behavior :(. DBA’s do not expect this unless until we hit.
Additional reference: Missing Lib32 In 11gR2 Client Software for Solaris [ID 1271236.1] -Thanks Geek DBA Situation: Application not able to connect and including sysdba sessions. Complete DB hung due to large number of session spike in very less time. Error:-ORA-00020: maximum number of processes exceeded Dependent parameters Action taken, We have killed some backups jobs and other local sessions and get a slot of sysdba connectivity at last. Now troubleshooting, why, who are making this happen ORA-00020: maximum number of processes exceeded Lets have a look at Resource limit to ensure we reached max utilization (800) of resources sessions/processes
See above the during 04:00 Am we have hit or spike in the sessions from 211/355 to 885 Lets have a look at process utilization
Let check the sysstat has an entries about it, nopes it does not listed the maximum utilization as it missed to collected the stats.
Q1 Logon currents showing 334 only, why it is not showing? Another area of interest to troubleshoot, dba_hist_sysmetric_summary gives you the hourly metrics of the statistics, we have to metrics that collected in database called Session Limit % and Process Limit % ,
You see above there was a sudden jump in the session and process limit % increase from 49% to 99% , so we determined now there are some obvious/dubios connections happening. Q2, How to find the which users or machine or terminal has most connections during the period? If you have audit enabled: use aud$ by filtering the statement column with "%logon%'; Use active session history, but the sample of 10 sec to 1 entry in active session history make the sessions count big large, we have to rollup here something, I still have to write queries for both of above and publish soon. Update 21-Feb-2013:-
-Thanks The So that’s from the documentation, DBMS_XPLAN has other functions as follows:-
So each of the above sub programs have different options and formats to view your execution plan, First look at the matrix table below before we proceed with examples.
Examples:-
Continue reading Explain Plan: Different formats/options with dbms_xplan The following are the top 10 Useful SAR commands for DBA’s. 1. CPU Usage of ALL CPUs (sar -u)This gives the cumulative real-time CPU usage of all CPUs. “1 3″ reports for every 1 seconds a total of 3 times. Most likely you’ll focus on the last field “%idle” to see the cpu load. $ sar -u 1 3 Linux 2.6.19-194.el5SMP (host) 09/26/2012 _i686_ (8 CPU) 01:27:32 PM CPU %user %nice %system %iowait %steal %idle 01:27:33 PM all 0.00 0.00 0.00 0.00 0.00 100.00 01:27:34 PM all 0.25 0.00 0.25 0.00 0.00 99.50 01:27:35 PM all 0.75 0.00 0.25 0.00 0.00 99.00 Average: all 0.33 0.00 0.17 0.00 0.00 99.50 Following are few variations:
Continue reading Unix: Top 10 useful SAR commands in unix, performance monitoring using SAR This is beginning of Unix series post for DBA’s to understand the monitoring tools for Linux Sysstat is the package that contains various tools or utilities to collect or view the performance of the Unix servers. Installing sysstat
Verify
Tools or Utilities that are part of Sysstat package
Next Post: Top 10 Useful commands in SAR -Thanks Geek DBA Compare statistics, good idea, Ok, here is the scenario You have a large table where you have collected the statistics with sample of 10%, and due to some reasons you want to collect the statistics with auto or 100% sample size, but you are unsure of the difference it make. Note its not about to restoring the old statistics, but its about to compare the statistics changes over the period. Oracle 11g (R1) comes up with three new procedures in dbms_stats package. The
stattab1 or time1) will be used as basis for computing the differential percentage.For example, Symptoms: Alert log says WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! Wait events in Node A: DFS Lock Handle and hanged Wait events in Node B: row cache locks and library cache locks (sessions permitted but very slow) AWR reports says for the interesting period: DFS Lock Handle Background:- DFS Lock Handle DFS stands for distributed file system is an ancient name, associated with cluster file system operations, in a Lock manager supplied by vendors in Oracle Parallel Server Environment (prior name for RAC). But, this wait event has morphed and is now associated with waits irrelevant to database files also. This will occur in RAC environment, possible with sequences especially when you have sequences + cache + Ordered set. Means created like this, create sequence s1 min value 1 cache 20 order; As RAC is multi instance environment, the values of the sequences need to be synchronized as they are need to be ordered. Simulation of the issue:- Adaptive cursor sharing introduced in 11g R1 to address the bind peeking issues with things track especially the bind aware and bind sensitivity. This is for plan stability right, Can this stability when bind peeking issues is be achievable in 10g, well the obvious answer is sql profiles, stored out lines. Another option is to disable the bind peeking using the hidden parameter _optim_peek_user_binds=false But this may have side affects as you are turning off for entire instance, what if and how you want to disable the bind peeking for only single statement,
|
||||
Oracle 23c New Features for DBA's
Follow Me!!!