Posts

Showing posts from April, 2017

ORACLE RAC VARIOUS COMMAND FOR DAY TO DAY OPERATION

ORACLE RAC VARIOUS COMMAND FOR DAY TO DAY OPERATION Checking CRS Status: The below two commands are generally used to check the status of CRS. The first command lists the status of CRS on the local node where as the other command shows the CRS status across all the nodes in Cluster. # cd /u01/app/11.2.0/grid/bin # ./crsctl check cluster -all crsctl check crs <<– for the local node crsctl check cluster <<– for remote nodes in the cluster Example: [root@YesBolisetty1-pub ~]# crsctl check crs For the below command to run, CSS needs to be running on the local node. The “ONLINE” status for remote node says that CSS is running on that node. When CSS is down on the remote node, the status of “OFFLINE” is displayed for that node. Example: [root@YesBolisetty1-pub ~]# crsctl check cluster Viewing Cluster name: The below command to get the name of Cluster. The similar information can be retrieved from the dump file. ocrdump -stdout -keyname SYSTEM | grep -A 1 clu

LOAD BALANCING IN ORACLE RAC.

LOAD BALANCING IN ORACLE RAC If you’ve installed Oracle RAC (Real Application Clusters) and want to test how Load Balancing works, you can run the following shell script and check GV$SESSION view: #!/bin/bash . /home/oracle/.bash_profile for ((i=1; i <= 50 ; i++)) do nohup sqlplus -S system/oracle@YesBolisetty1< begin dbms_lock.sleep(10); end; / This will open 50 sessions in the background. Check GV$SESSION view before and after running this query: SQL> select inst_id, count(*) from gv$session where username is not null group by inst_id; INST_ID COUNT(*) ———- ———- 1           10 2            9 Run the following command from the different session: [oracle@node1 ~] ./check_load_balancing.sh SQL> select inst_id, count(*) from gv$session where username is not null group by inst_id; INST_ID COUNT(*) ———- ———- 1           33 2           36 Wait for 10 seconds (as we’ve defined “10″ seconds at DBMS_LOCK.SLEEP procedure

My query is picking very bad execution plan ( using Oracle baselines )

My query is picking a bad execution plan , how to fix it to use the good execution plan available ( using Oracle baselines ) ? Using Oracle baselines you can fix the sql plan for a SQLID:                                                    SQL plan management is a preventative mechanism that records and evaluates the execution plans of SQL statements over time. This mechanism can build a SQL plan baseline, which is a set of accepted plans for a SQL statement. The accepted plans have been proven to perform well. The goal of SQL plan baselines is to preserve the performance of corresponding SQL statements, regardless of changes in the database. Examples of changes include: New optimizer version Changes to optimizer statistics and optimizer parameters Changes to schema and metadata definitions Changes to system settings SQL profile creation SQL plan baselines cannot help in cases where an event has caused irreversible execution plan changes, such as dropping an index

Tablespace Monitoring Scripts

To check Tablespace free space: SELECT TABLESPACE_NAME, SUM(BYTES/1024/1024) "Size (MB)"  FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME; To check Tablespace by datafile: SELECT tablespace_name, File_id, SUM(bytes/1024/1024)"Size (MB)" FROM DBA_FREE_SPACE group by tablespace_name, file_id; To Check Tablespace used and free space %: SELECT /* + RULE */  df.tablespace_name "Tablespace", df.bytes / (1024 * 1024) "Size (MB)", SUM(fs.bytes) / (1024 * 1024) "Free (MB)", Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free", Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used" FROM dba_free_space fs, (SELECT tablespace_name,SUM(bytes) bytes FROM dba_data_files GROUP BY tablespace_name) df WHERE fs.tablespace_name (+)  = df.tablespace_name GROUP BY df.tablespace_name,df.bytes UNION ALL SELECT /* + RULE */ df.tablespace_name tspace, fs.bytes / (1024 * 1024), SUM(df.bytes_free) / (1024 * 1024), Nvl(Round((SUM(fs.bytes) -