Performance Tuning :: Materialized View To Improve Search Queries?

Jan 31, 2012

In search queries generally we select 10-25 columns (more can't be displayed on the screen) from 5-10 tables

Say in case of insurance related application, the search might be on policy number, policy holder's first name, policy holder's last name, region, policy type etc.

And not to many columns we are displaying on the screen, say, 4 tables have collectively 4 * 20 = 80 columns, then we are displaying say 12-15 columns with 2-3 columns have aggregates on it.

since the search criteria (e.g. first name, last name, policy number etc.) is not known till last moment it will be a generic dynamic query

Is it possible that instead we create a Materialized view with query with only joining conditions but no filter conditions and selecting only columns to be displayed on the screen and then we will refresh the materialized view (to take care of recent business transactions) and fire refined query with filter criteria on this materialized view

Select col1,col2,col3,col4,col5
From tab1,tab2,tab3,tab4
Where tab1.col1=tab2.col1
And tab2.col2=tab3.col2
And tab2.col2=tab4.col2;

Will it improve performance of the search functionality

View 2 Replies


ADVERTISEMENT

Performance Tuning :: Queries Of Materialized Views

Jan 28, 2011

We are using the below query to create a Materialized View but it has been running since 3 hours. It is an Oracle 9i database running in HP-UX.The quey is as follows,

(SELECT
/*+ use_nl(A) parallel (A,4)*/
A.ICD_CODE AS ICD_CODE,
A.ICD_DESC AS ICD_DESC,
A.PROC_GROUP as PROC_GROUP,
B.COMPL_ICD_CODE AS COMPL_ICD_CODE,
B.COMPL_GRP_TXT AS COMPL_GRP_TXT,
C.PROC_TYPE AS PROC_TYPE ,
[code]....

View 21 Replies View Related

What Kind Of Index Would Improve Performance Of Queries

Jun 30, 2013

It seems certain queries search by the number of days to ship(number of days between the order and shipping dates). What kind of index would improve the performance of these queries?

View 2 Replies View Related

Performance Tuning :: How To Refresh Materialized View Data

Jun 12, 2013

I have created a materialized view and also a normal View, which has 3 tables used in both the views, when inserted new records it reflects in a normal view but when i select the materialized view i cant see the updated data.

here is the materialized view i created;

CREATE MATERIALIZED VIEW pct_sales_materialized
BUILD IMMEDIATE REFRESH ON DEMAND
ENABLE QUERY REWRITE
AS
SELECT A.DEP_NAME,B.EMP_ID,C.EMP_NAME
FROM department_head A,department_child B,emp_detail C
WHERE A.DEP_ID = B.DEP_ID
AND B.EMP_ID = C.EMP_ID

View 2 Replies View Related

Performance Tuning :: Materialized View With Nullable Column

Dec 29, 2011

I have an issue in materialized view which has got one of the null able column and query on this column taking approximately 2 mins where as other indexed columns takes less than 10 sec.

Here is the summary

SQL> Select Count (1), Count (VAT_NO) From Mv_customer;

COUNT(1) COUNT(VAT_NO)
---------------------------------
2893561 1516

If an index is created on VAT_NO will that improve the performance. What kind of index can be created considering very less number of records has got VAT_NO

View 4 Replies View Related

Performance Tuning :: Materialized View Which Should Refresh Every Tuesday And Friday At 5am

Mar 20, 2012

I need to create a Materialized View, which should refresh every Tuesday and Friday at 5am.

CREATE MATERIALIZED VIEW DUAL_MV
TABLESPACE USERS_SMALL
REFRESH NEXT NEXT_DAY(TRUNC(SYSDATE), 'TUESDAY') + 05/24
AS SELECT * FROM DUAL;

I can do it for single day like above, but how to refresh on multiple days, using create materialized view syntax .

View 2 Replies View Related

Performance Tuning :: Best Practices To Optimize A Fast Refresh Materialized View

Jan 16, 2012

I have a base table with ~20 mio. records with two FAST REFRESH Materialized Views based on that table using various aggregate functions in their view definition.

The problem is, when e.g. one record changes in the base table, I see two records in the MV log table MLOG$, but invoking the fast refresh mechanism by using using:

dbms_snapshot.refresh('mv1', 'f');
dbms_snapshot.refresh('mv2', 'f');

Is still running after ~20 minutes now.

View 2 Replies View Related

Performance Tuning :: How To Improve The Performance Of Export Job (expdp)

Dec 6, 2011

I have an issue with export(expdp).

When i exporting an user using expdp utility, the load the on the server is going up-to 5. The size of the database is 180GB. Below is the command that i use for export.

expdp sys/xxxx directory=dbpdump dumpfile=expdp_trk_backup.dmp logfile=expdp_trk_backup.log exclude=statistics schemas=trk

Do i need any look into any memory parameters for this?

View 1 Replies View Related

Performance Tuning :: Sub Queries With Function Call?

May 24, 2011

I am stuck with a query which is taking a lot of time to execute. Below is the pseudo code of the same:

SELECT TAB_ALIAS1.COL1,TAB_ALIAS1.COL2,TAB_ALIAS1.COL3
FROM TABLE1 TAB_ALIAS1
WHERE TAB_ALIAS1.COL4 = <INPUT PARAMETER1>
AND TRUNC(TAB_ALIAS1.ELAP_TIME) =
(
SELECT MAX(ELAP_TIME)

[code]....

View 6 Replies View Related

Performance Tuning :: How To Check SQL Queries Run By Application At Backend

Oct 28, 2010

Name some database tool from which I can check the SQL Queries which my application is running.

NOTE: I do not want to check the queries which I am executing at the SQL command prompt but queries that are being run by my application at the backend.

View 4 Replies View Related

Performance Tuning :: How To Find Out Queries That Are Executed In Particular Range Of Time

Sep 9, 2011

here we have an scenario where we want to find out all the sql statements that are executed in a particular time. The sql statements are executed via our application. I tried in awr report but it shows only the sql query which has taken long time to execute. and i even tried in V$session and V$sqlarea. how to view the executed sql statements in a particular session/current session

View 3 Replies View Related

Performance Tuning :: Does Parallel Hint Works In Cursor Queries

Aug 28, 2013

Does parallel hint works in cursor queries? The cursor query is something like :

cursor c is
select /*+ parallel(s,8) */
from table ref_tab s ---- >>
<where condition>;

The table ref_tab hold data for a single day at any point of time and gets truncate before loading the next days data.On average the table holds around 7 million rows and doesn't contains any index (think that's fine as all together we are loading the whole set).And, we are using bulk logic with save exceptions to open the cursor and load the data into the target table.

View 13 Replies View Related

Performance Tuning :: Queries On PGA Settings And Related Memory Management?

Feb 11, 2011

I have few queries on PGA memory management.Since these queries are based on 2-3 examples not exactly same by nature I am summarising it after my understanding for the same

As I understand many workareas can be allocated to a single sql statement and number and sizes of theses workareas is controlled internally by Oracle when Automatic Memory management (PGA_aggregate_target and workarea_size_policy=Auto are set) Since many sessions share the PGA memory, the amount of memory available to each session may vary and if less amount of memory is available for a session for sorting then TEMP tablespace is used

[1] Can we say paging happens and can be checked at this time?

[2] Is there a difference in handling memory while populating pl/sql tables?

As I have encountered ora-04030 while some our developers were populating pl/sql tables but never encountered this error for sorting, hash joins etc Though I don't remember the width of pl/sql table, I am sure the developer used 'LIMIT' clause during bulk collect and still faced the issue.

With a single session on the server, I noticed that the difference in values displayed issuing 'free' command in linux and output values from sesstat did not match at all while there wasn't any heavy OS process involved during the period. I was expecting 'used' and 'free' values displayed by free command (linux) will change and difference would be approximately equals 'before and after values of session pga memory.

[3] Isn't it expected to match?

[4] Can we say in dedicated server, at any moment of time, the SUM of 'session pga memory' represents all the memory used by Oracle SGA, at that point of time?

select sum(value)/1024/1024 "memory in MB" from v$sesstat where statistic#=20;

During one of the tests I got following output (divide value by 10 for my visibility and avoid formatting)

SQL> select a.name, to_char(b.value/10, '999,999,999') value
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
and a.name like '%ga memory%'; 2 3 4

[code]...

The above query is showing above values even when the pl/sql block execution is completed 30 minutes back

[5] Do we call this as 'memory leak' where memory is not released even while some time has passed since session has done something?Of course I am not checking at OS level as mentioned in question [3] above the values won't match!

Still the output of free command for reference(After the pl/sql block executed)

SQL> !free
total used free shared buffers cached
Mem: 3016796 2999660 17136 0 4308 1173260
-/+ buffers/cache: 1822092 1194704
Swap: 1048568 636124 412444

--(After the pl/sql block executed)

SQL> select * from v$pgastat;
NAMEVALUEUNIT
aggregate PGA target parameter 524288000bytes
aggregate PGA auto target 456256512bytes
global memory bound 26214400bytes
total PGA inuse 17328128bytes

[code]...

[6] What could be the significance of negative values of 'session pga memory/max'?

Last We have an OLTP system and in the night we run batch processes in 2-4 sessions

Suppose I have 10 GB RAM and with PGA setting of 3.5 GB Now I want the batch process sessions to use max possible memory during nighttime and toggle the setting back in the morning

[7] With above settings (10 GB RAM and 3.5 GB PGA) how can I divide the memory among 4 sessions?

Shall I set 1) PGA_aggregate_target=0 2)Workarea_size_policy=manual 3) Sort_are_size 4) Hash_area_size

