Ty chto, do six por mychaeshsya??

Ya ge tebe pisal:

Hard::
Oracle Kernel:: ora.ini, few queries + trace.
user's interface ( Net*, ODBC/JDBC, OLE DB etc).
all_tables, all_indexes, all_tab_partitions, all_index_partitions
( netstat, iostat, memstat esli est' )

V prinzipe, srednyaya nastroika - eto 1 - 1.5 chasa raboty.
Bolee tonkaya - eto nado poluchit' konkretnyi vopros + dannye.

E-mail

Otpravil...

Tvoi last mail ne poluchil. Our spy ne propystil...
prishli tvoi text ne kak attach..

Nugny rezul'taty etih statements::

SELECT * FROM V$SGASTAT WHERE NAME = 'free memory' AND POOL = 'shared pool';

SELECT size_for_estimate, buffers_for_estimate
, estd_physical_read_factor, estd_physical_reads
FROM V$DB_CACHE_ADVICE
WHERE name = 'KEEP'
AND block_size = (SELECT value FROM V$PARAMETER
WHERE name = 'db_block_size')
AND advice_status = 'ON';

SELECT NAME, PHYSICAL_READS, DB_BLOCK_GETS, CONSISTENT_GETS , 1 - (PHYSICAL_READS / (DB_BLOCK_GETS + CONSISTENT_GETS)) "Hit Ratio" FROM V$BUFFER_POOL_STATISTICS;

SELECT o.object_name, COUNT(1) number_of_blocks FROM DBA_OBJECTS o, V$BH bh WHERE o.object_id = bh.objd AND o.owner != 'SYS' GROUP BY o.object_name ORDER BY count(1);

SELECT namespace , pins , pinhits , reloads , invalidations FROM V$LIBRARYCACHE ORDER BY namespace;

SELECT parameter , sum(gets) , sum(getmisses) , 100*sum(gets - getmisses) / sum(gets) pct_succ_gets , sum(modifications) updates FROM V$ROWCACHE WHERE gets > 0 GROUP BY parameter;
select * from v$session_event
select * from v$session_wait
select * from v$waitstat

Privet!
Proshu prosheniya za molchanky.. ne bylo vozognosti..
Segodnya otpravlu..
Regards!

Y tebya glavnye problemy s:
latch free
buffer busy waits
library cache pin
I/O
SQL.
Krome togo, pravil'nee perevesti na partitions::
STRUCTURE_DEP
FACTS
STRUCTURE_DEP_HISTORY
ORDER_ITEMS
RAB_TABL
ORDERS
&
Pererabotat' plsql code.( Y've many reloads..)..
I'll send my recommendations latter..
Regards!

Prover' poka, moget byt' est' vozmognost' perebit'
eti tablizy po scheme:: partition + subpartition ( NOT HASH SUB!!!)??

Parameter max concurrent users mogno umen'shit' do 750 - 800..
( po statspack, etot parameter ne dostigal 450..)..
Ostal'noe = in letter..

Privet!
I'll send my remarks in mail afternoon.
Regards!

Solutions::

A. Ora.ini
In additions, change PGA_AGGREGATE_TARGET to 600 MByte.
CURSOR_SPACE_FOR_TIME = TRUE ( after cleaning a "buffer busy
waits" situation.
session_cached_cursors= 15 ( latch free waits! )
comment on [sga_max_size=943718400 ]
CURSOR_SHARING = SIMILAR
B. Application side:

I. Find statements with very high logical I/O or physical I/O, using unselective
indexes.
II. After I, find inefficient SQL that accesses incorrect indexes iteratively
(large index range scans) or many full table scans ( or HASH FULL SCAN )
III. Detected High RELOADS / INVALIDATIONS -> Hot blocks (!!)

C.Queries for checking::
SELECT operation, options, object_name name,
trunc(bytes/1024/1024) "input(MB)",
trunc(last_memory_used/1024) last_mem,
trunc(estimated_optimal_size/1024) optimal_mem,
trunc(estimated_onepass_size/1024) onepass_mem,
decode(optimal_executions, null, null,
optimal_executions||'/'||onepass_executions||'/'||
multipasses_executions) "O/1/M"
FROM V$SQL_PLAN p, V$SQL_WORKAREA w
WHERE p.address=w.address(+)
AND p.hash_value=w.hash_value(+)
AND p.id=w.operation_id(+)

SELECT parameter
, sum(gets)
, sum(getmisses)
, 100*sum(gets - getmisses) / sum(gets) pct_succ_gets
, sum(modifications) updates
FROM V$ROWCACHE
WHERE gets > 0
GROUP BY parameter

SELECT NAME,SUM(VALUE) || ' BYTES' "TOTAL MEMORY FOR ALL SESSIONS"
FROM V$SESSTAT, V$STATNAME
WHERE
V$SESSTAT.STATISTIC# = V$STATNAME.STATISTIC#
group by NAME

Hey, man!
How are You?
What's Your instance?

Hi!
Full ini received. Check Your mail tomorrow.