Oracle DBA - Oracle Optimization

Page : 1 2 3 4 5 6 7 8 9 10 11

Analyzing Materialized Views for Fast Refresh

This article shows how materialized views can be analyzed and optimized to ensure they can be FAST REFRESHed. As tools, the DBMS_MVIEW.explain_mview procedure and the MV_CAPABILITIES_TABLE are used. In this particular case, refresh time was reduced from more than 14 hours to less than 2!

Analyzing tkprof Results

This is the most difficult step in the process. Each tkprof output file contains a header, body, and summary section. The header simply displays the trace file name, definitions, and sort options selected. The body contains the performance metrics for SQL statements. The summary section contains an aggregate of performance statistics for all SQL statements in the file.

Automate the Sizing of your SGA in Oracle 10g

How much memory does each of the individual components of the SGA need? Oracle now has methods to determine most of the major parameters all on its own.

Automatic DB_FILE_MULTIBLOCK_READ_COUNT

Note: Originally this experiment was from a post I wrote on the Oracle Forum: Database - General. I recommend that you read Jonathan Lewis’ summarization of the thread instead of reading all 671 posts (as of today). You will spend much less time and get more out of the discussion. One of the new features that was released in 10gR2 is the automatic DB_FILE_MULTIBLOCK_READ_COUNT. Below are portions from the documentation that describe this feature.

Automatic SQL Tuning using SQL Tuning Advisor

SQL tuning is one of the challenging tasks faced by Database Administrators. It is an interesting and creative, but at the same time, daunting task. Manual tuning of SQL statements requires through knowledge of how the statements are executed in the background and experience to understand suitable access paths to yield better response times. Moreover, in a typical application, there are just too many SQL statements to tune and advise the developers.

Autotrace in SQLPLUS

You can automatically get a report on the execution path used by the SQL optimizer and the statement execution statistics. The report is generated after successful SQL DML (that is, SELECT, DELETE, UPDATE and INSERT) statements. It is useful for monitoring and tuning the performance of these statements.

Avoid buffered I/O

Buffered datafile I/O wastes copious memory because Oracle data blocks are cached both in the SGA and in the file system buffer cache with substantial overlap between the two sets of blocks that are cached.