This forum is currently locked. You can't register or post questions at this time. (read more)

MATERIALIZE hint intermittently failing in standby database

All posts relating to Oracle database administration.

Moderator: Tim...

shrinika
Advisor
Posts: 260
Joined: Fri Jun 13, 2008 3:18 pm

MATERIALIZE hint intermittently failing in standby database

Postby shrinika » Fri Nov 22, 2013 9:48 pm

Tim, I am using oracle 11g 11.2.0.3 database. It is data guard environment and standby is running in OPEN READ ONLY mode.

We recently moved all the reports from primary database to standby database to reduce the primary db load.

Now one of the report is intermittently failing since, the report has materialize hint.

I read this in your website.

http://www.oracle-base.com/articles/mis ... clause.php

Code: Select all

Note. This is an undocumented feature. I've given you an example that uses a global temporary table, but perhaps there are other circumstances that don't.


I see your note... What other circumstance, this query does not create global temp table?

Please let me know if you have any input.

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: MATERIALIZE hint intermittently failing in standby datab

Postby Tim... » Fri Nov 22, 2013 11:48 pm

Hi.

I gave this example because there are several references on the internet that claim it uses an in-memory table, or other such implications, using a variety of names. My understanding was always that it was implemented using a temporary table and the SQL trace in this example seems to prove that.

The point of the statement is, it is undocumented, so you can't read about it anywhere and get an official statement. If you happen to remember to ask one of the developers when you see them at Oracle OpenWorld, you might get an answer off the record, but then again, you might night.

The issue with undocumented features is there is not list of do's and don'ts to follow. There is nothing to base a prediction of the behaviour. It's undocumented, therefore it's unsupported, therefore you shouldn't use it and I can't provide answers because I don't know.

If you have questions, you could try directing them at Oracle Support, but they will most likely say, it is undocumented and not supported, so don't use it. :)

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

shrinika
Advisor
Posts: 260
Joined: Fri Jun 13, 2008 3:18 pm

Re: MATERIALIZE hint intermittently failing in standby datab

Postby shrinika » Mon Nov 25, 2013 4:18 pm

Thanks Tim! :-)

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: MATERIALIZE hint intermittently failing in standby datab

Postby Tim... » Mon Nov 25, 2013 5:33 pm

:)
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog


Return to “Oracle Database Administration”

Who is online

Users browsing this forum: No registered users and 8 guests

cron