Server Administration :: Refreshing Stale Statistics In Oracle Database?

Apr 21, 2010

the importance of refreshing the stale statistics in oracle database and also how often we should do this?

View 3 Replies


ADVERTISEMENT

Server Administration :: Stale Statistics In Oracle Database?

Jun 7, 2012

Is there any script that give us output of all Stale Statistics in Oracle Database

View 8 Replies View Related

Performance Tuning :: Stale Statistics For Table?

Apr 27, 2012

From the below query i found that there are some stale stats for 3 tables.

=================================
select table_name, stale_stats, last_analyzed
from dba_tab_statistics
where owner= 'SYSADM' and stale_stats='YES'
order by last_analyzed desc

I collect stats for those above 3 tables with dbms_stats.gather_table_stats().But no luck.After collection of stats immediately I ran the above query.But still it is showing there are stale stats for 3 tables.

how can I change "STALE-STATS" status, so that optimizer can use the updated stats eficiently.

View 3 Replies View Related

Server Administration :: Gathering Database Statistics?

Sep 3, 2013

,I need to view database statistics after executing SQL> exec dbms_stats.gather_database_stats;is that really possible? how to check it?

View 7 Replies View Related

Server Administration :: Table Statistics For Oracle 11g

Oct 25, 2012

I want to get the stale stats for Table resides at APPS schema. Is there is any table or view exists to get the details like DBA_STALE_STATS or anything? Currently I am checking LAST_ANALYZED column from DBA_TABLES?

View 2 Replies View Related

Server Administration :: MView Not Refreshing Automatically At Time Interval Specified?

Sep 14, 2011

I have create a mview using a dblink with the refresh difference of every 10min. Suddenly I check the mview refresh date and time it was not getting refreshed automatically at the time interval specified.

drop MATERIALIZED view log on afccv.tbl_voicechat;
drop MATERIALIZED VIEW SMSCHAT.TBL_VOICECHAT_NEW1 ;
CREATE MATERIALIZED VIEW SMSCHAT.TBL_VOICECHAT_NEW1
ORGANIZATION HEAP PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)

[code]....

I am trying to find out what is the cause of not refreshing and also googled for the same. got some link also either not linked to same problem or not cleared.. Where should i checked regarding the error cause.

View 33 Replies View Related

Server Administration :: Privileges To Gather Statistics

Nov 9, 2010

What privileges is required to gather statistics of oracle database using DBMS_STATS/ANALYZE command.

View 1 Replies View Related

Server Administration :: When To Gather Table Statistics

Apr 21, 2012

Why do we gather table statistics manually ?Is it because of database performance.

I know In Oracle Database 10g, Automatic Optimizer Statistics Collection reduces the likelihood of poorly performing SQL statements due to stale or invalid statistics and enhances SQL execution performance by providing optimal input to the query optimizer.

Optimizer gathers statistics when 10% table rows have been changed.

View 9 Replies View Related

Server Administration :: Updating Statistics - Not Working?

Jul 4, 2010

if there are any problems with updating statistics? My manager told me that they had big performance issue when they updated the statistics of whole database. Is this a rare case?

I know the advantages of updating statistics,but are there any dis-advantages?

View 2 Replies View Related

Server Administration :: New Feature Of 11g Called-Pending Statistics

Mar 31, 2011

What is the scenario, we should use the new feature of 11g called-Pending Statistics.

Eg:-
EXEC DBMS_STATS.set_table_prefs ('SCOTT', 'EMP', 'PUBLISH', 'false');

View 3 Replies View Related

Server Administration :: Time Remaining To Complete Table Statistics?

Jan 16, 2012

I use the following query to find out the remaining time to complete the table statistics which is running currently.

SELECT SID, SERIAL#, opname, SOFAR, TOTALWORK,username,context,
ROUND(SOFAR/TOTALWORK*100,2) COMPLETE
FROM V$SESSION_LONGOPS
WHERE
TOTALWORK != 0
AND SOFAR != TOTALWORK
order by 1;

