How To Import Questionable Statistics

May 16, 2011

I want to take the statistics of my Production database and import to my local database, to calculate the Production statistics.I have used the statistics=compute, to export statistics. In the log file, for some tables, there was a waring like "exp-00091 exporting questionable statistics"

Is this dump will be useful for me to calculate production statistics?What option i have to use while importing statistics=recalculate or statistics=safe?

View 5 Replies


ADVERTISEMENT

Export/Import/SQL Loader :: Size Of Statistics

Dec 24, 2012

Actually, while exporting the schema with expdp. It is exporting the statistics. I want to know the size of this statistics in the database. Is there a table or method that I can find the size of the statistics.

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

View 7 Replies View Related

How To Get I / O Statistics On ASM Diskgroup

Jan 21, 2011

How can we check the I/O statistics on a ASM diskgroup?

Do we have any command which can be run @ sqlplus prompt after logging into the asm instance?

What steps can we take to increase the Read speed and the write speed on the ASM diskgroup.

View 1 Replies View Related

SQL & PL/SQL :: How To Compute Statistics On Index

Aug 28, 2010

1) Why we need to do Compute Statistic on index.

2) Is it only for optimizer to make a better plan?

3) If yes, which means, optimizer will not able to collect statistic by itself?

4) if I'm not collect statistic, then optimizer will do it or skip.

View 5 Replies View Related

Incremental Statistics Gathering?

Sep 12, 2012

am having Oracle 9i RAC on IBM AIX .

I have large partitioned tables ( 4 partitions are added every month ). Is is possible to collect Incremental Statistics Gathering on these objects ( 9i ). If I collect stats with Ggranularity => ALL and ESTIMATE_PERCENT =100 the stats are accurate but it takes so much time .

One way may be to collect stats as Ggranularity => PARTITION for each new partition ( this quite fast ). but what about the Global Table Stats?

View 7 Replies View Related

Database Versus System Statistics

Aug 26, 2011

In the article regarding gathering CBO Statistics, it states: QUOTE When an Oracle database is created, a job will be scheduled that will generate the database statistics for you. You will still need to collect system statistics however, as these are not collected by the automatic statistics gathering mechanism.

what is the difference between "database statistics" and "system statistics"? In other words, do I need to run this script for each schema owner in my 10g/11g instance?

variable whoami varchar2(20);
begin
select user into :whoami from dual;
end;
exec dbms_stats.gather_schema_stats( -
ownname => :whoami, -
options => 'GATHER AUTO', -
estimate_percent => 15, -
cascade => true).

View 2 Replies View Related

Oracle 11g - Exception While Importing Statistics?

Jul 14, 2010

I am using Oracle 11g R2 version.I want to import the DB statistics. But i am getting an exception when i execute the command DBMS_STATS.IMPORT_SCHEMA_STATS ('user1','STATS_INFO', '','', TRUE, FALSE).

The error is ORA-20000: no statistics are imported ORA-06512: at "SYS.DBMS_STATS", line 10603 ORA-06512: at line 1.

The privileges 'ANALYZE ANY' and 'ANALYZE ANY DICTIONARY' is already given to the user.Also i executed this command as sys. But still error occurs.

Same command is successfully executed in Oracle 10g. Is there any difference in importing the statistics in Oracle 10g and 11g ?

View 2 Replies View Related

SQL & PL/SQL :: Updating Table Statistics - View Not Appropriate

Oct 22, 2010

how many rows certain tables have.

updating the statistics for a table (with GATER_TABLE_STATS) and using NUM_ROWS then. This works fine for me as long as I am the owner of the table, but when someone else is, I always get this error: ORA-20000: Table does not exist or insufficient privileges.what privileges do I need to use GATHER_ TABLE_ STATS on all Tables, which were created by Users?

when I tried to use ANALYZE TABLE TEST_TABLE COMPUTE STATISTICS on a certain table I got the following error: a view is not appropriate here. The strange thing is, TEST_TABLE is not a view (at least it is not listed in ALL_VIEWS and is listed in ALL_TABLES, so it cant be a view right?).

