Retrieving Oracle internal information

The below query will give you information on many of the internals such as no of base events ,number of latches and information on DBWR processes.


select KVIIDSC,KVIIVAL from X$KVII;


KVIIDSC                                                             KVIIVAL
---------------------------------------------------------------- ----------
# of base events                                                       1115
# of base events in session                                       400
number of latches                                                    535
event range base                                                      12625
initial number of CPUs in the system                     4
DBWR max outstanding writes                              4096
number of DBWR processes                                   1
DBWR write chunk                                                 204
true if Statically Allocated Thread                           1
THRead mounted by this instance - zero if none     1
sga shadow value of instance_number                     1

11 rows selected.



The below query shows the information such as HWM,CPU info etc.

15:00:01 SQL> select KVITDSC,KVITVAL from X$KVIT;

KVITDSC                                                                                         KVITVAL
----------------------------------------------------------------                      ----------
number of logical CPUs in the system used by Oracle                           4
number of physical CPU cores in the system used by Oracle                 2
number of physical CPU sockets in the system used by Oracle              1
high water mark of number of CPUs used by Oracle                              4
high water mark of number of CPU cores on system                              2
high water mark of number of CPU sockets on system                           1
number of available CPUs in the system                                                 4
CPU dynamic reconfiguration supported                                                1
number of buffers                                                                                   63680
large dirty queue if kcbclw reaches this                                                 25
Max percentage of LRU list foreground can scan for free                    40
Initial percentage of LRU list to keep clean                                          2
number buffer objects                                                                            750
Flag that indicates recovery or db suspension                                        0
Error Log Number for thread open                                                         0
SGA: opcode for checkpoint cross-instance call                                    0
SGA:opcode for pq checkpoint cross-instance call                                0

17 rows selected.

Hope this helps .

Fixed size and variable Size in SGA

Have you ever wondered what is that fixed size and variable size when you say show SGA?

11:30:35 SQL> show sga

Total System Global Area  778387456 bytes
Fixed Size                           1374808 bytes
Variable Size                      662701480 bytes
Database Buffers                109051904 bytes
Redo Buffers                      5259264 bytes


Lets dig into this now.

The first component "Total System Global Area" is nothing but

Fixed Size + Variable Size + Database Buffers + Redo Buffers


The second component "Fixed Size" is constant for a version/release for a particular platform. This is internally set by oracle kernel and absolutely the DBA cannot do anything about it.

The third component  "Variable Size" is calculated by

shared_pool_size + Java_pool_size + large_pool_size + streams_pool_size

Please use the below query to get this value to avoid doing it manually.

11:42:13 SQL> select sum(bytes)/1024/1024 from  v$sgastat
11:42:28   2  where pool in ('java pool','streams pool','shared pool','large pool');

SUM(BYTES)/1024/1024
--------------------
          632.004478

1 row selected.

The third and fourth component  are db buffers and redo buffers.

Creating Restore Point

Steps to do flashback:

1)COnfigure the three parameters.

SQL> show parameter db_recovery_file_dest

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

db_recovery_file_dest string /decoopdbdmmk1/oracle/arch/div03/fra

db_recovery_file_dest_size big integer 2G

SQL> show parameter flashback

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

db_flashback_retention_target integer 1440

2)SQL> select flashback_on from V$database;

FLASHBACK_ON

NO

if the value is YES then go to step 3 else

SQL> alter database flashback on;

Database altered.

3)SQL> Create table vijay.flash_t (id int);

Table created.

SQL> begin

2 for i in 1..100

3 loop

4 insert into vijay.flash_t values(i);

5 end loop;

6 commit;

7 end;

8 /

PL/SQL procedure successfully completed.

4)SQL> create restore point vijay gUARANTEE flashback database;

Restore point created.

5)SQL> truncate table vijay.flash_t;

Table truncated.

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area 3206836224 bytes

Fixed Size 2232640 bytes

Variable Size 2113932992 bytes

Database Buffers 1073741824 bytes

Redo Buffers 16928768 bytes

Database mounted.

