| DBMS_UTILITY AND DBMS_STATS IN ORACLE 8i [message #59578] |
Thu, 04 December 2003 14:45  |
Prasad
Messages: 104
Registered: October 2000
|
Senior Member |
|
|
Hi
The benefit of DBMS_STATS I am not able to obatain using parallel option also, I am getting the almost same time for both DBMS_STATS and DBMS_UTILITY, So someone can tell me where I am going wrong with this DBMS_STATS,
BEGIN
dbms_stats.gather_schema_stats(ownname => 'C4QA',
estimate_percent => 15,
cascade => true,
degree => 8,
options => 'GATHER',
method_opt => 'FOR ALL COLUMNS SIZE 1',
granularity => 'ALL'
);
END;
Elapsed Time : 8:10:32:12
Though I have given degree 8 and FOR ALL COLUMNS SIZE 1, I hv checked in V$px_process too for parallel.
execute dbms_utility.analyze_schema('C4QA','ESTIMATE',NULL,15)
Elapsed Time : 8:32:32:12
I am not finding that diffrence just hardly 22 mins.
Whether DBMS_STATS actually uses parallel or I am going wrong in usage. Please someone can expain me.
Thanks and Regard
Prasad
|
|
|
|
| Re: DBMS_UTILITY AND DBMS_STATS IN ORACLE 8i [message #59598 is a reply to message #59578] |
Sat, 06 December 2003 07:16  |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
In some cases,using PQ slaves actually slows down the operation , it all depends(cpu,concurrent usage etc). What did V$px_process and v$pq_sesstat tell you ? Did they tell you that the parallel query slaves were being used to gather the statistics ?
For eg)
thiru@9.2.0:SQL>set timing on
thiru@9.2.0:SQL>execute dbms_utility.analyze_Schema('PERFSTAT','COMPUTE',NULL);
PL/SQL procedure successfully completed.
Elapsed: 00:00:09.00
-- takes 9 seconds
thiru@9.2.0:SQL>execute dbms_stats.delete_schema_Stats('PERFSTAT');
PL/SQL procedure successfully completed.
Elapsed: 00:00:11.07
thiru@9.2.0:SQL>begin
2 dbms_stats.gather_schema_stats(ownname=>'PERFSTAT',
3 degree=>4,cascade=>true,options => 'GATHER',granularity => 'ALL');
4 end;
5 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:10.02
-- dbms_stats with 4 PQ slaves takes about 10 seconds
thiru@9.2.0:SQL>execute dbms_stats.delete_schema_Stats('PERFSTAT');
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.01
thiru@9.2.0:SQL>begin
2 dbms_stats.gather_schema_stats(ownname=>'PERFSTAT',
3 degree=>NULL,cascade=>true,options => 'GATHER',granularity => 'ALL');
4 end;
5 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:06.05
-- dbms_stats with NO(default) PQ slaves takes about 6 seconds
-- Following is the o/p from v$px_process when those dbms_stats were running :
thiru@9.2.0:SQL>/
SERV STATUS PID SPID SID SERIAL#
---- --------- ---------- ------------ ---------- ----------
P002 IN USE 18 2208 19 245
P001 IN USE 16 2756 17 242
P003 IN USE 19 3004 18 244
P000 IN USE 15 3172 20 243
thiru@9.2.0:SQL>/
SERV STATUS PID SPID SID SERIAL#
---- --------- ---------- ------------ ---------- ----------
P002 AVAILABLE 18 2208
P001 AVAILABLE 16 2756
P003 AVAILABLE 19 3004
P000 AVAILABLE 15 3172
|
|
|
|
| Re: DBMS_UTILITY AND DBMS_STATS IN ORACLE 8i [message #59605 is a reply to message #59598] |
Sun, 07 December 2003 11:41 |
Prasad
Messages: 104
Registered: October 2000
|
Senior Member |
|
|
Hi Thiru
Thanks for the reply, But I have 6 CPU's all symmentric ones and PARALLEL_MIN_SERVERS AND PARALLEL_MAX_SERVERS are also set. Actually I hvn't checked concurrent usage,but my schema is having around 85 tables,So in order to take benefit I hv used a parallel, let me try with without using parallel. But Can u tell me What could be other reasons behind consuming this much time.The same script I have tested on 9i it gives me 50% improvement over DBMS_UTILITY.
Thanks and Regards
Prasad
|
|
|
|
| Re: DBMS_UTILITY AND DBMS_STATS IN ORACLE 8i [message #59608 is a reply to message #59605] |
Sun, 07 December 2003 13:09 |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
Prasad,
Dbms_stats with Parallel computing of statistics is supposed to be faster than dbms_utility , but again it depends on the actual workload,free cpu available,coordination of slave processes etc.
My suggestion is to start with lower parallel degree and 'only' increase if you benefit from it and everything else remains fine . Try increasing your sort_area_size that could help.
-Thiru
|
|
|
|
| Re: DBMS_UTILITY AND DBMS_STATS IN ORACLE 8i [message #59610 is a reply to message #59608] |
Sun, 07 December 2003 20:25 |
Prasad
Messages: 104
Registered: October 2000
|
Senior Member |
|
|
Hi Thiru,
By going through Some oracle documentation I found INDEX statistics can not be generated in parallel.
and degree should be set to twice the number of cpu's.
So is it good idea to capture table and statistics separately. Or If I am doing toghether the common information can be used to update both dba_tables/dba_indexes quickly. Problem is, it takes at least 8 hours to complete the process, so everyday its becoming expreimentation without solid productivity.
My question is here is anyway possible to predict the amount of time it takes to complete operation by looking into v$session_longops and v$SQL.
Have u encountered such problems earlier.
Thanks and Regards
Prasad
|
|
|
|
| Re: DBMS_UTILITY AND DBMS_STATS IN ORACLE 8i [message #59617 is a reply to message #59610] |
Mon, 08 December 2003 14:04 |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
Prasad,
Index statistics being gathered via GATHER_SCHEMA_STATS or GATHER_TABLE_STATS using 'cascade' option may not be parallalized but GATHER_INDEX_STATS will be parallalized. So maybe you can do them separately in parallel(dbms_jobs or cronjobs).
Degree being set to twice the number of CPUs is not a fixed rule.Start with a number equal to the number of cpus and increase only if they benefit you.
Yes,v$session_longops will give you a rough time estimated to complete the operation.
HTH
-Thiru
|
|
|
|
|
|
| Re: DBMS_UTILITY AND DBMS_STATS IN ORACLE 8i [message #59627 is a reply to message #59617] |
Tue, 09 December 2003 19:35 |
Prasad
Messages: 104
Registered: October 2000
|
Senior Member |
|
|
Hi Thiru
I am not able to test what u suggested beacuse
1) Here One more Sr DBA(Chinese Guy) very adamant on changes. He sticks to his own decisions, beacuse he is using DBMS_UTILITY in his script. Not ready to change
him so easily. I am not finding any other 8i test servers to prove the benefit of DBMS_STATS over dbms_utiltity. All 8i are production only.
But in 9i I already shown that diffrence to him.
2) I am working on someother replication problems not finding enough time to really dig into problem with DBMS_STATS in 8i.
But I will keep this question open and I will come back once I get 8i servers for implementing this.
Thanks and Regards
Prasad
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