Besides, is there another way to gather Table Statistics (not using Analyze Table or Gather_Table_Stats)?

View 3 Replies View Related

Windows :: How To Create Cmd Script For Ora Statistics

Aug 9, 2011

I need to do a cmd script for launching oracle statistics every week and scheduling it from task scheduler of my server with win 2008 R2 O.S.

the script is:

"statistiche-oracle.bat"

set ORACLE_SID=GW1
sqlplus "/ as sysdba" @statistiche.sql

and "statistiche.sql" is:

exec dbms_stats.gather_schema_stats(ownname => 'PDMUSER', cascade=>true)
exit

When I launch it from command line it stops with error message: "insufficient privileges" and ask me the user, so I put 'PDMUSER' that is my user, it asks also pwd, I put it and the works.

View 3 Replies View Related

PL/SQL :: Oracle 11g - Huge Table Statistics

Jun 13, 2012

I have a table which have 300+ columns and have 13 million rows. It is on a 32 kb block size. This is a table in data ware house environment. There no# of rows in the table haven't changed much but I see that the time taken to collect statistics have increased significantly.Initially it took only 15 minutes (with the same 13M rows) now it runs for 4+ hours. The max parallel servers is 4 (which is unchanged). The table is not partitioned.

OS: HP UX Itanium
Database: Oracle 11g (11.2.0.2)

Command is:
exec dbms_stats.gather_table_stats(ownname=>'ABC',tabname=>'ABC_LOAD',estimate_percent=>dbms_stats.auto_sample_size,cascade=>TRUE,DEGREE=>dbms_stats.auto_degree);

I would like to understand:

1) What could have been the causes of this change in time. 15 minutes to 4+hours ?
2) How can we gather statistics of huge table at a faster rate?

View 1 Replies View Related

PL/SQL :: Compute Statistics After Creating Index

May 15, 2013

I have to create some indexes in a production database. Do I need to Compute Statistics after creating indexes? Or when I create they automatically are computed?

The version I'm using is:

Oracle Database 10g Release 10.2.0.5.0 - 64bit Production
PL/SQL Release 10.2.0.5.0 - Production
CORE     10.2.0.5.0     Production
TNS for 64-bit Windows: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production

View 2 Replies View Related

SQL & PL/SQL :: Materialized Views - Take Statistics For Table Again?

Oct 26, 2012

I have created materialized view which hold few million records.Should i have to analyse the view and compute the statistics after i create the materialized view?

Also,just in case i need further indexing,should i have to take the statistics for the table again??

View 5 Replies View Related

Online Gathering Statistics For A Schema

Dec 24, 2012

Is it possible to gather stats for a schema which its in use. When i try to analyze the tables of a schema it shows that the statistics for that table is locked. So is it possible that instead of analyzing a table one by one , can i go for gathering the Schema stats while the objects of that Schema is still in use ( like DML or select statements being issued on those schema objects) .

DB version : 10.2.0.4
OS version : RHEL 5.8
DB type : RAC

View 12 Replies View Related

How To Lock Statistics In Column Level

Jul 4, 2012

i am using 10.2.0.1.0 version of oracle.

I have gathered frequency histogram manually on one of my column of a table to provide more information to optimizer for better calculation of cardinality.

Now i have my weekend job runs for gathering stats on schema level with method_opt as 'For all column size repeat'. But i don't want the stats of above column to be overridden by the stats job. I don't want to lock the statistics of whole table, but i just want to lock the column level stats for this table.

View 7 Replies View Related

Execution Plan Randomly Changes After Statistics Collection

Oct 10, 2012

I don't know, if this is the intent behavior of oracle or not. But i noticed, my queries Execution plan randomly changes after statistics collection. Several tables are truncated after the daily run at 8AM and statistics gathered for all the tables in that schema.

However execution plans for 2-3 sql statements always changes after this and performance is brought back to normal by executing the procedure by explicitly calling it from the command line with arguments instead of bind variables.

View 3 Replies View Related

Replication :: Gather Statistics On Materialized Views

Jul 15, 2009

Any best way to gather statistics on Materialized Views.

View 1 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

SQL & PL/SQL :: Privilege Required To Gather Table Statistics?

