OracleDBA interview questions

Public Yum Server

posted Apr 22, 2013, 8:43 AM by Sachchida Ojha

The Oracle public yum server offers a free and convenient way to install the latest Oracle Linux packages as well as packages from the Oracle VM installation media via a yum client.

You can download the full Oracle Linux and Oracle VM installation media via edelivery.oracle.com/linux. To stay current on errata updates, you may wish to subscribe to the Oracle Linux errata mailing list.

This yum server is offered without support of any kind. If you require support, please consider purchasing Oracle Linux Support via the online store, or via your sales representative.

Getting Started

  1. Download and Install Oracle Linux
  2. Download and copy the appropriate yum configuration file in place, by running the following commands as root:

    Oracle Linux 4, Update 6 or Newer

    # cd /etc/yum.repos.d
    # mv Oracle-Base.repo Oracle-Base.repo.disabled
    # wget http://public-yum.oracle.com/public-yum-el4.repo
    

    Oracle Linux 5

    # cd /etc/yum.repos.d
    # wget http://public-yum.oracle.com/public-yum-el5.repo
    

    Oracle Linux 6

    # cd /etc/yum.repos.d
    # wget http://public-yum.oracle.com/public-yum-ol6.repo
    

    Oracle VM 2

    # cd /etc/yum.repos.d
    # wget http://public-yum.oracle.com/public-yum-ovm2.repo
    
  3. Enable the appropriate repository by editing the yum configuration file

    • Open the yum configuration file in a text editor
    • Locate the section in the file for the repository you plan to update from, e.g. [el4_u6_base]
    • Change enabled=0 to enabled=1
  4. Begin using yum, for example:

    yum list

    yum install firefox

You may be prompted to confirm the import of the Oracle OSS Group GPG key.

Oracle external table just like external table in Greenplum

posted Feb 23, 2013, 9:10 AM by Sachchida Ojha   [ updated Feb 23, 2013, 5:08 PM ]

In the Greenplum database, we use external table combined with gpfdist to read the data from a  csv file located in in a directory and then then load that data in a regular table. This is a great feature of Greenplum database as gpfdist protocol uses MPP architecture to load the data. this is amazingly fast. I have tested it in an environment where I have a DCA connected with DIA.  The steps involved are

1. Place the .csv file in data directory of  DIA server.
2. start the gpfdist on DIA with the location same as csv file data directory.
3. Create external table and regular table in the greenplum database.
4. insert into <regular_table> select * from <external_table>.


After loading this data, I was curious if I can do the same thing in Oracle database. Oracle database can also use Oracle external table to load the data in the same way as Greenplum does. Here is the sample.

1. Create a directory in the oracle database and grant READ/WRITE priv to the user running the test.

CREATE OR REPLACE DIRECTORY mydir AS '/home/oracle/exttable';
GRANT READ ON DIRECTORY mydir TO sachi;
GRANT WRITE ON DIRECTORY mydir TO sachi;

2. Copy the .csv file to /home/oracle/exttable directory. Make sure that file is readable.

3. Create external table in oracle.  Here is the sample of 2 distinct set of data.

a)

056,baker,beth,mary
057,smith,teddy,john

2)

SCHEMA NAME                    TABLE NAME                     ROW COUNT                                                                                                                                                                                                                                     
------------------------------ ------------------------------                            ----------                                                                                                                                                                                                                                    
HR                       EMPLOYEE                                         504567 


External table for COMMA delimited file (a)

CREATE TABLE emp_load
    (employee_number      CHAR(3),
     employee_last_name   CHAR(20),
     employee_middle_name CHAR(15),
     employee_first_name  CHAR(15)
     )
  ORGANIZATION EXTERNAL
    (TYPE ORACLE_LOADER
     DEFAULT DIRECTORY mydir
     ACCESS PARAMETERS
       (RECORDS DELIMITED BY NEWLINE
        BADFILE mydir:'EMP.BAD'
        LOGFILE mydir:'EMP.LOG'
        FIELDS TERMINATED BY ','
       )
    LOCATION ('external_table_test1.dat')
)
REJECT LIMIT UNLIMITED;

