Tuning Access on fixed tables

Printable View

11-13-2002, 10:11 AM
Orca777

Tuning Access on fixed tables

Hi,
Oracle 8.x/9.x

i have a sql to monitor SQL's Temp-Usage, but it's very slow!!
is there a chance to tune it?

Code:

SELECT su.tablespace, se.sid, se.user#, se.serial#, su.blocks, su.segtype, sa.sql_text FROM v$sort_usage su ,v$sqlarea sa ,v$session se WHERE su.session_addr = se.saddr AND se.sql_address = sa.address(+)

The explain-plan :

Code:

SELECT STATEMENT, GOAL = RULE 265 1 734 HASH JOIN OUTER 265 1 734 HASH JOIN 35 1 215 HASH JOIN 23 1 129 FIXED TABLE FULL SYS X$KTSSO 11 20 1460 FIXED TABLE FULL SYS X$KSUSE 11 1 56 FIXED TABLE FULL SYS X$KSUSE 11 1 86 VIEW SYS GV$SQLAREA 229 2000 1038000 FILTER SORT GROUP BY 229 2000 2128000 FIXED TABLE FULL SYS X$KGLOB 11 2000 2128000

Thanks for help
Orca

:cool:
11-14-2002, 07:43 AM
millerg

You could try using optimizer hints to utilize the cost-based optimizer. Try SELECT/*+ choose */......

You can even gather statistics on the fixed tables and it will affect the execution plan, although I would almost certainly say that Oracle would not support such a thing.

HTH
Gerry
11-14-2002, 07:50 AM
pando

analyze fixed tables? I am not sure they are even tables, they are like memory structures
11-14-2002, 07:52 AM
millerg

Pando,

why don't you try it???????????
11-14-2002, 07:58 AM
pando

dont want to screw up anything and whatÂ´s the point? even it works can it be used in production?

and whatÂ´s the point analyze tables which are dynamic, i.e the statistics can vary second after second
11-14-2002, 08:01 AM
millerg

So, are you conceding that they can be analyzed or not????
11-14-2002, 08:05 AM
pando

uh? you tell me whatÂ´s the point?
11-14-2002, 08:09 AM
millerg

The point being that you implied that I was incorrect with my statement that they could be analyzed.
11-14-2002, 08:13 AM
pando

hmm did I say you was incorrect?

I said I am not sure they are even tables or not

and then I asked whatÂ´s the point of doing it
11-14-2002, 08:18 AM
millerg

Did I say you said I was incorrect? I said you implied I was incorrect, which you did when you said 'Analyze fixed tables? I don't think they are even tables.'
11-14-2002, 08:20 AM
pando

so you felt offended by that

omg geez
11-14-2002, 08:34 AM
millerg

OK, let me put it this way:
I ran Orca's query with autotrace on
I ran dbms_stats.gather_table_stats on the fixed tables
I ran Orca's query with autotrace on
The plan was radically different, even including cost values which were not there before. I must admit I was surprised that the analyze worked.

I must also admit that you annoy me, Pando. I read this forum on a regular basis and think it is very good, but see your name on many threads and find a substantial number of your replies patronising, glib and unhelpful.
11-14-2002, 08:43 AM
pando

if you feel offended I cant do anything

if you feel annoyed then itÂ´s your business

:D
11-14-2002, 08:53 AM
millerg

Bye, pando. Don't forget I'm watching you.
11-14-2002, 08:55 AM
pando

I think you really have serious problems!

Gime a break, watching what? I mean whatÂ´s your point?
11-17-2002, 08:17 AM
Orca777

Hi,
my Orcale-friends i am surprised
Ã* thank you for taking my point.
And the answer is interesting and i will check it.
But why here doing war?

I know pando and i know that his type
of answers are short and efficient,
and it's ok!
Some users should even try to read a little bit the orcle-docs
and would find the answer, but even some asks i try to answer.

So end war and let us help to be successful i administring Orcale
and be the Solutionfinder for our customers and be worth the money.

Be blessed
pando and Gerry

:cool: