SEO

Hey, all.

I've got a non-managed standby database, which I am hoping to release
as a reporting database.

I'm trying to determine what I can and can't do with this db. Clearly,
as it can only be opened in read only mode, I can't modify anything.

I am trying an explain plan on a SQL statement (something I'm sure the
developers would like for testing) and get an error:

RA-01552: cannot use system rollback segment for non-system tablespace

My current impression as to why this is happening is that explain plan
writes to the plan_table, which is stored in a permanent tablespace.

Can I not run explain plans in a read-only standby, then? Or is there
some mechanism of moving the plan_table to a temp tablespace? Sounds
like a bad idea, but otherwise, I see no way of using explain plan for
tuning in a read-only db...

Thanks for all comments,

BD.

> Can I not run explain plans in a read-only standby, then?

Why would you? The Optimizer takes things into account like the load on
the system (in 10g). The load in your read-only system will differ from
your production system, so that can lead to different execution plans.
And unless you guarantee that every other configuration item is
identical in the two systems, tuning a query on your read-only database
may not yield the same results on your production database.

> Or is there
> some mechanism of moving the plan_table to a temp tablespace?

Sorry but no. The PLAN_TABLE is a permanent segment and you can only
have temporary segments in the TEMP tablespace.

HTH,
Brian


--
================================================== =================

Brian Peasland
oracle_dba@nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown

BD wrote:
> Hey, all.
>
> I've got a non-managed standby database, which I am hoping to release
> as a reporting database.
>
> I'm trying to determine what I can and can't do with this db. Clearly,
> as it can only be opened in read only mode, I can't modify anything.
>
> I am trying an explain plan on a SQL statement (something I'm sure the
> developers would like for testing) and get an error:
>
> RA-01552: cannot use system rollback segment for non-system tablespace
>
> My current impression as to why this is happening is that explain plan
> writes to the plan_table, which is stored in a permanent tablespace.
>
> Can I not run explain plans in a read-only standby, then? Or is there
> some mechanism of moving the plan_table to a temp tablespace? Sounds
> like a bad idea, but otherwise, I see no way of using explain plan for
> tuning in a read-only db...
>
> Thanks for all comments,
>
> BD.

One could certainly create the plan table as a global temporary table
in the source production database.
Whether that is sufficient to work on a standby database is another
matter.

-bdbafh

"BD" <bobby_dread@hotmail.com> wrote in message
news:1149195265.097581.181410@g10g2000cwb.googlegr oups.com...
> Hey, all.
>
> I've got a non-managed standby database, which I am hoping to release
> as a reporting database.
>
> I'm trying to determine what I can and can't do with this db. Clearly,
> as it can only be opened in read only mode, I can't modify anything.
>
> I am trying an explain plan on a SQL statement (something I'm sure the
> developers would like for testing) and get an error:
>
> RA-01552: cannot use system rollback segment for non-system tablespace
>
> My current impression as to why this is happening is that explain plan
> writes to the plan_table, which is stored in a permanent tablespace.
>
> Can I not run explain plans in a read-only standby, then? Or is there
> some mechanism of moving the plan_table to a temp tablespace? Sounds
> like a bad idea, but otherwise, I see no way of using explain plan for
> tuning in a read-only db...
>
> Thanks for all comments,
>
> BD.
>

Interesting question.

In 10g, the default install of the plan_table
table is a global temporary table (called
plan_table$) in the SYS schema with a
public synonym and public access - and
I've the same sort of thing in 8i and 9i
but using the system schema for the last
few years.

But explain plan executes
SELECT ORA_PLAN_ID_SEQ$.NEXTVAL FROM DUAL

which leads to an update of the seq$ table, which
means there is another reason why explain plan would
probably not work in a read-only database.


--
Regards

Jonathan Lewis
http://www.oracle.com/technology/com...ce1.html#lewis

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

> In 10g, the default install of the plan_table
> table is a global temporary table

Sorry forgot to say I'm running 8.1.7.4 here. Bit of a different world.

"BD" <bobby_dread@hotmail.com> wrote in message
news:1149265536.856099.119680@h76g2000cwa.googlegr oups.com...
>> In 10g, the default install of the plan_table
>> table is a global temporary table
>
> Sorry forgot to say I'm running 8.1.7.4 here. Bit of a different world.
>
>

On the other hand, if you look closely
you will note that I've put:

I've [done] the same sort of thing in 8i and 9i
but using the system schema for the last
few years.

--
Regards

Jonathan Lewis
http://www.oracle.com/technology/com...ce1.html#lewis

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

> On the other hand, if you look closely
> you will note that I've put:


Yes, I see... But I think that in my case it's moot, because it's a
read-only standby database that I'm trying to get Explain Plan working
in; creating a temporary table in a standard schema would not do much
for me here, as that schema will still be read only.

The only way I could make it work would be to either create another
tablespace which I could write to, or create the plan_table in the
temporary tablespace - neither of which is allowed.

I do not believe there's a way to make it work. Your comment about
updating the seq$ table convinces me even more.

Thanks for the insights; not the news I wanted, but at least I've done
due diligence. ;-)