6 Replies Latest reply: Feb 8, 2010 10:01 AM by smon RSS

    Database Architect Interview questions


      We have a requirement for Database Architect ( Developement perspective). Can anyone help me in getting the toughest SQL / PL/SQL interview questions( Architecture wise).

        • 1. Re: Database Architect Interview questions
          One basic show stopper question comes to mind: You are dealing with a 3 tier architecture - in what tier do you implement the business rules and why?

          If he/she does not answer PL/SQL in Oracle, and want to implement it in the external application tier, that turns Oracle into a "+single application+" system as any access from any other system will bypass the business rules and validation layer... and open a huge hole where data can be turned into garbage in the database.

          This means the person fails to grasp the very fundamentals of client-server (the app tier is the client, the database the server). The person does not understand the basic concepts of today's RDBMS , nor how to utilise it to provide a sound and robust foundation for data integrity, performance and scalability.
          • 2. Re: Database Architect Interview questions
            Here are a few...

            0. Draw me a picture of Oracle on that white board. Show me what processes/threads there are and what their jobs are. Describe all of the files involved in the database, what they do, what they mean. Show how they interact.

            Expected Answer: This picture drawing and the resulting discussion is very revealing. BTW, this is covered quite thoroughly in lesson one of the first day of Oracle DBA training. (Thanks to Tom Kyte for this question)

            1. What is a mutating table error and how can you get around it?
            Level: Intermediate

            Expected Answer: This happens with triggers. It occurs because the trigger is trying to update a row it is currently using. The usual fix involves either use of views or temporary tables so the database is selecting from one while updating the other.
            Better Answer: This is a bug in the update process. Review and correct the process so you no longer have to concurrently read and update a given row.

            2. What is a “Snapshot too old” exception and how do you avoid, or prevent, them:
            Expected Answer: Occurs when the redo required for a read consistent view of the data is not longer available. Avoid or prevent by increasing the number and size of redo segments so that the longest running transaction can be contained in one segment. – or – Increase the undo retention period to a value >= the longest running transaction in the database and make sure you have sufficient disk to support that much redo.

            3. What effect will interim commits in a select (or cursor) loop used for updating records have on a “Snapshot too old” error?
            Expected Answer: Increase the probability of encountering the error. The greater the commit frequency, the greater the probability of encountering this error. Should commit only once after all work is complete.

            4. You are using hot backup without being in archivelog mode, can you recover in the event of a failure? Why or why not?

            Expected answer: You can’t use hot backup without being in archivelog mode. So no, you couldn’t recover.

            5. A developer is trying to create a view and the database won’t let him. He has the “DEVELOPER” role which has the “CREATE VIEW” system privilege and SELECT grants on the tables he is using, what is the problem?

            Expected answer: You need to verify the developer has direct grants on all tables used in the view. You can’t create a stored object with grants given through roles.

            6. How can you find out how many users are currently logged into the database? How can you find their operating system id?

            Expected answer: There are several ways. One is to look at the v$session or v$process views. Another way is to check the current_logins parameter in the v$sysstat view. Another if you are on UNIX is to do a “ps -ef|grep LOCAL=wc -l|grep -v grep’ command, but this only works against a single instance installation.

            7. You are joining a local and a remote table, the network manager complains about the traffic involved, how can you reduce the network traffic?

            Expected answer: Push the processing of the remote data to the remote instance by using a view to pre-select the information for the join. This will result in only the data required for the join being sent across.
            Also, can use the DRIVING_SITE hint

            8. What is tkprof how is it used, and how do you make it easy for developers to use.

            Expected answer: The tkprof tool is a tuning tool used to determine cpu and execution times for SQL statements. You use it by first setting timed_statistics to true in the initialization file and then turning on tracing for either the entire database via the sql_trace parameter or for the session using the ALTER SESSION command. Once the trace file is generated you run the tkprof tool against the trace file and then look at the output from the tkprof tool. If you get an answer that indicates that the candidate does not know what tkprof is or that developers to not need this facility, end the interview and get a new candidate.

            9. What effect will chained (due to migration) rows have on a full table scan.
            Expected answer: None. Oracle will skip over the chain pointer because it will ‘get to the row’ eventually during the scan.

            10. When looking at v$sysstat you see that sorts (disk) is high. Is this bad or good? If bad -How do you correct it?

            Expected answer: If you get excessive disk sorts this is bad. This indicates you need to tune the sort area parameters in the initialization files. The major sort parameter is the SORT_AREA_SIZE parameter.

            11. When should you increase copy latches? What parameters control copy latches?

            Expected answer: When you get excessive contention for the copy latches as shown by the “redo copy” latch hit ratio. You can increase copy latches via the initialization parameter LOG_SIMULTANEOUS_COPIES to twice the number of CPUs on your system.

            12. If you see contention for library caches how can you fix it?

            Expected answer: Increase the size of the shared pool.

            13. You see the following on a status report:

            redo log space requests 23
            redo log space wait time 0

            Is this something to worry about? What if redo log space wait time is high? How can you fix this?

            Expected answer: Since the wait time is zero, no. If the wait time was high it might indicate a need for more or larger redo logs.

            14. When is it acceptable to run a database in NOARCHIVELOG mode?

            Expected answer: For some test instances that can be easily refreshed from another source, like production, or some data warehouses that are incrementally loaded. If you get the answer that all databases can be run NOARCHIVELOG, end the interview and find another candidate.

            15. What is the Oracle “Duty Manager”, and what can he do for you?
            Expected answer: The DM is in charge of the Oracle Support technicians at a given support centre and shift. Among other things, he can escalate a SR, change support engineer, and help expedite an action plan. He is your first contact at Oracle Support if you are not getting satisfactory support from your current engineer/technician.

            16. The sample anonymous block…
            BEGIN proc_a

            < do some stuff here >

            WHEN OTHERS THEN
            P_OUT_VAL := 1;
            P_OUT_MSG := SQLERRM;
            dbms_output.put_line('Error: '||SQLERRM);
            END proc_a;

            Q: when will the dbms_output... statement execute?
            a) immediately after the control returns from the RAISE_APPLICATION_ERROR() call.
            b) immediately before the proc returns control to the calling proc/user.
            c) never.
            d) whenever the RAISE_APPLICATION_ERROR() call fails.

            Expected answer: “C” – never. Once control passes to the RAISE_APPLICATION_ERROR function, it will not return – the application will terminate.

            Some questions that are more open ended…

            17. Tell me how backups work, how recovery works. Walk through various scenarios and tell me what would happen. For example, we are archivelog mode -- hot backup every sunday. It is wednesday and we lost the disk with the users tablespace on it. What do you do now... and so on.

            18. What errors did you hit during your most recent database recovery? How did you handle them?

            19. What was the most difficult technical obstacle you encountered during your last project? How did you deal with it?

            Ask for a recent ‘writing’ such as a proposal, status report, or etc. so that you can evaluate candidate’s ability to write clearly and concisely.
            • 3. Re: Database Architect Interview questions
              Somehow i feel those questions are more relevant to an database administrator then to an architect....
              • 4. Re: Database Architect Interview questions
                Is not a database architect someone who sets up a database - storage, memory, backup policy, initialization parameters, and so forth? And a data architect someone who figures out tables, indexes, integrity constraints, and how they all relate to each other? Seem to me that my questions are directed toward a database architect - knowing a bit about how it works. I agree that a couple of questions are more developer and DBA directed, because it is fairly general list. Still, a good database architect should have no trouble with them.
                • 5. Re: Database Architect Interview questions
                  I tend to rate an architect more on the abstract level side than the technical side. Kind of like an architect and a builder that does a building.

                  The architect does the design, the builder and his team (DBA & developers) implement it. Thus the architect will deal with more abstract and conceptual issues - like how to apply the technology instead of how to configure it and technically implement it.

                  For example, deciding on how data validation and data integrity is to be implemented. Where the boundaries are between presentation layer, application layer and database layer, what languages are going to be used and how. If it a web based system, what the rendering engine will be and where will it reside? How to ensure that the server side layers can scale - where the application layer is a server to the presentation/interface layer, and that in turn is the client to the database layer.
                  • 6. Re: Database Architect Interview questions
                    ajallen wrote:
                    Is not a database architect someone who sets up a database - storage, memory, backup policy, initialization parameters, and so forth? And a data architect someone who figures out tables, indexes, integrity constraints, and how they all relate to each other?
                    what do your DBAs do? :)