You are currently browsing the articles from Techno Oracle matching the category
Programmers often need a way to count and/or add up variable conditions for a group of rows. The DECODE statement provides a very efficient way of doing this. Because DECODE is rather complex, few programmers take the time to learn to use this statement to full advantage. The following statement uses DECODE to count the [...]
Written by admin on January 27th, 2011 with comments disabled.
Read more articles on Oracle Tuning.
SQL Trace causes trace files to be produced by Oracle Database. Various items of interest are generated into trace files. This information comprises details of CPU and I/O needs plus parsing and execution information, processed rows, commits, and rollbacks. The output in trace files is more or less impossible to interpret by hand. Setting up [...]
Written by admin on December 29th, 2010 with comments disabled.
Read more articles on Oracle Tuning.
SGA_TARGET Specifies the total size of all SGA components. If SGA_TARGET is specified, then the buffer cache (DB_CACHE_SIZE), Java pool (JAVA_POOL_SIZE), large pool (LARGE_POOL_SIZE), and shared pool (SHARED_POOL_SIZE) memory pools are automatically sized.Before you increase the size of your SGA, you must understand the effects on the physical memory of your system. If you increase [...]
Written by admin on December 6th, 2010 with comments disabled.
Read more articles on Oracle Tuning.
The init.ora file in oracle database determines Oracle operating system environment attributes, such as memory allocated for statements, memory allocated for data,and other crucial performance-related parameters. In Oracle 10g there are now 1381 different initialization parameters .We will focuses on the key parameters that affect database performance. The key to an optimized Oracle database is [...]
Written by admin on December 6th, 2010 with comments disabled.
Read more articles on Oracle Tuning.
When creating tables and indexes, note the following: 1. Specify automatic segment-space management for tablespaces This allows Oracle to automatically manage segment space for best performance. 2.Set storage options carefully Applications should carefully set storage options for the intended use of the table or index. This includes setting the value for PCTFREE. Note that using [...]
Written by admin on December 6th, 2010 with comments disabled.
Read more articles on Oracle Tuning.
The redo log buffer is a circular buffer in the SGA that holds information about changes made to the database. This information is stored in redo entries. Redo entries contain the information necessary to reconstruct, or redo, changes made to the database by INSERT, UPDATE, DELETE, CREATE, ALTER, or DROP operations. Redo entries are used [...]
Written by admin on December 5th, 2010 with comments disabled.
Read more articles on Oracle Tuning.
The buffer cache is part of the System Global area(SGA). It holds copies of data blocks so as they can be accessed quicker by oracle than by reading them off disk. For many types of operations, Oracle uses the buffer cache to store blocks read from disk. Oracle bypasses the buffer cache for particular operations, [...]
Written by admin on December 5th, 2010 with comments disabled.
Read more articles on Oracle Tuning.
DB_CACHE_SIZE is the first parameter to look at in the initialization parameter file because it’s the most crucial parameter in Oracle.The data cache hit ratio is the percentage of the data block accesses that occur without requiring a physical read from disk. DB_CACHE_SIZE it is the first parameter to look at in the initialization parameter [...]
Written by admin on December 3rd, 2010 with comments disabled.
Read more articles on Oracle Tuning.
Oracle8i and later has a great feature that stores information on long-running queries currently active in the V$SESSION_LONGOPS view. The following example shows the results of a query against V$SESSION_LONGOPS: SELECT username, sql_text, sofar, totalwork, units FROM v$sql, v$session_longops WHERE sql_address=address AND sql_hash_value=hash_value ORDER BY address, hash_value, child_number HROA select count(*) from winners w1, winners_backup [...]
Written by admin on November 23rd, 2010 with comments disabled.
Read more articles on Oracle Tuning.
A working Oracle database instance is organized using initialization parameters,which are set in the initialization parameter file. These parameters influence the behavior of the running instance, including influencing performance. In general, a simple initialization file with applicable settings covers most situations, and the initialization file should be the first place you expect to do performance [...]
Written by admin on August 27th, 2010 with no comments.
Read more articles on Oracle PL/SQL and Oracle Tuning.