Sunday, March 18, 2018

Exadata X7-2 - Full Rack, Half Rack, Quarter Rack, and Eighth Rack Comparison


Four versions of the Exadata Database Machine X7-2 are available – the Full Rack, Half Rack, Quarter Rack, and Eighth Rack – depending on the size, performance and I/O requirements of the database to be deployed. One version can be expanded online to another ensuring a smooth upgrade path as processing requirements grow.

Eighth Rack is the minimum Exadata configuration. Eighth Rack database servers have one processor each with all cores enabled. Maximum memory supported per database server is 768GB. Eighth Rack EF storage servers have half the cores and flash drives enabled. Eighth Rack HC storage servers have half the cores enabled and half the disks and flash cards removed. The optional NIC is not available for the Eighth Rack

Exadata X7-2 Database Server/Storage Server Hardware Components


Oracle Exadata Database Machine consists of Oracle Database servers (Comute node), Exadata Storage Servers(commonly called as cell server), and the network components to connect to your network. 
Oracle Exadata Database Machine is available as a full rack, half rack, quarter rack or eighth rack.
Exadata X7-2 Database Machines(Compute node)Hardware components
CPU
Two Intel Xeon Platinum 8160 Processors 8160(2.1 GHz) each having 24-core.So 48 Core available in a compute node and a total of 48*8= 384 CPU Core for a Full Rack. Eighth Rack HC/EF database servers have only half the cores enabled.
RAM
384GB DRAM  by default(Dynamic random access memory)- expandable upto 1.5T (24 x 64GB) with memory expansion kit
DISK
Four 600 GB 10K RPM SAS disks, hot swappable, expandable up-to eight disks. 
FLASH
No flash in compute node.
Network
  • Two QDR( Quad Data Rate) infiniBand (40 Gbps) ports (PCIe 3.0), both ports active
  • Two 1 GbE/10 GbE Base-T Ethernet ports or two 10GbE/25GbE Optical Ethernet SFP28 Ports - external/Client 
  • Two 10 GbE/25 GbE Optical Ethernet SFP28 Ports - For external network. The 25 GbE is new in X7-2.In X6-2 there was only 10GbE so in X7-2 we have a 150% faster Ethernet.
  • One 1 GbE/10 GbE  Base-T ethernet port for Integrated Lights Out Manager (ILOM) for remote management

Infiniband has been classified into 5 catagories based upton their speed FDR, FDR-10,QDR,DDR and SDR. The QDR InfiniBand provides a 40 Gbps link. InfiniBand switches and cables to form a 40 Gb/second InfiniBand fabric for database server to Exadata Storage Server communication, and RAC internode communication.
OS
Oracle Linux 6.9 with Unbreakable Enterprise Kernel 4 or Oracle VM Server 3.4.4
Exadata Storage Server X7-2 Hardware Components
CPU
Two 10 core Intel Xeon Silver 4114 Processors (2.20GHz) - So 20 CPU core per cell server. In eighth rack only 10 core will be activated.
RAM
192 GB RAM (12 x 16 GB) 
DISK
Twelve 10 TB 7.2k rpm High Capacity (HC) SAS disks ,so each cell having 12*10=120 TB raw capacity disks. Eighth Rack HC storage servers have half the disks and flash cards removed. So in a eighth RAC HC storage server there would be only 6*10=60TB available as raw storage. There is no disks in Extrem flash(EF) storage server.
Flash
Four 6.4 TB NVMe PCIe 3.0 flash cards (for High Capacity configuration)
Eight  6.4 TB NVMe PCIe 3.0 flash card (for Extrem flash configuration)
Two 6.4 TB NVMe PCIe 3.0 flash card (for Eighth Rack)
Network
Two InfiniBand QDR (40 Gb/s) InfiniBand ports all ports active.
4 embedded Gigabit Ethernet ports
1 Ethernet port for ILOM for remote management
Software/OS
Oracle Linux 6.9 with Unbreakable Enterprise Kernel 4
Oracle Exadata Storage Server Software release 18.1.0.0.0
Note:- Eighth Rack is the minimum Exadata configuration. Eighth Rack database servers have one processor each with all cores enabled. Maximum memory supported per database server is 768GB. Eighth Rack EF storage servers have half the cores and flash drives enabled. Eighth Rack HC storage servers have half the cores enabled and half the disks and flash cards removed. The optional NIC is not available for the Eighth Rack

