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
ADVERTISEMENT
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
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 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
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
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
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
Jan 20, 2011
I have a stocking program , i need to use opening balance + debit-credit
In the beginning of my cursor then what ever the result i need to but it in a variable to used for the next record; i try many time but i failed
my statement look like this
opening balance 1000
receive used balance
-----------------------------------------------
0 50 950
0 100 850
100 0 1850
View 3 Replies
View Related
Sep 3, 2010
"If no level 0 backup is available, then the behavior depends upon the compatibility mode setting. If compatibility is >=10.0.0, RMAN copies all blocks changed since the file was created, and stores the results as a level 1 backup. In other words, the SCN at the time the incremental backup is taken is the file creation SCN. If compatibility <10.0.0, RMAN generates a level 0 backup of the file contents at the time of the backup, to be consistent with the behavior in previous releases."
So yes, that's it. Even in the Oracle 11g OCP course and exam, the information given is for Oracle 9 (pre 10g). Seems like a major functional regression.
------------------------------------------------------------------------------------------------------------------------------------------------
Original problem.
Am I not understanding something about rman????
Using oracle 10g standard edition and rman.
No existing backups.
When I run repeated level 1 cumulative incrementals, the appear to back up everything (like a level 0 would).
My understanding is that if a level 1 is run w/o an existing level 0 backup, it will generate a level 0 backup.
All subsequent level 1 backups should be level 1's as expected.
If I explicitly generate an level 0, followed by level 1's it all works as expected.
I am determining what got backed up by the size of the resulting save sets.
Do I have to do an explicit level 0 and then explicit level 1's? I thought not.
View 1 Replies
View Related
Aug 19, 2011
i already created metalized view
now i want to set MV as incremental refresh option?
is it possible to set this attribute after mv created..?
View 1 Replies
View Related
Sep 5, 2010
I have RMAN configured TO RECOVERY WINDOW OF 7 DAYS;I do a Level 0 backup on SAT, SUN-FRI I do a Level 1 (all to disk)
After I do a successful Level 0 backup, why would I need to keep Level 1 backups around on disk?I know the recovery window is 7 days but is there any way to remove the other 6 days of Level 1 backups after a successful Level 0?To me this seems like a waste of space since I have a succesful Level 0 or am I missing something?
View 6 Replies
View Related
Nov 11, 2010
I've got a physical standby (10g) which is missing some archive logs, and thus the managed recovery is stuck. I followed the procedure for taking an incremental backup of the primary from the last SCN in the standby, and then recovering the standby from that backup using rman.
It seemed that everything went according to the instructions, except that the "recover database" of the standby from the backup went much faster than I expected. Afterwards I checked the SCN of the standby (select current_scn from v$database) and it had indeed updated to a number beyond where it was stuck originally. The standby controlfile was restored from a backup of the primary's current controlfile, and the standby restarted. The problem is that when I started up standby recovery again, the standby is still looking for the logs which were missing. I can't figure out why! I've been googling around and digging through the docs, and the only clues I can find suggest that this would happen if the standby controlfile wasn't updated. But I did that.
View 6 Replies
View Related
Jun 6, 2012
We are going to implement oracle 11g. Now I wanna use rman to take the incremental backup in the follwing way:
Level-0 backup weekly, in the way:
run
{
allocate channel c1 type disk;
allocate channel c2 type disk;
BACKUP incremental level 0 DATABASE
TAG 'Weekly_full' FORMAT 'E:RMAN_backupweekly_full_%d_%Y%M%D_s%s_p%p.bak';
backup archivelog all not backed up 1 times delete input
TAG 'Weekly_full_arc' FORMAT 'E:RMAN_backupweekly_full_arc_%d_%Y%M%D_s%s_p%p.bak';
[code].....
and RETENTION POLICY TO RECOVERY WINDOW is set to 10 days, so that there is no chance that i will delete expired backup before taking backup.
Now my question is,
1) Is the way is correct that there is no wrong configuration. My main concern is about archive log, is it correct?
2) As i am deleting archive log files with the backup and after that the expired archive log backup is also deleted, so is there any chance of failure in recovery.
3) here how archive log backup will work with 10 days retention policy.
View 10 Replies
View Related
Feb 15, 2013
We just purchased NAS (Raid5) unit to manage our data storage. I am planning to create a virtual partition on this nas device and use one partition for oracle data storage and another virtual partition will be used by other data (files and may be sqlserver data files....etc..)
We will have oracle installed on seperate oracle server. Can we use RMAN to manage incremental backups in this environment? May main worry point is that our data storage device will have many different type of datas and will we be able to tell RMAN to make backups only from certain virtual drives?
View 3 Replies
View Related
Jan 18, 2013
what is the difference between incremental and differential backup?
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
Mar 25, 2012
after i issue the following command,what checkpoint does it trigger?(full checkpoint or incremental checkpoint):
SQL> ALTE SYSTEM SWITCH LOGFILE;
View 1 Replies
View Related
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
May 17, 2013
As part of an incremental backup I recover copies of the tablespaces. These by default are stored in the disk backup location diskgroup. Is it possible to move these copies to another disk group / location whilst still backing up to the default disk backup location diskgroup. I know how to do it with the main datafiles but not with the copies.
e.g.
Current situation:
Original Datafiles in +DATA
Backups in +FRA
Recovered Datafile copies in +FRA
New setup to be:
Original Datafiles in +DATA
Backups in +FRA
Recovered Datafile copies in +BACKUP
View 4 Replies
View Related
Oct 15, 2012
In Oracle 11g/R2, I created replica of HR.Employees table & executed the following statement (+Although using SUM() function is non-logical in this case, but just testifying the result+)
STEP - 1
SELECT /+ RESULT_CACHE */ employee_id, first_name, last_name, SUM(salary)*
FROM HR.Employees_copy
WHERE department_id = 20
GROUP BY employee_id, first_name, last_name;
EMPLOYEE_ID FIRST_NAME LAST_NAME SUM(SALARY)
-------------------------------------------------------------------------------------------------------
202 Pat Fay 6000
201 Michael Hartstein 13000
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 3837552314
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 130 | 4 (25)| 00:00:01 |
| 1 | RESULT CACHE | 3acbj133x8qkq8f8m7zm0br3mu | | | | |
| 2 | HASH GROUP BY | | 2 | 130 | 4 (25)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | EMPLOYEES_COPY | 2 | 130 | 3 (0)| 00:00:01 |
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
*690* bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
STEP - 2
INSERT INTO HR.employees_copy
VALUES(200, 'Dummy', 'User','Dummy.User@email.com',NULL, sysdate, 'MANAGER',5000, NULL,NULL,20);
STEP - 3
SELECT /*+ RESULT_CACHE */ employee_id, first_name, last_name, SUM(salary)
FROM HR.Employees_copy
WHERE department_id = 20
GROUP BY employee_id, first_name, last_name;
EMPLOYEE_ID FIRST_NAME LAST_NAME SUM(SALARY)
--------------------------------------------------------------------------------------------------
202 Pat Fay 6000
201 Michael Hartstein 13000
200 Dummy User 5000
Elapsed: 00:00:00.03
Execution Plan
----------------------------------------------------------
Plan hash value: 3837552314
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 195 | 4 (25)| 00:00:01 |
| 1 | RESULT CACHE | 3acbj133x8qkq8f8m7zm0br3mu | | | | |
| 2 | HASH GROUP BY | | 3 | 195 | 4 (25)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| EMPLOYEES_COPY | 3 | 195 | 3 (0)| 00:00:01 |
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
*714* bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3 rows processed
In the execution plan of STEP-3, against ID-1 the operation RESULT CACHE is shown which shows the result has been retrieved directly from Result cache. Does this mean that Oracle Server has Incrementally Retrieved the resultset?
Because, before the execution of STEP-2, the cache contained only 2 records. Then 1 record was inserted but after STEP-3, a total of 3 records was returned from cache. Does this mean that newly inserted row is retrieved from database and merged to the cached result of STEP-1?
If Oracle server has incrementally retrieved and merged newly inserted record, what mechanism is being used by the Oracle to do so?
View 2 Replies
View Related
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
View Related
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
May 3, 2012
Oracle database concepts 11gr2 manual states that "An incremental checkpoint is a type of thread checkpoint partly intended to avoid writing large numbers of blocks at online redo log switches. DBWn checks at least every three seconds to determine whether it has work to do. When DBWn writes dirty buffers, it advances the checkpoint position, causing CKPT to write the checkpoint position to the control file, but not to the data file headers."
As i have understand the DBWR write DIRTY BUFFER (that are under active checkpoint requests in the ACQ) to the datafiles from The Buffer Checkpoint Queues and after that the CKPT process may write the checkpoint RBA to the control files.[URL]...
The CKPT porcess write the chekpoint RBA to the controlfile after that the DBWR write the Dirty BUFFER in the datafiles, so as i understand at some point the datafiles containt block with higher RBA than the controle files. am i wrong ?
So if the instance craches before the CKPT record the chekpoint RBA in the controlfile what will be the recover behavior.Will the redo record be reapplied from the last recoded chekpoint RBA in the controlfile to the tail of the log ?
View 2 Replies
View Related
Aug 24, 2012
Here we have a dataguard environment with db1 as primary and db2 as physical standby database. We configured dataguard in 10g environments and we are not using grid control. What are the steps for configuring rman for incremental backups in this environment?
We want to schedule the backups to be taken in standby db, (that is applicable in case of switchover too). In the case of switchover to db2 what all do we need to do in rman manually to resume backups in db1?
View 1 Replies
View Related
Sep 10, 2012
7 of days ago Full backup has been taken to disk due to issues with tape devices.3 days ago tape devices have been fixed and we switched to CommVault managed tape backups. CommVault calls RMAN with following command:
run {
allocate channel ch1 type 'sbt_tape'
PARMS="SBT_LIBRARY=/usr/local/bin/simpana/Base/libobk.so,BLKSIZE=1048576,ENV=(CV_mmsApiVsn=2,CV_channelPar=ch1,ThreadCommandLine=BACKUP -jm 45 -a 2:71 -cl 9 -ins 9 -at 22 -j 294321 -jt 294321:4:1 -bal 1 -rcp 0 -ms 1 -data -ma 89 -cn oraclehost -vm Instance001)"
TRACE 0;
setlimit channel ch1 maxopenfiles 8;
backup
incremental level = 0
filesperset = 32
database
include current controlfile spfile ;
}
exit;
These backups done successfully.Then archivelog backup taken similar way.But when I issue RESTORE DATABASE PREVIEW SUMMARY; RMAN starts with Full backup set, though newer Incremental Level 0 ones are available.Why it does not use these newer ones?
Oracle 10.2.0.5 for Linux on IBM POWER
View 2 Replies
View Related
Nov 13, 2013
Any procedure which we can initiate an incremental backup without Level 0 backup.
View 9 Replies
View Related
