SQL & PL/SQL :: Any Way To Get Stats Quickly

Dec 10, 2012

I have used the following statement to gather the stats of a table..

EXEC dbms_stats.gather_table_stats('APP','SALE_AMT',ESTIMATE_PERCENT=>100,CASCADE=>True);

To gather the stats it is taking one hour and the table SALE_AMT is having 237213998 records.is there any way to get the stats quickly.

View 3 Replies


ADVERTISEMENT

Performance Tuning :: Using Stats Table Instead Of Regular Stats To Get Plan?

Jan 27, 2011

DBMS_STATS.CREATE_STATS_TABLE
DBMS_STATS.EXPORT_SCHEMA_STATS
DBMS_STATS.GATHER_TABLE_STATS

I have used the above to get a copy of schema stats and gather new stats for specific tables into a STATS TABLE in my personal schema. What I want to do now is use this stats table to generate plans for queries where I believe stats are off. Is it even possible? To be clear, I do not want to import stats because this replaces the stats currently there. I just want to point the CBO to my stats table for generating plans.

there was a session parameter I could set to tell oracle to use my stats table when generating plans, or an explain plan clause I could use or a DBMS_XPLAN paramter I could provide that would tell these tools to use my stats table when generating a plan, or even some way to tell autotrace. But I have found none of this.

View 2 Replies View Related

Server Administration :: Delete Stats Gather Stats

Sep 11, 2012

During STATS gather running for the table, unknowingly i deleted the old stats using EXEC DBMS_STATS.DELETE_TABLE_STATS. I would like to know will it affect the stats gather job currently running for the table and whether my stats will be gathered successfully.

View 5 Replies View Related

Adding Hundreds Of New Rows To Table Quickly

Jul 13, 2007

Ive got loads of new entries that need to be entered into a table if they're not in there already.

Is there a quick way of doing this rather than having to add the entries manually using some kind of tool rather than using manual sql statements(would take a very long time since theres 800 new entries).?

View 5 Replies View Related

Real Application Clusters :: Quickly Relocate Resources To Available Node?

Sep 21, 2012

version : 11.2.0.2 on RHEL 5.4

In our 2-node RAC , Node1 was hung for a while. So, I wanted to restart that Instance. But I had some 50 services running in Instance 1(Preferred Instance) which has to relocated before I bounce the instance.

Question 1.
Instead of using srvctl relocate for each service, Is there any way to quickly relocate all services to the Node2 (Available Instance) ?

Question2.
When a service is relocated , are DMLs running from sessions using that service be 'moved' to the available instance ? Or, is it just the SELECTs that can be failed over ?

View 1 Replies View Related

Login To Database - Connection Establish Quickly When Count Reduced

Aug 27, 2012

In the recent past we have been observing the DB connection from application to database using ODBC is taking long time. When I fire LSNRCTL SERVICE command, for our instance the ESTABLISHED = count is being shown a huge number like 45000 and even more some times.

When we restart the instance suing shutdown immediate or abort commands. The established connections count is becoming 0 and again getting increased slowly. Once the count is reduced the connections are getting established quickly without any issue.

View 9 Replies View Related

Tkprof Stats For A Query?

Oct 21, 2011

understand this stats from the TKPROF. The below stats is for INSERT SELECT statment.

Here what does Disk and Current values refer ?
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 1 1141.84 1117.85 1829639 517129893 1749 109
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1 1141.84 1117.85 1829639 517129893 1749 109

[code]...

View 1 Replies View Related

Security :: Privileges For Stats

Aug 31, 2012

My tables are in ers_stg schema and code which collects state on these table are in etls_ers schema, what permission i need in order to get the stats collected from etls_srs schema. i am getting in sufficient privilege error.

View 1 Replies View Related

Stats Collection In Oracle 11g

Nov 9, 2013