SOFAR column shows 9325 and totalwork column shows 12287.How to calculate the columns in terms of hours and minutes ?

And also sometime the query never shows the output of the query for the current running query.

View 1 Replies View Related

Server Administration :: Unable To Gather Statistics Of Neither Schema Nor Table?

Jul 13, 2011

I am connect to remote database with a user named 'TEST', this user has dba privileges. I am not able to gather the statistics of neither test schema nor for any table that exists in this schema.

SQL> EXEC dbms_stats.gather_schema_stats('TEST', cascade=>TRUE);
BEGIN dbms_stats.gather_schema_stats('TEST', cascade=>TRUE); END;
*
ERROR at line 1:
ORA-44004: invalid qualified SQL name
ORA-06512: at "SYS.DBMS_STATS", line 13210
ORA-06512: at "SYS.DBMS_STATS", line 13556
ORA-06512: at "SYS.DBMS_STATS", line 13634
ORA-06512: at "SYS.DBMS_STATS", line 13593
ORA-06512: at line 1

[code]....

View 17 Replies View Related

Server Administration :: Impact Of Up-to-date Statistics On Main Schema Objects

Feb 29, 2012

I am looking at a performance issue at the moment and trying to replicate on a test system. I am initially looking at the impact of upto-date statistics on the main schema's objects.

For this I wanted to:

first run the batch with whatever stats were present in the database Flashback the db to before the batch . Gather stats Re-run the batch with updated stats and compare results.

However, I inadvertently ran the stats job before running the load the first time! I have the SCN from when the environment was set up like production (ie before the stats were run) so am I correct in saying that if I flashback to this point then the stats will be "old" and I can just run the batch then? I know I can verify this when I Flashback the database by looking at LAST_ANALYZED on tables etc but it would be good to know this before hand as it's a 12 hour batch.

View 1 Replies View Related

Server Administration :: Installing Oracle Database 10.1.0.2.0 On Windows 2003 Server 64bit?

Jan 11, 2011

i am trying to install Oracle 10.10.2.0 on Windows Server 2003 standard x64 Edition Service Pack, but when i try to run the installer or open DVD it gives me below error.

"The image file D: is Valid, but is for a machine type other than the current machine."

View 1 Replies View Related

Server Administration :: Unable To Find Server Process In Oracle Database For Specific Application / Client

Feb 13, 2013

I am trying to find the unix process for one of my application in the database but I am unable to view the same. To simulate, I did the following.

1. My database runs on different server.
2. I invoked "sqlplus" from another unix box to login to the database.
3. I found that the process id (ps -ef |grep sqlplus).
4. When I execute the below mentioned query it does not display the process id that I am looking for. But the osuser, username, program and machine details are correct. How can I know the process details from the database?

SELECT SYS.GV_$SESSION.OSUSER, SYS.GV_$SESSION.USERNAME, SYS.GV_$PROCESS.SPID,
SYS.GV_$SESSION.MACHINE, SYS.GV_$SESSION.PROGRAM,
SYS.GV_$PROCESS.PROGRAM ,SYS.GV_$SESSION.SQL_ID
FROM
SYS.GV_$PROCESS, SYS.GV_$SESSION
WHERE
SYS.GV_$PROCESS.ADDR=SYS.GV_$SESSION.PADDR and SYS.GV_$SESSION.USERNAME='TEST'
and SYS.GV_$SESSION.MACHINE like '%hostname%'

View 3 Replies View Related

Server Administration :: Oracle 10g - Database Server Startup / Shutdown

Jun 29, 2011

I recently installed Oracle 10g on my windows Xp laptop. It has become considerably slow since then. I want to start the database server only when I need it, and not every time I start my laptop. I looked around in OEM and did found a way.

View 5 Replies View Related

Server Administration :: Oracle Database Server - Logon Denied

Jul 19, 2011

I am connected as System. It was the only user I set-up a password when installed the database on personal computer.

SQL> alter user sys identified by mypass007
2 /
User altered.
SQL> connect sys/mypass007
ERROR:
ORA-28009: connection as SYS should be as SYSDBA or SYSOPER