Monday, March 12, 2018

Index fragmentation - use coalesce/shrink/rebuild wisely


Over time, indexes can potentially experience fragmentation due to large numbers of deletions. DBAs often wonder about the correct approach to handling the space usage by indexes, especially large ones. Oracle provides three ways to handle fragmentation within an index—rebuilding, coalescing, and shrinking an index— and each of them serves a different purpose.


Index Rebuild

You can rebuild an index either in offline mode(default) or in online mode.

Alter index rebuild online: 

During a online index rebuild, Oracle will make a snapshot log(an intermediate table) on the target table to hold DML activity, read the table in a full-table scan (read consistent), build the new index and then apply the changes from the snapshot log after the index has been rebuilt.You can create or rebuild indexes even when doing DML (INSERT/UPDATE/DELETE) statements on the base table. You can use NOLOGGING and PARALLEL with the online rebuild.Rebuilding an index online allows the database to use the index during its rebuild, thus enhancing availability.

Online Index Rebuild Features:


  •  ALTER INDEX REBUILD ONLINE;
  •  DMLs are allowed on the base table
  •  It is comparatively Slow
  •  Base table is referred for the new index
  •  Base table is locked in shared mode and DDLs are not possible
  •  Intermediate table stores the data changes in the base table, during the index rebuild to update the new index later

Example
alter index my_idx rebuild online parallel 63 nologging; 
alter index my_idx noparallel;
alter index my_idx logging;

Alter index rebuild(offline):

During a "regular" index rebuild, an exclusive lock occurs as the existing index is read.  Hence, this command is designed for scheduled downtime periods where there is no DML activity.However, this operation can be parallelized and run in NOLOGGING mode.

Offline Index Rebuild Features:


  •   ALTER INDEX REBUILD; (Default)
  •   New index is created from the old index
  •   No DML and DDL possible on the base table
  •   Comparatively faster
  •   Here base table is not referred while index is rebuild offline.
  •   Does not refer the base table and the base table is exclusively  locked

Example
alter index my_idx rebuild parallel 63 nologging; 
alter index my_idx noparallel;
alter index my_idx logging;

Note:-  
Even for Online operation,a table lock is required on the index base table at the start of the CREATE or REBUILD process to guarantee DDL information. A lock at the end of the process is also required so as to merge changes into the final index structure.The time taken to complete the indexing process(rebuild or create) will increase as the indexing process will hang if there is an active transaction on the base table of the index being created or rebuilt at the time one of these locks is required. However Oracle 11g has provided enormous improvements in the locking implications regarding creating or rebuilding indexes online. So such operation will no longer be locked and complete successfully if the indexing process has been locked out and subsequent transactions relating to the base table starts afterwards.In Oracle 11g the indexing process no longer effects other concurrent transactions on the base table.

++Even during index build you can use the CREATE INDEX ONLINE to create an index without placing an exclusive lock over the table. 

CREATE INDEX ONLINE statement can speed up the creation as it works even when reads or updates are happening on the table.

Coalesce and Shrink compact

Instead of rebuilding an index, you can shrink the space used by an index by coalescing or shrinking an index. 
Using the alter index ...shrink space compact command gets you the same results as when you execute the alter index...coalesce command. Both the shrink+ compact and coalesce commands are alternative ways to compact an index segment. Both operations achieve the same purpose and are in essence identical, but the shrink command offers more options.

*Coalescing an index doesn't release space back to the database.

The purpose of the coalesce command is to reduce fragmentation in an index. It doesn't deallocate space that has been allocated to an index segment.

*It combines adjacent leaf blocks into a single leaf block and puts the newly empty leaf blocks on the free list of the index segment.Following figure illustrate the effect of coalesce on an index.


Image result for Image result for index coalesce vs rebuild vs shrink

*Coalescing an index keeps the space you allocated for the index intact; it doesn't return the unused space to the database


*Shrinking an index compacts the index segment and the database will immediately release any space that has been freed up.


