saibal's picture

Shared Server -- Part III


To continue with the shared server series, as mentioned in my last post, I will here be taking up the issues of configuration of memory, and the management of shared servers using available data dictionary views and initialization parameters.
How much memory should you configure for shared servers?

saibal's picture

Shared Server -- Part II


So, to continue with our shared server series, let's begin with how a user connects through a shared server connection:

saibal's picture

Shared Server -- Part I


This is the first of my three-part series dealing with Shared servers: Today an organization can have hundreds or thousands of users connecting to their databases. The traditional dedicated server model maps each user to a dedicated process running on the database server. Each dedicated server process requires memory and as the number of users increase, there is a linear increase in the demand for memory.

Tuning "BETWEEN" Queries


A common sight in databases is a table that contains the start and end values of a range - usually dates. One or both of the dates are typically part of the primary key; sometimes they are the entire key. Some examples:

  • History tables History tables record the changes made to a row over time. Instead of updating a row and losing the prior values of each non-key attribute, a new row in inserted. Each row is differentiated by two columns that record the date the row was created (START_DATE) and the date it was superceded (END_DATE).

bhupinderbs's picture

A PL/SQL program to convert numbers into words


Here is a Function that convert NUMBERS into WORDS:

[code]CREATE OR REPLACE FUNCTION f_words (p_amount IN Number) RETURN Varchar2 IS
--Author : Bhupinder Singh
--Creation Date : 05/03/2007
--Purpose : This Function returns amount in words.
--Parameters :
--1) p_amount : Only positive and negative values are allowed.
Precision can be entered upto 10 digits and only 2 scales
are allowed e.g 9999999999.99

World's Fastest Scalable Join


One glance at my golf clubs would be enough to determine that I'm a terrible golfer. The pitching wedge is dirty. Nine-iron: dirty. Same with the eight, seven and six irons. Five, four and three irons are fairly clean. Woods: pristine. I play percentage golf (actually 110%, if you count penalties); I figure a 5-iron 150 meters down the fairway is a better bet than a 3-wood 200 meters into the trees.

So I've got a golf bag with 2 clubs that I paid for but never use. Madness? Well no, not really; but then I'm not paid to play golf. Can you imagine a professional golfer never using the driver? It wouldn't happen.

Can you picture an Oracle programmer never using the most powerful join method available? No? Get a mirror.

Redo Logfile Size Advisor


The Redo Logfile Size Advisor can be used to determine the least optimal online redo log file size based on the current FAST_START_MTTR_TARGET setting and MTTR statistics.
Which means that Redo Logfile Size Advisor is enabled only if FAST_START_MTTR_TARGET is set.
The Advisor provides a recommended optimal smallest online redo log file size.
we can then adjust the online redo log file size to the recommended optimal size ,eliminating the need to manually determine an optimal size.

To use the advisor we need to query the V$INSTANCE_RECOVERY VIEW.

If our redo log file size is under sized then the checkpoint process is driven by WRITES_LOGFILE_SIZE i.e. its driven by the smallest redo log file size.

Automatic Work Load Management and High Availability


Automatic Work Load Management

Application workloads can be defined as services so that they can be individually managed and controlled.
We can control and monitor which processing resources are allocated to each service during both normal operations and in response to failures.
Performance metrics are tracked by service and thresholds set to automatically generate alerts should these thresholds be crossed.
CPU resource allocations and resource consumption controls are managed for services using Resource Manager.
we deifne rules to automatically allocate processing resources to services. Oracle RAC 10g instances can be allocated to process individual services or multiple services as needed.

Understanding Explain Plan


Benjamin Disraeli, circa 1870 wrote:
Never complain and never explain.

Disraeli was a lot of things, but Oracle Programmer was not amongst them. To be fair, perhaps he wasn't talking about Explain Plan?

SQL is a goal-oriented language. Unlike procedural languages, we tell the database what we want rather than how to get it. Oracle's Cost Based Optimizer comes up with an execution plan that is hopefully the most efficient way to resolve the query, but for many reasons it will often choose a sub-optimal plan.

cbruhn2's picture

Getting information about previously run commands with statspack



When you setup statspack with level 6 you can have information on the sql_plan associated with a sql. (statpack on oracle db version >=9i)


Subscribe to RSS - blogs