Re: performance tuning

"May" <sunxushi_at_yahoo.com> wrote in message news:8c0f1a9e.0109050101.462a6750_at_posting.google.com...
> i have several problem on performance tunning:
>
> 1)how can see the fragmentation level? and what is its influence on
performance.

dba_free_space. If count(*) goes up and max(blocks) goes down, it indicates lots of small pieces of free space, which is what fragmentation is. Impact on performance? None.

> 2)what's MTS setup? and its influence.(we are a website database)

It's where, when you start an instance, you have multiple pre-spawned dispatcher processes, and multiple pre-spawned server processes. There's a single job queue, and multiple response queues (one for each dispatcher). A User requests a connection; the listener determines which dispatcher is least heavily loaded, and connects the User to that dispatcher. User now submits a query. The dispatcher places it on the job queue. Any available server process then works at it, and places the response back on the relevant response queue, for the dispatcher to return it to the User. In effect, you've got server processes being shared by multiple users, rather than sitting around dedicated to someone who's gone off for a smoke break.

It's therefore a more efficient use of resources, especially on Unix, and switching between Users is lighter on the CPU (you're switching between threads within the dispatcher, not performing a context switch between separate processes). It's the only feasible way of getting thousands of users connected to the one Instance, and is thus very suitable for web access.

All configured with a handful of init.ora parameters, and some judicious tweaking of tnsnames.ora and listener.ora. Tricky to set up. Works like a charm when you get it right. Not good if you are a DBA wanting dedicated resources to perform a major operation -but fortunately, you can request dedicated server connections for specific connections even when the majority of Users are connected via shared servers.

> 3)how can i see the waiting and locking?

Huge topic. V$lock will give you the locking. But there's a variety of ways to see waits. v$waitstat springs to mind as one possible view. But utlbstat/estat will also do the deed in report.txt.

> 4)how can i deside memory size correct (including sql area and database
blocking)

SGA should be no more than 1/3rd of total available RAM on the box (that's just a sensible rule of thumb to avoid unnecessary paging by the O/S). Within the SGA, allocation is done based on hit ratios. Your library cache hit ratio should probably be in the 90%+ range, with a reloads to pins ratio of less than 1%. If that last ratio is bigger than 1%, make the shared_pool_size bigger. If the first one is less than 90%, consider re-writing your SQL (bind variables etc).

The buffer cache ratio should be around 95%, too (85% for a data warehouse). Any smaller, and you should consider increasing db_block_buffers.

That's a ridiculously short summary of what is a 5 day training course. It's a huge, huge topic.

Regards
HJR
>
>
> thank you
Received on Wed Sep 05 2001 - 15:01:29 CDT