*There are several problems with the analyze index ...validate structure command, including the fact that the command locks the table until the index is analyzed


*For a large index, the database may take quite a bit of time to complete the shrink operation. Therefore, Oracle lets you perform a shrink operation in two steps. If you specify the compact clause with the shrink space command, the database only performs a defragmentation of the segment space. It compacts the index but doesn't immediately release the free space. You must issue a separate alter index ...shrink space command to make the index release the free space. Since compacting an index segment may require the database to perform row movement, you must first enable row movement for a table before you specify the compact clause.


*If your goal is merely to defragment an index, coalesce the index instead of shrinking it;


*Coalescing has an advantage over shrinking an index because the database never locks the index during the index coalescing operation (coalesce is always an online operation), while it does lock the table briefly during a shrink operation to release the free space.


*Large indexes sometimes get fragmented over time and you may have a valid reason to reduce the fragmentation. Whether you employ the coalesce or rebuild operation depends on exactly how the index

is getting fragmented. If you have an index on a sequence, for example, and the rows are deleted from an older part of the index, then you may have a case where the deleted space does become deadwood in the sense that it can't be used by new index entries that are being inserted into the newer, rightmost part of the index. In a case such as this, where the deletions are all occurring from a small portion of the index, a rebuild is overkill in general; you're better off with a coalesce (or shrink operation). The coalesce operation will just go through the small portion of the index that's fragmented and leave the rest of the index alone; it takes far less time and resources to get the job down via coalescing the index in this case. However, if there are numerous deletions through the index, and not from a specific part of the index structure, you are better off rebuilding the index completely. Rebuilding is far more efficient because, unlike a coalesce operation, it has to perform only a single scan of the index structure in order to create a new structure. Since the deletions are large and are all over the index structure, coalescing an index is far less efficient, as it has to keep moving the same index blocks through multiple leaf blocks to defragment them.

*Unlike in the case of a table, you can't move an index by using a "move index" command. You move an index to a different tablespace by rebuilding the index.


SQL>alter index test_idx1 rebuild parallel 12 nologging tablespace dev_oim


Here is a demonstration- 


C:\Users\Mahi>sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Sat Mar 10 18:43:12 2018
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> conn test/test

Connected.

First we test Coalesce-


SQL> DROP TABLE test;


Table dropped.


SQL> CREATE TABLE test (id NUMBER, name VARCHAR2(20));


Table created.


SQL> INSERT INTO test SELECT rownum, 'INDEX TEST' FROM dual CONNECT BY LEVEL <= 1000000;


1000000 rows created.


SQL> COMMIT;


Commit complete.


SQL> CREATE INDEX test_idx ON test(id);


Index created.


SQL>


SQL> col segment_name for a20


SQL> select segment_name,bytes/1024/1024 meg from dba_segments where segment_type='INDEX' and segment_name='TEST_IDX';


SEGMENT_NAME                MEG

-------------------- ----------
TEST_IDX                     18

SQL>


Delete very small percentage of the table data-


SQL> DELETE test WHERE mod(id,2) = 1 and id <= 100000;


50000 rows deleted.


SQL>  COMMIT;


Commit complete.


SQL>  ANALYZE INDEX test_idx VALIDATE STRUCTURE;


Index analyzed.


SQL> SELECT height, blocks, lf_blks, br_blks, del_lf_rows, btree_space,pct_used FROM index_stats where name='TEST_IDX';


    HEIGHT     BLOCKS    LF_BLKS    BR_BLKS DEL_LF_ROWS BTREE_SPACE   PCT_USED

---------- ---------- ---------- ---------- ----------- ----------- ----------
         3       2304       2226          5       50000    17848160         90

The PCT_USED used 90% and we need to get rid off 50,000 deleted leaf rows.


SQL> col name for a25

SQL> SELECT n.name, round(s.value/1024) redo_kb FROM v$mystat s, v$statname n WHERE s.statistic# = n.statistic# AND n.name = 'redo 
size';

NAME                         REDO_KB

------------------------- ----------
redo size                      61259

SQL>


Now coalesce the index-


SQL> ALTER INDEX test_idx COALESCE;


