| SYSTEM STATISTICS in Distributed Enviornment [message #64874] |
Mon, 23 February 2004 21:21  |
Prasad
Messages: 104
Registered: October 2000
|
Senior Member |
|
|
Hi
Yesterday Evening I captured System statistics using DBMS_STATS by giving interval of 2 hours I think not much of load on the interval . It has populated some information in sys.aux_stats$ where multiblock readcount acheived is 29. Then next day morning I started to replicate 5 Million rows using Materilized views my database was literaly crying.Materilized log was going for full tablescan since Index was not there earlier also no index was there but not consuming that much time. It was working fine before capturing system stats. I have generated the trace for those sessions using dbms_support with wait option. I saw the waits were due to
1) db file sequential read 2) PQ ACK SEND and RECIVE
3) some buffer busy waits
My doubt is why by capturing System stats it has slown down system performance.Once I deleted System stats and bounced db it is fine. My optimizer settings are
1) OPTIMIZER_INDEX_COST_ADJ=50
2) OPTIMIZER_INDEX_CACHING=40
3) PARALLEL_MIN_SERVERS=16
4) PARALLEL_MAX_SERVERS=46
5) PARALLEL_AUTOMATIC_TUNING=TRUE
6) DB_FILE_MULTIBLOCK_READ_COUNT=32
So all these things should drive Oracle to use indexes even when the System statistics captured right?. Replicated schema is anlyzed every three hours incrementally.
So please guide where I went wrong in utilizing System statistics to achieve better performance and any parameters needs change
Finally MTS settings won't work with parallel servers.Since My system won't change type of load is advicble to keep system stats in start mode only forever instead of 2 hours interval every 6 hours. What more privileges are required for dba user to run as job apart from "analyze any" and "select any".
Thanks in advance.
Prasad
|
|
|
|
| Re: SYSTEM STATISTICS in Distributed Enviornment [message #64876 is a reply to message #64874] |
Tue, 24 February 2004 05:22  |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
your optimizer_index_caching seems to be low(40). If your buffer_cache is sufficient enough,you will be caching 80-90% of the index blocks and hence this value needs to be increased to reflect that.
db file sequential reads are usually associated with single block index reads.
If the System statistics indicate that the multiblock read time is very quick,then the optimizer inclines towards full table scans,especially when you are saying that only 40% of the index blocks are cached.
Those two are the privileges required .
SQL> select * from dba_sys_privs where grantee='TEST';
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
TEST CREATE SESSION NO
SQL> grant select any table,analyze any to test;
Grant succeeded.
SQL> connect test/test
Connected.
SQL> variable jobno number;
SQL> execute dbms_job.submit(:jobno,'dbms_stats.gather_schema_stats(''THIRU'');',sysdate,'sysdate+1',false);
PL/SQL procedure successfully completed.
SQL> execute dbms_job.run(2);
PL/SQL procedure successfully completed.
-Thiru
|
|
|
|
| Re: SYSTEM STATISTICS in Distributed Enviornment [message #64879 is a reply to message #64876] |
Tue, 24 February 2004 09:23 |
Prasad
Messages: 104
Registered: October 2000
|
Senior Member |
|
|
Hi Thiru,
Thanks lot for the reply. Actually I have bundeled lot many doubts in one post.
1) What privileges to run DBMS_STATS.SYSTEM_STATS apart from Analyze any,select any table.
2)What is the best method to capture SYSTEM_STATS by specifying interval or Gathering mode=> START for ever,if any overhead by running GATHERING_MODE =>START continosuly.
3)For replicationg 15 Million Rows I have increased my UNDO tablespace till 80GB what is the impact of having such a large UNDO tablespace on database performance.Since 9i gives only V$undostat and DBA_UNDO_EXTENTS to monitor the UNDO tablespace.Unlike V$rollstat(GETS,WAITS,WRAPS).
or Any Good article on UNDO TABLESPACE
4)What is the impact of Parallel processing in MTS Enviorment.
5)I read that by using large block size for TEMP and INDEX tablespaces and tables with lots of FTS improves I/O performance is it true?.
I know once again I am asking lot many questions in a single thread.I think first three can be choosen for preferance over the rest 2.
Thanks and Regards
Prasad
|
|
|
|
| Re: SYSTEM STATISTICS in Distributed Enviornment [message #64900 is a reply to message #64879] |
Sun, 29 February 2004 04:27 |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
Prasad,
Finally found some free time after few days of heavy performance issues(still continuing ! at my company)...
again,I'd appreciate if you could post your questions as separate threads. that makes it a lot easier for me and others to answer.
I'll take a couple of questions,here.
1) Gather_System_statistics role ( which has Select,Insert,Update,Delete on SYS.AUX_STATS$ table ). Analyze any,select any table privileges are required for analyzing other tables/schemas.
2)You should gather system statistics not forever,but in intervals of time that best reflects your system workload.
-Thiru
|
|
|
|
| Re: SYSTEM STATISTICS in Distributed Enviornment [message #64912 is a reply to message #64900] |
Mon, 01 March 2004 20:36 |
Prasad
Messages: 104
Registered: October 2000
|
Senior Member |
|
|
Hi Thiru
Thanks a lot for the reply.Hope your office performance problems have settled down.
1) First I have implemented working fine.
2) Mine is 24/7 system I am actually pretty not sure, how the load on the system ie during which intervals
Moreover I damn new to this project for last three months application point not much exposed so I thought of gathering in continuos mode.
Infact I have created one awk script to capture system load using vmstat/sar/iostat. and thought of running as crontab in regualr intervals of 3 hours to observere the load.
Once again thanks a lot for the reply.
My next question is what is u'r opinion on using non-standard block size for Index/undo tablespace. Since while creating database it won't allow nonstandard blocksize but afterwards we can create nonstandard blocksize.But max IO
DFMRC*DB_BLOCK_SIZE=IO SIZE.Since my DFMRC is already 32 and I put 32 block size. it exceeds maximum IO of HP UNIX 1M isn't it.?
Thanks and Regards
Prasad
|
|
|
|
| Re: SYSTEM STATISTICS in Distributed Enviornment [message #64923 is a reply to message #64912] |
Wed, 03 March 2004 08:29 |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
Prasad,
when you collect the system statistics continously,the values might get averaged out over that time and you may not get the actual picture and thats why its recommended to collect them during your busiest time or an interval of time that best reflects your system workload.
A larger block size for Index tablespace should benefit ( although I havent done this myself). The argument is that it allows for lot more leaf nodes to be stored in the data blocks.
32*32k=1MB and that matches HP's max io size. Is this a DW environment ? 32K block size is quite adequate and big,if you ask me.
-Thiru
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
