I passed today (May 31, 2000) the forth Exam of the Certified Oracle8 DBA Series: 1Z0-014 "Oracle8: Performance Tuning Workshop". The passing score is 48 out of 61 questions, but I got 57 correct. The passing score (and the number of questions) is slightly different for each exam, and this one is the "hardest". For the database administration exam (the second), you have to get only 43 out of 63 right (68%). Probably they think that in order to be effective in performance tuning, you need to know almost everything, whereas not every DBA is exposed to all the things they test the database administration exam. The technical things about the exam are as for the previous ones: I called Sylvan Prometric (1-800-891-3926) to schedule an appointment, said the code "OCP2K" which I got from the Oracle Technology Network email list and which gave me a 30% discount (unfortunately only valid until today if I understood correctly), so I paid only $87.50 instead of $125. I did the test in Riverhead Training downtown (Park Building, on the opposite site of the bus stop back to the University, 355 Fifth Avenue nearly on the corner to Smithfield street). There are other places where you can take the test, e.g. there is another location downtown, see the prometric website (www.2test.com). It is even possible to do it in Germany, although not in every city. You have 90 minutes time, which is more than enough. For instance, I didn't know the answer to four questions (probably I got three of them wrong, plus one other unexpected one). But what should I do? The exam is completely closed book. So I read it again carefully, eliminated the certainly wrong answers, and then made a guess. Once another question helped me. I didn't know whether the dynamic performance view was called V$FILESTAT or V$IOSTAT (they had both as answers). But then V$FILESTAT appeared in another question, so I knew that this was the right name (there is no view V$IOSTAT). You also shouldn't be afraid of checking an obvious answer. Sometimes the main keyword for the answer is already contained in the question, and although it is so easy, it is correct (sometimes also all except one answer are obviously wrong). However, there are also cases where the obvious answer is wrong. For instance, one might expect that LOG_SIMULTANEOUS_COPIES determines the number of log buffers or log buffer copies etc. But this is wrong. It determines the number of copy latches for the redo log buffer. But you should not eliminate an answer just because it is "too easy". It is good to take notes about questions where you are not sure about the answer. Sometimes this helps later to identify the questions which you actually got wrong, sometimes it doesn't. You are supplied with a piece of paper and a pen, but you cannot take any notes home. The test software doesn't show you which questions you got wrong and what would have been the correct answer, but at the end you can click on section scores to see in which areas you lost points. By the way, when you click on the next button to see the next question, do not do this at the lower boundary of the window. You can make the window smaller, but the test software does not allow the mouse to leave the window, so you cannot make it larger again. Of course, there is a scrollbar, so that it is no real problem, but it is a bit annoying. I prepared again by using the book of Couchman and by investing $99 for 300 example questions from selftestsoftware.com. I didn't like some of the questions, and the explanations are often very short or not real explanations but only a rewording of the correct answer. I also read part of the Oracle8 Tuning Manual, and used the Oracle8 Reference Manual for looking up the definition of initialization parameters and V$ views. I looked at many V$ views in SQL Plus. Teaching INFSCI 2711 was a very good preparation. I estimate that about 20 questions could be directly answered with what we have learnt in 2711, and also it gave some foundation for the rest. But I wish we would have talked more about tuning the memory structures, such as the shared pool, the buffer cache, the redo log buffer. Also, we should have talked more about rollback segments and how the backup and recovery (with the redo log files) works. We also should have talked more about tuning tools, such as utlbstat/utlestat and SQL Trace. I didn't have Oracle Expert which is a kind of expert system for tuning. Oracle sells the "Oracle Tuning Pack" separately. I do not know yet on which CD it is and whether it is possible to get it from the software licencing services. Maybe it comes with the enterprise version. Here is the list of topics of my test: 1. Business Requirements and Tuning (2 questions) 2. Oracle Alert, Trace Files, and Events (3 questions) 3. Utilities and Dynamic Performance Views (3 questions) 4. Tuning Considerations for Different Applications (2 questions) 5. SQL Tuning (4 questions) 6. Generic Operating System Tuning Issues and Oracle (2 questions) 7. Tuning the Shared Pool (6 questions, I got one wrong) 8. Tuning the Buffer cache (7 questions, I got one wrong) 9. Tuning the Redo Log Buffer (3 questions) 10. Database Configuration and I/O Issues (4 questions) 11. Using Oracle Blocks Efficiently (6 questions) 12. Optimize Sort Operations (5 questions) 13. Rollback Segment Tuning (5 questions) 14. Monitoring and Detecting Lock Contention (3 questions, I got one wrong) 15. Latch and Contention Issues (3 questions) 16. Tuning with Oracle Expert (3 questions, I got one wrong) - You should know quite a lot of dynamic performance views in the data dictionary, e.g.: - V$SORT_SEGMENT - V$FILESTAT - V$SESSION_EVENT - V$SYSTEM_EVENT - V$EVENT_NAME - V$WAIT_STAT - V$LATCH - V$LATCHNAME - V$LATCHHOLDER - V$LOCK - V$SQLAREA - V$SESSION - V$TRANSACTION - V$SYSSTAT - V$SESSTAT - V$DB_OBJECT_CACHE - V$ROWCACHE - V$CACHE (Parallel Server) - V$SESSION_WAIT - V$RECENT_BUCKET - V$CURRENT_BUCKET - V$LIBRARYCACHE 9 questions in my exam required that you know these or other V$ views. Sometimes you also need to know column names (at least approximately) and e.g. names of events in V$SYSTEM_EVENT. - You should know a lot of initialization parameters, e.g. - TIMED_STATISTICS - PRE_PAGE_SGA - MAX_DUMP_FILE_SIZE - DB_FILE_MULTIBLOCK_READ_COUNT - SQL_DIRECT_WRITES - LOG_SIMULTANEOUS_COPIES - LOG_SMALL_ENTRY_MAX_SIZE - SHARED_POOL_SIZE - LAST_FAILURE_SIZE ? - SHARED_POOL_RESERVED_MIN_ALLOC - SORT_DIRECT_WRITES - DB_BLOCK_SIZE - DB_BLOCK_BUFFERS - DB_BLOCK_LRU_STATISTICS - DB_BLOCK_LRU_EXTENDED_STATISTICS 12 questions in my test required that you know these or other parameters. - You should know some event names in V$SYSTEM_EVENT, e.g. - buffer busy waits - The alert.log file appeared in some example queries. This is easy. - You should know some shorthands, e.g. - MTS: Multi-Threaded Server (opposite to one dedicated server process for each user) - DSS: Decision Support System (which is considered more or less opposite to OLTP) (lots of long queries, possibly many ad-hoc queries, very few updates/inserts/deletes) (sometimes also OLTP, DSS, OLAP, Data Warehouse is distinguished) - OLTP: Online Transaction Processing (lots of inserts and updates) - SEP: Sort Extend Pool (used for finding an extend in a segment in a temporary tablespace for sorting) - SGA - PGA - UGA User Global Area (is created in the MTS configuration and part of the shared pool, and therefore the SGA. It contains session and cursor state information) - You should know a few percentages: - The hit ratio for latches should be less than 1% (i.e. only one in a hundred times a process has to wait for this type of internal lock) - The ratio of disk sorts to memory sorts should be less than 5% - You should know the DBMS_SHARED_POOL package and how to pin objects (triggers, packages) in the shared pool. - Oracle wants you to know the right order of tuning. You begin with the business rules, then the database design (e.g. denormalization, then the application (subdivided into application design, index selection, SQL statement and DB interface tuning, index tuning), then the memory, then the IO, then contention/locks, and finally the OS. (Please check this sequence yourself. It differs e.g. in the book by Couchman and the Oracle tuning manual.) The idea is that whatever comes first in this sequence can give a larger reward for less work, and also things that come earlier influence things which come later. E.g. under "memory" you will tune the various caching mechanisms, and this will reduce the IO, so that maybe less IO tuning is necessary or at least the distribution of IO to the various tablespaces has changed. - You need to know how to use utlbstat.sql and utlestat.sql and that the output is written to report.txt - You need to know the alert file and what is written there. - You should now the maximum number of redo copy latches (two per CPU) and that there is only one redo allocation latch. - You should know the different buffer pools (DEFAULT, KEEP, RECYCLE). - Direct path loading in NOARCHIVELOG mode generates no redo information, in ARCHIVELOG mode it generates minimal redo information to mark the blocks into which the data was loaded as logically corrupt should a media failure occur (in NOARCHIVELOG mode, the database cannot be recovered from media failures). I hope I understood this correctly. In the example questions, there was one where you had to distinguish between minimal redo information and no redo information. You must also specify NOLOGGING in the table creation (for ARCHIVELOG mode). Actually, even in NOARCHIVELOG mode, the data dictionary changes are logged. Oracle is quite proud of their direct path feature, so it appears in every exam except the first. - Another thing Oracle is proud about is the support for star queries in data warehouse applications. - You should know how many rollback segments Oracle recommends (For OLTP systems: one per 4 concurrent transactions, but 4 if less than 16 concurrent transactions, and 8 if less than 32 concurrent transactions i.e. rounded to the next multiple of 4 if less than 32 concurrent trans.) (this rule is also only good for up to 200 concurrent transactions ...) (For batch intensive systems: One per transaction.) You should also know about the number of extents in rollback segments etc. - You should know which SQL constructs need a sort operation (we did this extensively in 2711). - You should know that committing work regularly saves space in the rollback segments and reduces the chances of lock contention. It is also good to avoid using table locks if lock contention is a problem. - The shared pool should have about 250 bytes per open cursor. - In case of a deadlock, Oracle only rolls back the statement. However, you should roll back the transaction, or you risk getting into an infinite loop of deadlocks. Deadlocks are reported in the alert.log file (?), but the rowid of the row which caused the problem is shown only (?) in a trace file. - You should know the difference between IMMEDIATE and WILLING_TO_WAIT requests for a latch and which columns in V$LATCH reflect which kind of request. You should also know that requests for copy latches are immediate. - You should know that the high water mark for a table does not change if all rows are deleted with DELETE (in contrast to TRUNCATE). We talked about this in 2711. - You should know that there can be only one LRU latch for the buffer pool for each 50 buffers (this is the maximum number of LRU latches compared to the buffer size). - You should know PCTUSED, PCTFREE, and migrated or chained rows and the ANALYZE TABLE command (we did all this in detail in 2711). - You should know that indexes and tables should be put into separate tablespaces (on separate disks) because the are often inserted into and read from simultaneously (especially the insert is clear). - You should know that only the data dictionary should be stored in the SYSTEM tablespace (and the system rollback segment). The system tablespace is not good for temporary segments since this will lead to fragmentation. Basically, Oracle recommends that you have at least tablespaces for SYSTEM (data dictionary), DATA (Tables), INDEX, RBS (Rollback Segments), TEMP (temporary segments). - Reasons why rollback segments should be in their own tablespace: - Fragmentation can occur - When data is changed in the tables, heavy IO is done on the tables, the rollback segments, and the redo log (actually, on the tables with a slight delay when buffer space is needed or a checkpoint occurs). So it would be good if these three things would be stored on three different disks. - You cannot take a tablespace offline if it contains active rollback segments. - You should know about the DBMS_APPLICATION_INFO package. - You should know that using few (best: 1) extend for a table is good. Performance especially degrades if more than one I/O is needed to read the extend map. - You should know that INSERT statements need very little rollback space because only the rowid is stored. DELETE statements need the most rollback space. - You should also know about the configuration of temporary tablespaces. - You should know e.g. the following commands: ALTER SYSTEM KILL SESSION (if a table is locked and the person is gone) ALTER TABLE ALLOCATE EXTENT (for manual striping) ALTER SYSTEM FLUSH SHARED POOL EXPLAIN PLAN ALTER TABLE ... CACHE, PCTUSED, PCTFREE ... CREATE INDEX ... NOSORT ANALYZE TABLE ... COMPUTE STATISTICS / VALIDATE .. I didn't check the correct syntax, so some of this commands might be wrong. Especially it is difficult for me to distinguish between ALTER SYSTEM and ALTER DATABASE (system refers to the processes, database to the files?).