Tuning Access on fixed tables
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Thread: Tuning Access on fixed tables

  1. #1
    Join Date
    Apr 2002
    Location
    Germany.Laudenbach
    Posts
    448

    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


  2. #2
    Join Date
    Feb 2000
    Location
    Brisbane, Australia
    Posts
    46
    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

  3. #3
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    analyze fixed tables? I am not sure they are even tables, they are like memory structures

  4. #4
    Join Date
    Feb 2000
    Location
    Brisbane, Australia
    Posts
    46
    Pando,

    why don't you try it???????????

  5. #5
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    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

  6. #6
    Join Date
    Feb 2000
    Location
    Brisbane, Australia
    Posts
    46
    So, are you conceding that they can be analyzed or not????

  7. #7
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    uh? you tell me whatīs the point?

  8. #8
    Join Date
    Feb 2000
    Location
    Brisbane, Australia
    Posts
    46
    The point being that you implied that I was incorrect with my statement that they could be analyzed.

  9. #9
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    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

  10. #10
    Join Date
    Feb 2000
    Location
    Brisbane, Australia
    Posts
    46
    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
  •  



Click Here to Expand Forum to Full Width