Statistics (URGENT)


how does computing statistics improve performance in Oracle

Posted On: Thursday 15th of November 2012 10:18:56 PM Total Views:  419
View Complete with Replies

RELATED TOPICS OF Oracle PROGRAMMING LANGUAGE




UNIX Shell Script for gathering table statistics using DBMS_STATS..!!

Hi all, Its very urgent can somebody help me to develop a Unix shell script for gathering a table statistics..\t Since the DBMS_jobs doesn't work unfortunately, so i can use this shell script and can add it into the crontab... Thanx .
VIEWS ON THIS POST

174

Posted on:

Thursday 1st November 2012
View Replies!

timed_statistics=True

Hi all, I read somewhere and I couldn't recall exactly of whichone is true. any way, I have gone through some docs and it's saying it's not a good practice to set TIMED_STATISTICS=TRUE. Could someone tell me why\t also, there was a parameter TRACE_ENABLED in the database, and if we set this to TRUE that would degrade the performance of the database, is that true\t\t
VIEWS ON THIS POST

126

Posted on:

Thursday 1st November 2012
View Replies!

Thoughts on statistics

Having just taken over a prod db, I was told to be very careful of gathering stats against the schema as it "might" affect performance. So not sure if this is a red herring or not. comparing num_rows v actual rows, the differences are huge. I.e num rows 0, actual rows 500,000! Last analyzed DB 4 1/2 months ago! So do I just gather stats for the whole schema or do table stats one by one to check that performance isnt affected. Not sure how to test that though....
VIEWS ON THIS POST

138

Posted on:

Thursday 1st November 2012
View Replies!

timed statistics

, I have just started using the statspack. I have set timed_statistics by using: alter system set timed_statistics = true. I don't want to reboot the system now. Will this parameter hold until I reboot the system Also, is there any directories/filesystem etc (or overhead) I should be looking out for
VIEWS ON THIS POST

237

Posted on:

Friday 2nd November 2012
View Replies!

Table statistics are generated automatically?

Table statistics are generated automatically We current using Oracle 8i datanase. One of our table, without any reason keeps creating statistics and that is messing-up the execution plan. What this happening and how can we stop it permanently
VIEWS ON THIS POST

171

Posted on:

Friday 2nd November 2012
View Replies!

statistics generation in sqlserver

Hi Does sqlserver generates statistics automatically or manuall.If manually please help in knowing it. regards anandkl
VIEWS ON THIS POST

158

Posted on:

Friday 2nd November 2012
View Replies!

OS statistics

I want to gather statistics through OS as well, instead of gathering them just from oracle (v$filestat, v$sgastat, etc) ... I know in Unix there are sar and iostat commands (for example) ... what commands I should use in linux for doing it I know that I can use 'free' and 'netstat'. What about iostat and sar in linux (these commands are available in unix, not in linux) F
VIEWS ON THIS POST

134

Posted on:

Friday 2nd November 2012
View Replies!

Questions ON Gathering statistics on Index & table

Everyone, few quick questions (1) Is it advisable gathering statistics on Index & table after creating a NEW index on an OLD large table Why (2) Is it advisable gathering statistics on Index & table if we move any table to KEEP or Buffer pool Why (3) Is it advisable gathering statistics on Index & table if we REBUILD a table in primary key order Why (4) Is it advisable gathering statistics on Index & table after reorganizinf a table based on primary key Why
VIEWS ON THIS POST

143

Posted on:

Saturday 3rd November 2012
View Replies!

questionable statistics

Hi all, Can anyone explain about questionable statistics i get this message while exporting.i know how to avoid it but i jus wanted to know what it is and where can we use this Cheers gtham
VIEWS ON THIS POST

185

Posted on:

Saturday 3rd November 2012
View Replies!

timed statistics and statspack report

Hi Folks I need to prove it to management that runing a statspack report without the setting the timed_statistics parameter equal to true is pretty useless.Does somebody have a document that provides me arguments which can strengthen my case. regards Hrishy , Base your argument by confusing them...the fact is you know more...and then smack them!! or ask them on what knowledge do they base this question...and then smack them!!
VIEWS ON THIS POST

273

Posted on:

Sunday 4th November 2012
View Replies!

Strange Behavior by collecting statistics

I've a strange behavior in a database. When I collect statistics on the database, everything is slow (select, insert statments,...in all schemas). When I delete statistics the database queries become in normal state. Any Idea I collect statistics with the package dbms_stat for all users except System,Sys, DBSNMP and OUTLN. Oracle Version:Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit OS:SunOS prosun03 5.8 Generic_117350-33 sun4u sparc SUNW,Sun-Fire-880
VIEWS ON THIS POST

171

Posted on:

Sunday 4th November 2012
View Replies!

statistics on import