[8] What would be approx values for parameter 3 and 4? will it be straight 3.5 GB/ 4?

View 8 Replies View Related

Performance Tuning :: Hierarchical Queries - Run Jobs Within Stipulated Time Frame

Nov 17, 2011

We have a big hierarchical query which is now running for a long time (around 6 hours. earlier it was running for 3 hours). We have to tune this query so that we run the jobs within a stipulated time frame.

The query below inserts around 42 million records in to the table WK_ACCT_WSTORE. I have attached in the text file.

View 4 Replies View Related

SQL & PL/SQL :: Performance With Materialized View Fast Refresh

Oct 10, 2013

I've 2 tables in schema A.

1) TXN_HD_FCT (This table has 6 billion records. Has fields TXN_DT, CUST_ID, TXN_AMT, TXN_ID and the table is partitioned on TXN_DT. We load data approximately 600K+ records to this table every day
2) RELINK_CUST (This table has 2 fieds CUST_ID and NEW_CUST_ID). We load data approximatley 100-200K+ records every day into this table. This table has total 10MM+ records now.

Now i need to create a table (TXN_UPD_HDR_FCT) in Schema B by joining above 2 tables using below query every day

CREATE TABLE TXN_UPD_HDR_FCT
select a.TXN_DT,
nvl(b.new_cust_id, a.cust_id) as cust_id,
a.txn_amt,
a.txn_id,
from schema_A.TXN_HD_FCT a,
schema_A.RELINK_CUST b
where a.cust_id = b.cust_id(+);