Index altered.


SQL>  SELECT n.name, round(s.value/1024) redo_kb FROM v$mystat s, v$statname n WHERE s.statistic# = n.statistic# AND n.name = 'redo 


size';


NAME                         REDO_KB

------------------------  ----------
redo size                      64039

SQL> select 64039-61259 from dual;


64039-61259

-----------
       2780

SQL> ANALYZE INDEX test_idx VALIDATE STRUCTURE;


Index analyzed.


SQL> SELECT height, blocks, lf_blks, br_blks, del_lf_rows, btree_space,pct_used FROM index_stats where name='TEST_IDX';


    HEIGHT     BLOCKS    LF_BLKS    BR_BLKS DEL_LF_ROWS BTREE_SPACE   PCT_USED

---------- ---------- ---------- ---------- ----------- ----------- ----------
         3       2304       2116          5           0    16968160         90

SQL> select segment_name,bytes/1024/1024 meg from dba_segments where segment_type='INDEX' and segment_name='TEST_IDX';


SEGMENT_NAME                MEG

-------------------- ----------
TEST_IDX                     18

SQL>


After coalesce leaf blocks has been changed by 110 block and the overall size of the index unchanged.


Now SHRINK SPACE COMPACT-


SQL>  DROP TABLE test;


Table dropped.


SQL> CREATE TABLE test (id NUMBER, name VARCHAR2(20));


Table created.


SQL> INSERT INTO test SELECT rownum, 'INDEX TEST' FROM dual CONNECT BY LEVEL <= 1000000;


1000000 rows created.


SQL> COMMIT;


Commit complete.


SQL> CREATE INDEX test_idx ON test(id);


Index created.


SQL> DELETE test WHERE mod(id,2) = 1 and id <= 100000;


50000 rows deleted.


SQL> ANALYZE INDEX test_idx VALIDATE STRUCTURE;


Index analyzed.


SQL> SELECT height, blocks, lf_blks, br_blks, del_lf_rows, btree_space,pct_used FROM index_stats where name='TEST_IDX';


    HEIGHT     BLOCKS    LF_BLKS    BR_BLKS DEL_LF_ROWS BTREE_SPACE   PCT_USED

---------- ---------- ---------- ---------- ----------- ----------- ----------
         3       2304       2226          5       50000    17848160         90

SQL> col name for a25

SQL> SELECT n.name, round(s.value/1024) redo_kb FROM v$mystat s, v$statname n WHERE s.statistic# = n.statistic# AND n.name = 'redo 
size';

NAME                         REDO_KB

------------------------- ----------
redo size                     125298

SQL> ALTER INDEX test_idx SHRINK SPACE COMPACT;


Index altered.


SQL>  SELECT n.name, round(s.value/1024) redo_kb FROM v$mystat s, v$statname n WHERE s.statistic# = n.statistic# AND n.name = 'redo 

size';

NAME                         REDO_KB

------------------------- ----------
redo size                     129995

SQL>

SQL> select 129995-125298 from dual;

129995-125298

-------------
         4697

SQL> ANALYZE INDEX test_idx VALIDATE STRUCTURE;


Index analyzed.


SQL> SELECT height, blocks, lf_blks, br_blks, del_lf_rows, btree_space,pct_used FROM index_stats where name='TEST_IDX';


    HEIGHT     BLOCKS    LF_BLKS    BR_BLKS DEL_LF_ROWS BTREE_SPACE   PCT_USED

---------- ---------- ---------- ---------- ----------- ----------- ----------
         3       2304       2116          5           0    16968160         90

SQL> select segment_name,bytes/1024/1024 meg from dba_segments where segment_type='INDEX' and segment_name='TEST_IDX';


SEGMENT_NAME                MEG

-------------------- ----------
TEST_IDX                     18

Index in the same state as after the Coalesce with leaf blocks down by 110 blocks but at nearly double the costs of the redo.


SHRINK SPACE


SQL> SELECT n.name, round(s.value/1024) redo_kb FROM v$mystat s, v$statname n WHERE s.statistic# = n.statistic# AND n.name = 'redo 

size';

NAME                         REDO_KB

------------------------- ----------
redo size                     129996