6)SQL> flashback database to restore point vijay;

Flashback complete.

SQL> alter database open resetlogs;

Database altered.

7)SQL> select count(1) from vijay.flash_t;

COUNT(1)

100

Using Temp tablespace Groups

I wanted to share the experience on using temp tablespace groups that I proposed on our last staff meeting.

Abstract:

Test advantages of using temporary tablespace group.

Steps performed for testing:

• Created three temporary tablespaces each of 8GB with AUTOEXTEND OFF and assigned them to a single tablespace group. Now the tablespace group is of total size 8X3=24GB

• Created a session and ran a SQL query which will use temp tablespaces. Also making sure this query will need space more than the size of one tablespace (i.e.) 8GB. I used a order by on a big table.

• Created multiple sessions from a single database user to use temporary tablespace group.

• Created multiple sessions of different users to use temporary tablespace group.

Result Obtained:

• Single SQL query could not span multiple temporary tablespaces for sorting. Temporary segments do not span tablespaces. Although many blogs says it will and I did not find that happen.

• Different sessions of same user used different tablespaces in the group at the same time. (i.e.) Each session with its own SQL query runs on different tablespaces.

• Different user sessions used different tablespaces in the group.

Conclusion:

• Multiple default temporary tablespace will ease maintenance (i.e.) in the event of single temp tablespace gets corrupted, we can just remove that from the tablespace group and drop the same and create a new temp tablespace and assign the same in tablespace group.

• Huge temp waits for read and writes can be minimized.

• The I/O gets distributed when the having multiple tempfiles.

• For large hash processes, where temp tablespace usage is unavoidable, temp tablespace groups offer significant improvement in I/O performance and more CPU utilization.

Using Flash cache

As we all know EXADATA comes with inbuilt flash cache. I just did some search on how to achieve the same in non EXADATA boxes.

Starting from 11gR2 oracle introduced database smart flash cache which is available only on Linux and Solaris operating systems.

Using flash cache the size of buffer cache can be increased without adding more memory.

Until oracle 11gR1 , the buffers in buffer cache are overwritten using LRU list. so if the buffer is needed again then a physical read is performed (ie) the data blocks are fetched from disk and placed in the buffer cache. With flash cache, the buffers are written from buffer cache to flash cache where the buffers are not overwritten. So when these buffers are needed, the data blocks are fetched from flaSH cache directly.

To implement this we need to set two parameters in out init.ora file :

• db_flash_cache_file

• db_flash_cache_size

The db_flash_cache_file takes a value of a filename with its path. This file acts as memory space. The DB_FLASH_CACHE_FILE parameter can point to a raw device (including a logical volume), afile, or an ASM disk group

The parameter db_flash_cache_size is the size of flash cache.

Note : If we do not have the onboard flash_cache on host and we try to enable the flash cache, we get this error message:

ORA-00439: feature not enabled: Server Flash Cache

ORA-01078: failure in processing system parameters

. A typical sizing of the Database Smart Flash Cache is two to ten times the size of SGA memory buffers. Note that header information is stored in the SGA for each flash cache buffer - 100 bytes perbuffer in exclusive mode, 200 bytes per buffer in RAC mode - so the number of available SGA buffers is reduced as the Database Smart Flash Cache size increases, and the SGA size should be increased accordingly.

Running RAC database on flash cache will remove bandwidth saturation at the cache fusion layer as a source of contention. Database Smart Flash Cache is not shared across RAC nodes; it is private to each RAC instance. For that reason, each RAC instance must define its own local flash cache file path. When the flash cache is managed by ASM, a separate diskgroup is needed for the flash cache of each instance.

This feature is good for read mostly or read only databases so that we can get maximum performance benefits.

How to Enable Tracing for srvctl and asmcmd utilities

The following environmental variables needs to be set to have tracing enabled.

For using srvctl utility

export SRVM_TRACE=TRUE

Setting the above variable to true enbles tracing . Setting it to false disables (This is default).

For asmcmd utilty

export DBI_TRACE=1

setting this to 1 will enable tracing and a value of 0 will disable (This is default).

