Oracle 11G - Update Is Very Slow On View?
Dec 7, 2012
I have big trouble with some Update query on Oracle 11G.I have a set of tables (5) of identical structures and a view that consists in an UNION ALL of the 5 tables.None of this table contains more than 20 000 rows.Let's call the view V_INTE_NE. Each of the basic table has a PRIMARY KEY defined on 3 NUMBERS(10,0) -> INTE_REF / NE_REF / INSTANCE.
Now, I get 6 rows in another table and I want to update my view from the data of this small table (let's call it SMALL). This table has the 3 columns INTE_REF / NE_REF / INSTANCE.
When I try to join the two tables :
SELECT * FROM T_INTE_NE T2
WHERE EXISTS ( SELECT 1 FROM SMALL T1 WHERE T2.INTE_REF = T1.INTEREF AND T2.NE_REF = T1.NEREF AND T2.INTE_INST = T1.INSTANCE )
I get the 6 lines in 0.037 seconds
When I try to update the view (I have an INSTEAD OF trigger that does nothing (just return for testing even without modifying anything), I execute the following query :
UPDATE T_INTE_NE T2
SET INTE_STATE = -11 WHERE
EXISTS ( SELECT 1 FROM SMALL T1 WHERE T2.INTE_REF = T1.INTEREF AND T2.NE_REF = T1.NEREF AND T2.INTE_INST = T1.INSTANCE )
The 6 rows are updated (at least TRIGGER is called) in 20 seconds.
However, in the execution plan, I can't see where Oracle takes time to achieve the query :
Plan hash value: 907176690
[code]....
Predicate Information (identified by operation id):
2 - access("T2"."INTE_REF"="T1"."INTEREF" AND "T2"."NE_REF"="T1"."NEREF" AND
"T2"."INTE_INST"="T1"."INSTANCE")
Note- dynamic sampling used for this statement (level=2)
Statistics
-----------------------------------------------------------
3 user calls
0 physical read total bytes
0 physical write total bytes
0 spare statistic 3
0 commit cleanout failures: cannot pin
[code]....
I get exactly the same execution plan (when autotrace is ON).Furthermore, if I try to do the same update on each of the basic tables, I get the rows updated instantaneously.
View 10 Replies
ADVERTISEMENT
Apr 15, 2010
I have created a MV joining 10 tables with FAST REFRESH ON COMMIT It made my sytem very slow.
View 18 Replies
View Related
Nov 1, 2012
The customer moved their database from 10g to a 11gR2 database.They created the MV in the new system and now it takes 26 hours to refresh as opposed to 15 min in the old 10g database.Just looking for a game plan for troubleshooting.
View 8 Replies
View Related
Jun 28, 2009
I created a Materialized view using joining of two tables with FAST REFRESH ON COMMIT option. These tables are the main tables of the DB and contains alot of data(about 15 millions of records). two master tables have index but mview doesn't have any indexes. I've thought the entire concept of the fast refresh is that it should be a relatively quick operation but the result showed that DB performance reduced, for example I could insert 900 records per minute thru application into master tables before but now I can just insert about 400 records per hour!.Also, It slows down the select operation, went from 0.11 seconds(with joining two tables) to 33 seconds (in mview)!.
First of all, I considered following possibilities and test them:
1) The most likely solution was that a complete refresh was happening. However, the materialized view refresh was confirmed to be a fast refresh by querying USER_MVIEWS.
2) I checked the query of the materialized view and confirmed that it was a simple select from the master tables without any aggregation or sorting.
3) DBMS_MVIEW.explain_mview was run on the materialized view. MV_CAPABILITIES_TABLE did not reveal any problems.
4) A query of dba_registered_snapshots showed that no other snapshots appeared to be using these logs. So, After each refresh, the records in mview logs were deleting.
As you see, it seems everything is ok, but what was causing this fast refresh to go so slow?
I use oracle 10.2 on Suse Linux enterprise server.
View 3 Replies
View Related
Jan 6, 2013
I had to drop the MV with preserve table and rebuilt it after modifcation of materalized table.Before dropping the materialized view metdata was.
CREATE MATERIALIZED VIEW "DM_TGT_SHARED"."EA_MERGED_DATA_MV"
ORGANIZATION HEAP PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "DM_TGT_SHARED"
BUILD IMMEDIATE
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
[code]...
now this view taking an ages to refresh as before , there is a MV log i suspect i did not use "with rowid" clause when rebuilding the materliazed view , how can i add with rowid clause which i reckon will improve the MV refreshment.
View 7 Replies
View Related
Sep 3, 2012
I am migrating a oracle 9i database to 11g r3. I can only use imp. As the database is huge, I have split the exp dump by schemas. In my recent test, i have split the schema into 4 seperate threads to be imported into the new oracle11g database. The 4 thread of imp consist of almost similar sizes of schema (Eg thread 1 - Schema 1, 2 ,3. Thread 2 - Schema 4,5,6 etc)
All the dump files are in the same mount point. When i execute the import (4 threads) together, the total import timing is each thread is between 2.5 days to 3.5 days.
Then i proceed to try only 1 thread, only 2 hrs. So could this be a IO issue or oracle memory problem?
Specs
IBM AIX 6.1, 32GB RAM
Oracle
24GB AMM
Number of processes 500
View 1 Replies
View Related
May 20, 2010
9i worked fine 11g release2 giving ora-01779
alternative sql for the following :
UPDATE /*+ BYPASS_UJVC */
(
SELECT
c.c1,
c.c2,
c.c3,
[code].....
View 5 Replies
View Related
Dec 31, 2011
We are using one software it is a test tool for verify the data base posting speed from server to client systems. In windows 2008 R2, database posting speed is very slow when compare to windows 2003 server .
Server configuration is same for both servers ( RAID 5 , RAM 4 GB) how we can improve writing performance in Oracle
View 1 Replies
View Related
Oct 11, 2012
We have a function that is called in various other PL/SQL packages, and performance has always been very good. On 29th Sept we upgraded our db to 10.2.0.5.0 and since then, a package that calls the function has gone from ~4mins, to ~2.5hrs to run.
In PL/SQL Developer, a simple select that calls the function has gone from ~0.5secs to retrieve the first 100 rows, to ~12secs. I ran a profile of the main package, which highlighted the where the bottleneck was (a fetch from an explicit cursor). Running an explain plan on the cursor SQL doesn't really show up anything untoward.
However, I found that if I subtly changed the cursor SQL, (so that it did the same thing, but was written differently), it fixed the performance problems.
where ade_start_date between cpDate-cpDays and cpDate-1
/*and ade_start_date < cpDate
and ade_start_date >= (cpDate-cpDays)*/
From this, we thought that there may have been a bad cached execution plan which the change of code forced a recalculation of. However, about 2 hours later, the changed code ran slowly again. So a further subtle change was made, which fixed the issue again. Until this morning, when it was running slowly again.
This feels like it is CBO/stats related potentially, but is out of my area of knowledge unfortunately. We have our DBA investigating this, but there may be things I can test to narrow down the possibilities in the meantime.
View 5 Replies
View Related
Jul 18, 2012
I am looking to build a query to update a current value in a DBA view to a new one.i.e. updating directories based on the current value:
CREATE OR REPLACE DIRECTORY 'DIRECTORY_NAME' AS 'DIRECTORY_PATH'(substr(directory_path, 1,5) + '/&dbname' {i.e. this is different for every database name }+ 'DIRECTORY_PATH'(string after /xyz/)
WHERE DIRECTORY_NAME in
( select DIRECTORY_NAME
from DBA_DIRECTORIES
WHERE DIRECTORY_PATH
like '/xyz/%'
)
i.e. resulting output should be:
CREATE OR REPLACE DIRECTORY 'ABC' AS '/xyz/DBNAME/abc/def/';
(when the directory previously was 'xyz/abc/def/') i.e. basically inserting the db name into the directory.where DBNAME is a variable more directories are added frequently so therefore this needs to be a dynamic procedure to change the directories in the db.
View 26 Replies
View Related
Jan 21, 2013
Can I update inline view like below?
update
(select * from surveys s join answers a on s.survey_id = a.survey_seq_id where month = 201212 and qa = 1 and main_group_id = 55) a,
(select * from surveys s join answers a on s.survey_id = a.survey_seq_id where month = 201212 and qa = 1 and main_group_id = 3) b
where a.survey_id = b.survey_id and substr(a.question_uid , 3) = substr(b.question_uid, 2))
set b.answer = a.answer;
My second question is: The following query is giving error. What can I do?
update answers ans set (ans.answer) = (with a as (select * from surveys s join answers a on s.survey_id = a.survey_seq_id where month = 201212 and qa = 1 and main_group_id = 55),
b as (select * from surveys s join answers a on s.survey_id = a.survey_seq_id where month = 201212 and qa = 1 and main_group_id = 3)
select a.answer from a join b on a.survey_id = b.survey_id and substr(a.question_uid , 3) = substr(b.question_uid, 2) and b.answer_id = ans.answer_id) where ans.main_group_id = 3;
SQL Error: ORA-00904: "ANS"."ANSWER_ID":
View 8 Replies
View Related
Jan 29, 2013
I'm using PreparedStatement to execute a query with some parameters. in sql developer, it just spends 12 seconds but in jdbc, it's around 140 seconds.
I disabled "_optim_peek_user_binds" parameter already but it didn't work. alter session set "_optim_peek_user_binds"=false;
View 1 Replies
View Related
May 30, 2008
i want to create "on update" database trigger on materialized view which is on replication schema.I would like to update rows in table when update occurs on materialized view on master side.I wrote some code and tried with this:
CREATE OR REPLACE TRIGGER a
after update on A@dirfep.us.oracle.com
FOR EACH ROW
BEGIN
[code]...
View 1 Replies
View Related
May 25, 2013
I am using 11gR2 on windows server. This is the query that runs many times a day and effect badly the performance of database. I don't have much idea about this query.
SELECT TO_CHAR(current_timestamp AT TIME ZONE 'GMT', 'YYYY-MM-DD HH24:MI:SS TZD') AS curr_timestamp, COUNT(username) AS failed_count
FROM sys.dba_audit_session
WHERE returncode != 0 AND TO_CHAR(timestamp, 'YYYY-MM-DD HH24:MI:SS') >= TO_CHAR(current_timestamp - TO_DSINTERVAL('0 0:30:00'), 'YYYY-MM-DD HH24:MI:SS')
View 1 Replies
View Related
Aug 28, 2013
we are trying to create a materialized view (MV) which would access the remote database through db link. Now we need to do update on the local MV so that it should be reflected on the master table.
There is no primary key on this table and we are using "complete refresh" option. since we dont have control over remote database, we are not allowed to create MV log over there.
in this scenario, if i try to create updatetable MV with complete refresh, we are getting below error:
SQL Error: ORA-12013: updatable materialized views must be simple enough to do fast refresh
how to create such MV on this scenario?
our environment is:
Oracle 11.2.0.3
View 1 Replies
View Related
Sep 12, 2013
I am using sqlplus. example to update a base table using a updatable materialized view.
View 17 Replies
View Related
Aug 24, 2012
i have a little doubt in Materialized view. i created a materialized view and log with the following query,
create table test_mv (a number ,b number )
alter table test_mv add constraint t_pk primary key ( a );
insert into test_mv values (1,2);
[Code]...
Now i want to update the query in the MV as 'Select a from test_mv' . for that i tried with
*'ALTER MATERIALIZED VIEW product_sales_mv AS SELECT a from test_mv;'*
But it throwing error,
Error starting at line 5 in command:
alter MATERIALIZED VIEW product_sales_mv AS SELECT b from test_mv
Error report:
SQL Error: ORA-00933: SQL command not properly ended +00933. 00000 - "SQL command not properly ended"+
*Cause:+
*Action:+
i guess i am doing wrong. i want to update it without drop and recreate
View 2 Replies
View Related
Jun 18, 2012
I have an Oracle database (9.2.0.7) installed on a HP-UX server.When trying to access this database from another HP-UX or Linux server, connection is fine. But when trying to connect from a Windows based client, connection is very slow (almost 1 minute to return the result of a 'select count(*)' like query, which is immediate from the Linux client).
Here are some facts I can add :
- Clients and servers are on the same network segment (it is not a network matter)
- No matter which client version I use, there no difference
- I tried to know what happens on the Oracle server when performing my sample query using tusc command : the result is that the server is performing exactly the same actions when sending my query from a Linux client or a Windows client
- The only relevant difference seems to be the client OS
View 13 Replies
View Related
Jul 28, 2010
Are oracle view have Dynamic view function?
View 8 Replies
View Related
Aug 24, 2013
My scenario is I need to insert into History table when a record is been updated into a tabular form(insert the updated record along with the additional columns Action_by,Action_type(Like Update or delete) and Action Date Into History table i.e History table contains all the records as the main table which is been visible in tabular form along with these additional columns ...Action_by,action_type and action_date.
So now i dont want to create a befor/after update trigger on base table rather i would like to create a generic procedure which will insert the updated record into history table taking the page alias and pade ID as the parameters(GENERIC procedure is nothing but whcih applies to all the tabular forms(Tables) contained int he application ).
View 2 Replies
View Related
Jun 27, 2013
I would like to update an XML element without using the function APPENDCHILDXML or INSERTCHILDXML because they are not available in Oracle 10GR1.
In my database, Oracle XDB is not installed.
The following query fail with the following error : ORA-00904: "INSERTCHILDXML" : identificateur non valide
update scl_profile
set profile_data =
insertChildXML(profile_data,'/exportImportMarcheCriteria','colonnesExport',
XMLType('<colonnesExport>ENTETE_GESTIONNAIRES_AUTORISES</colonnesExport>'))
where profile_xmltype =
'fr.mipih.marches.marche.criteres.ExportImportMarcheCriteria'
and profile_type =
'eMagh2.MRGS.AccesMarche.ListeMarche.Export.OptionsExportImport';
[code]........
If i try to use the package DBMS_XMLDOM, i have the following error :
ORA-06550: Ligne 3, colonne 11 :
PLS-00201: l'identificateur 'DBMS_XMLDOM.DOMDOCUMENT' doit etre declare
ORA-06550: Ligne 3, colonne 11 :
PL/SQL: Item ignored
I think it's because ORACLE XDB component is not installed in my database.
View 1 Replies
View Related
Sep 6, 2011
Is there any view in oracle which gives the foreign key mapping.
E.g.
Table DEPT has DEPTNO as PK.
Table Emp has DEPTNO as FK to DEPT.DEPTNO.
I can get the information for DEPT table from R_CONSTRAINT_NAME column of all_constraints table.My requirement is to get the the which column of Emp table refers to DEPT.DEPTNO column .
View 3 Replies
View Related
Jul 5, 2011
I have created a Materialized View (MV) and within the MV DDL have included a statement as per below. However, the MV does not seem to be refreshing on a daily basis. I can see the job in dba_jobs table. So the question, is there some system setting i need to execute or create in order for Oracle to know that it needs to run the Job defined in the dba_jobs table on a daily Basis?
View 8 Replies
View Related
Apr 15, 2013
Is there a way to find the source sql that created a view? Do you know the sql the sql that created a view?
View 5 Replies
View Related
Jun 4, 2013
Can one use the CommandType.TableDirect to access a VIEW in oracle?
I have a view that I want to access and rather than call a Stored Procedure that in turn accesses the view, can I do this directly from with .NET?
I am using VS2012, and I am using Oracle 11.2g.
View 2 Replies
View Related
Jan 17, 2013
can we change the existing materialized view to normal view? if yes how?
View 2 Replies
View Related
Apr 17, 2012
The issue is slow insertion in particular table(i.e A Table) it means insertion in all other tables(i.e B, C, D tables) in same schema is going properly but only when i am trying to insert in one particular table(i.e A table) in same schema it takes long time to complete insertion. Daily insertion is 6000 rows.
I have check all the details like Tablespace size, Analyzing of table, Analyzing of indexes and all. There is no any error alertlog file.
View 1 Replies
View Related
May 16, 2007
We have two database instances on the same server. One was left at 9.2.0.7 and one was upgraded to 10.2.0.3. Connecting externally (sqlplus '/as sysdba') to the 9.2.0.7 database is lightning fast. Connecting externally to the 10.2.0.3 database is very slow, comparatively speakiing. This is on an IBM AIX-5L (64-bit) machine. We are using "tnsnames".
View 10 Replies
View Related
Aug 3, 2010
WHY PARAMETRIZED CURSOR IS SLOW
I have the following query which is very slow
CURSOR c_party_ids (p_party_name VARCHAR2, p_party_type VARCHAR2, p_eval_active NUMBER)
IS
SELECT a.party_id,
a.party_type,
b.cust_account_id,
b.attribute17
[code].......
View 3 Replies
View Related
Apr 22, 2012
We have done cloning of our ORACLE APPLICATION(11i),after that performance of ERP is getting slow (like fetching of data). What we can do to increase the performance.
View 1 Replies
View Related