[code]...

SQL> conn sysoper/mypass007
ERROR:
ORA-01017: invalid username/password; logon denied

View 1 Replies View Related

Server Administration :: From Where To Download Oracle 9i Database Server

Aug 17, 2010

I have some requirement wherein I need to install Oracle 9i database server (9.2.0.7). I am not getting it on Oracle website. where can I get the download.

View 1 Replies View Related

SQL & PL/SQL :: Refreshing Schema From One Database To Another

Jul 10, 2012

I am often tasked with refreshing schema's from one DB to another.The first thing I need to check is the space the objects take up in the source DB. SQL statement that prints the size of the following objects

('SEQUENCE','SYNONYM','TABLE','INDEX','PACKAGE','PACKAGE BODY','DATABASE LINK','VIEW','PROCEDURE')

which is totaled by owner, TS.. Or the SQL can just take in an owner. I am looking for an output similiar to this:

TS name, owner, object_name, object_type MB SIZE
users xxx t1 table 100
users xxx t2 table 200

sum of tables 300
[code]....

View 3 Replies View Related

Server Administration :: Changing Of SID Oracle Database

Jan 3, 2011

i do know how change the database but can any one assist me how to chnage SID of database which in windows 2003 server?

View 1 Replies View Related

Server Administration :: How To Create A Database (Oracle 10g)

Jul 6, 2012

How to create own database in oracle 10g. I want to create new database in oracle.

View 10 Replies View Related

Server Administration :: Size Of The Oracle Database 11g?

Apr 21, 2010

how can we find the size of the oracle database 11g.

View 2 Replies View Related

Server Administration :: Creating New Database In Oracle?

Nov 10, 2010

how to create a new database in oracle??

View 2 Replies View Related

Server Administration :: Upgrading The Database To 11g From Oracle 8i?

Aug 27, 2013

I need to upgrade an oracle 8i database on HP-Unix server to Oracle 11g on Linux server, whether to upgrade it first to 10g linux and then to 11g ?

View 4 Replies View Related

Server Administration :: How To Get Tables In Oracle 10g Database

Dec 13, 2010

I have installed oracle 10g software and created Database on RHEL 4. when i run the run.sql its successively done but im not getting tables. whats the prob.

View 17 Replies View Related

Server Administration :: Oracle Database 12.1.0.1.0 No Emctl?

Aug 1, 2013

installed and configured oracle 12.1.0.1.0 on linux and i noticed there is no emctl? I went through the oracle docs and i see no references as what to use,

View 12 Replies View Related

Server Administration :: Upgrade 9.2.0.8 Database In Oracle 11.2.0.2

Sep 21, 2011

I am trying to upgrade the oracle 9.2.0.8 database in oracle 11.2.0.2. I have installed the new oracle home for 11g and after running the pre-upgrade script and setting the all environment variable like oracle base, oracle home, oracle sid and ora_nls

i am running the dbua but after click on finish button it's do the processing of 2% but after that i get
-------------------------------------------------
ORA-12709 error while loading create
database character set
--------------------------------------------------

View 11 Replies View Related

Server Administration :: Oracle 11gr2 Database And 11.1 ASM

Jun 26, 2012

I have oracle 10g running on oracle ASM 11.1, I want to know that if i install oracle 11gr2 database , can i use oracle 11.1 ASM which is already installed ?

View 5 Replies View Related

Server Administration :: CPU Usage By Oracle 10g Database

Aug 16, 2012

I have three oracle 10g databases (db1, db2, db3)on a linux box.

A command at the OS level to find total cpu used by each of the above databases.

View 12 Replies View Related

Server Administration :: Default Count Of Oracle 11g Database

Mar 6, 2011

I installed Oracle 11g and created a test database in that the default count should be 4196... but it is 4143.. some packages are missing.. even when i'm creating materialized view it is showing some error that packages are missing. what can i do for that? Is my oracle s/w corrupted ? even when i downloaded from oracle site it also shows the same count.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved