When do we rebuild an Index and Table https://mwidlake.wordpress.com/2009/11/18/depth-of-indexes-on-vldbs/ https://community.oracle.com/thread/589369?tstart=0 http://www.orafaq.com/node/2903 – SQL vs Oracle rebuild Depth/height of index – Blevel + 1. Blevel = branch+root, not leaf. Ex: Blevel 3 = Depth is 4, which has a rowid pointer to original table, So the IO taken to read a data is 5. For IOT only 4, since leaf and data is same. select max(blevel) from dba_indexes; select index_name,blevel,leaf_blocks from dba_indexes where owner=’PNNORPT’ and index_name like ‘SLM_STEP%’; select owner,index_name from dba_indexes where blevel=3; SELECT t.table_name, i.index_name, t.blocks, t.num_rows, i.clustering_factor,max(blevel) FROM DBA_tables t, DBA_indexes i WHERE t.table_name = i.table_name AND i.index_name like ‘SLM_STEP’ group by t.table_name, i.index_name, t.blocks, t.num_rows,…
-
-
Oracle DBA table needs to know and maintenance job-29
DBA Tables needs to know To know the database properties and parameters. Show parameter <Parameter name> Select * from v$parameters; – Parameres Select name from v$controlfile; – Control file Select * from v$logfile; – Members info Select * from v$log; – Log size Archive log list; – Archive details Sql> select * from v$sgastat – SGA size Sql> select * from v$pgastat; – PGA size sql> select * from v$tablespace; – Tablespace size sql> select * from dba_tablespaces; – Tablespace size sql> select * from v$datafile; – Datafile size sql> select * from dba_data_files; – Datafile size Rename data file Code Offline tablespace alter tablespace <ts name>…
-
Oracle Locking and isolation levels-28
Locking DML locking: It acquire locks at both the table level and the row level. Row_locking Always – Low level locking – Row lock – Keyword: TX Intent – High level locking – table lock – Keyword: TM Row level lock- DML operations Table level lock – when this a DDL & DML. Table locks do not affect concurrency of DML. Table lock modes Row share lock (RS) — ROW SHARE permits concurrent access to the locked table but prohibits users from locking the entire table for exclusive access. Row exclusive lock (RX) — ROW EXCLUSIVE is the same as ROW SHARE, but it also prohibits locking in SHARE mode…
-
Oracle Data Guard standby-27
Data Guard It’s a standby database. A copy of primary database. Oracle Data Guard ensures high availability, data protection, and disaster recovery for enterprise data. Primary and standby will be in sync mode. Standby is used for high availability. It will reduce the recovery and downtime and used for D/R. Technologies in standby data guard Physical standby (Redo apply technologies P–Archive–S) Logical Standby (SQL statement will run) Switchover (Changing) Failover (For server shutdowns) Data guard broker (Third server for witness) Use of broker: Data guard broker will be managed as centralized. The switchover and failover will be automated. We need separate listener for data guard broker. A primary can have…
-
Oracle ASM Automatic Storage Management-26
ASM – Automatic Storage Management It’s an oracle 10g feature. ASM is same as normal database instead of storing a DB files on normal filesystem. It will store a file an ASM managed file system, which will give disk redundancy. It only prevents a disk failure not the instance failure. ASM uses a disk group to store the oracle files. A disk group is a collection of disks. It supports single-instance and (Oracle RAC) configurations. ASM only supports for external disk. External disk should be a raw disk, mostly a RAID. “ ASM is a volume manager and a file system for Oracle database files that supports single-instance Oracle…