数据管理员面试7个顶级问题
Seven Interview Questions for Data Base Administrators
--------------------------------------------------------------------------------
1. Why is a UNION ALL faster than a UNION?
The union operation, you will recall, brings two sets of data together. It will not, however, produce duplicate or redundant rows. To perform this feat of magic, a SORT operation is done on both tables. This is obviously computationally
intensive, and uses significant memory as well.
A UNION ALL conversely just dumps collection of both sets together in random order, not worrying about duplicates.
--------------------------------------------------------------------------------
2. What are some advantages to using Oracle's CREATE DATABASE statement to create a new database manually?
• You can script the process to include it in a set of install scripts you deliver with a product.
• You can put your create database script in CVS for version control, so as you make changes or adjustments to it, you can track them like you do changes to software code.
• You can log the output and review it for errors.
• You learn more about the process of database creation, such as what options are available and why.
--------------------------------------------------------------------------------
3. What are three rules of thumb to create good passwords? How would a DBA enforce those rules in Oracle? What business challenges might you encounter?
Typical password cracking software uses a dictionary in the local language, as well as a list of proper names, and combinations thereof to attempt to guess unknown passwords. Since computers can churn through tens of thousands of attempts quickly, this can be a very effective way to break into a database. A good password therefore should not be a dictionary word; it should not be a proper name, birthday, or other obvious guessable information. It should also be of sufficient length, such as eight to 10 characters, including upper and lowercase, special characters, and even alternate characters if possible.
Oracle has a facility called password security profiles. When installed they can enforce complexity, and length rules as well as other password related security measures.
In the security arena, passwords can be made better, and it is a fairly solvable problem. What about in the real world? Often the biggest challenge is in implementing a set of rules like this in the enterprise. There will likely be a lot of resistance to this, as it creates additional hassles for users of the system who may not be used to thinking about security seriously. Educating business folks about the real risks, by coming up with real stories of vulnerabilities and break-ins you've encountered on the job, or those discussed on the internet goes a long way towards emphasizing what is at stake.
--------------------------------------------------------------------------------
4. Describe the Oracle Wait Interface, how it works, and what it provides. What are some limitations? What do the db_file_sequential_read and db_file_scattered_read events indicate?
The Oracle Wait Interface refers to Oracle's data dictionary for managing wait events. Selecting from tables such as v$system_event and v$session_event give you event totals through the life of the database (or session). The former are totals for the whole system, and latter on a per session basis. The event db_file_sequential_read refers to single block reads, and table accesses by rowid. db_file_scattered_read conversely refers to full table scans. It is so named because the blocks are read, and scattered into the buffer cache.