We update CUST_ID to NEW_CUST_ID based on some rules (and keep it in the table RELINK_CUST) and need to take this new_cust_id while building the table TXN_UPD_HDR_FCT.

Building this table is taking approximately 4+ hours every day. just refresh only records from TXN_HDR_FCT whose CUST_ID is changed or any records inserted into TXN_HDR_FCT? I read about Materialized views but not sure how can i create MV logs on 2 tables? If i use materialized views do i need to create indexes every day?

View 15 Replies View Related

SQL & PL/SQL :: Why Do Stored Procedures And Functions Improve Performance

Feb 10, 2012

Why do stored procedures and functions improve performance?

A. They reduce network round trips.
B. They reduce the number of calls to the database and decrease network traffic by bundling commands.
C. They reduce the number of calls to the database and decrease network traffic by using the local PL/SQL engine.
D. They allow the application to perform high-speed processing locally.
E. They postpone PL/SQL parsing until run time.

I think the answer should be A and B but i came across answers as B and E Can u explain me what is the difference between option A and B and does it postpone parsing till run time?

View 1 Replies View Related

Performance Tuning :: Optimizing Select From View

Dec 23, 2010

I have a view, below, which does few left outer joins to the same V_MARKET view to get data i need. When I run SQL by itself, ut runs pretty fast, 2-5 seconds. But when I do "select * from V_DEPT_DATA where busines_date = '01-APR-10'", it takes more than 10 minutes to run. I added all needed indexes and still have problems with it .

CREATE OR REPLACE VIEW V_DEPT_DATA
AS
SELECT
v1.business_date ,
v1.division ,
v1.department ,
v1.account ,
en.trader ,
[code]........

View 7 Replies View Related

Partitioning Table To Improve Load And Query Performance?

Nov 20, 2012

I have a big table in which we load about 37M recrods. We have informatica ETL which Loads the data in bulk Mode and creats index after completion. The data load takes about 1Hr and Index Creation takes about 1/2 hr. In total it takes about 90 to 95 Mnts.

Now I thought if Partition and Load paralley, It will improve perfromance. We did 4 partition and and each Partition about 9M records. The data load in Bulk mode is completing in 25 Mnts. Again When I am creating index over it, It is taking about 40 Mnts. and in Total Load time is 65 Mnts.

Is there way I can better performance to complete the load in 1/2 hr ?

View 2 Replies View Related

Performance Tuning :: Creating Index On Base Tables Of A View?

Dec 9, 2010

I have a view on base tables holding historical data for previous 60 months(one table per month) with union all operators.create index on those base tables will improve performance or creating a primary key with disabled novalidate will improve for retrieving data?