while doing stats collection weather system takes the backup of current statistics. i think we can specify stattab. but weather it takes stats backup before over writing? I got this requirement as a part of upgrade, i have already gone through export_schema_stats and import_schema stats already.  Just trying all other possible options only.

View 4 Replies View Related

Gather Stats On Table

May 26, 2011

I have doupts in gathering stats on table. I analyzed one table it took 2 hours first time.. the same table after one week later i analyzed, its got completed within 45 minutes.. I don't know exact reason why i got completed very soon. Is there any specific reason?

View 2 Replies View Related

Automated Gathering Of Optimizer Stats

Sep 10, 2013

I am quiet confused with the optimizer collection stats job on 11g. when run the following query i see the statistics enabled.

SQL> select client_name, status from dba_autotask_client;

CLIENT_NAME STATUS
---------------------------------------------------------------- --------
auto optimizer stats collection ENABLED
auto space advisor ENABLED
sql tuning advisor ENABLED

I can gather statistics manually using DBMS_STATS, but there is no automated gathering of optimizer stats. how can i have system run and collect statistics on a daily bases?

View 1 Replies View Related

How To Check Cursor Stats At Run Time

Sep 24, 2011

I have a stored procedure running in which there is a cursor which fetches around 1500000 records and then query another table using the fetched record values.

I cannot modify the procedure as its on production. I want to know which cursor record is currently being processed by the procedure, and how many are still remaining ? How to check the cursor stats at runtime. I want to check up to which record the cursor has been fetched and how many are still remaining. I have cursor name. Is there some dynamic view to check cursor stats at runtime ?

View 1 Replies View Related

SQL & PL/SQL :: 11g Oracle Upgrade Stats Table?

Jan 30, 2012

When we upgrade from 10g to 11g oracle upgrades the stats table by EXECUTE DBMS_STATS.UPGRADE_STAT_TABLE('OWNER','TABLE'); But When we import from 10g to 11g do we need to upgarde the stats table??

View 2 Replies View Related

PL/SQL :: Generate Stats For DBLink Table (SQL)

Sep 7, 2012

I am trying to generate some statistics on tables connected by a dblink. I know with oracle you have table_columns which you can reference and pull some stats from.

Trying to get the column count and record counts for each table connected by a dblink. I have tried these queries below to see if I could see any db properties: (some just to try something different)

select * from "table_owner".table_column@dblink ;
select * from "status"@dblink;
select /*DRIVING_SITE(a) */ count(*) from @dblink a;

What is the best method to finding this out without spending a lot of time? I have over 30 tables which are with large record sets and would love to learn a faster approach then pulling a sample table and doing a manual count and query for each table to count the rows.

View 3 Replies View Related

Stats-pack Not Working On RAC 2 Node?

Jun 15, 2013

We have 2 node oracle 11.2.0.3 RAC database and we enabled stats-pack on the database(since its STD edition). However we found that stats pack report coming from 1st node properly but second node it show as below. 

Instance     DB Name        Snap Id   Snap Started    Level Comment------------ ------------ --------- ----------------- ----- --------------------INST2         PRDDB  2139 15 Jun 2013 04:00     5                               2140 15 Jun 2013 04:00     5                               2151 15 Jun 2013 04:00     5                               2152 15 Jun 2013 04:00     5                               2153 15 Jun 2013 04:00     5                               2154 15 Jun 2013 04:00     5                               2155 15 Jun 2013 04:00     5                               2156 15 Jun 2013 05:00     5                               2157 15 Jun 2013 05:00     5                               2158 15 Jun 2013 05:00    

