PL/SQL :: High Consistent Reads / Cannot Replicate In Test Environment
Feb 8, 2013
I am having performance issues on a query in a production environment that I cannot replicate in our test environment. Our test environment is an import of production. Version information is:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for Solaris: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - ProductionWhen I run the query in test, I get the following results using TKPROF
[code]....
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWSThis performance problem started a few weeks ago and the problem seems to be in the high number of consistent reads during fetch. The DBA tried restarting the instance and gathering fresh stats on the tables but that has not made a difference. Now he wants to export the tables, drop the schema and re-import.
View 8 Replies
ADVERTISEMENT
Aug 29, 2012
recently i have tried to upgrrade from oracle 11.2.0.2 to 11.2.0.3 in a test environment (virtual machine).
there i have mentioned the SID as ORCL, but when we took the same snapshot to other machine (VM). iam unable to connect to the VM, unless i gave the local net service configuration as ORCL but it is unable to connect. but when i ran the query "select * from GLOBAL_NAME" output was ORCL. domain. com again i have configured in LNSC as ORCL.DOMAIN.COM it is working.
but i have given the sid as ORCL..
View 5 Replies
View Related
Feb 15, 2012
We have a RAC in a test environment.It's 2 nodes connecting to an IBM DS3512. The DS3512 has one pool of 8 hard disks (RAID10) which has been then sliced into logical drives:
2 x 1GB
then
2 * 400GB
I have been tasked with changing the current configuration as follow:
2 pools of 4HDs in a RAID10 configuration
Then from the first pool:
2 * 1GB
1 * 398GB
from 2nd pool:
1 * 400GB
What I intend to do:
1) shutdown
SQL> shutdown immediate;
./srvctl crs stop
2) backup
dd if=votingdisk of=voting_disk_backup
I already got backups of OCR in $CRS_HOME/cdata/crs/
3) Perform changes on SAN
4) recreate ocfs2 partitions
5) mount the disks to their folder.
6) recovery
recover voting from backup
recover ocr from backup
7) Turn service on again
./srvctl crs start
SQL> startup;
Would this scenario work. There can be some glitches like the disk names changing after the reconfiguration but I only need to modify fstab for that.
View 3 Replies
View Related
Aug 20, 2012
I wanted to downgrade our 2 node RAC 11.2.0.3 to previous version11.2.0.2 on our test environment, so i could prepare to upgrade production later.
I followed oracle documentation and issued
'/u01/app/11.2.0.3/grid/crs/install/rootcrs.pl -downgrade -oldcrshome /u01/app/11.2.0/grid -version 11.2.0.2.0'
and both nodes and manually removed file in upgraded oracle home (rm -r u01/app/11.2.0.3).
This seems to have caused havoc and ASM and srvctl are no working. lsinvemntory lsinventory still poitns to removed oracle 11.2.0.3
I tried to set
export ORACLE_HOME=$ORACLE_HOME
.oraenv
+ASM1
srvctl config ##n
srvctl config
PRCR-1119 : Failed to look up CRS resources of database type
PRCR-1115 : Failed to find entities of type resource that match filters (TYPE == ora.database.type) and contain attributes DB_UNIQUE_NAME,ORACLE_HOME,VERSION
Cannot communicate with crsd
View 11 Replies
View Related
Jul 10, 2012
We need to do a node eviction test. How do I do a manual node eviction?
View 5 Replies
View Related
Jun 13, 2012
I've just patched a development oracle environment (RAC 11GR2) with temporary patch 8730312. I would like to do same thing on Production RAC. Is it necessary to test all applications on dev environment with this kind of patch before deploy it on Production ?
View 2 Replies
View Related
Jun 19, 2012
I'm trying to move my backup sets from windows database environment, to OEL 5.7 environment on another server.
I've found a manual [URL] by which I am trying to do it.I took backup sets from last night's backup using RMAN,and the current parameter(initSID.ora) file from the running live database.Now i need to configure control files in the pfile accordingly.
1. can i take current control files from the running system, to restore and recover backup sets from last night, to the state the database was at backup time?
2. how can i find out if control files are backed up and know by RMAN? "list backup completed after '2012-JUN-19';" >> gives me Archive redo logs, datafiles, but don't see the control files(or don't reconize them).
View 15 Replies
View Related
Jun 27, 2012
Does the counter of event buffer gets include the logical reads ? Does the mertic buffer gets include the event of reading from undo buffer ?
View 4 Replies
View Related
Aug 30, 2013
AWR Report regarding ' SQL ordered by Physical Reads (UnOptimized))'. SQL ordered by Physical Reads (UnOptimized)UnOptimized Read Reqs =
Physical Read Reqts - Optimized Read Reqs %Opt - Optimized Reads as percentage of SQL Read Requests %Total - UnOptimized Read Reqs as a percentage of Total UnOptimized Read Reqs Total Physical Read Requests: 3,311,261 Captured SQL account for 13.5% of Total Total UnOptimized Read Requests: 3,311,261 Captured SQL account for 13.5% of Total Total Optimized Read Requests: 1 Captured SQL account for 0.0% of Total UnOptimized Read ReqsPhysical Read ReqsExecutionsUnOptimized Reqs per Exec%Opt%TotalSQL IdSQL ModuleSQL
[code]....
I want to know what is the mean by 'UnOptimized Read Reqs' and 'Optimized Read Reqs 'Question 2) what is 'SQL ordered by Physical Reads tuning'.
View 2 Replies
View Related
Apr 23, 2012
Attached query taking 80000 reads to execute...
View 8 Replies
View Related
Nov 21, 2011
On 10gR2, I cannot find the exact definition of the 2 following metrics :
"Physical Read Total Bytes Per Sec"
and
"Physical Read Bytes Per Sec"
I would want to know what is the difference between them.
View 3 Replies
View Related
Oct 7, 2013
Firstly the table structures :
Create table cd_patient (pat_mrn varchar2(100)) ;
Create table cd_encount (pat_mrn varchar2(100), encounter_id varchar2(1000));
I need to write a code which dynamically reads the table name from user_tables (starting with cd) & load the data from remote database which has the same table name & structure based on the load number..
Static PLSQL statement would be :
declare
BEGIN
INSERT INTO "cd_patient" C-- DYNAMICALLY GET THE TABLE NAME
SELECT C1.PAT_MRN -- DYNAMICALLY GET THE COLUMN NAME
FROM REMOTE_DB.CD_PATIENT@XXX C1
WHERE C1.LOAD_NUMBER > 2;
[code]....
View 11 Replies
View Related
Aug 31, 2010
My understanding of DB_FILE_MULTIBLOCK_READ_COUNT parameter is that it affects only Full Table Scans and Fast Full Index Scans - all other disk retrieval is single block.If so, then maybe I'm reading this trace incorrectly:
select /*+ first_rows */ pk
from test_join_tgt
where pk >= 0
and rownum > 1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 21.48 27.77 22368 22134 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 21.49 27.78 22368 22134 0 0
[code]...
What the heck is going on with the Multi-block reads in the Range Scan?
View 3 Replies
View Related
Mar 22, 2011
i am given a database to recover. The person who gave me the database says that he has taken a full hot backup of the database on 19th march and i was also given the archives of the incremental backups until 21march now how can i check if the backup he has taken is consistent or not? after unzipping the available archivelogfiles on and after 19th(THE DATE on which full backup was taken) i tried to open the database using
>startup mount
>recover database using backup controlfile until cancel;
here i got an error and i found out that the archive log files after 13 march are missing.i queried select * from v$log and found that the last log was on 13 March. is this the correct way to say that, since this returned 13 MARCH the database is taken full backup on 13 March itself?
but the person who has taken full backup is saying that he has taken the full backup on 19th march.
how can i confirm whether the full backup taken on 19th was not consistent concretely? are there any other ways to find whether a backup taken is consistent or not i am not using RMAN backup i want to know the procedure when i have to do it manually
View 1 Replies
View Related
Jun 27, 2011
How can I reduce the consistent gets of the sql:
select b.OFFER_ID,
b.OFFER_CODE,
b.OFFER_NAME,
b.OFFER_COMMENTS,
b.BAND_ID,
b.CAN_BE_BUY_ALONE,
b.PRICING_PLAN_ID,
b.PRIORITY,
b.STATE,
[code]...
I have already added the indexes for the outer sql to reduce the cost from 374 to 150. But the the consistent gets seems not reduce that much. I notice that the function will cost 6 consistents gets per execution. It seems it not very high. But if this function goes with the sql, it will cost a very high consistent gets, if I remove this function, the sql only cost 1903 consistents gets.
So what I am thinking there should be two ways to reduce the consistent gets..The first one is reduce the recursive call of the sql. The second is reduce the consistent gets of the function. (but it seems that the consistent gets in this function is very low, only 6.)
View 39 Replies
View Related
Sep 16, 2012
After running the following command:
RUN{
SET NEWNAME FOR DATAFILE 1 TO 'F:oracleproduct10.2.0oradata estsystem01.dbf';
SET NEWNAME FOR DATAFILE 2 TO 'F:oracleproduct10.2.0oradata estundotbs01.dbf';
SET NEWNAME FOR DATAFILE 3 TO 'F:oracleproduct10.2.0oradata estsysaux01.dbf';
SET NEWNAME FOR DATAFILE 4 TO 'F:oracleproduct10.2.0oradata estusers01.dbf';
SET NEWNAME FOR DATAFILE 5 TO 'F:oracleproduct10.2.0oradata estusers02.dbf';
SET NEWNAME FOR DATAFILE 6 TO 'F:oracleproduct10.2.0oradata estperfstat.dbf';
SET NEWNAME FOR TEMPFILE 1 TO 'F:oracleproduct10.2.0oradata est emp01.dbf';
[code]....
Recovery Manager complete.It seems when i run alter database open resetlogs i get this:
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: 'F:ORACLEPRODUCT10.2.0ORADATATESTSYSTEM01.DBF'
View 4 Replies
View Related
Mar 23, 2007
I want to replicate my Oracle XE database in Oracle 10G. Is their a 'simple' way to do that? I know that XE does not have server backup.
View 2 Replies
View Related
Aug 27, 2012
How can i add exttrail to a replicate?
View 1 Replies
View Related
Aug 5, 2012
I taking export using consistent parameter. Theoretically i can understand . practically i couldn't understand how it works.
for ex
I am updating tab1 table under sams user. table having one lakh records.
while updating the query using consistent=y and consistent=n. i mean
exp sams/sams file=cons.dmp owner=sams consistent=y
exp sams/sams file=cons2.dmp owner=sams consistent=n
then both files imported to separate user(sam ,san).
Updated info not visible in san and sam user.
I want to know practically how it works. I need perfect example. while using consistent=y and consistent=n
View 2 Replies
View Related
Jun 22, 2010
In oracle 10g data pump (Logical backup) How to take the consistent backup. What parameter can we use?
View 2 Replies
View Related
Nov 22, 2010
I have some questions about Oracle + EMC shared storage. I have Oracle 11gR1 RAC (2nodes) + ASM environment with shared shorage EMC Clariion AX4.
The database is running no archivelog mode. I'd like to implement point-in-time recovery using Snap View snapshots.
Currently my AX4 platform has the following LUNS:
LUN1 - registry 1
LUN2 - registry 2
LUN3 - vote 1
LUN4 - vote 2
LUN5 - vote 3
LUN6 - ASM - DISKGROUP DATA DISK DATA01 (actual db datafiles)
LUN7 - ASM - DISKGROUP DATA DISK DATA02 (actual db datafiles)
Using source LUNs in consistent session will take sync snapshots of all the LUNs working against my database. Once something happens with database, the LUNs can be returned to specific point in time when snapshot consistent session was taken and I'm expecting the database will up and continue to work.
Questions:
1. Is my approach correct at all? (The database is running noarchivelog mode, not dealing with hot backups. The recovery point in time implemented purely via EMC snapshot consistent sessions.)
2. The AX4 platform has a limit 8 source LUNs in session. If I understand it correctly, I can't place more than 8 LUNs in session. What will be the workaround if my database will occupy more than 8 LUNs, I'd like to take their consistent snapshot; for example:
LUN1 - registry 1
LUN2 - registry 2
LUN3 - vote 1
LUN4 - vote 2
LUN5 - vote 3
LUN6 - ASM - DISKGROUP DATA DISK DATA01 (actual db datafiles)
LUN7 - ASM - DISKGROUP DATA DISK DATA02 (actual db datafiles)
LUN8 - ASM - DISKGROUP DATA DISK DATA03 (actual db datafiles)
LUN9 - ASM - DISKGROUP DATA DISK DATA04 (actual db datafiles)
View 2 Replies
View Related
Dec 4, 2007
I have a table MYTABLE in database mydb1 duplicated via materialized view and materialized view log and refresh_snapshot commands to a MYTABLE on mydb2 database.
I like to duplicate this table MYTABLE to a third database mydb2, using the same method (materialized view and refresh_snapshot command).
Is it possible ? What's hapend to the materialized view log where I launch a refresh_snapshots on mydb2 ? How is this materialized view log truncated ?
View 5 Replies
View Related
Aug 3, 2012
I need the steps to replicate one schema to other system. I am using standard edition one 11gR2. Necessary steps to implement (basic replication).
View 2 Replies
View Related
Jan 31, 2013
I Have one oracle user ABC which is having 10 Tables.
Now i want to create replica of this user XyZ which can access only 5 tables from . This user having All access.
Example :
Any DML changes made in XYZ these changes should be reflected in ABC user. and vice a versa.
How can i achive this in ORACLE DB.
View 10 Replies
View Related
Jun 14, 2013
I have a requirement where i need to replicate the for loop functionality through sql query.In pl/sql i can achieve this through cursors or for loop but is there any way to do or replicate the same functionality in sql queries . For example i want to repeat the operation for set of records or iterate through set of records and fire query accordingly .Is it possible through sql query
View 19 Replies
View Related
Sep 4, 2013
I have a package called getEmpData in database orcl. I want to move/creat the same package on other database called ora. I want to do/simplyfy this work through PLSQL DEVELOPER.
View 3 Replies
View Related
Mar 30, 2011
I told about Oracle multi master replication below.
Can I replicate some objects of "X" schema at A server to "Y" schema at B server using MM replication?
View 13 Replies
View Related
Oct 9, 2012
The document says "Propagators are also useful for distributing replication loads in configurations that involve a master database that must replicate to a large number of subscribers".
Link [URL]........
My question is how do we define this " large number ". Is 5 a large number or 10 a large number ? I've a bidirectional legacy replication scheme wherein a node replicates to 10 other nodes. Should i introduce Propagator between these nodes.
View 3 Replies
View Related
Sep 24, 2013
APEX_ITEM functions do not seem to be working correctly. When I create a region with this query:
select APEX_ITEM.SELECT_LIST_FROM_QUERY(1, 1, 'select ''Jan'' d, 1 r from dual union select ''Feb'' d, 2 r from dual') month_id
from dual I get the error:report error:ORA-06502: PL/SQL: numeric or value error: associative array shape is not consistent with session parametersWhen I run that query in SQL Developer I get this:
<select name="f01" ><option value="%null%">%</option><option value="1" selected="selected">1</option></select>It
works but returns an incorrect result, there is no list.When I run that query in SQL Workshop on apex.oracle.com I get this:
<select name="f01" ><option value="%null%">%</option><option value="2" >Feb</option><option value="1" selected="selected">Jan</option></select>
which is correct.I tried changing NLS_COMP parameter from Linguistic to Binary and changing NLS_SORT to BINARY_CI but that did not make a difference. This seems like a rare problem and generally related to installation but it has me stopped dead in my tracks.
View 2 Replies
View Related
Mar 31, 2013
I have read almost all docs about distributed transaction on tahihi.oracle.com website,But I can find a statment about this:
Can Oracle always guarrantee the data consistent in a distributed transaction?
For example,there is a distributed transaction on node a,node b and nod c.node b and node c informed node a they were prepared,so node a committed,and inform node b and node c commit.then node b committed and feedback,but network on node c broken at this point,So node a can't not get feedback from node c,but node a and node b has been committed, so what will Oracle do in this condition?
If node c rollback the data on local node, consistent in this distributed transaction was failed ,yes?
View 5 Replies
View Related