SQL> select * from emp_load;

EMP EMPLOYEE_LAST_NAME   EMPLOYEE_MIDDLE EMPLOYEE_FIRST_
--- -------------------- --------------- ---------------
056 baker                beth            mary
057 smith                teddy           john

SQL>


External table for TAB delimited file (b)

CREATE TABLE tblcnt
(
SCHEMA_NAME CHAR(1028),
TABLE_NAME CHAR(1028),
ROW_COUNT CHAR(1028)
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY mydir
ACCESS PARAMETERS
(RECORDS DELIMITED BY NEWLINE
BADFILE mydir:'tablecount.BAD'
LOGFILE mydir:'tablecount.LOG'
FIELDS TERMINATED BY X'9'
)
LOCATION('tablecount.txt')
)
REJECT LIMIT UNLIMITED;

SQL> select * from tblcnt;

SCHEMA_NAME
----------------------------------------------------------------
TABLE_NAME
----------------------------------------------------------------
ROW_COUNT
--------------------------------
HR
EMPLOYEE
504567 

External table for fixed width file (c)

CREATE TABLE tblcnt1
(
SCHEMA_NAME CHAR(30),
TABLE_NAME CHAR(32),
ROW_COUNT CHAR(1028)
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY mydir
ACCESS PARAMETERS
(RECORDS DELIMITED BY NEWLINE
BADFILE mydir:'tablecount.BAD'
LOGFILE mydir:'tablecount.LOG'
FIELDS TERMINATED BY X'9' 
        (
SCHEMA_NAME POSITION(1:30),
TABLE_NAME POSITION(31:60),
ROW_COUNT   POSITION(61:1024)
)
)
LOCATION('tablecount.txt')
)
REJECT LIMIT UNLIMITED;

SQL> select * from tblcnt1;

SCHEMA_NAME
----------------------------------------------------------------
TABLE_NAME
----------------------------------------------------------------
ROW_COUNT
--------------------------------
HR
EMPLOYEE
504567 


RESUMABLE SPACE ALLOCATION

posted Feb 4, 2012, 12:00 AM by Sachchida Ojha   [ updated Feb 4, 2012, 12:01 AM ]

Oracle database provides a way to suspend long-running operations in the event of space allocation failure. Once the DBA is notified and space issue has been corrected, the long running operation can resume and complete. No need to restart the long running operations.

There are 3 types of space management problem that can be addressed with RESUMABLE SPACE ALLOCATION.

1. Out of space in the tablespace - (Common issue during impdp from upper environment to lower environment)

2. Maximum extents reached in the segment

3. Spece Quota exceeded for a user

DBA can automatically make statement resumable by setting init.ora parameter RESUMABLE_TIMEOUT = 5000 (value >0). This value is in seconds.

It can also be done in the session level,

SQL> alter session enable resumable timeout 5000;

SQL> desc dba_resumable;
 Name                               Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 USER_ID                            NUMBER
 SESSION_ID                            NUMBER
 INSTANCE_ID                            NUMBER
 COORD_INSTANCE_ID                        NUMBER
 COORD_SESSION_ID                        NUMBER
 STATUS                             VARCHAR2(9)
 TIMEOUT                            NUMBER
 START_TIME                            VARCHAR2(20)
 SUSPEND_TIME                            VARCHAR2(20)
 RESUME_TIME                            VARCHAR2(20)
 NAME                                VARCHAR2(4000)
 SQL_TEXT                            VARCHAR2(1000)
 ERROR_NUMBER                            NUMBER
 ERROR_PARAMETER1                        VARCHAR2(80)
 ERROR_PARAMETER2                        VARCHAR2(80)
 ERROR_PARAMETER3                        VARCHAR2(80)
 ERROR_PARAMETER4                        VARCHAR2(80)
 ERROR_PARAMETER5                        VARCHAR2(80)
 ERROR_MSG                            VARCHAR2(4000)







SQL> select  USER_ID, INSTANCE_ID, STATUS, NAME, ERROR_MSG from dba_resumable;

