Oracle DBA Questions

posted Aug 2, 2011, 4:26 PM by Sachchida Ojha   [ updated Feb 4, 2012, 11:10 PM ]
1.SQL Monitoring and Tuning
  • Monitoring Real-Time SQL Execution
  • Displaying a Query’s Progress
  • Determining SQL Work Left
  • Identifying Resource-Intensive SQL Statements
  • Using Oracle Performance Reports to Identify Resource-Intensive SQL
  • Using Operating System to Identify Resource-Intensive Queries
  • Displaying an Execution Plan Using AUTOTRACE
  • Generating an Execution Plan Using DBMSS_XPLAN
  • Tracing All SQL Statements for a Session
  • Interpreting an Execution Plan
  • Obtaining SQL Tuning Advice
  • Forcing Your Own Execution Plan On a Query
  • Viewing Optimizer Statistics
  • Generating Statistics
2.Database Troubleshooting
  • Displaying Open Cursors
  • Determining if Online REDO Logs are Sized Properly
  • Displaying Table Fullness
  • Monitoring Index Usage
  • Auditing Object Usage

3.Partitioning
  • Determining if a Table Should be Partitioned
  • Partitioning Types
  • Generating Statistics for a Partition


4.Managing Tablespaces
a.Locally Managed vs. Dictionary Managed

5.Managing Indexes
a.What is Fast Full Index Scan vs. Index Range Scan

6.Managing SQL Operation
a.Unix Server Load
b.Index Usage
c.Compare History Plan

7.Temporary Tables vs. Global Temporary Tables
a.Collect Statistics on Global Temporary Tables

8.What is Statistics Locking

9.Wait Events
a.What are Wait Events
b.What are the common Wait Events

10.CDC

11. DataPump
a.Remap Data
b.Encryption
c.Compression

12.11g New INIT Parameters

13.What are Invoker Rights and Definer Rights
UNIX
1. Shell
   a. Current Shell
   b. Change Shell
2. Debug Shell Script
3. How to pass variables in a script
4. What is a Tee Command
5. Difference Between Find/Grep
6. Major UNIX/LINUX Commands

14. AWR report
  • Know AWR report very well
  • What is the AWR report used for?
  • How do you generate an AWR report?  For example: snap id, html or txt, etc?
  • How do you read AWR report?
  • What’s important info in the AWR report or give an example how to analysis the report?
  • Know the details of the statistics information presented in the AWR report. For example:
  1. What’s db file scattered read?
  2. What’s db file sequential read?
  3. What’s wait event and how do you analysis it?

15. Data Pump
  • Know the concept of data pump very well
  • What's the fundamental difference between imp/exp vs. data pump?
  • When you import data, which step takes the most time?
  • How can you speed up the data pump process or make it go faster?

16. Shell Script
  • When you write a shell script, how do you know which shell you are using?
  • How do you know how many arguments passed or what’s the output of previous code?
  • Know the basic shell scripting and be ready to give exact command or examples.

17. Debug in Unix/Linux
  • How do you know or how do you debug the code in UNIX shell scripting?
  • How do you do trig in debug (example: trig/flag)?

18. Execution Plan
  • What is execution plan?
  • How do you analysis it or use it?

19. PL/SQL
Review it and know the logic behind and syntax well.

20. Performance tuning
  • Be prepared for any performance tuning questions such as how to tune SQL query

21. Partitioning
  • how to partitioning and how to index
  • What’s the different type of partitioning and what’s new in 11g new features?



Comments