1. Explain FORCE LOGGING feature in 9i.

By setting FORCE LOGGING to TRUE, all transactions will generate REDO. This is used for Data Guard, so no data will be missed (i.e. transactions that were run with NOLOGGING option) in Standby database

2. How can you tell if an index on particular table is USED or NOT USED in 9i?

By turning MONITORING ON that index and querying into INDEX_USAGE table

3. How do you put database is ARCHIVELOG mode, explain procedure?

1. Modify init.ora parameter START_ARCHIVE=TRUE
2. SQL> SHUTDOWN IMMEDIATE;
3. STARTUP MOUNT;
4. ALTER DATAVASE ARCHIVELOG;
5. ALTER DATABASE OPEN;

5. Explain procedure to Change CHARACTERSET of a database.

Can't change CHARACTERSET of a database, you will need to re-create the database with
appropriate CHARACTERSET.

7. How do you analyze table partition using Oracle provided package?

DBMS_STATS.GATHER_TABLE_STATS with GRANULARITY => 'PARTITION' OPTION

8. What is PGA_AGGREGATE_TARGET?

This parameter controls the maximum amount of memory PGA which can be used by the queries when WORKAREA_SIZE_POLICY is set to Auto.
The value you can be set in Bytes, kilobytes (K), megabytes (M) or gigabytes (G). The default value is 0
This parameter also has an effect on the execution plans of the cost based optimizer. The optimizer uses the value of the parameter PGA_AGGREGATE_TARGET to derive an estimate
for the minimum and maximum amount of memory which should be available at run-time for each sort, hash-join and bitmap operator in the query. Based on this minimum and maximum value, the
optimizer selects the best plan.

9. List five most important parameter in 9i affecting performance?

CURSOR_SHARING
DB_CACHE_SIZE
PGA_AGGREGATE_TARGET
DB_16K_CACHE_SIZE, DB_8K_CACHE_SIZE, DB_2K_CACHE_SIZE

10. Explain below wait events in STATSPACK report DB SCATTERED READ, DB SEQUENTIAL REAL, ENQUEUE?

DB SCATTERED READ - FULL TABLE SCAN
DB SEQUENTIAL REAL - IO
ENQUEUE - LOCKING

11. Process you follow to start looking into Performance issue at database level
(If the application is running very slow, at what points do you need to go about the database in
order to improve the performance?)

► Run a TOP command in Unix to see CPU usage (identify CPU killer processes)
► Run VMSTAT, SAR, and PRSTAT command to get more information on CPU and memory usage and possible blocking
► Run STATSPACK report to identify:
1. TOP 5 WAIT EVENTS
2. RESOURCE intensive SQL statements
► See if STATISTICS on affected tables needs to be re-generated
► IF poorly written statements are culprit, run a EXPLAIN PLAN on these statements and see whether new index or use of HINT brings the cost of SQL down.

12. How do you install Statspack?

By running $ORACLE_HOME/rdbms/admin/spcreate.sql script

15. what is the difference between group by and order by?

group by is used when we use aggregate functions on the columns in a query the other columns
should be in group by query
example:
select empno,ename,sum(sal) from emp
group by empno,ename
Order by is used to sort values either in ascending or descending order

16. How would you pass hints to the SQL processor?

USING COMMENT LINES WITH (+) SIGN YOU CAN PASS THE HINTS TO SQL ENGINE
For example
/* +PARALLEL( ) */

17. Do you need a commit after DDL statements?

DDL IS A AUTO COMMIT, YOU DON􀂶T NEED TO COMMIT AGAIN

18. What are the steps involved in Instance Recovery in Oracle?

Rolling forward to recover data that has not been recorded in data files, yet has been recorded
in the on-line redo log, including the contents of rollback segments.
Rolling back transactions that have been explicitly rolled back or have not been committed as
indicated by the rollback segments regenerated in step a.
Releasing any resources (locks) held by transactions in process at the time of the failure.
Resolving any pending distributed transactions undergoing a two-phase commit at the time of
the instance failure.

19. What is Oracle Log Switch?

The point at which ORACLE ends writing to one online redo log file and begins writing to another
is called a log switch.

20. What are the advantages of operating a database in ARCHIVELOG mode
over operating it in NO ARCHIVELOG mode?

Complete database recovery from disk failure is possible only in ARCHIVELOG mode.
Online database backup is possible only in ARCHIVELOG mode.

21. What is Oracle Archived Redo Log?

Archived Redo Log consists of Redo Log files that have archived before being reused.

22. What is Oracle Partial Backup?

A Partial Backup is any operating system backup short of a full backup, taken while the
database is open or shut down.

23. What is Oracle Full Backup?

A full backup is an operating system backup of all data files, on-line redo log files and control
file that constitute ORACLE database and the parameter.

24. What are the different modes of mounting a Database with the Parallel
Server?

Exclusive Mode If the first instance that mounts a database does so in exclusive mode, only
that Instance can mount the database.
Parallel Mode If the first instance that mounts a database is started in parallel mode, other
instances that are started in parallel mode can also mount the database.

25. What are the steps involved in Database Start up?

Start an instance, Mount the Database and Open the Database.

28. What is the advantage of specifying WITH GRANT OPTION in the GRANT
command?

The privilege receiver can further grant the privileges he/she has obtained from the owner to any
other user.

30. What is the use of the DROP option in the ALTER TABLE command?

It is used to drop constraints specified on the table.

32. How do I write a cron which will run a SQL query and mail the results to a
group?

Use DBMS_JOB for scheduling a cron job and DBMS_MAIL to send the results through email.

33. what is difference between Co-related sub query and nested sub query?

Co-related sub query is one in which inner query is evaluated only once and from that result outer
query is evaluated.
Nested query is one in which Inner query is evaluated for multiple times for getting one row of that
outer query.
ex. Query used with IN() clause is Co-related query.
Query used with = operator is Nested query

34. What is the difference between RAID 5 and RAID 10? Which is better for Oracle?

RAID 5 is striping with an extra disk for parity. If we lose a disk we can reconstruct from that parity disk.

RAID 10 is mirroring pairs of disks, and then striping across those sets.

RAID 5 was created when disks were expensive. Its purpose was to provide RAID on the cheap. If a disk fails, the IO subsystem will perform VERY slowly during the rebuild process. What's more your liklihood of failure increases dramatically during this period, with all the added weight of the rebuild. Even when it is operating normally RAID 5 is slow for everything but reading. Given that and knowing databases (especially Oracle's redo logs) continue to experience write activity all the time, we should avoid RAID5 in all but the rare database that is MOSTLY read activity. Don't put redologs on RAID5.

RAID10 is just all around goodness. If you lose one disk in a set of 10 for example, you could lose any one of eight other disks and have no troubles. What's more rebuilding does not impact performance at all since you're simply making a mirror copy. Lastly RAID10 perform exceedingly well in all types of databases.

35. What are the tradeoffs between many vs few indexes? When would you want to have many, and when would it be better to have fewer?

Fewer indexes on a table mean faster inserts/updates. More indexes mean faster, more specific WHERE clauses possibly without index merges.