Reference:http://www.iheavy.com/node/87
1. What is the difference between RMAN and a traditional hotbackup?
RMAN is faster, can do incremental (changes only) backups, and does not place tablespaces into hotbackup mode.

2. What are bind variables and why are they important?
With bind variables in SQL, Oracle can cache related queries a single timein the SQL cache (area). This avoids a hard parse each time, which saves onvarious locking and latching resources we use to check objects existence andso on. BONUS: For rarely run queries, especially BATCH queries, we explicitely DO NOT want to use bind variables, as they hide information from the Cost Based Opitmizer.BONUS BONUS: For batch queries from 3rd party apps like peoplesoft, if wecan't remove bind variables, we can use bind variable peeking!

3. In PL/SQL, what is bulk binding, and when/how would it help performance?
Oracle's SQL and PL/SQL engines are separate parts of the kernel whichrequire context switching, like between unix processes. This is slow, anduses up resources. If we loop on an SQL statement, we are implicitelyflipping between these two engines. We can minimize this by loading ourdata into an array, and using PL/SQL bulk binding operation to do it all inone go!

4. Why is SQL*Loader direct path so fast?
SQL*Loader with direct path option can load data ABOVE the high water markof a table, and DIRECTLY into the datafiles, without going through the SQLengine at all. This avoids all the locking, latching, and so on, anddoesn't impact the db (except possibly the I/O subsystem) at all.

5. What are the tradeoffs between many vs few indexes? When would you wantto have many, and when would it be better to have fewer?
Fewer indexes on a table mean faster inserts/updates. More indexes meanfaster, more specific WHERE clauses possibly without index merges.

6. What is the difference between RAID 5 and RAID 10? Which is better forOracle?
RAID 5 is striping with an extra disk for parity. If we lose a disk we canreconstruct from that parity disk. RAID 10 is mirroring pairs of disks, andthen striping across those sets.
RAID 5 was created when disks were expensive. Its purpose was to provideRAID on the cheap. If a disk fails, the IO subsystem will perform VERYslowly during the rebuild process. What's more your liklihood of failureincreases dramatically during this period, with all the added weight of therebuild. Even when it is operating normally RAID 5 is slow for everythingbut reading. Given that and knowing databases (especially Oracle's redologs) continue to experience write activity all the time, we should avoidRAID5 in all but the rare database that is MOSTLY read activity. Don't putredologs on RAID5.
RAID10 is just all around goodness. If you lose one disk in a set of 10 forexample, 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'resimply making a mirror copy. Lastly RAID10 perform exceedingly well in alltypes of databases.

7. When using Oracle export/import what character set concerns might comeup? How do you handle them?
Be sure to set NLS_LANG for example to "AMERCIAN_AMERICA.WE8ISO8859P1". Ifyour source database is US7ASCII, beware of 8-bit characters. Also be waryof multi-byte characters sets as those may require extra attention. Alsowatch export/import for messages about any "character set conversions" whichmay occur.

8. How do you use automatic PGA memory management with Oracle 9i and above?
Set the WORKAREA_SIZE_POLICY parameter to AUTO and set PGA_AGGREGATE_TARGET

9. Explain two easy SQL optimizations.
a. EXISTS can be better than IN under various conditionsb. UNION ALL is faster than UNION (not sorting)

10. Name three SQL operations that perform a SORT.
a. CREATE INDEXb. DISTINCTc. GROUP BYd. ORDER BYf. INTERSECTg. MINUSh. UNIONi. UNINDEXED TABLE JOIN

11. What is your favorite tool for day-to-day Oracle operation?
Hopefully we hear some use of command line as the answer!

12. What is the difference between Truncate and Delete? Why is one faster?Can we ROLLBACK both? How would a full table scan behave after?
Truncate is nearly instantaenous, cannot be rolled back, and is fast becauseOracle simply resets the HWM. When a full table scan is performed on atable, such as for a sort operation, Oracle reads to the HWM. So if youdelete every single solitary row in 10 million row table so it is now empty,sorting on that table of 0 rows would still be extremely slow.

13. What is the difference between a materialized view (snapshot) fastrefresh versus complete refresh? When is one better, and when the other?
Fast refresh maintains a change log table, which records change vectors, notunlike how the redo logs work. There is overhead to this, as with a tablethat has a LOT of indexes on it, and inserts and updates will be slower.However if you are performing refreshes often, like every few minutes, youwant to do fast refresh so you don't have to full-table-scan the sourcetable. Complete refresh is good if you're going to refresh once a day.Does a full table scan on the source table, and recreats the snapshot/mview.Also inserts/updates on the source table are NOT impacted on tables wherecomplete refresh snapshots have been created.

14. What does the NO LOGGING option do? Why would we use it? Why would webe careful of using it?
It disables the logging of changes to the redologs. It does not disable ALLLOGGING, however as Oracle continues to use a base of changes, for recoveryif you pull the plug on the box, for instance. However it will causeproblems if you are using standby database. Use it to speed up operations,like an index rebuild, or partition maintenance operations.

15. Tell me about standby database? What are some of the configurations ofit? What should we watch out for?
Standby databases allow us to create a copy of our production db, fordisaster recovery. We merely switch mode on the target db, and bring it upas read/write. Can setup as master->slave or master->master. The latterallows the former prod db to become the standby, once the failure cause isremedied. Watch out for NO LOGGING!! Be sure we're in archivelog mode.