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
ADVERTISEMENT
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
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
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
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
May 16, 2011
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
View 16 Replies
View Related
Jan 26, 2012
I have created a table like below-
PROMPT CREATE TABLE tst_fetch_vendor_data
CREATE TABLE tst_fetch_vendor_data (
vendor_data_seq_no NUMBER NOT NULL,
study_seq_no NUMBER NOT NULL,
vendor_record_seq_no NUMBER NOT NULL,
control_column_seq_no NUMBER NOT NULL,
resolved_value VARCHAR2(4000) NULL,
original_value VARCHAR2(4000) NULL,
transaction_user VARCHAR2(30) NOT NULL,
[code]....
Its just a temporary table, in which data comes and goes. I am using this in middle of a process.I am using it in a process like below--
--EXECUTE IMMEDIATE 'TRUNCATE TABLE TST_FETCH_VENDOR_DATA DROP STORAGE';
insert /*+ append */ into tst_fetch_vendor_data
(select * from vendor_data vd
where vd.control_column_seq_no in
(select control_column_seq_no from temp_control_column));
dbms_stats.gather_table_stats('EPDSYSREP','TST_FETCH_VENDOR_DATA',ESTIMATE_PERCENT=>100,
METHOD_OPT=>'for all indexed columns size auto',CASCADE=>True);
code to use that table..This table can contain data from 0 to 108000000 records.Now my questions are-
1. How much should I select sampling size (currently its 100%)Can I use dbms_stats.auto_sample_size, what will be the effect?
2. dbms_stats is good approach or should I use dynamic sampling.
3. what about the approach using CTAS instead of inserting data through insert.
4. What about pl/sql table with index or with clause query.
5. Do I need to rebuild index after inserting data into table.
View 3 Replies
View Related
Jun 4, 2010
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?
View 10 Replies
View Related
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
Oct 10, 2012
Using oracle 11.2.0.3...How long does the content in recycle bin stay?
e.g is it dependant on size of undo tablespace and expires after certain time? Also if drop table, then recreate table with same name will this wipe the dropped table from the recycle bin automatically?
View 4 Replies
View Related
Jun 15, 2012
the most accurate/efficient way of obtaining the execution plan for a piece of running SQL in Oracle 9i. in 10g and 11g obviously dbms_xplan.display_cursor(sql_id) can be used,
How can this be achieved in 9i, currently I am simply obtaining the SQL_TEXT and then running an explain plan ("EXPLAIN PLAN FOR..") - I believe this is not necessarily the same explain plan that will be used for the sql that is executing though
View 7 Replies
View Related
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
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
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
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
Jul 5, 2011
I need to exclude a single schema from the autostats gathering feature in 11g. The tables in this schema are analyzed at the appropriate time via the application code. The autostats gathering job sometimes kicks in at a time in which the tables are getting updated or loaded which can skew explain plans during the updates/inserts.
I've searched through the oracle documentation and cannot find a way to simply "exclude" the schema without locking it. I see it is possible to disable the autostats at the entire db level but not at the schema level.
View 5 Replies
View Related
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
View Related
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
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
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
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
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
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
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
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
May 12, 2013
How to use see the query plan.
View 1 Replies
View Related
Aug 13, 2012
After we have upgraded our database from 10g to 11gR2 one of the sql started running very slow, is there any way to use the 10g sql plan for this query in 11gR2..? the 10g sql plan still shows up in history table along with it hash value.
We have tried using SPM but due select_workload_repository is set to basic(default) in 10g, the plans are not getting populated into profiles.
View 1 Replies
View Related
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
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
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