no rows selected

SQL>



SYSAUX Tablespace

posted Feb 3, 2012, 11:22 PM by Sachchida Ojha   [ updated Feb 3, 2012, 11:24 PM ]

SYSAUX Tablespace was introduced in Oracle 10g. The SYSAUX tablespace is an auxiliary tablespace to the SYSTEM tablespace. Many database components use the SYSAUX tablespace as their default location to store data. Therefore, the SYSAUX tablespace is always created during database creation or database upgrade.
Unlike SYSTEM tablespace if SYSAUX tablespace is unavailable database will not go down but some of the features may not be available.


SQL> desc V$SYSAUX_OCCUPANTS 
 Name                       Null?    Type
 ----------------------------------------- -------- ----------------------------
 OCCUPANT_NAME                        VARCHAR2(64)
 OCCUPANT_DESC                        VARCHAR2(64)
 SCHEMA_NAME                        VARCHAR2(64)
 MOVE_PROCEDURE                     VARCHAR2(64)
 MOVE_PROCEDURE_DESC                    VARCHAR2(64)
 SPACE_USAGE_KBYTES                    NUMBER

The SYSAUX tablespace provides a centralized location for database metadata that does not reside in the SYSTEM tablespace. It reduces the number of tablespaces created by default, both in the seed database and in user-defined databases.

During normal database operation, Oracle Database does not allow the SYSAUX tablespace to be dropped or renamed. Transportable tablespaces for SYSAUX is not supported.

SQL> select OCCUPANT_NAME,OCCUPANT_DESC,SPACE_USAGE_KBYTES from V$SYSAUX_OCCUPANTS ;

OCCUPANT_NAME             OCCUPANT_DESC              SPACE_USAGE_KBYTES
-------------------------------- -------------------------------- ------------------
LOGMNR                 LogMiner                    8064
LOGSTDBY             Logical Standby                1408
SMON_SCN_TIME             Transaction Layer - SCN to TIME        3200
                 mapping

PL/SCOPE             PL/SQL Identifier Collection            1600
STREAMS              Oracle Streams                 1024
AUDIT_TABLES             DB audit tables                   0
XDB                 XDB                          131008
AO                 Analytical Workspace Object Tabl           46208
                 e

XSOQHIST             OLAP API History Tables               46208
XSAMD                 OLAP Catalog                    7488
SM/AWR                 Server Manageability - Automatic           73280
                  Workload Repository

SM/ADVISOR             Server Manageability - Advisor F        8448
                 ramework

SM/OPTSTAT             Server Manageability - Optimizer           14272
                  Statistics History

SM/OTHER             Server Manageability - Other Com        6144
                 ponents

STATSPACK             Statspack Repository                   0
SDO                 Oracle Spatial                    46016
WM                 Workspace Manager                3136
ORDIM                 Oracle Multimedia ORDSYS Compone         448
                 nts

ORDIM/ORDDATA             Oracle Multimedia ORDDATA Compon           11584
                 ents

ORDIM/ORDPLUGINS         Oracle Multimedia ORDPLUGINS Com           0
                 ponents

ORDIM/SI_INFORMTN_SCHEMA     Oracle Multimedia SI_INFORMTN_SC           0
                 HEMA Components

EM                 Enterprise Manager Repository               49280
TEXT                 Oracle Text                    3584
ULTRASEARCH             Oracle Ultra Search                   0
ULTRASEARCH_DEMO_USER         Oracle Ultra Search Demo User               0
EXPRESSION_FILTER         Expression Filter System            3712
EM_MONITORING_USER         Enterprise Manager Monitoring Us         512
                 er

TSM                 Oracle Transparent Session Migra           0
                 tion User

SQL_MANAGEMENT_BASE         SQL Management Base Schema            1728
AUTO_TASK             Automated Maintenance Tasks             320
JOB_SCHEDULER             Unified Job Scheduler                 448

31 rows selected.

SQL>


BIGFILE TABLESPACE

posted Feb 3, 2012, 4:07 PM by Sachchida Ojha

