SQL Performance (MOSC)

MOSC Banner

MVIEW refresh complete very slow

edited May 24, 2013 2:40AM in SQL Performance (MOSC) 10 commentsAnswered
Hi,

i have a oracle DB 11.1.0.7.0 RAC 2 on AIX 6.1. I have created  several MVIEW with the follow statement:

CREATE MATERIALIZED VIEW MV_TEST

TABLESPACE TBS_BSM

PCTUSED 0

PCTFREE 10

INITRANS 2

MAXTRANS 255

STORAGE (

INITIAL 100M

NEXT 100M

MINEXTENTS 1

MAXEXTENTS UNLIMITED

PCTINCREASE 0

BUFFER_POOL DEFAULT

)

NOCACHE

NOLOGGING

NOCOMPRESS

PARALLEL ( DEGREE 8 INSTANCES 1 )

BUILD IMMEDIATE

REFRESH FORCE ON DEMAND

WITH PRIMARY KEY

ENABLE QUERY REWRITE

AS

.......complex query with more join......

when i make the refresh complete with the follow statement:

exec dbms_MVIEW.refresh ('BSM.MV_TEST', 'C', PARALLELISM=>8)

it spend more time to complete the execution: about 35 min against 1 min in normal execution.

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

Instructions for posting

×

1. Select a discussion category from the picklist.


2. Enter a title that clearly identifies the subject of your question.


3. In the body, insert detailed information, including Oracle product and version.


Please abide by the Oracle Community guidelines and refrain from posting any customer or personally identifiable information (PI/CI).


Cloud / Fusion customers - Our Cloud community has moved! Please go to Cloud Customer Connect .


New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center