Home » SQL & PL/SQL » SQL & PL/SQL » Oracle ,d2k Interview Questions
| Oracle ,d2k Interview Questions [message #27444] |
Fri, 03 October 2003 03:23  |
Gurusubramanyam
Messages: 79
Registered: July 2001
|
Member |
|
|
1) What are the Back ground processes in Oracle and what are they.
There are basically 9 Processes but in a general system we need to mention the first five background processes. They do the house keeping activities for the Oracle and are common in any system.
The various background processes in oracle are
a) Data Base Writer(DBWR) :: Data Base Writer Writes Modified blocks from Database buffer cache to Data Files. This is required since the data is not written whenever a transaction is committed.
b)LogWriter(LGWR) :: LogWriter writes the redo log entries to disk. Redo Log data is generated in redo log buffer of SGA. As transaction commits and log buffer fills, LGWR writes log entries into a online redo log file.
c) System Monitor(SMON) :: The System Monitor performs instance recovery at instance startup.This is useful for recovery from system failure
d)Process Monitor(PMON) :: The Process Monitor performs process recovery when user Process fails. Pmon Clears and Frees resources that process were using.
e) CheckPoint (CKPT) :: At Specified times, all modified database buffers in SGA are written to data files by DBWR at Checkpoints and Updating all data files and control files of database to indicate the most recent checkpoint
f)Archieves(ARCH) :: The Archiver copies online redo log files to archival storage when they are busy.
g) Recoveror(RECO) :: The Recoveror is used to resolve the distributed transaction in network
h) Dispatcher (Dnnn) :: The Dispatcher is useful in Multi Threaded Architecture
i) Lckn :: We can have upto 10 lock processes for inter instance locking in parallel sql.
2) How many types of SQL Statements are there in Oracle
2) There are basically 6 types of sql statements.They are
a) Data Definition Language(DDL) :: The DDL statements define and maintain objects and drop objects.
b) Data Manipulation Language(DML) :: The DML statements manipulate database data.
c) Transaction Control Statements :: Manage change by DML
d) Session Control :: Used to control the properties of current session enabling and disabling roles and changing .e.g. :: Alter statements, Set Role
e) System Control Statements :: Change Properties of Oracle Instance .e.g.:: Alter System
f) Embedded SQL :: Incorporate DDL,DML and T.C.S in Programming Language. E.g.: Using the SQL Statements in languages such as 'C', Open, Fetch, execute and close
3) What is a Transaction in Oracle?
3) A transaction is a logical unit of work that compromises one or more SQL Statements executed by a single User. According to ANSI, a transaction begins with first executable statement and ends when it is explicitly committed or rolled back.
4) Key Words Used in Oracle
4) The Key words that are used in Oracle are:
a) Committing: A transaction is said to be committed when the transaction makes permanent changes resulting from the SQL statements.
b) Rollback: A transaction that retracts any of the changes resulting from SQL statements in Transaction.
c) SavePoint :: For long transactions that contain many SQL statements, intermediate markers or savepoints are declared. Savepoints can be used to divide a transaction into smaller points.
d) Rolling Forward: Process of applying redo log during recovery is called rolling forward.
e) Cursor: A cursor is a handle (name or a pointer) for the memory associated with a specific statement. A cursor is basically an area allocated by Oracle for executing the SQL Statement. Oracle uses an implicit cursor statement for Single row query and Uses Explicit cursor for a multi row query.
f) System Global Area (SGA): The SGA is a shared memory region allocated by the Oracle that contains Data and control information for one Oracle Instance. It consists of Database Buffer Cache and Redo log Buffer.
g) Program Global Area (PGA): The PGA is a memory buffer that contains data and control information for server process.
g) Database Buffer Cache: Database Buffer of SGA stores the most recently used blocks of database data. The set of database buffers in an instance is called Database Buffer Cache.
h) Redo log Buffer: Redo log Buffer of SGA stores all the redo log entries.
I) Redo Log Files: Redo log files are set of files that protect altered database data in memory that has not been written to Data Files. They are basically used for backup when a database crashes.
j) Process: A Process is a 'thread of control' or mechanism in Operating System that executes series of steps.
5) What are procedures ,functions and Packages ?
5) Procedures and functions consist of set of PL/SQL statements that are grouped together as a unit to solve a specific problem or perform set of related tasks.
Procedures do not Return values while Functions return one value
Packages :: Packages Provide a method of encapsulating and storing related procedures, functions, variables and other Package Contents
6) What are Database Triggers and Stored Procedures ?
6) Database Triggers :: Database Triggers are Procedures that are automatically executed as a result of insert in, update to, or delete from table.
Database triggers have the values old and new to denote the old value in the table before it is deleted and the new indicated the new value that will be used. DT are useful for implementing complex business rules which cannot be enforced using the integrity rules. We can have the trigger as Before trigger or After Trigger and at Statement or Row level.
e.g.:: operations insert, update ,delete 3
before ,after 3*2 A total of 6 combinations
At statement level(once for the trigger) or row level( for every execution ) 6 * 2 A total of 12.
Thus a total of 12 combinations are there and the restriction of usage of 12 triggers has been lifted from Oracle 7.3 Onwards.
Stored Procedures :: Stored Procedures are Procedures that are stored in compiled form in the database. The advantage of using the stored procedures is that many users can use the same procedure in compiled and ready to use format.
7) How many Integrity Rules are there and what are they
7) There are Three Integrity Rules. They are as follows ::
a) Entity Integrity Rule :: The Entity Integrity Rule enforces that the Primary key cannot be Null
b) Foreign Key Integrity Rule :: The FKIR denotes that the relationship between the foreign key and the primary key has to be enforced. When there is data in Child Tables the Master tables cannot be deleted.
c) Business Integrity Rules :: The Third Integrity rule is about the complex business processes which cannot be implemented by the above 2 rules.
8) What are the Various Master and Detail Relation ships.
8) The various Master and Detail Relationship are
a) NonIsolated :: The Master cannot be deleted when a child is existing.
b) Isolated :: The Master can be deleted when the child is existing
c) Cascading :: The child gets deleted when the Master is deleted.
9) What are the Various Block Coordination Properties
9) The various Block Coordination Properties are
a) Immediate
Default Setting. The Detail records are shown when the Master Record are shown.
b) Deffered with Auto Query
Oracle Forms defer fetching the detail records until the operator navigates to the detail block.
c) Deffered with No Auto Query
The operator must navigate to the detail block and explicitly execute a query
10) What are the Different Optimization Techniques
10) The Various Optimization techniques are
a) Execute Plan :: we can see the plan of the query and change it accordingly based on the indexes
b) Optimizer_hint ::
set_item_property('DeptBlock',OPTIMIZER_HINT,'FIRST_ROWS');
Select /*+ First_Rows */ Deptno,Dname,Loc,Rowid from dept
where (Deptno > 25)
c) Optimize_Sql ::
By setting the Optimize_Sql = No, Oracle Forms assigns a single cursor for all SQL statements. This slow downs the processing because for everytime the SQL must be parsed whenever they are executed.
f45run module = my_firstform userid = scott/tiger optimize_sql = No
d) Optimize_Tp ::
By setting the Optimize_Tp= No, Oracle Forms assigns seperate cursor only for each query SELECT statement. All other SQL statements reuse the cursor.
f45run module = my_firstform userid = scott/tiger optimize_Tp = No
11) How do u implement the If statement in the Select Statement
11) We can implement the if statement in the select statement by using the Decode statement.
e.g. select DECODE (EMP_CAT,'1','First','2','Second'Null);
Here the Null is the else statement where null is done .
12)How many types of Exceptions are there
12) There are 2 types of exceptions. They are
a) System Exceptions
e.g. When no_data_found, When too_many_rows, Dup_Val_on_index, Value_Error,Others
b) User Defined Exceptions
e.g. My_exception exception
When My_exception then
13) What are the inline and the precompiler directives
13) The inline and precompiler directives detect the values directly
14) How do you use the same lov for 2 columns
14) We can use the same lov for 2 columns by passing the return values in global values and using the global values in the code
15) How many minimum groups are required for a matrix report
15) The minimum number of groups in matrix report are 4
16) What is the difference between static and dynamic lov
Optimizer and Statistics
Optimizers & Statistics
1. What are the Two keys to get good performance out of SQL statements?
„Ï Choosing the correct optimizer
„Ï Maintaing the current statistics
2. What do you mean by Optimizer?
Oracle's functionality which defines algorithms to retrieve the data
From oracle database against the SQL Statements.
3. What do you mean by Execution Plan?
The algorithm for retrieving data is referred as execution plan.
4. What are all the optimizers which oracle supports?
„Ï Rule Based Optimizers
„Ï Cost Based Optimizers
5. What do you mean by Rule Based Optimizer?
Rule Based Optimizer checks for the Joins & the usage of the indexed columns in the WHERE Clause to determine the access path to the data.
6.What are disadvantages of using Rule Based Optimizer?
„Ï It makes no attempt to compare the amount of Disk I/O & CPU requirements for different Execution Plans
„Ï In many Cases it won¡¦t choose the best execution plan for the query.
„Ï Changing the order of tables listed in the FROM clause can have disastrous effect on Performance because it chooses the execution plan depends on how the SELECT statement is written.
6. What do you mean by Cost Based Optimizer?
Cost Based optimizer chooses its execution plan based on the estimated cost of various alternative approaches. The COST is estimated by the amount of Disk I/O and various CPU Resources that a given Execution Plan expected to consume. If the cost based optimizer is used then the statistics should be generated regularly.
7. How to get the better performance from Cost Based Optimizer?
Gather Statistics for the Tables & Indexes and keep them current.
8. Cost Based Optimizers support for which new features of Oracle?
„Ï Index organized tables
„Ï Star Joins
„Ï Parallel Execution
9. How to setup the default optimizer for a database?
„Ï In the init.ora (Database Parameter) File place an entry
OPTIMIZER_MODE = CHOOSE
„Ï Statistics should be generated for atleast one of the tables involved in the query otherwise oracle will start using the Rule Based Optimizer.
10. What are the possible values for the Optimizer Modes?
Sr.No Mode Use
1. CHOOSE Enables Cost Based Optimizer using the statistics are Present
2. RULE Enables Rule Based Optimizer
3. FIRST_ROWS Enables Cost Based Optimizer with returning first few rows quickly even if leads to the overall Higher costs
4. ALL_ROWS Enables Cost Based Optimizer with Low cost (Same as CHOOSE)
1. How to Override the Optimizer Mode in Session Level?
SQL> ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS;
2. How to Override the Optimizer Mode in Statement Level?
We can specify the Optimizer Mode at the individual statement level through Optimizer Hints - /*+ hint */
SQL> SELECT /*+ ALL_ROWS */ EMPID, ENAME FROM EMP WHERE MGR IS NULL;
3. How to ensure that the given Optimizer Hint is working or Not?
By using EXPLAIN PLAN to view oracles execution plan for that statement.
4. What do you mean by Statistics?
The information for generating the execution path for a cost based optimizer is known as Statistics. The information used is
„Ï How many rows are there in table
„Ï How big the Indexes are.
„Ï How closely the Order of data in a table matches the order in an index.
5. How to Generate the Statistics for Tables, Indexes & Columns?
„Ï By using the ANALYZE Command we can generate the statistics for Tables, Indexes & Columns. For Tables & Indexes oracle will automatically generate statistics during the execution of ANALYSE command and for Columns Explicitly we need to specify in ANALYSE command. ANALYZE command can be used for only one table at a time.
„Ï By Using the DBMS_UTILITY package, which contains procedure, that analyzes an entire schema with One command.
16. What are Data Dictionary Views that are used to obtain the statistics?
Sr.No Object Data Dictionary Views
1. TABLES DBA_TABLES,ALL_TABLES,USER_TABLES
2. INDEXES DBA_INDEXES,ALL_ INDEXES,USER_ INDEXES
3. COLUMNS DBA_TAB_COLUMNS,ALL_TAB_COLUMNS,
USER_TAB_COLUMNS
17. What are the Column names corresponding to the TABLE statistics in Data dictionary Views?
Sr.
No Column Names Statistic
1. NUM_ROWS Number of rows in the table
2. BLOCKS Number of Datablocks currently being used for Data
3. EMPTY_BLOCKS Number of blocks allocated to the table but never been used for data
4. AVG_SPACE The average amount of free space (Bytes) within each block
5. CHAIN_CNT The number of chained rows
6. AVG_ROW_LEN The Average length of the rows in Table (Bytes)
7. LAST_ANALYSED The Date on which the statistics for the table were generated.
18. What are the Column names corresponding to the INDEX statistics in Data dictionary Views?
Sr.
No Column Names Statistic
1. BLEVEL The Depth of the Index or the Number of Levels from Root to Leaf
2. LEAF_BLOCKS The Number of Leaf Blocks which are the blocks containing pointers to the rows in table, in the index
3. DISTINCT_KEYS The Number of distinct index values
4. AVG_LEAF_BLOCKS_PER_KEY The average number of leaf blocks containing entries for one value
5. AVG_DATA_BLOCKS_PER_KEY The Number of Datablocks ,on average, Pointed by one index value
6. CLUSTERING_FACTOR Indicates How closely the Order of the Order of Rows in the Table happens to match the ordering in the Index
7. LAST_ANALYSED The Date on which the statistics for the Index were generated.
19. What are the Column names corresponding to the COLUMN statistics in Data dictionary Views?
Sr.
No Column Names Statistic
1. NUM_DISTINCT Number of Distinct Values contained in the column
2. LOW_VALUE The lowest value in the column (First 32 Bytes)
3. HIGH_VALUE The highest value in the column (First 32 Bytes)
4. DENSITY The Column¡¦s Density
5. NUM_NULLS Number of rows that contains NULL values
6. NUM_BUCKETS The Number of Buckets in the column¡¦s histogram
7. LAST_ANALYSED The Date on which the statistics for the Index were generated.
20. What do you mean by HEIGHT-BALANCED HISTOGRAMS for Column Statistics?
Oracle first determines how many buckets (NUM_BUCKETS) to use. Oracle devides the number of rows (NUM_ROWS) in the Table by the number of buckets (NUM_BUCKETS) to get value for number of rows to place in each bucket and records the highest value in each bucket. Oracle Generates histograms in ALL_TAB_HISTOGRAMS data dictionary view. Oracle generates height-balanced histograms to record the distribution of values within a column.
|
|
|
|
|
|
Goto Forum:
Current Time: Thu Aug 21 01:25:51 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