May 14, 2011

What privilege is required to gather table statistics using dbms_stats ?

View 2 Replies View Related

Performance Tuning :: Gather Statistics On SYS Objects

Mar 7, 2012

Do we need to gather statistics on SYS objects and fixed objects in 11gR2 database regularly ?

I read in some article that in 10gR2, statistics for SYS objects are by default whereas in 10gR1, statistics for SYS objects are not by default using

dbms_stats.gather_database_stats(
...
gather_sys = False
...
)

View 4 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

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

Extended Statistics Causes Incorrect Query Results?

Nov 2, 2012

I have the following DDL:

drop table tmp_guid;

CREATE TABLE tmp_guid (
      c1   raw(16) not null
     ,c2   raw(16) not null
);    
begin

[code]...

It seems that a combination of a unique index and extended stats are to blame. Removing any one of them causes the query to also produce correct results.Extended stats basically captures the fact that despite being unique, c1 depends on c2.

View 0 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

Gathering Statistics With Cascade Option Is Slow

Oct 9, 2012

The database is 11.2.0.3 on a linux machine. I issued the following command, but the session was a little slow. The table size is about 50 GB and has 3 indexes. I specified "degree=8" for parallel processing.

When gathering statistics on the table , parallel slaves were invoked and gathering statistics on the table has finished fast enough. However, when it goes to gathering statistics on the indexes, only one active session was invoked, and thus "degree=8" option was ignored.

My question is :

Do I need to use dbms_stats.gahter_index_stats instead of "cascade" option in order to gather statistic on the indexes with parallelism?

exec dbms_stats.gather_table_stats(ownname=>'SDPSTGOUT',tabname=>'OUT_SDP_CONTACT_HIS',estimate_percent=>10, degree=>8 , method_opt=>'FOR ALL COLUMNS SIZE 1',Granularity=>'ALL',cascade=>TRUE)

View 2 Replies View Related

Exadata :: Gather Statistics In Parallel On Schema

Sep 7, 2012

I am trying to gather stats in parallel on a schema in which tables are OLTP compressed

The command I use

BEGIN
DBMS_APPLICATION_INFO.set_module ('Gather Stats', user);
DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>'schemaname', DEGREE=>64, estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', CASCADE => TRUE, GRANULARITY => 'AUTO');
END;
/

I see blocker with wait event=PX DEQ: Execution Msg

Waiter with wait event= PX Deq: Execute Reply

When I run ASH report all I see is : IPC send completion sync

View 2 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 :: 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

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

Export/Import/SQL Loader :: Table Import Takes Long Time And Still Running?

Jun 22, 2012

MY DB Version: 10.2.o

OS: Windows Server 2003

I am trying to import on table which i have the export dump file which i take using expdp previously when i load that table on the same host

by using below command:

expdp scott/tiger@db10g tables=EMP,DEPT directory=TEST_DIR dumpfile=EMP_DEPT.dmp logfile=expdpEMP_DEPT.log

after that i zip that dump and move it to external usb and now i need that table i copy that table and unzip that that dump

Command i am using to do the import is :

impdp scott/tiger@db10g tables=EMP,DEPT directory=TEST_DIR dumpfile=EMP_DEPT.dmp logfile=impdpEMP_DEPT.log

But the query of import is still runing even not showing any amount of rows to be imported.

i already make the tablespace in which the table was previosuly before dropping but when i check the sapce of tablespace that is also not consuming one error i got preiviously while performing this task is:

Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
Master table "CDR"."SYS_IMPORT_TABLE_03" successfully loaded/unloaded
Starting "CDR"."SYS_IMPORT_TABLE_03":  cdr/********@tsiindia directory=TEST_DIR dumpfile=CAT_IN_DATA_042012.DMP tables=CAT_IN_DATA_042012 logfile=impdpCAT_IN_DATA_042012.log

[code]....

i check streams_pool_size it will show zero and then i make it to 48M and after that

SQL> show parameter streams_pool_size;
NAME                                 TYPE        VALUE
-----------
streams_pool_size                    big integer 48M

But still it takes time

View 13 Replies View Related







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