Bigfile tablespace eases database administration because it consist of only one datafile. The single datafile can be up to 128TB in size if the tablespace block size is 32KB.

CREATE BIGFILE TABLESPACE bigtbs DATAFILE '/u01/oracle/data/bigftbs01.dbf' SIZE 50G

Bigfile tablespaces are supported only for locally managed tablespaces with automatic segment
space management, with three exceptions: locally managed undo tablespaces, temporary tablespaces,
and the SYSTEM tablespace.

Pseudocolumns

posted Feb 2, 2012, 7:38 PM by Sachchida Ojha

A pseudocolumn behaves like a table column, but is not actually stored in the table. You can select from pseudocolumns, but you cannot insert, update, or delete their values. A pseudocolumn is also similar to a function without arguments. However, functions without arguments typically return the same value for every row in the result set, whereas pseudocolumns typically return a different value for each row.

DBA_PART_KEY_COLUMNS AND DBA_SUBPART_KEY_COLUMNS

posted Feb 1, 2012, 12:36 PM by Sachchida Ojha

 
DBA_PART_KEY_COLUMNS describes the partitioning key columns for all partitioned objects in the database. Its columns are the same as those in ALL_PART_KEY_COLUMNS.

DBA_SUBPART_KEY_COLUMNS lists subpartitioning key columns for all composite-partitioned tables (and local indexes on composite-partitioned tables) in the database. Its columns are the same as those in ALL_SUBPART_KEY_COLUMNS.
 
select * from DBA_PART_KEY_COLUMNS where OWNER='SYS';
select * from DBA_SUBPART_KEY_COLUMNS where OWNER='SYS';

Maintaining statistics on large partitioned tables

posted Jan 31, 2012, 8:09 AM by Sachchida Ojha   [ updated Jan 31, 2012, 8:11 AM ]

- When queries access a single partition with stale or non-existent partition level statistics I get a sub optimal plan due to "Out of Range" values
- Global statistics collection is extremely expensive in terms of time and system resources

Published in Oracle Blog - By an user in Optimizer development team

This article will describe both of these issues and explain how you can address them both in Oracle Database 10gR2 and 11gR1.

Out of Range Large tables are often decomposed into smaller pieces called partitions in order to improve query performance and ease of data management. The Oracle query optimizer relies on both the statistics of the entire table (global statistics) and the statistics of the individual partitions (partition statistics) to select a good execution plan for a SQL statement. If the query needs to access only a single partition, the optimizer uses only the statistics of the accessed partition. If the query access more than one partition, it uses a combination of global and partition statistics.

"Out of Range" means that the value supplied in a where clause predicate is outside the domain of values represented by the [minimum, maximum] column statistics. The optimizer prorates the selectivity based on the distance between the predicate value and the maximum value (assuming the value is higher than the max), that is, the farther the value is from the maximum value, the lower the selectivity will be. This situation occurs most frequently in tables that are range partitioned by a date column, a new partition is added, and then queried while rows are still being loaded in the new partition. The partition statistics will be stale very quickly due to the continuous trickle feed load even if the statistics get refreshed periodically. The maximum value known to the optimizer is not correct leading to the "Out of Range" condition. The under-estimation of selectivity often leads the query optimizer to pick a sub optimal plan. For example, the query optimizer would pick an index access path while a full scan is a better choice.

The "Out of Range" condition can be prevented by using the new copy table statistics procedure available in Oracle Database10.2.0.4 and 11g. This procedure copies the statistics of the source [sub] partition to the destination [sub] partition. It also copies the statistics of the dependent objects: columns, local (partitioned) indexes etc. It adjusts the minimum and maximum values of the partitioning column as follows; it uses the high bound partitioning value as the maximum value of the first partitioning column (it is possible to have concatenated partition columns) and high bound partitioning value of the previous partition as the minimum value of the first partitioning column for range partitioned table. It can optionally scale some of the other statistics like the number of blocks, number of rows etc. of the destination partition.