[code][....

View 3 Replies View Related

Does Old Plan Stay After Re-gathering Stats

Oct 1, 2012

I am on 11.2 on Linux.I am looking into a performance issue. The issue is around 1 particular SQL, involving about 5 tables.I re-gathered statistics on 2 main tables in the query (out of 5 tables).

When I say re-gathered, I first did DBMS_STATS.DELETE_TABLE_STATS and then did DBMS_STATS.GATHER_TABLE_STATS.

Earlier, we had histograms on these tables, which I removed and gathered stats without generating histograms.
SQL> select table_name, num_rows, sample_size, last_analyzed from user_tables where
  2  table_name in ( 'DETAIL_TABLE','MASTER_TABLE');

TABLE_NAME                       NUM_ROWS SAMPLE_SIZE LAST_ANALYZED
------------------------------ ---------- ----------- -------------------
MASTER_TABLE                     50615338    50615338 01/10/2012 11:09:27
DETAIL_TABLE                    353550440   353550440 01/10/2012 11:10:05

2 rows selected.Then ran the SQL again couple of times (actually, that SQL is in a stored procedure, which I ran couple of times).I found this wonderfull SQL on internet, which tells me when the SQL ran and which plan (identified by its hash value) it used. Using this SQL I tried to check if my SQL was run using any different plan, but it used exactly same plan it used before I re-gathered the stats. See the last analyzed time above and begin_interval_time below, same SQL has run before and after stats collection, with same plan_hash_value.

SQL> select ss.snap_id, ss.instance_number node, begin_interval_time, sql_id, plan_hash_value,
  2  nvl(executions_delta,0) execs,
  3  (elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 avg_etime,
  4  (buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio
  5  from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS

[code]....

7 rows selected.

My question is, when I re-gathered stats on 2 tables out of 5 tables in a given SQL, are the plans not flushed out of SGA? I was expecting that, at least a new plan hash value would show up front of my SQL, before and after stats collection.

View 8 Replies View Related

Stats Gather Error On Production Database

Aug 27, 2012

I got following error when i am gathered stats on Schema level.

SQL> EXEC dbms_stats.gather_schema_stats(ownname=>'KDB', estimate_percent=>dbms_stats.auto_sample_size,cascade=>TRUE, force=>TRUE);
BEGIN dbms_stats.gather_schema_stats(ownname=>'MFDB', estimate_percent=>dbms_stats.auto_sample_size,cascade=>TRUE, force=>TRUE); END;
*
ERROR at line 1:
ORA-20003: Specified bug number (9196440) does not exist
ORA-06512: at "SYS.DBMS_STATS", line 15342
ORA-06512: at "SYS.DBMS_STATS", line 15688
ORA-06512: at "SYS.DBMS_STATS", line 15766
ORA-06512: at "SYS.DBMS_STATS", line 15725
ORA-06512: at line 1

SQL> EXEC dbms_stats.gather_schema_stats(ownname=>'CDC_DATA', estimate_percent=>dbms_stats.auto_sample_size,cascade=>TRUE,DEGREE=>10);

BEGIN dbms_stats.gather_schema_stats(ownname=>'CDOMIG_DATA', estimate_percent=>dbms_stats.auto_sample_size,cascade=>TRUE,DEGREE=>10); END;
*
ERROR at line 1:
ORA-20003: Specified bug number (9196440) does not exist
ORA-06512: at "SYS.DBMS_STATS", line 15342
ORA-06512: at "SYS.DBMS_STATS", line 15688
ORA-06512: at "SYS.DBMS_STATS", line 15766
ORA-06512: at "SYS.DBMS_STATS", line 15725
ORA-06512: at line 1

View 5 Replies View Related

Performance Tuning :: Incremental Stats Not Happening

Oct 15, 2012

I have set the incremental stats for my partition table as it takes more than 20 min to gather , though the incremental is set to 'true' the table is getting analyzed completely.

View 3 Replies View Related

Server Administration :: Collect Table Stats?

Oct 10, 2013

We are running 11g (11.2.0.3)We have a "working table" that is empty at the beginning of the day.Then we start adding rows (insert) with a key column called STATE with a value of 100.At the same time, there are other apps that pickup data in state 100 , process that data and change that state to 200 or 300.There is also another app that pickup data in state 200 , process that data and change that state to 300 or 400.

So in summary, the data on that table is at the beginning empty, then all the rows are in state 100, they slowly move to different states (200, 300, etc) and by the end of the day, they are all in 400.

My question is what would be the best way to collect stats on this table?

I was thinking to create an hourly job to collect stats on that table:
exec dbms_stats.gather_table_stats (
ownname => 'SCOTT',
tabname => 'WORK_T

View 6 Replies View Related

Client Tools :: Spool Only Time And Stats?

May 9, 2010

m spooling the results of some performance tests to a text file, the test script im using calls various sql statements in other scripts and spools to the results, stats and time to the same file...Howver I dont want to spool all the results... i only want the stats and time.. how can i do this?

my script looks like:
=====================================================
SET TERMOUT OFF
SET AUTOTRACE ON STAT
SPOOL f:/testresults.txt

[code]...

View 39 Replies View Related

Performance Tuning :: DBMS Stats Is Gathering

Oct 26, 2010

Oracle 10g has the feature of automatic stats gathering in this case is it necessary to run DBMS_STATS on tables manually. Does the stats gathered become stale when the auto stat runs ?

View 1 Replies View Related

Server Administration :: DBMS-STATS And ORA-20000?

Dec 20, 2011

I've just installed an Oracle 11.2.0.1.0 64 bit server on my windows 7 machine in order to play around while using attempting to run

exec dbms_stats.gather_table_stats('eii','v2x4e')

I get the following:
ORA-20000: Unable to analyze TABLE "EII"."V2X4E", insufficient privileges or does not exist
ORA-06512: at "SYS.DBMS_STATS", line 20327
ORA-06512: at "SYS.DBMS_STATS", line 20360
ORA-06512: at line 1

My initial google searches indicate that I need the select any table and analyze any privileges. I don't think that can be right/appropriate - but I've granted them anyway to no avail.

Select * from user_tables

returns tables in the System and sysaux tablespaces, but not my own schema/tablespace?

View 5 Replies View Related

Performance Tuning :: Stats On A Partitioned Table?

Aug 8, 2013

Is it possible for the DBMS_STATS "LIST STALE" command to show a stale partition but NOT have its table show as stale?

I had a scenario where the table itself AND 1 partition showed as stale. I ran a fnd_stats gather table stats just on that 1 partition. Once it was completed it showed the partition to no longer be stale. it also showed that the table was no longer stale. so I guess I do not need to run stats on the whole table as well?

so if this is the case, when would I need to run stats on the full partitioned table if running it on the partitions themselves removes the staleness of the table?

View 3 Replies View Related

Drop Partitions Stale Percent Of Stats?

Nov 12, 2012

In my database,stale_percent is set to 10. and i have table which has partition. i have dropped table partition dropped which has 10% of data. I would like to know whether oracle will consider only insert,update,delete as stale percent or will it include the dropping paritition data also. Because my stats gather is not running. When i include drop partition data it exceed 10% of stale_percent,But excluding dropped partition it is not exceeds 10% of stale.

TABLE_NAME PARTITION_NAME SUBPARTITION_NAME INSERTS UPDATES DELETES TIMESTAMP TRU DROP_SEGMENTS
------------------------------ ------------------------------ ------------------------------ ---------- ---------- ---------- ----------- --- -------------
sample_DATA_DATA 235825577 0 0 11-NOV-2012 NO 3
test_DATA_DATA 811618472 0 0 11-NOV-2012 NO 12
sample_DATA_DATA SYS_P2665099 3005966 0 0 11-NOV-2012 NO 0
sample_DATA_DATA SYS_P2665119 3873671 0 0 11-NOV-2012 NO 0

[code].....

View 6 Replies View Related

Find Schema Stats Lock Or Unlock

Apr 20, 2013

How to find schema stats locked or unlocked?

View 3 Replies View Related

Performance Tuning :: Speed Up Gathering Of Stats

Jun 2, 2011

I am gathering stats by using below block i.e., for some 3 million records and there are 6 indexes on the table. What is the relevance of value 4 here (i.e., method_opt => 'FOR ALL INDEXED COLUMNS SIZE 4')? If I increase 4 to 250 will there be any speed change in gathering stats. My intention is to speed up the gathering of stats.

begin
dbms_stats.gather_table_stats(
ownname => SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA'),
tabname => 'LEGAL_VIEW_TARGET',
method_opt => 'FOR ALL INDEXED COLUMNS SIZE 4',
cascade => TRUE
);
END;

View 12 Replies View Related

Server Administration :: Privilege Require To Gather Stats?

May 27, 2012

what privilege is required to run dbms_stats package for gathering table and schema statistics.

SQL> show user
USER is "JACK"
SQL>
SQL> select * from user_sys_privs;

no rows selected

SQL> select * from user_role_privs;

no rows selected

SQL> select * from role_sys_privs;

no rows selected

SQL> exec dbms_stats.gather_table_stats('JACK','EN1')

PL/SQL procedure successfully completed.

I revoke all the privileges from JACK user but still i am able to gather stats for a table.what privilege is require to gather stats.

View 4 Replies View Related

Client Tools :: Auto Optimizer Stats Collection

Sep 20, 2012

In my database auto optimizer stats collection job scheduled. It is successfully running,i am confirming this by viewing DBA_AUTOTASK_JOB_HISTORY. My doubt is whether stats gather job collect statistics when 10%(default value) of data modified in a hole table or table partitions.

Below is the output for user_tab_modification.I am able to see two entry for same table.

SQL> select TABLE_NAME,PARTITION_NAME,INSERTS,UPDATES,DELETES,TIMESTAMP,TRUNCATED from USER_tab_modifications where table_name='DLM_PERFORMANCE_DATA';

TABLE_NAME PARTITION_NAME INSERTS UPDATES DELETES TIMESTAMP TRU
------------------------------ ------------------------------ ---------- ---------- ---------- ----------- ---
DLM_PERFORMANCE_DATA 169812174 0 0 20-SEP-2012 NO
DLM_PERFORMANCE_DATA SYS_P2663580 4946409 0 0 20-SEP-2012 NO

View 4 Replies View Related

Server Administration :: Method To Execute Schema Stats?

Mar 22, 2012

When we want to gather schema statistics which method we should follow and why ?

exec dbms_utility.compile_schema('SHIKHAR');

or

BEGIN
SYS.DBMS_STATS.GATHER_SCHEMA_STATS (
OwnName => 'JACK'

[Code].....

View 3 Replies View Related

Server Administration :: Remove Synonym STATS$SNAPSHOT-ID?

Sep 11, 2007

I've got to collect statistics by DBMS_STAT. For the first time, the spcreate.sql is to be called, however, it returned one error following:

If this script is automatically called from spcreate (which is the supported method), all STATSPACK segments will be created in the PERFSTAT user's default tablespace.

Using perfstat tablespace to store Statspack objects

... Creating STATS$SNAPSHOT_ID Sequence Sequence created.

create public synonym STATS$SNAPSHOT_ID for STATS$SNAPSHOT_ID
*
ERROR at line 1:
ORA-00955: name is already used by an existing object Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options

And then, I checked it again with its owner:

SQL> select owner, synonym_name
2 from dba_synonyms
3 where synonym_name='STATS$SNAPSHOT_ID';

OWNER SYNONYM_NAME
------------------------------ ------------------------------
PUBLIC STATS$SNAPSHOT_ID

Then, I checked again after dropping schema PERFSTAT:

SQL> conn /as sysdba
Connected.
SQL>
SQL> select username, account_status

[code]...

What's the STATS@SNAPSHOT_ID synonym? Can I remove it from PUBLIC owner and recreate?

View 13 Replies View Related







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