SQL> ALTER INDEX test_idx SHRINK SPACE;


Index altered.


SQL> SELECT n.name, round(s.value/1024) redo_kb FROM v$mystat s, v$statname n WHERE s.statistic# = n.statistic# AND n.name = 'redo 

size';

NAME                         REDO_KB

------------------------- ----------
redo size                     130030

SQL> select 130030 - 129996 from dual;


130030-129996

-------------
           34

SQL> select segment_name,bytes/1024/1024 meg from dba_segments where segment_type='INDEX' and segment_name='TEST_IDX';


SEGMENT_NAME                MEG

-------------------- ----------
TEST_IDX                16.9375

SQL> ANALYZE INDEX test_idx VALIDATE STRUCTURE;


Index analyzed.


SQL> SELECT height, blocks, lf_blks, br_blks, del_lf_rows, btree_space,pct_used FROM index_stats where name='TEST_IDX';


    HEIGHT     BLOCKS    LF_BLKS    BR_BLKS DEL_LF_ROWS BTREE_SPACE   PCT_USED

---------- ---------- ---------- ---------- ----------- ----------- ----------
         3       2168       2116          5           0    16968160         90

Block size also reduced by 2168(and thus index overall size) with an additional redo of just 34 KB.


Finally we Rebuild


SQL>  DROP TABLE test;


Table dropped.


SQL> CREATE TABLE test (id NUMBER, name VARCHAR2(20));


Table created.


SQL>  INSERT INTO test SELECT rownum, 'INDEX TEST' FROM dual CONNECT BY LEVEL <= 1000000;


1000000 rows created.


SQL> COMMIT;


Commit complete.


SQL> CREATE INDEX test_idx ON test(id);


Index created.


SQL> DELETE test WHERE mod(id,2) = 1 and id <= 100000;


50000 rows deleted.


SQL> select segment_name,bytes/1024/1024 meg from dba_segments where segment_type='INDEX' and segment_name='TEST_IDX';


SEGMENT_NAME                MEG

-------------------- ----------
TEST_IDX                     18

SQL> ANALYZE INDEX test_idx VALIDATE STRUCTURE;


Index analyzed.


SQL> SELECT height, blocks, lf_blks, br_blks, del_lf_rows, btree_space,pct_used FROM index_stats where name='TEST_IDX';


    HEIGHT     BLOCKS    LF_BLKS    BR_BLKS DEL_LF_ROWS BTREE_SPACE   PCT_USED

---------- ---------- ---------- ---------- ----------- ----------- ----------
         3       2304       2226          5       50000    17848160         90

SQL> SELECT n.name, round(s.value/1024) redo_kb FROM v$mystat s, v$statname n WHERE s.statistic# = n.statistic# AND n.name = 'redo 

size';

NAME                         REDO_KB

------------------------- ----------
redo size                     191288

SQL> ALTER INDEX test_idx REBUILD;


Index altered.


SQL> SELECT n.name, round(s.value/1024) redo_kb FROM v$mystat s, v$statname n WHERE s.statistic# = n.statistic# AND n.name = 'redo 

size';

NAME                         REDO_KB

------------------------- ----------
redo size                     208457

SQL> select 208457 - 191288 from dual;


208457-191288

------------
        17169

SQL> ANALYZE INDEX test_idx VALIDATE STRUCTURE;


Index analyzed.


SQL> SELECT height, blocks, lf_blks, br_blks, del_lf_rows, btree_space,pct_used FROM index_stats where name='TEST_IDX';


    HEIGHT     BLOCKS    LF_BLKS    BR_BLKS DEL_LF_ROWS BTREE_SPACE   PCT_USED

---------- ---------- ---------- ---------- ----------- ----------- ----------
         3       2176       2116          5           0    16968160         90

SQL> select segment_name,bytes/1024/1024 meg from dba_segments where segment_type='INDEX' and segment_name='TEST_IDX';


SEGMENT_NAME                MEG

-------------------- ----------
TEST_IDX                     17

SQL>


Index rebuild also reduced the leaf block by 110,at the same time it also reduced the number of block assigned to the index segment. But,the index rebuild operation generated much more redo than the other operations.


