RDA AWR Report

Here is a simple example how to generate RDA report that will include AWR report for specific time.

The commands:

PRF_START=$(date +"%d-%h-%Y_%H:%M" --date="yesterday")
PRF_END=$(date +"%d-%h-%Y_%H:%M")
./rda.sh -vCRP -e PERF_START_TIME=$PRF_START,PERF_END_TIME=$PRF_END OS DB DBA INST ONET PROF PERF

so if you will schedule a crontab task let's say to 1AM with the above commands then the RDA report would be include AWR report for last the 24 hours period.

That's it.

Parallel execution in RAC

Wrong usage of parallelism it is a strong enemy for query performance in Oracle RAC.
If you use RAC with two or more nodes and some of your TABLES or INDEXES have a "DEFAULT" value for DEGREE and INSTANCES parameters then you have troubles :).

To check if you have "DEFAULT" values for tables / indexes:

SELECT TABLE_NAME, OWNER, DEGREE, INSTANCES 
FROM DBA_TABLES WHERE INSTANCES = 'DEFAULT';

SELECT INDEX_NAME,TABLE_NAME, OWNER, DEGREE, INSTANCES 
FROM DBA_INDEXES WHERE INSTANCES = 'DEFAULT'; 

Oracle calculates DEFAULT degree of parallelism for a query by multiply the following instance parameters:

for 3 nodes RACs with 16 cores per node:
parallel_server_instances = 3
cpu_count=16
parallel_threads_per_cpu=2

So we have DEGREE= 16* 2 * 3 = 96 !!!

Here is the example of performance difference for simple query:

SQL> select degree,instances from user_indexes where index_name='IDX_TEST_2312';

   DEGREE         INSTANCES
--------------        -----------------
DEFAULT             DEFAULT

1. The query use degree with level 96.












2. The same query but with the hint to set the degree to 1.

So keep an eye the "DEFAULT" value for your tables / indexes if you use RAC with more then one node. 

That is all!

Slow connection to Oracle

   About two years ago I was investigating a request from DEV team about slow connection to the database. The problem looked strange for me since we had two databases in one RAC and one had a version 10.2.0.4 and the other one was 11.1.0.7. The issue with slow connection was only for 11.1.0.7 database.
After long digging I found that one of two DNS servers in /etc/resolv.conf was unreachable. I had commented the bad DNS and the issue with slow connection has gone.
   Two months later the same problem was reported on the support.oracle.com like a bug that fixed in version 11.2.0.2. Note ID 561429.1.

So if you experiencing the same problem then just do the same!

Speed up TEXT search.

My friends were searching the better solution to speed up search engine for text data and asked me about any recommendations regarding that. I did research and found very nice project Scotas. It is a realization of Solr/Lucene search engine inside the Oracle RDBMS.

This very interesting solution has the following features:
OJVM integrated
Partitioning support
Encrypted/Compressed Storage
Sarbanes Oxley (SOX)/HIPAA/PCI
IOT Tables
Solr Query Syntax
Highlighting, faceting, etc
Multiple tables-columns definition
Complex data types
Complex Text Analysis
Parallel Indexing
Rich Composite Domain Index
Transactional storage
On-line backup
NRT Updates/ RT deletes
Per index isolated storage

I hope it will be useful for you.

Recreate TEMP tablespace.

Time to time due to different reasons we may need to recreate TEMP tablespace.
Here is the steps that I usually use.

1. First of all we need create new temporrary tablespace:

If we use the OMF:
CREATE TEMPORARY TABLESPACE NEW_TEMP TEMPFILE SIZE 300M AUTOEXTEND ON NEXT 50M MAXSIZE 30G;

If we don't use the OMF:
CREATE TEMPORARY TABLESPACE NEW_TEMP TEMPFILE '/oradat/new_temp1.dbf' SIZE 300M AUTOEXTEND ON NEXT 50M MAXSIZE 30G;  - put your path

2. Switch your database to the new temporary tablespace:

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE NEW_TEMP;

3. Confirm that the new temporary tablespace was set correctly:

SELECT * FROM DATABASE_PROPERTIES where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';
select username,TEMPORARY_TABLESPACE from dba_users;

4. Drop old temp tablesppace.

drop tablespace TEMP including contents and datafiles; - in case TEMP was your old temp tablespace.

5. If your session stuck on DROP step then check how use the old temp now:

SELECT se.username,se.sid, se.serial#,se.status, se.machine ,
su.tablespace,su.segtype,su.contents FROM v$session se,v$sort_usage su
WHERE se.saddr=su.session_addr;

release the old temp from any sessions and then you will be able to drop it.

That is all!

Find a TABLE size in Oracle.

I've heard this question so many times so decided to answer on it one time and forever :).

You don't need any DBA permissions for this since this view is available for all users!

View: USER_EXTENTS

so to check a table size just select from it:

select sum(de.bytes)/1024/1024 MB_size from user_extents ue
where ue.segment_name='YOUR_TABLE';

That is all!

Change the REDO Log size in RAC.

There is no any ALTER... command to resize the REDO logs.
So if you want to resize your REDO logs you will need to create a new group with a new size and then drop the old one.

Let's say you have this situation in your RAC for two nodes:

SQL> select GROUP#,MEMBERS,BYTES/1024/1024 MB_Size,status from v$log;
    GROUP#    THREAD#    MEMBERS    MB_SIZE STATUS
   ----------      ----------         ----------      ---------- ----------------
             1                      1                     2                 50 CURRENT
             2                      1                     2                 50 INACTIVE
             3                      2                     2                 50 INACTIVE
             4                      2                     2                 50 CURRENT

and you want to resize all your groups. Lets say you want to set 100M instead of 50M.

Action plan:
1. Add new REDO groups with a new size.

ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 5 ( '+DATA','+FLASH') SIZE 100M;
ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 6 ( '+DATA','+FLASH') SIZE 100M;
ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 7 ( '+DATA','+FLASH') SIZE 100M;
ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 8 ( '+DATA','+FLASH') SIZE 100M;

Mentioned commands will create 4 new groups with two members in each.

2. Now you should wait till the Group 1/2/3/4 will start to be INACTIVE so you would be able to drop them.
Also you can speed up this process by executing:

alter system switch logfile;
alter system checkpoint;

3. To DROP the old groups.

ALTER DATABASE DROP LOGFILE GROUP 1;

In busy DB it is possible to see something like this during drop operation:
ORA-01623: log 1 is current log for instance RPTDB (thread 1) - cannot drop

in that case you should execute the following again:
alter system switch logfile;
or
alter system checkpoint;
or
just continue your tries to drop it.

Once all your old GROUPs will be dropped your output would be look like this:

SQL> select GROUP#,MEMBERS,BYTES/1024/1024 MB_Size from v$log;

 GROUP#    THREAD#    MEMBERS    MB_SIZE
 ----------     ----------         ----------     ----------
             5                    1                     2         100
             6                    1                     2         100
             7                    2                     2         100
             8                    2                     2         100

That is all!