hi, if you do an import, is it better to set the statistics option on the import itself, or should we gather_schema_stats on the schema after the import, or is it not necessary at all
VIEWS ON THIS POST

166

Posted on:

Sunday 4th November 2012
View Replies!

query for gathering all the statistics of indexes in a schema

Kindly guide me for the query for gathering all the statistics of indexes in one schema. I have given the following query but it is giving error. exec DBMS.STATS.GATHER_INDEX_STATS('name of the index') it is giving me error the DBMS.STATS must be declared. however when i tried to analyze all my tables of whole schema by similar query.it worked nicely. Kindly guide me to suitable solution
VIEWS ON THIS POST

175

Posted on:

Sunday 4th November 2012
View Replies!

Urgent! Urgent! - about statistics

Can anyone tell me how do I delete statistics about a particular table
VIEWS ON THIS POST

166

Posted on:

Wednesday 7th November 2012
View Replies!

statistics_level & db_cache_advice, Do I need both the parameters in 9i

, Until 8i, in order to collect the db cache/db buffer information one has to set the db_cache_advice=ON. In 9i I read that the db_cache_advice parameter was replaced with statistics_level=TYPICAL/ALL. Do I have to set both the parameters in order to populate the v$db_cache_advice view or I can switch off the db_cache_advice=OFF\t\t I really appreciate your response.
VIEWS ON THIS POST

169

Posted on:

Wednesday 7th November 2012
View Replies!

Timed statistics

I am working as a DBA on Oracle 7.2.3 on Unix. How can I change the parameter timed_statistics parameter dynamicallyI have tried alter system set option but its giving illegal set option. Or is it not possible to change the parameter dynamically in Oracle 7.2.3
VIEWS ON THIS POST

322

Posted on:

Wednesday 7th November 2012
View Replies!

PGA and UGA statistics

We are currently running MTS and due to some bugs with MTS we are thinking of going to dedicated server. I was trying to estimate the memory overhead with the dedicated server configuration and the values from v$sysstat and v$sesstat seems to be incorrect. The database server has 4gb of memory. Database SGA show sga Total System Global Area 2118164464 bytes Fixed Size 69616 bytes Variable Size 1462554624 bytes Database Buffers 655360000 bytes Redo Buffers 180224 bytes Values from initSID.ora db_block_buffers = 80000 (DB_BLOCK_SIZE=8192) shared_pool_size = 872415232 large_pool_size = 536870912 Values from v$sysstat and v$sesstat. select name ,sum(value/1024/1024) Mem_In_MB from v$sysstat where name like '%memory%' group by name; NAME MEM_IN_MB ---------------------------------------------------------------- ---------- session pga memory 531199.958 session pga memory max 873752.614 session uga memory 496495.934 session uga memory max 8152.02377 sorts (memory) 12.7339716 select n.name,sum(s.value) from v$statname n, v$sesstat s where n.statistic# = s.statistic# and n.name like '%memory%' group by n.name; NAME SUM(S.VALUE/1024) --------------------------------------------- ----------------- session pga memory 930469.129 session pga memory max 2016793.41 session uga memory -849.53516 session uga memory max 4962.24219 sorts (memory) 146.436523 Also we are getting negative values for the session uga memory". Can somebody help me with this.
VIEWS ON THIS POST

213

Posted on:

Wednesday 7th November 2012
View Replies!

refresh materialized view time statistics

Hi Everyone, Is there a way i can tell how long does it take my materialized view to complete its refresh Say I have inserted 10,000 rows on EMP table at the Master Site, then I issue a REFRESH at my Materialized View Site, is there a logfile where I can see the time statistics
VIEWS ON THIS POST

230

Posted on:

Wednesday 7th November 2012
View Replies!

rdbms lpc message statistics in statspack?

I'm searching for more information concerning "rdbms lpc message" as a background process; it is generating enough statistics in my statspack report not to ignore it. I search this site and other sites, but the definition of "plc" seems to change every minute. Could someone who has got the correct info enlighten me on this subject...please!!!
VIEWS ON THIS POST

235

Posted on:

Wednesday 7th November 2012
View Replies!

usage statistics in portal 3.0.9

Hi everybody, After logging into the oracle potal 3.0.9..home page comes.On the home page there are different tabs(e.g Build,Administer..etc).There is one tab called moniter if u click on the Monitor tab.There are various charts by user for e.g chart all page view requests by user. Similarly what i need is chart all content areas view requests by user or there is some inbuilt with which i know which is the most accessed content area of all the areas.I just want to find the usage statistics of all the pages,content areas,folders,items by users.what are the views where i can find the usage statistics foreverything (pages.content areas,folders,items). Thanking you all in advance. pras
VIEWS ON THIS POST

141

Posted on:

Thursday 15th November 2012
View Replies!