-
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
-
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
-
analyze fixed tables? I am not sure they are even tables, they are like memory structures
-
Pando,
why don't you try it???????????
-
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
-
So, are you conceding that they can be analyzed or not????
-
uh? you tell me what´s the point?
-
The point being that you implied that I was incorrect with my statement that they could be analyzed.
-
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
-
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.'
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|