snowflake VS star schema

	Snowflake Schema	Star Schema
Query Performance:	More foreign keys-and hence more query execution time	Less no. of foreign keys and hence lesser query execution time
Normalization:	Has normalized tables	Has De-normalized tables
Type ofDatawarehouse:	Good to use for small datawarehouses/datamarts	Good for large datawarehouses
Joins:	Higher number of Joins	Fewer Joins
Dimension table:	It may have more than onedimension table for each dimension	Contains only single dimension table for each dimension
When to use:	When dimension table is relatively big in size, snoflaking is better as it reduces space.	When dimension table contains less number of rows, we can go forStar schema.
Ease of maintenance/change:	No redundancy and hence more easy to maintain and change	Has redundant data and hence less easy to maintain/change
Ease of Use:	More complex queries and hence less easy to understand	Less complex queries and easy to understand

Use of DBMS_COMPARISON for Data sync

Use of DBMS_COMPARISON for Data sync:

--------------------------------------------------


Oracle 11g has provided us a wonderful package named DBMS_COMPARISION which can be used to sync table data .



The only requirement here is both the source and target tables should have atleast one index on them.



Please check the steps below



12:26:31 SQL> select count(1) from EMP;



COUNT(1)

----------

5



1 row selected.



The table EMP_BKP has same structure of EMP, but does not have any rows.



12:29:25 SQL> select count(1) from EMP_BKP;



COUNT(1)

----------

0



1 row selected.



Now to sync this data, we use DBMS_COMPARISION,



exec dbms_comparison.create_comparison('COMP_VIJAY','VIJAY','EMP_BKP',null,null,null,'VIJAY','EMP');



The above is just to let oracle know we are doing a comparision of the above schemas.



declare

compare_info dbms_comparison.comparison_type;

compare_return boolean;

begin

compare_return :=dbms_comparison.compare (comparison_name=>'COMP_VIJAY',scan_info=>compare_info,perform_row_dif=>TRUE);

if compare_return=TRUE

then

dbms_output.put_line('the tables have same Data.');

else

dbms_output.put_line('Table data is not same.');

dbms_output.put_line('Check the dba_comparison and dba_comparison_scan_summary views for locate the differences for scan_id:'

compare_info.scan_id);

end if;

end;

/

The PL/SQL block will tell you if the table datas are same or not. If not same, then it gives the scan_id which can be used to query some of the dictionary views.



Now use the above scan_id in the below query . For example lets say scan_id is 9 then



select a.owner, a.comparison_name, a.schema_name, a.object_name,

z.current_dif_count difference

from dba_comparison a, dba_comparison_scan_summary z

where a.comparison_name=z.comparison_name

and a.owner=z.owner

and z.scan_id=9;



This will give you more details of the data changes.



Now to sync them use the below PL/SQL block



declare

compare_info dbms_comparison.comparison_type;

begin

dbms_comparison.converge (comparison_name=>'COMP_VIJAY',scan_id=>9,scan_info=>compare_info,converge_options=>dbms_comparison.cmp_converge_remote_wins);

dbms_output.put_line('--- Results ---');

dbms_output.put_line('Local rows Merged by process: '

compare_info.loc_rows_merged);

dbms_output.put_line('Remote rows Merged by process: '

compare_info.rmt_rows_merged);

dbms_output.put_line('Local rows Deleted by process: '

compare_info.loc_rows_deleted);

dbms_output.put_line('Remote rows Deleted by process: '

compare_info.rmt_rows_deleted);

end;

/



This is very useful in data sync and is much faster.



You can also use DBLINK for remote databases. I have just used a local schema and database for my testing purpose.

Concepts on updating table Data

Updating the column value to the same value modifies the table block. data in the table block is physically modified. But, the corresponding index entry is not modified.


Updating the column value to a different value modifies both table and index blocks. Updating an indexed column value results in a delete and insert of index entries in the index leaf block.

Delete of an index entry does not delete the entry physically, rather marks the entry with a flag. Future inserts into this block will reuse the space allocated.