As discussed earlier,if there are numerous deletions through the index then rebuild is the right choice rather than coalesce/Shrink.Because if you tried to defragment a index which has been gone through a massive deletion using coalesce/shrink it will generate huge amount of redo to rearrange the leaf blocks than would have been generated by rebuild command. So if PCT_USED  falls below 25% it's always better to use REBUILD command to reallocate the unused space within the index.In that case rebuild will also helps to reduce the height of the index.


Reference:- 


http://www.dba-oracle.com/t_rebuild_index_online.htm


https://richardfoote.wordpress.com/2008/02/08/index-rebuild-vs-coalesce-vs-shrink-space-pigs-3-different-ones/


Ebook Expert Indexing in Oracle Database 11g

Sunday, March 4, 2018

Oracle nologging - does it generate redo?


Some SQL statements and operations support the use of a NOLOGGING clause. This does not mean that all operations against the object will be performed without generating redo log, just that some very specific operations will generate significantly less redo then normal.


All operations will generate some redo ‐ all data dictionary operations will be logged regardless of the logging mode.


Remember,It is not possible to roll forward through a point in time when an NOLOGGING operation has taken place. This can be a CREATE INDEX NOLOGGING, CREATE TABLE AS SELECT NOLOGGING, or an NOLOGGING table load. Let's say you created a table using nologging, using CREATE TABLE AS SELECT NOLOGGING and that table is started using by the application now. Here if the disk that the table is on fails we can't do a media recovery even if our database is running in archivelog mode. The problem here is the table was created using nologging option and it was not logged, is not recoverable from the archived redo log. So after doing any nologging operation DBA has to take a full backup immediately.


Note:- 


NOLOGGING does not prevent redo from being generated by all subsequent operations. In the above example, I did not create a table that is never logged. Only the single, individual operation of creating the table was not logged. All subsequent ʹnormalʹ operations such as INSERTs, UPDATEs, and DELETEs will be logged. Other special operations such as a direct path load using SQLLDR, or a direct path insert using the INSERT /*+ APPEND */ syntax will not be logged.


After performing NOLOGGING operations in an ARCHIVELOG mode database, you must take a new baseline backup of the affected data files as soon as possible.


There are two ways to use the NOLOGGING option.

1.by embedding the keyword NOLOGGING in the SQL command itself at the appropriate location.

2.Second method allows operations to be performed implicitly in a

NOLOGGING mode. For example, I can alter a table to be NOLOGGING by default. This means that subsequent direct path loads and direct path inserts performed which affect this table, will not be logged.
Remember If you use the APPEND hint and place the table in nologging mode, redo will be bypassed. 

3.It is always recommenced to schedule any nologging operation(such as alter table...move partition + alter index...rebuild) to take place immediately before a backup occurs


The operations that may be performed in a NOLOGGING mode are:



DML:
Direct-path INSERT (serial or parallel) resulting either from an INSERT or a MERGE statement. NOLOGGING is not applicable to any UPDATE operations resulting from the MERGE statement.
Direct Loader (SQL*Loader)
DDL:
CREATE TABLE … AS SELECT
CREATE TABLE … LOB_storage_clause … LOB_parameters … NOCACHE | CACHE READS
ALTER TABLE … LOB_storage_clause … LOB_parameters … NOCACHE | CACHE READS(to specify logging of newly created LOB columns)
ALTER TABLE … modify_LOB_storage_clause … modify_LOB_parameters … NOCACHE| CACHE READS (to change logging of existing LOB columns)
ALTER TABLE … MOVE
ALTER TABLE … (all partition operations that involve data movement)
ALTER TABLE … ADD PARTITION (hash partition only)
ALTER TABLE … MERGE PARTITIONS
ALTER TABLE … SPLIT PARTITION
ALTER TABLE … MOVE PARTITION
ALTER TABLE … MODIFY PARTITION … ADD SUBPARTITION
ALTER TABLE … MODIFY PARTITION … COALESCE SUBPARTITION
CREATE INDEX
ALTER INDEX … REBUILD
ALTER INDEX … REBUILD [SUB]PARTITION
ALTER INDEX … SPLIT PARTITION