Assume we have a table called SALES that is ranged partitioned by quarter on the SALES_DATE column. At the end of every day data is loaded into latest partition. However, statistics are only gathered at the end of every quarter when the partition is fully loaded. Assuming global and partition level statistics (for all fully loaded partitions) are up to date, use the following steps in order to prevent getting a sub-optimal plan due to "out of range".

1. Lock the table statistics using LOCK_TABLE_STATS procedure in DBMS_STATS. This is to avoid interference from auto statistics job.

EXEC DBMS_STATS.LOCK_TABLE_STATS('SH','SALES');

2. Before beginning the initial load into each new partition (say SALES_Q4_2000) copy the statistics from the previous partition (say SALES_Q3_2000) using COPY_TABLE_STATS. You need to specify FORCE=>TRUE to override the statistics lock. 

EXEC DBMS_STATS.COPY_TABLE_STATS ('SH', 'SALES', 'SALES_Q3_2000', 'SALES_Q4_2000', FORCE=>TRUE);

Expensive global statistics collection

In data warehouse environment it is very common to do a bulk load directly into one or more empty partitions. This will make the partition statistics stale and may also make the global statistics stale. Re-gathering statistics for the effected partitions and for the entire table can be very time consuming. Traditionally, statistics collection is done in a two-pass approach:

  • In the first pass we will scan the table to gather the global statistics
  • In the second pass we will scan the partitions that have been changed to gather their partition level statistics.
The full scan of the table for global statistics collection can be very expensive depending on the size of the table. Note that the scan of the entire table is done even if we change a small subset of partitions.

In Oracle Database 11g, we avoid scanning the whole table when computing global statistics by deriving the global statistics from the partition statistics. Some of the statistics can be derived easily and accurately from partition statistics. For example, number of rows at global level is the sum of number of rows of partitions. Even global histogram can be derived from partition histograms. But the number of distinct values (NDV) of a column cannot be derived from partition level NDVs. So, Oracle maintains another structure called a synopsis for each column at the partition level. A synopsis can be considered as sample of distinct values. The NDV can be accurately derived from synopses. We can also merge multiple synopses into one. The global NDV is derived from the synopsis generated by merging all of the partition level synopses. To summarize


  1. Gather statistics and create synopses for the changed partitions only
  2. Oracle automatically merges partition level synopses into a global synopsis
  3. The global statistics are automatically derived from the partition level statistics and global synopses

Incremental maintenance feature is disabled by default. It can be enabled by changing the INCREMENTAL table preference to true. It can also be enabled for a particular schema or at the database level. If you are interested in more details of the incremental maintenance feature, please refer to the following paper presented in SIGMOD 2008 and to our previous blog entry on new ndv gathering in 11g.

Assume we have table called SALES that is range partitioned by day on the SALES_DATE column. At the end of every day data is loaded into latest partition and partition statistics are gathered. Global statistics are only gathered at the end of every month because gathering them is very time and resource intensive. Use the following steps in order to maintain global statistics after every load.

1 -Turn on incremental feature for the table. 

EXEC DBMS_STATS.SET_TABLE_PREFS('SH','SALES','INCREMENTAL','TRUE');

2 -At the end of every load gather table statistics using GATHER_TABLE_STATS command. You don't need to specify the partition name. Also, do not specify the granularity parameter. The command will collect statistics for partitions with stale or missing statistics and update the global statistics based on the partition level statistics and synopsis.

EXEC DBMS_STATS.GATHER_TABLE_STATS('SH','SALES');
Note: that the incremental maintenance feature was introduced in Oracle Database 11g Release 1. However, we also provide a solution in Oracle Database10g Release 2 (10.2.0.4) that simulates the same behavior. The 10g solution is a new value, 'APPROX_GLOBAL AND PARTITION' for the GRANULARITY parameter of the GATHER_TABLE_STATS procedures. It behaves the same as the incremental maintenance feature except that we don't update the NDV for non-partitioning columns and number of distinct keys of the index at the global level. For partitioned column we update the NDV as the sum of NDV at the partition levels. Also we set the NDV of columns of unique indexes as the number of rows of the table. In general, non-partitioning column NDV at the global level becomes stale less often. It may be possible to collect global statistics less frequently then the default (when table changes 10%) since approx_global option maintains most of the global statistics accurately.

