I load a table through sql loader which takes nearly 14 min for 8-9 millions records, once the records complete i run the analyze table compute statics to gather stats and it takes nearly 15 min. is there any ways so that i can reduce the stats timing. the stats collection command runs from other schema not from where the table is residing.
View 1 RepliesI have a Datamart DB which has a continuously increasing volume. I run a daily optimize job, to have the data analysed using the below:-
EXECUTE dbms_stats.gather_schema_stats(ownname=> 'xxx' ,estimate_percent=> 25 , DEGREE => 4, cascade=> TRUE );
analyze table xxx.abc compute statistics; But this optimization itself is taking nearly 4 hrs to complete and I can't afford to have the delay.
Is there a better way of running this optimization?
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.
I support to get a handle on statistics collectionn in their data warehouses. It seems developers have created several ANALYZE TABLE jobs but the code for these is not stored as PLSQL in the database and thus it is problematic for statistics collection. Even if we collect stats that way we want, these jobs kick in and overlay the statistics we collect every day.
Is there a way to AUDIT ANALYZE TABLE? I can't find it anywhere.
Is there a way to globally turn of ANALYZE TABLE in a 9i database?
I have a procedure which mainly run queries on a Table which has nearly 9.5 million recodes. This procedures takes nearly 15 min to complete execution on our main database. I exported and imported the schema to our backup database and the same procedure just took 3 seconds to complete.
I tried to analyze the table in our main database and tried to execute the procedure again but did not show any improvements. ANALYZE TABLE DN_ACTIONS COMPUTE STATISTICS;
I am not sure computing the statistics for all the tables in the schema will work. I also checked there is enough disk space where oracle data files are stored. I am also turning on the sql trace to see what sql statements in the procedure is taking longer time.
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 RelatedIs 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?
Our UNDO space remains at a high level 85 to 95 percent. We keep adding database files and it doesn't seem to go down significantly. When we do a backup of the system where we shut the database down, it does go down some but then within a week or so it is back up again.
View 11 Replies View RelatedDuring 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 RelatedDo 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
...
)
We are using 11.2.0.3.0 on solaris 10 facing slow performance, following are the Wait Events in AWR report, Also if any specific document to analyze AWR report and to pin point the performance bottleneck.
Foreground Wait Events
**********************
Avg
%Time Total Wait wait Waits % DB
Event Waits -outs Time (s) (ms) /txn time
-------------------------- ------------ ----- ---------- ------- -------- ------
direct path read 308,729 0 21,191 69 58.0 39.5
db file sequential read 208,754 0 3,742 18 39.2 7.0
cursor: pin S 19,541,899 0 2,561 0 3,668.5 4.8
[code]....
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 RelatedOracle 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 RelatedI 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;
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
I have several databases that i've recently upgraded from 9i to 11g. With all of them, the automatic stats gathering process has worked just fine every night during the maintenance window.
However, i have this other database that i created and it seems that the only stats being gathered are on the sys and system schemas and not the actual schema that holds all of our tables.
I did some searching, but i'm not sure i was using the right search terms, because i came up empty.
BANNER
-----------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Solaris: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
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.
Using below script i run stats gather job..But LAST_ANALYZED column is not updated for the table.. Why it was not updated.. My DB version is 11.1.0.7.0
exec DBMS_STATS.GATHER_TABLE_STATS(ownname => 'XXXX', tabname => 'XXXXXX',estimate_percent =>5, method_opt => 'FOR ALL INDEXED COLUMNS SIZE AUTO', degree => 8, granularity => 'ALL',Cascade => TRUE);
attached query giving consistent execution plan but different timings across run
SELECT /*+ INDEX (CRT CRT_CUN_FK_I)*/
DISTINCT odr.dve_id
FROM company_requirements crt, orders odr, lelo_products la_pct
WHERE crt.qtn_cun_id = 10035637--10000021--10035667
AND crt.ID = odr.crt_id_quote_implemented
AND NVL (odr.cancellation_date, '31-Dec-9999') = '31-Dec-9999'
[code]....
we have 4 databases, 2 on each servers, such that db1 and db2 on server1 and db3 and db4 on server2
refer count of the records for column of biggest table in the query, taken on all 4 databases (The column is nullable)
select count(*) from company_requirements crt WHERE crt.qtn_cun_id = 10035637
db1 = 73335
db2 = 89073
db3 = 81182
db4 = 82936
First I executed the query on db1 and db2 while there wasn't any user logged on to the system
db1
**********
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.06 0.08 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 17.47 473.39 85704 1508102 0 0
[code]...
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 1 0.00 0.00
db file sequential read 85704 0.31 460.55
latch free 1 0.00 0.00
SQL*Net message from client 1 14.98 14.98
[code]...
Why the elasped time changed when data and plan hasn't changed at all? Also why the plan has different stats for round 1 and 2 on db1 and db2?
I ran it 2 times each round each database so hard parsing shall not be issue.Also why the number of rows accessed are different in db1,db2 and db3,db4 especially for step1 when count of crt.qtn_cun_id is similar?
In fact when the query was taking long I was the only user on the system Also I used hard coded value (no bind variables at all)
I checked num_rows, distinct keys as well which are quite similar across all 4 databases Also no stats where gather during the query execution
What I should have checked or monitored?
I used v$locked_object and v$lock query to get the output.. But still I'm an one year exp in ORACLE. How to analyze the output of lock queries. what are the parameters to be analyzed on AWR report.
How to do proper performance checkup in ORACLE database as well analyze it.
I have two tables with 113M records in DWH_BILL_DET & 103M in prd_rerate_chg_que and Im running following merge query, which is running for 13 hrs to update records, which is quiet longer time.
SQL> explain plan for MERGE /*+ parallel (rq, 16) */
INTO DWH_BILL_DET rq
USING (SELECT rated_que_rowid,
detail_rerate_flag_code,
rerate_sel_key,
[code].....
we about analyse tables in oracle.
1) why required table analyze.
2) how to do table analyze.
3) and after analyze what taking steps.
Currently we are deleting the partition daily. Table structure is Interval partitioning. After deleting partition how to analyze the partition table.
View 12 Replies View RelatedI am trying to update columns of Table A with the columns of Table B. Both these tables have 60,000 rows each. I tried this operation using following 2 queries:
Query 1
Update TableA A
set
(A.col1,A.col2,A.col3)=(select B.col1,B.col2,B.col3
from TableB
where A.CODE=B.CODE)
Query 2
Update TableA A
set
(A.col1,A.col2,A.col3)=(select B.col1,B.col2,B.col3
from TableB
where A.CODE=B.CODE)
where exists
A.code = (select B.code
from TableB B
where A.code=B.code)
When i execute these two above queries, it keeps executing indefinitely.
Below query is taking a long time...
select gam.SOL_ID,COUNT(gam.FORACID) from gam,smt where
gam.ACID=smt.ACID and gam.ACID NOT IN(select ACID from imt) and
gam.SCHM_TYPE in('SBA','CCA','CAA','ODA') and GAM.ACCT_CLS_FLG='N' and
gam.SOL_ID IN(select SOL_ID from IMT) group by gam.SOL_ID
/
attached is the explain plan.
in which index on IMT table is not used. And the query is doing a FTS on IMT table. What needs to be done to avoid FTS on IMT table.
What are the factors that decide on which column we should partition the table and which partition method we should chose.
View 2 Replies View RelatedI am using one script to delete the records from a table, its taking 1hr to delete.
declare
cursor c1 is select ownerid,ownertype from nightly_metric_projects
;
v1 c1%rowtype;
open c1;
loop
fetch c1 into v1;
exit when c1%notfound;
DELETE FROM DGT_ITEMEFFORTDATA WHERE OWNERTYPE = c1.OWNERTYPE
AND OWNERID = c1.OWNERID;
end loop;
close c1;
commit;
nightly_metric_projects--1200 records
DGT_ITEMEFFORTDATA--13200000
I have normal tables with hugh Data and would like to increase the performace by following means:
1) Add a new column in each table. Say this column Name is IS_LIVE. This new column have only two value 1 ( LIVE ) OR 0 ( NOT LIVE ).
2) Change the normal tables to Partitioned table. There would be only two partitioned in all the table. The partitioned key column would be IS_LIVE and both partitioend recrods would be in two different tablespace.
3) Added a POLICY function to these partitioned table to Always add a Query Predicate of '1' to all queuries.
I am interested to know that what kind of Indexes ( Global Or local ) would be suitable for these kind of Design.Is there any use of having Local index on IS_LIVE.Please note that Primary Key doesnot have this new column in it.
what analyzing a table does to existing indexes? Do I need to rebuild the indexes after dbms_stats.gather_table_stats command ?
View 4 Replies View RelatedObjective : To find solution to archieve data from 2 big tables which is occupying maximum size in the data base. With current data (From Jan 2005 to Sept 2011) it has records as mentioned below:
transaction - 41687927
trnansaction_dtl - 83945934
We need to load data and run monthly batches from October 2011 to current month which will increase this space.
1. Issue is there will not be having so much space.
2. Maintenance of such table is diffcult now.Also there is huge impact on performance. Can we think of partitioning the table base on date aswe query 1st table based on certain date range?
3. Most of reports use this table and creating performances issues