The view has around 8 million data and used as a fact table with 4 dimension tables.A DTS package from MSSql side refreshes OLAP cube by retrieving data from these tables in oracle.

View 1 Replies View Related

Performance Tuning :: Access Direct Table Versus View

Dec 9, 2011

If you have 3 tables (yr09, yr10,yr11) one with 2009 data, 2010 and 2011 data respectively. And a view (vw_yr091011) with a "union all" on all three.

Question: Will the performance be same for the following two queries ?

Question: Will Oracle read all 3 tables in the view when we search for only one year ?

select count(*) from yr09
where year = 2009;

-- vs

select count(*) from vw_yr091011
where year = 2009;

The following link says yes, the performance remains the same.

Link: [URL]..........

when I tried on a volume of 14000 records. The count came out same but the view took 50 more sec. And the explain plan shows it accessed all three tables.

View 9 Replies View Related

Performance Tuning :: Retrieve Data From View Or Mention Table Names Directly?

Aug 11, 2011

base on performance it is better to retrieve data from view or mention the table names directly?

I have a select statement in from clause one of my table is view (which is having data collected from four tables) my question is whether performance of querry will be improved if i use directly all tables( four tables of a view) instead of a view

View 9 Replies View Related

Server Administration :: How To Change Existing Materialized View To Normal View

Jan 17, 2013

can we change the existing materialized view to normal view? if yes how?

View 2 Replies View Related

Creating Materialized View On Remote Simple View

Dec 11, 2012

I'm trying to create a Materialized View on a remote database from a simple view. The reason is, the data owners don't want to grant explicit tables privileges to external subscribers.

A new schema is created to publish data in the form of a view. I've created mlogs on the master tables, and granted them to the subscriber, but it's still complaining about a missing primary key on the view. A primary key does exist in the master table.

Is there another work around for this situation without having to work inside the data sources' environment?

View 5 Replies View Related

SQL & PL/SQL :: Materialized View - Table Or View Does Not Exist

May 2, 2012

I have a materialized view "pro_mview",I am trying to refresh the MVIEW by using the following statement.

EXEC DBMS_MVIEW.REFRESH('pro_mview','C');

But I am getting the below error.

*
Error at line 1:
ORA-12008: error in materialized view refresh path
ORA-00942: table or view does not exist
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2256
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2462
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2431
ORA-06512: at line 1

I am able to fetch the data from that materialized view(pro_mview).

View 3 Replies View Related

How To Search For A Particular Value In The View

Dec 27, 2009

Which is better to use of Views and Materialized views so as to have good performance ? Also I need to search for a particular value in the view. Any function in sql to implement this ?

View 3 Replies View Related

Multiple Sub-queries In View

Jun 16, 2009

I have a table similar to the following,

USER, DETAILS, TYPE, UPDATED
1, user1home1, 1, 01/05/2009
1, user1home2, 1, 02/05/2009
1, user1work, 2, 03/05/2009
1, user1mobile1, 3, 04/05/2009
1, user1mobile2, 3, 05/05/2009
1, user1email, 4, 06/05/2009
1, user1other, 5 ,07/05/2009
2, user2home1, 1, 01/05/2009
2, user2home2, 1, 02/05/2009
[code]...

which contains multiple contact details for users of different types; type 1 is home, type 2 work etc. The following query returns the user's number and the latest home number for that user.

select user, details as latest_home_number from nc_test t
where type = 1
and updated = (select max(updated) from nc_test t2
where t2.user = t.user
and t2.type = t.type)
order by t.user

However I am not very experienced with sql and I am not sure how to create a view which would contain the fields:

user, latest_home_number, latest_work_number

View 3 Replies View Related

SQL & PL/SQL :: Difference Between View And Materialized View

Apr 26, 2011

difference between view and materialized view?

View 1 Replies View Related

SQL & PL/SQL :: Difference Between View And Materialized View?

Aug 12, 2013

what would be the difference between a view and a materialized view? whether DML possible on a view? i think error occurs if DML tried on a view which is a combination of two or more tables, whether DML possible on a materialized view?

View 7 Replies View Related

SQL & PL/SQL :: View User Who Is Running Long Queries

Nov 23, 2011

An user is running some queries and that's making database hang. I want to view what queries this user is running. Can this be done by session auditing turning on?

View 13 Replies View Related

Tuning Aggregate Queries?

May 1, 2011

I have performance problem with 7 queries involving groupby clauses in OLAP database.These are queries triggered during siebel DAC run

kumar[size="4"][/size][color="#0000FF"][/color]kumardba

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved