Hoje teremos várias questões para testar seus conhecimentos em Performance Tuning como DBA Oracle. As questões são relativamente antigas, por isso pode haver alguma questão que não seja aplicável à versão mais recente do Oracle Database.

  1. A tablespace has a table with 300 extents in it. Is this bad? Why or why not?
  2. How do you set up tablespaces during an Oracle installation?
  3. You use multiple fragments in the SYSTEM tablespace. What should you check first?
  4. What are the indications that you need to increase or decrease the shared_pool size parameter?
  5. What are the general guidelines for sizing DB_BLOCK_SIZE and DB_FILE_MULTIBLOCK_READ_COUNT for an application that does many table scans?
  6. What is the fastest query method of a table in the RULE based optimizer?
  7. Explain the use of TKPROF. What OS parameter should be set to get full TKPROFF output?
  8. When looking at V$SYSSTAT you see that sorts (disk) is high. Is that bad or good? If bad – how do you correct it?
  9. When should you increase copy latches? What parameter controls copy latches?
  10. Where can you get a list of all initialization parameters for your system? How about if they are the default settings or have been changed?
  11. Describe hit ratios as pertains to the database buffers. What is the difference between an instantaneous and cumulative hit ratio? Which one should you use for tuning?
  12. Discuss row chaining. How does it happen? How do you correct it?
  13. When looking at the estat events report you see that you are getting buffer busy waits. Is this bad/ How can you find what is causing it?
  14. If you see contention for library caches how can you fix it?
  15. If you see statistics that deal with UNDO, what are they really talking about?
  16. If a tablespace has a default PCTINCREASE of 0, what will it cause (in relation to SMON).
  17. If a tablespace shows excessive fragmentation, what are the methods to de-fragment the tablespace?
  18. How can you tell if the tablespace has excessive fragmentation?
  19. You see the following on your status report: redo log space requests – 23, redo log space wait time – 0. Is this something to worry about? What is the redo log wait time is high? How can you fix this?
  20. What can cause a high value for recursive calls? How can this be fixed?
  21. If you see a pin hit ratio of less than 0.8 in the estat library report – is this a problem? If so, how do you resolve it?
  22. If you see a high value for reloads in estat library cache report, is this a matter for concern?
  23. You look at the DBA_ROLLBACK_SEGS and see that there is a large number of shrinks and they are relatively small in size. Is this a problem? How can this be fixed?
  24. You look at the DBA_ROLLBACK_SEGS and see a large number of wraps. Is this a problem?
  25. You have a room to grow extents by 20%. Is there a problem? Should you take any action?
  26. You see multiple extents in the temporary tablespace. Is this good or bad?


Amanhã divulgaremos as respostas!

Milton Bastos é DBA Oracle e Desenvolvedor PL/SQL, dividido entre Apucarana/PR e Curitiba/PR. Certificações: OCA (Oracle 11g DBA Certified Associate), Oracle Database 11g Data Warehousing Certified Implementation Specialist, Oracle Database 11g Sales Specialist Assessment, Oracle Database Appliance PreSales Specialist Assessment, Oracle Database Appliance Sales Specialist Assessment