Let's take a look at an example to see how you would effectively use the Oracle Database 10g approach.
After the data load is complete, gather statistics using DBMS_STATS.GATHER_TABLE_STATS for the last partition (say SALES_11FEB2009), specify granularity => 'APPROX_GLOBAL AND PARTITION'. It will collect statistics for the specified partition and derive global statistics from partition statistics (except for NDV as described before).

EXEC DBMS_STATS.GATHER_TABLE_STATS ('SH', 'SALES', 'SALES_11FEB2009', GRANULARITY => 'APPROX_GLOBAL AND PARTITION');

It is necessary to install the one off patch for bug 8719831 if you are using the above features in 10.2.0.4 (patch 8877245) or in 11.1.0.7 (patch 8877251)

ORACLE 11G EXTENDED COMPOSITE PARTITIONING

posted Jan 29, 2012, 7:30 PM by Sachchida Ojha

Oracle database 11g extends composite partitioning adding the following composite partitioning methods.

1. Composite range-range partitioning : The partition method will partition data using the range method. Then for each partition, subpartition will be created using range method.

2. Composite list-range partitioning : The partition method will partition data using the list method. Then for each partition, subpartition will be created using range method.

3. Composite list-hash partitioning : The partition method will partition data using the list method. Then for each partition, subpartition will be created using hash method.

4. Composite list-list partitioning : The partition method will partition data using the list method. Then for each partition, subpartition will be created using list method.

Earth Day 2011

DOP - DEGREE OF PARALLELISM

posted Jan 28, 2012, 8:21 PM by Sachchida Ojha   [ updated Jan 29, 2012, 5:57 AM ]

CONFIGURING PARALLEL PROCESSING - DETERMINING THE DEGREE OF PARALLELISM and PARALLEL CONFIGURATION PARAMETERS
Oracle tries to automate the configuration of the system to maximize the performance of parallel operations, However there's still  a lot of scope for manual tweaking. Increasing the DOP beyond and optimal point fails to result in further performance increase.

DETERMINING THE DEGREE OF PARALLELISM (DOP): An optimal DOP is critical for good  parallel performance. Oracle determines the DOP as follows.

1. If parallel execution is indicated or requested, but no DOP is specified, the default DOP is set to twice the no of CPU cores on the system. For a RAC system, the DOP will be twice the number of cores in the entire cluster. This default is controlled by the configuration parameter PARALLEL_THREADS_PER_CPU.

2. From Oracle 11g R2 forward, if PARALLEL_DEGREE_POLICY is set to auto, Oracle will adjust the DOP depending on the nature of the operation to be performed and the sizes of the objects involved.

3. If PARALLEL_ADAPTIVE_MULTI_USER is set to TRUE, Oracle will adjust the DOP based on the overall load on the system. When the system is more heavily loaded , the DOP will be reduced.

4. if PARALLEL_IO_CAP is set to TRUE
in Oracle 11g or higher, Oracle will limit the DOP to that which the IO subsystem can support. These IO subsystem limit can be calculated by using the procedure DBMS_RESOURCE_MANAGER.CALIBRATE_IO.

5. A DOP
can be specified at the table or index level by using PARALLEL clause of CREATE TABLE, CREATE INDEX, ALTER TABLE, or ALTER INDEX.

6.
A PARALLEL hint can be used to specify the DOP for a specific table within a query.

7.
Regardless of any other setting, the DOP can not exceed that which can be supported by PARALLEL_MAX_SERVERS.  For most SQL statements, the no of servers required will be twice the requested DOP.


NOTE: For ad-hoc query execution, you might want to set an explicit DOP. However, for SQL embedded within an application, this might not be good idea because the SQL will be less able to adapt to change in machine configuration (more CPU for instance), workload(more concurrent sessions), or configuration(change in the no of parallel slaves or default DOP). For embedded SQL, it's probably better to omit an explicit DOP or to use the AUTO keyword (in Oracle 11g R2 and higher).











1-10 of 19