Re: What server 9.2.0 read?

From: Orca777 <member28638_at_dbforums.com>
Date: Fri, 23 May 2003 10:08:22 +0000
Message-ID: <2914780.1053684502@dbforums.com>

Hi, had the same efect on win2k 9i r2, but have no chande to test it under Linux;

The CPU goes to 100% and Oracle is working :

SELECT STATEMENT, GOAL = CHOOSE			77	1	795
 NESTED LOOPS			77	1	795
  MERGE JOIN CARTESIAN			33	1	714
   MERGE JOIN CARTESIAN			22	1	681
    FIXED TABLE FULL	SYS	X$KSUSE	11	1	651
    BUFFER SORT			11	1	30
     FIXED TABLE FULL	SYS	X$KSUSE		1	30
   BUFFER SORT			22	1	33
    FIXED TABLE FULL	SYS	X$KSQRS		1	33
  VIEW	SYS	GV$_LOCK		1	81
   UNION-ALL					
    VIEW	SYS	GV$_LOCK1	22	2	162
     UNION-ALL					
      FIXED TABLE FULL	SYS	X$KDNSSF	11	1	94
      FIXED TABLE FULL	SYS	X$KSQEQ	11	1	94
    FIXED TABLE FULL	SYS	X$KTADM	11	1	94
    FIXED TABLE FULL	SYS	X$KTCXB	11	1	94

As you will see there ar not lot rows in the tables but what could get heavy are the two nested MERGE JOIN CARTESIAN

Here the num of rows:
There is no stats on fixed tables, so i think to speed up the queries you have to use a lot of hints.

Connected to Oracle9i Enterprise Edition Release 9.2.0.2.1 Connected as SYS

CLEAR
SELECT COUNT(*) AS X$KSUSE_COUNT FROM SYS.X$KSUSE;

SELECT COUNT(*) AS X$KSUSE_COUNT FROM SYS.X$KDNSSF;
SELECT COUNT(*) AS X$KSQEQ_COUNT FROM SYS.X$KSQEQ;
SELECT COUNT(*) AS X$KDNSSF_COUNT FROM SYS.X$KDNSSF;
SELECT COUNT(*) AS X$KTCXB_COUNT FROM SYS.X$KTCXB;

SELECT COUNT(*) AS GV$_LOCK_COUNT FROM SYS.GV$_LOCK; SELECT COUNT(*) AS X$KSQRS_COUNT FROM SYS.X$KSQRS; X$KSUSE_COUNT

---

          170

X$KSUSE_COUNT

---

          170

X$KSQEQ_COUNT

---

         2240

X$KDNSSF_COUNT

---

           170

X$KTCXB_COUNT

---

          220

GV$_LOCK_COUNT

---

            13

X$KSQRS_COUNT

---

          992

SQL> Orca

--
Posted via http://dbforums.com

Received on Fri May 23 2003 - 05:08:22 CDT