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

Hybrid View

  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