learn-oracle

Oracle DBA






Oracle DBA - Oracle Optimization


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

Introduction to Oracle 10g's New SQL Tuning Advisor

Oracle has once again given us a great tool that will assist us with manual tuning of SQL statements. This tool is called the SQL tuning advisor and it will eat SQL statements for lunch and spit out optimizing techniques and recommendations we can use in our efforts.



Introduction to Oracle 10g's New SQLAccess Advisor

If you have ever had a difficult time tuning applications because you can never find the SQL or code behind these applications, Oracle 10g's new SQLAccess Advisor, a new tool from Oracle, is a must.

Log Block Size

Although the size of redo entries is measured in bytes, LGWR writes the redo to the log files on disk in blocks. The size of redo log blocks is fixed in the Oracle source code and is operating system specific. Oracle's documentation uses the term "operating system block size" to refer to the log block size. Normally it is the smallest unit of I/O supported by the operating system for raw I/O, but on some operating systems it is the smallest possible unit of file system based I/O. The following table shows the most common log block sizes and some of the operating systems that use them.



Managing Contention for Oracle Latches

Internally, Oracle uses various types of structures, the access of which is controlled using a variety of mechanisms, including latches. In this article, Oracle expert Megh Thakkar discusses how contention for Oracle latches can be minimized. Internally, Oracle uses various types of structures. Access to these structures is controlled using a variety of mechanisms: Latches Enqueues Distributed locks Global locks (used in parallel instance implementations) This article discusses how contention for Oracle latches can be minimized.

Managing SQL and Shared PL/SQL Areas (DBMS_SHARED_POOL)

Oracle compares SQL statements and PL/SQL blocks issued directly by users and applications as well as recursive SQL statements issued internally by a DDL statement. If two identical statements are issued, the SQL or PL/SQL area used to process the first instance of the statement is shared, or used for the processing of the subsequent executions of that same statement. Shared SQL and PL/SQL areas are shared memory areas; any Oracle process can use a shared SQL area. The use of shared SQL areas reduces memory usage on the database server, thereby increasing system throughput. Shared SQL and PL/SQL areas age out of the shared pool according to a "least recently used" (LRU) algorithm (similar to database buffers). To improve performance and prevent reparsing, you may want to prevent large SQL or PL/SQL areas from aging out of the shared pool. This chapter explains the use of shared SQL to improve performance.

Monitoring Open and Cached Cursors

Just about every DBA has had to deal with ora-1000 errors, "Maximum open cursors exceeded." This article will discuss initialization parameters that affect open cursors, the difference between open and cached cursors, closing cursors, and monitoring open and cached cursors.

Oracle 10g's Redo Logfile Sizing Advisor

Improperly sized redo logs can impede the performance of your database and hamper recovery time in the event of a database crash. Oracle has given us yet another advisory that helps us to properly size redo logs, taking yet another tedious task from us.

Oracle 11g: Incremental Global Statistics On Partitioned Tables

Incremental Global Stats works by collecting stats on partitions and storing a synopsis which is the statistics metadata for that partition and the columns for that partition. This synopsis is stored in the SYSAUX tablespace, but is quite small (only a few kilobytes). Global stats are then created not by reading the entire table, but by aggregating the synopses from each partition. Incremental Global Stats, in conjunction with the new 11g DBMS_STATS.AUTO_SAMPLE_SIZE, yield a significant reduction in the time to collect statistics and produce near perfect accuracy.

Oracle 9i: Working with Multiple Block Sizes

Prior to Oracle9i, the size of the System Global Area (SGA) was static and changing it would require a restart of the Oracle instance. This used to pose a number of problems for DBAs who would like to change the SGA size as needed by the application without worrying about downtime. This is particularly true for Oracle DBAs responsible for e-businesses. For an e-business, minimizing downtime is probably the most important requirement, and Oracle DBAs need to look for innovative ways to achieve this objective. With a static SGA, it becomes very difficult to react to changing application demands, especially if you have not performed proper capacity planning. Oracle9i allows the SGA size to be changed dynamically by using the ALTER SYSTEM command.

Oracle Cumulative High Water Mark Sessions.

Here is a good script by Steve Adams to display the high water mark for connected sessions in Oracle7, and there may be changes required for later releases.

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

More Tutorials on Oracle dba ...


Liked it ? Want to share it ? Social Bookmarking

Add to: Mr. Wong Add to: BoniTrust Add to: Newsider Add to: Digg Add to: Del.icio.us Add to: Reddit Add to: Jumptags Add to: StumbleUpon Add to: Slashdot Add to: Netscape Add to: Furl Add to: Yahoo Add to: Spurl Add to: Google Add to: Blinklist Add to: Technorati Add to: Newsvine Information


Oracle DBA

Want to share or request Oracle Tutorial articles to become a Oracle DBA. Direct your requests to webmaster@oracleonline.info





   Oracle Jobs   

   Forum
Oracle Forum   
   Ask A Question   

Oracle DBA - Step 1
Oracle DBA - Step 2
Oracle DBA - Step 3
Oracle DBA - Step 4
Oracle DBA - Step 5
Oracle DBA - Step 6
Oracle DBA - Step 7
Oracle DBA - Step 8
Oracle DBA - Step 9
Oracle DBA - Step 10

SQL Statement Tuning
Backup and Recovery Concepts
Oracle 11g New Features
Oracle E Suite & Others
Oracle Data Guard
Oracle DBA FAQ