Home » RDBMS Server » Performance Tuning » slow sql
slow sql [message #613399] Tue, 06 May 2014 06:17 Go to next message
fenor
Messages: 4
Registered: May 2014
Junior Member
Hi!

For example I have the following tables:
maintable (
	maintableid NUMBER(12,0), 
	baseid NUMBER(12,0), 
	type NUMBER(1,0), 
	name NVARCHAR2(50),
	PRIMARY KEY (maintableid),
	FOREIGN KEY (baseid) REFERENCES maintable (maintableid)
);

subtable (
	subtableid NUMBER(12,0), 
	maintableid NUMBER(12,0),
	groupcode NUMBER(2,0),
	status NUMBER(3,0),
	PRIMARY KEY (subtableid),
	FOREIGN KEY (maintableid) REFERENCES maintable (maintableid),
	CONSTRAINT CHK_SUBTABLE_GROUPCODE CHECK (groupcode IN (1,10)),
	CONSTRAINT CHK_SUBTABLE_STATUS CHECK (status IN (0,100))
);

CREATE INDEX "I_ST_INDEX1" ON subtable (maintableid, groupcode, STATUS);
CREATE INDEX "I_ST_INDEX2" ON subtable (maintableid);


I need all the minimum of subtable.status grouped by groupcode in one list.
What makes the problem a bit more complicated is, that more maintable rows can be linked together with the maintable.baseid.
In the case the maintable.type is 9 then I need the minimum of subtable.status of every maintable row linked together not just one maintable row.


I wrote the following sql that gives a good result but is is extremly slow:

Select m.maintableid, m.name, s.status_1, s.status_2, ..., s.status_10
FROM (
		SELECT mt.maintableid, 
			(SELECT min(st.status) FROM subtable st WHERE st.maintableid = mt.maintableid AND groupcode = 1) AS status_1,  
			(SELECT min(st.status) FROM subtable st WHERE st.maintableid = mt.maintableid AND groupcode = 2) AS status_2,
			...
			(SELECT min(st.status) FROM subtable st WHERE st.maintableid = mt.maintableid AND groupcode = 10) AS status_10,
		FROM maintable mt
		WHERE mt.type != 9
		UNION ALL
		SELECT mt.baseid, 
			MIN(SELECT min(st.status) FROM subtable st WHERE st.maintableid = mt.maintableid AND groupcode = 1) AS status_1,  
			MIN(SELECT min(st.status) FROM subtable st WHERE st.maintableid = mt.maintableid AND groupcode = 2) AS status_2,
			...
			MIN(SELECT min(st.status) FROM subtable st WHERE st.maintableid = mt.maintableid AND groupcode = 10) AS status_10,
		FROM maintable mt
		WHERE mt.type = 9
		GROUP BY mt.baseid
	) s
	JOIN maintable m ON (m.maintableid = s.maintableid)


To make it more quicker I made a table with the structure of the result of the previous sql:

maintable (
	maintableid NUMBER(12,0), 
	status_1 NUMBER(3,0),
	status_2 NUMBER(3,0),
	...
	status_10 NUMBER(3,0),
	PRIMARY KEY (maintableid),
	FOREIGN KEY (maintableid) REFERENCES maintable (maintableid)
);


and I wanted to fill it with after triggers but it had not worked. A wrote a procedure witch worked fine but if I called it from the trigger it did nothing.
As I read triggers are rarely a good solution.

Now I am stucked. Has somebody an idea how can I solve this problem?

Thanks.
Re: slow sql [message #613404 is a reply to message #613399] Tue, 06 May 2014 06:59 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Please read How to tune SQL or Identify Performance Problem and Bottleneck and post the required details.

And yes, a trigger is not always a good option. But you need to post above details to proceed further.
Re: slow sql [message #613421 is a reply to message #613399] Tue, 06 May 2014 08:04 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
You are projecting all those scalar subqueries. They are going to run as correlated subqueries, which will be pretty slow. Can you not re-write the query to use a join instead?
Re: slow sql [message #613429 is a reply to message #613404] Tue, 06 May 2014 10:32 Go to previous messageGo to next message
fenor
Messages: 4
Registered: May 2014
Junior Member
This is the ddl:

  CREATE TABLE "MINTA"
   (	"MINTAID" NUMBER(12,0) NOT NULL ENABLE,
	"TIPUS" NUMBER(1,0) NOT NULL ENABLE,
	"BAZISID" NUMBER(12,0) NOT NULL ENABLE,
	"STATUS" NUMBER(3,0) DEFAULT 2 NOT NULL ENABLE,
	"MINTATIPUS" NUMBER(2,0) NOT NULL ENABLE,
	"SURGOS" NUMBER(1,0) DEFAULT 0 NOT NULL ENABLE,
	"MINOSITES" NUMBER(1,0) DEFAULT 0 NOT NULL ENABLE,
	 PRIMARY KEY ("MINTAID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 131072 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"  ENABLE
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 2097152 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ;


  CREATE INDEX "I_M_MTIPUSSTATUS" ON "MINTA" ('MINTATIPUS', 'STATUS')
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 196608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ;


  CREATE OR REPLACE TRIGGER "T_AU_MINTA"
	AFTER UPDATE ON MINTA
REFERENCING NEW AS NEW FOR EACH ROW
DECLARE
	PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
	setMintaStatus(:NEW.mintaid);
	COMMIT;
END;
/
ALTER TRIGGER "T_AU_MINTA" ENABLE;

  CREATE OR REPLACE TRIGGER "T_AD_MINTA"
	AFTER DELETE ON MINTA
REFERENCING OLD AS OLD FOR EACH ROW
DECLARE
	PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
	setMintaStatus(:OLD.mintaid);
	COMMIT;
END;
/
ALTER TRIGGER "T_AD_MINTA" ENABLE;

  CREATE OR REPLACE TRIGGER "T_AI_MINTA"
	AFTER INSERT ON MINTA
REFERENCING NEW AS NEW FOR EACH ROW
DECLARE
	PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
	setMintaStatus(:NEW.mintaid);
	COMMIT;
END;
/
ALTER TRIGGER "T_AI_MINTA" ENABLE;



  CREATE TABLE "MINTA_VIZSGALAT"
   (	"MINTA_VIZSGALATID" NUMBER(12,0) NOT NULL ENABLE,
	"MINTAID" NUMBER(12,0) NOT NULL ENABLE,
	"STATUS" NUMBER(3,0) DEFAULT 2 NOT NULL ENABLE,
	"MINOSITES" NUMBER(1,0) DEFAULT 0 NOT NULL ENABLE,
	"CSOPORT" NUMBER(3,0) NOT NULL ENABLE,
	"ACTIVE" NUMBER(1,0) DEFAULT 1,
	 PRIMARY KEY ("MINTA_VIZSGALATID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 196608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"  ENABLE,
	 CONSTRAINT "FK_MBL_V_MINTAID" FOREIGN KEY ("MINTAID")
	  REFERENCES "MINTA" ("MINTAID") ENABLE
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 2097152 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ;



  CREATE INDEX "I_MV_MIDCSAS" ON "MINTA_VIZSGALAT" ('MINTAID', 'CSOPORT', 'ACTIVE', 'STATUS')
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 524288 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ;

  CREATE INDEX "I_MV_MINTAID" ON "MINTA_VIZSGALAT" ('MINTAID')
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 327680 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ;

  CREATE OR REPLACE TRIGGER "T_AD_MINTAVIZSGALAT"
	AFTER DELETE ON MINTA_VIZSGALAT
REFERENCING OLD AS OLD FOR EACH ROW
DECLARE
	PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
	setMintaCsoportStatus(:OLD.mintaid, :OLD.csoport);
	COMMIT;
END;
/
ALTER TRIGGER "T_AD_MINTAVIZSGALAT" ENABLE;

  CREATE OR REPLACE TRIGGER "T_AU_MINTAVIZSGALAT"
	AFTER UPDATE ON MINTA_VIZSGALAT
REFERENCING NEW AS NEW FOR EACH ROW
DECLARE
	PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
	setMintaCsoportStatus(:NEW.mintaid, :NEW.csoport);
	COMMIT;
END;
/
ALTER TRIGGER "T_AU_MINTAVIZSGALAT" ENABLE;

  CREATE OR REPLACE TRIGGER "T_AI_MINTAVIZSGALAT"
	AFTER INSERT ON MINTA_VIZSGALAT
REFERENCING NEW AS NEW FOR EACH ROW
DECLARE
	PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
	setMintaCsoportStatus(:NEW.mintaid, :NEW.csoport);
	COMMIT;
END;
/
ALTER TRIGGER "T_AI_MINTAVIZSGALAT" ENABLE;


The original sql is:
SELECT m.mintaid,
       m.tipus,
       m.mintatipus,
       m.surgos,
       CASE
         WHEN m.status = 100 THEN opm.minosites
         ELSE
           CASE
             WHEN opm.vminosites = 2 THEN 2
             ELSE -1
           END
       END        AS minosites,
       CASE
         WHEN m.surgos = 1
              AND opm.status < 70 THEN 1
         ELSE 0
       END        AS hilight,
       opm.status AS status,
       csoportstatus_1,
       csoportstatus_2,
       csoportstatus_3,
       csoportstatus_4,
       csoportstatus_5,
       csoportstatus_6,
       csoportstatus_7,
       csoportstatus_8,
       csoportstatus_9,
       csoportstatus_10
FROM   (SELECT m.mintaid,
               m.status,
               m.minosites,
               (SELECT Max(minmv.minosites)
                FROM   minta_vizsgalat minmv
                WHERE  minmv.mintaid = m.mintaid
                       AND minmv.active = 1
                       AND minmv.status >= 50
                       AND minmv.status <= 100) AS vminosites,
               (SELECT Min(stmv.status)
                FROM   minta_vizsgalat stmv
                WHERE  stmv.mintaid = m.mintaid
                       AND stmv.active = 1
                       AND stmv.status >= 50
                       AND stmv.status <= 100
                       AND csoport = 1)         AS csoportstatus_1,
               (SELECT Min(stmv.status)
                FROM   minta_vizsgalat stmv
                WHERE  stmv.mintaid = m.mintaid
                       AND stmv.active = 1
                       AND stmv.status >= 50
                       AND stmv.status <= 100
                       AND csoport = 2)         AS csoportstatus_2,
               (SELECT Min(stmv.status)
                FROM   minta_vizsgalat stmv
                WHERE  stmv.mintaid = m.mintaid
                       AND stmv.active = 1
                       AND stmv.status >= 50
                       AND stmv.status <= 100
                       AND csoport = 3)         AS csoportstatus_3,
               (SELECT Min(stmv.status)
                FROM   minta_vizsgalat stmv
                WHERE  stmv.mintaid = m.mintaid
                       AND stmv.active = 1
                       AND stmv.status >= 50
                       AND stmv.status <= 100
                       AND csoport = 4)         AS csoportstatus_4,
               (SELECT Min(stmv.status)
                FROM   minta_vizsgalat stmv
                WHERE  stmv.mintaid = m.mintaid
                       AND stmv.active = 1
                       AND stmv.status >= 50
                       AND stmv.status <= 100
                       AND csoport = 5)         AS csoportstatus_5,
               (SELECT Min(stmv.status)
                FROM   minta_vizsgalat stmv
                WHERE  stmv.mintaid = m.mintaid
                       AND stmv.active = 1
                       AND stmv.status >= 50
                       AND stmv.status <= 100
                       AND csoport = 6)         AS csoportstatus_6,
               (SELECT Min(stmv.status)
                FROM   minta_vizsgalat stmv
                WHERE  stmv.mintaid = m.mintaid
                       AND stmv.active = 1
                       AND stmv.status >= 50
                       AND stmv.status <= 100
                       AND csoport = 7)         AS csoportstatus_7,
               (SELECT Min(stmv.status)
                FROM   minta_vizsgalat stmv
                WHERE  stmv.mintaid = m.mintaid
                       AND stmv.active = 1
                       AND stmv.status >= 50
                       AND stmv.status <= 100
                       AND csoport = 8)         AS csoportstatus_8,
               (SELECT Min(stmv.status)
                FROM   minta_vizsgalat stmv
                WHERE  stmv.mintaid = m.mintaid
                       AND stmv.active = 1
                       AND stmv.status >= 50
                       AND stmv.status <= 100
                       AND csoport = 10)        AS csoportstatus_10,
               (SELECT Min(stmv.status)
                FROM   minta_vizsgalat stmv
                WHERE  stmv.mintaid = m.mintaid
                       AND stmv.active = 1
                       AND stmv.status >= 50
                       AND stmv.status <= 100
                       AND csoport = 9)         AS csoportstatus_9
        FROM   minta m
        WHERE  m.mintatipus != 9
        UNION ALL
        SELECT m.bazisid,
               Min(m.status),
               Max(m.minosites),
               Max((SELECT Max(minmv.minosites)
                    FROM   minta_vizsgalat minmv
                    WHERE  minmv.mintaid = m.mintaid
                           AND minmv.active = 1
                           AND minmv.status >= 50
                           AND minmv.status <= 100)) AS vminosites,
               Min((SELECT Min(stmv.status)
                    FROM   minta_vizsgalat stmv
                    WHERE  stmv.mintaid = m.mintaid
                           AND stmv.active = 1
                           AND stmv.status >= 50
                           AND stmv.status <= 100
                           AND csoport = 1))         AS csoportstatus_1,
               Min((SELECT Min(stmv.status)
                    FROM   minta_vizsgalat stmv
                    WHERE  stmv.mintaid = m.mintaid
                           AND stmv.active = 1
                           AND stmv.status >= 50
                           AND stmv.status <= 100
                           AND csoport = 2))         AS csoportstatus_2,
               Min((SELECT Min(stmv.status)
                    FROM   minta_vizsgalat stmv
                    WHERE  stmv.mintaid = m.mintaid
                           AND stmv.active = 1
                           AND stmv.status >= 50
                           AND stmv.status <= 100
                           AND csoport = 3))         AS csoportstatus_3,
               Min((SELECT Min(stmv.status)
                    FROM   minta_vizsgalat stmv
                    WHERE  stmv.mintaid = m.mintaid
                           AND stmv.active = 1
                           AND stmv.status >= 50
                           AND stmv.status <= 100
                           AND csoport = 4))         AS csoportstatus_4,
               Min((SELECT Min(stmv.status)
                    FROM   minta_vizsgalat stmv
                    WHERE  stmv.mintaid = m.mintaid
                           AND stmv.active = 1
                           AND stmv.status >= 50
                           AND stmv.status <= 100
                           AND csoport = 5))         AS csoportstatus_5,
               Min((SELECT Min(stmv.status)
                    FROM   minta_vizsgalat stmv
                    WHERE  stmv.mintaid = m.mintaid
                           AND stmv.active = 1
                           AND stmv.status >= 50
                           AND stmv.status <= 100
                           AND csoport = 6))         AS csoportstatus_6,
               Min((SELECT Min(stmv.status)
                    FROM   minta_vizsgalat stmv
                    WHERE  stmv.mintaid = m.mintaid
                           AND stmv.active = 1
                           AND stmv.status >= 50
                           AND stmv.status <= 100
                           AND csoport = 7))         AS csoportstatus_7,
               Min((SELECT Min(stmv.status)
                    FROM   minta_vizsgalat stmv
                    WHERE  stmv.mintaid = m.mintaid
                           AND stmv.active = 1
                           AND stmv.status >= 50
                           AND stmv.status <= 100
                           AND csoport = 8))         AS csoportstatus_8,
               Min((SELECT Min(stmv.status)
                    FROM   minta_vizsgalat stmv
                    WHERE  stmv.mintaid = m.mintaid
                           AND stmv.active = 1
                           AND stmv.status >= 50
                           AND stmv.status <= 100
                           AND csoport = 10))        AS csoportstatus_10,
               Min((SELECT Min(stmv.status)
                    FROM   minta_vizsgalat stmv
                    WHERE  stmv.mintaid = m.mintaid
                           AND stmv.active = 1
                           AND stmv.status >= 50
                           AND stmv.status <= 100
                           AND csoport = 9))         AS csoportstatus_9
        FROM   minta m
        WHERE  m.tipus != 3
               AND m.mintatipus = 9
        GROUP  BY m.bazisid) opm
       join minta m
         ON ( opm.mintaid = m.mintaid )
WHERE  opm.status < 100
       AND opm.status >= 50
       AND opm.status <= 60
       AND ( opm.status >= '50' )
ORDER  BY CASE
            WHEN m.surgos = 1
                 AND opm.status < 70 THEN 1
            ELSE 0
          END DESC,
          mintaid


The explain plan:
Plan hash value: 573517320

-------------------------------------------------------------------------------------------
| Id  | Operation               | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |                 |   606 |   110K|   210   (2)| 00:00:03 |
|   1 |  SORT ORDER BY          |                 |   606 |   110K|   210   (2)| 00:00:03 |
|*  2 |   HASH JOIN             |                 |   606 |   110K|   209   (1)| 00:00:03 |
|   3 |    TABLE ACCESS FULL    | MINTA           |  2838 | 36894 |    69   (0)| 00:00:01 |
|   4 |    VIEW                 |                 |   606 |   102K|   139   (1)| 00:00:02 |
|   5 |     UNION-ALL           |                 |       |       |            |          |
|   6 |      SORT AGGREGATE     |                 |     1 |    11 |            |          |
|*  7 |       TABLE ACCESS FULL | MINTA_VIZSGALAT |     2 |    22 |    52   (0)| 00:00:01 |
|   8 |      SORT AGGREGATE     |                 |     1 |    11 |            |          |
|*  9 |       TABLE ACCESS FULL | MINTA_VIZSGALAT |     1 |    11 |    52   (0)| 00:00:01 |
|  10 |      SORT AGGREGATE     |                 |     1 |    11 |            |          |
|* 11 |       TABLE ACCESS FULL | MINTA_VIZSGALAT |     1 |    11 |    52   (0)| 00:00:01 |
|  12 |      SORT AGGREGATE     |                 |     1 |    11 |            |          |
|* 13 |       TABLE ACCESS FULL | MINTA_VIZSGALAT |     1 |    11 |    52   (0)| 00:00:01 |
|  14 |      SORT AGGREGATE     |                 |     1 |    11 |            |          |
|* 15 |       TABLE ACCESS FULL | MINTA_VIZSGALAT |     1 |    11 |    52   (0)| 00:00:01 |
|  16 |      SORT AGGREGATE     |                 |     1 |    11 |            |          |
|* 17 |       TABLE ACCESS FULL | MINTA_VIZSGALAT |     1 |    11 |    52   (0)| 00:00:01 |
|  18 |      SORT AGGREGATE     |                 |     1 |    11 |            |          |
|* 19 |       TABLE ACCESS FULL | MINTA_VIZSGALAT |     1 |    11 |    52   (0)| 00:00:01 |
|  20 |      SORT AGGREGATE     |                 |     1 |    11 |            |          |
|* 21 |       TABLE ACCESS FULL | MINTA_VIZSGALAT |     1 |    11 |    52   (0)| 00:00:01 |
|  22 |      SORT AGGREGATE     |                 |     1 |    11 |            |          |
|* 23 |       TABLE ACCESS FULL | MINTA_VIZSGALAT |     1 |    11 |    52   (0)| 00:00:01 |
|  24 |      SORT AGGREGATE     |                 |     1 |    11 |            |          |
|* 25 |       TABLE ACCESS FULL | MINTA_VIZSGALAT |     1 |    11 |    52   (0)| 00:00:01 |
|  26 |      SORT AGGREGATE     |                 |     1 |    11 |            |          |
|* 27 |       TABLE ACCESS FULL | MINTA_VIZSGALAT |     1 |    11 |    52   (0)| 00:00:01 |
|* 28 |      TABLE ACCESS FULL  | MINTA           |   605 |  6655 |    69   (0)| 00:00:01 |
|  29 |      SORT AGGREGATE     |                 |     1 |    11 |            |          |
|* 30 |       TABLE ACCESS FULL | MINTA_VIZSGALAT |     2 |    22 |    52   (0)| 00:00:01 |
|  31 |      SORT AGGREGATE     |                 |     1 |    11 |            |          |
|* 32 |       TABLE ACCESS FULL | MINTA_VIZSGALAT |     1 |    11 |    52   (0)| 00:00:01 |
|  33 |      SORT AGGREGATE     |                 |     1 |    11 |            |          |
|* 34 |       TABLE ACCESS FULL | MINTA_VIZSGALAT |     1 |    11 |    52   (0)| 00:00:01 |
|  35 |      SORT AGGREGATE     |                 |     1 |    11 |            |          |
|* 36 |       TABLE ACCESS FULL | MINTA_VIZSGALAT |     1 |    11 |    52   (0)| 00:00:01 |
|  37 |      SORT AGGREGATE     |                 |     1 |    11 |            |          |
|* 38 |       TABLE ACCESS FULL | MINTA_VIZSGALAT |     1 |    11 |    52   (0)| 00:00:01 |
|  39 |      SORT AGGREGATE     |                 |     1 |    11 |            |          |
|* 40 |       TABLE ACCESS FULL | MINTA_VIZSGALAT |     1 |    11 |    52   (0)| 00:00:01 |
|  41 |      SORT AGGREGATE     |                 |     1 |    11 |            |          |
|* 42 |       TABLE ACCESS FULL | MINTA_VIZSGALAT |     1 |    11 |    52   (0)| 00:00:01 |
|  43 |      SORT AGGREGATE     |                 |     1 |    11 |            |          |
|* 44 |       TABLE ACCESS FULL | MINTA_VIZSGALAT |     1 |    11 |    52   (0)| 00:00:01 |
|  45 |      SORT AGGREGATE     |                 |     1 |    11 |            |          |
|* 46 |       TABLE ACCESS FULL | MINTA_VIZSGALAT |     1 |    11 |    52   (0)| 00:00:01 |
|  47 |      SORT AGGREGATE     |                 |     1 |    11 |            |          |
|* 48 |       TABLE ACCESS FULL | MINTA_VIZSGALAT |     1 |    11 |    52   (0)| 00:00:01 |
|  49 |      SORT AGGREGATE     |                 |     1 |    11 |            |          |
|* 50 |       TABLE ACCESS FULL | MINTA_VIZSGALAT |     1 |    11 |    52   (0)| 00:00:01 |
|* 51 |      FILTER             |                 |       |       |            |          |
|  52 |       HASH GROUP BY     |                 |     1 |    18 |    70   (2)| 00:00:01 |
|* 53 |        TABLE ACCESS FULL| MINTA           |   426 |  7668 |    69   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OPM"."MINTAID"="M"."MINTAID")
   7 - filter("MINMV"."MINTAID"=:B1 AND "MINMV"."ACTIVE"=1 AND
              "MINMV"."STATUS">=50 AND "MINMV"."STATUS"<=100)
   9 - filter("STMV"."MINTAID"=:B1 AND "CSOPORT"=1 AND "STMV"."ACTIVE"=1 AND
              "STMV"."STATUS">=50 AND "STMV"."STATUS"<=100)
  11 - filter("STMV"."MINTAID"=:B1 AND "CSOPORT"=2 AND "STMV"."ACTIVE"=1 AND
              "STMV"."STATUS">=50 AND "STMV"."STATUS"<=100)
  13 - filter("STMV"."MINTAID"=:B1 AND "CSOPORT"=3 AND "STMV"."ACTIVE"=1 AND
              "STMV"."STATUS">=50 AND "STMV"."STATUS"<=100)
  15 - filter("STMV"."MINTAID"=:B1 AND "CSOPORT"=4 AND "STMV"."ACTIVE"=1 AND
              "STMV"."STATUS">=50 AND "STMV"."STATUS"<=100)
  17 - filter("STMV"."MINTAID"=:B1 AND "CSOPORT"=5 AND "STMV"."ACTIVE"=1 AND
              "STMV"."STATUS">=50 AND "STMV"."STATUS"<=100)
  19 - filter("STMV"."MINTAID"=:B1 AND "CSOPORT"=6 AND "STMV"."ACTIVE"=1 AND
              "STMV"."STATUS">=50 AND "STMV"."STATUS"<=100)
  21 - filter("STMV"."MINTAID"=:B1 AND "CSOPORT"=7 AND "STMV"."ACTIVE"=1 AND
              "STMV"."STATUS">=50 AND "STMV"."STATUS"<=100)
  23 - filter("STMV"."MINTAID"=:B1 AND "CSOPORT"=8 AND "STMV"."ACTIVE"=1 AND
              "STMV"."STATUS">=50 AND "STMV"."STATUS"<=100)
  25 - filter("STMV"."MINTAID"=:B1 AND "CSOPORT"=10 AND "STMV"."ACTIVE"=1 AND
              "STMV"."STATUS">=50 AND "STMV"."STATUS"<=100)
  27 - filter("STMV"."MINTAID"=:B1 AND "CSOPORT"=9 AND "STMV"."ACTIVE"=1 AND
              "STMV"."STATUS">=50 AND "STMV"."STATUS"<=100)
  28 - filter("SYS_ALIAS_11"."STATUS">=50 AND "SYS_ALIAS_11"."STATUS"<=60 AND
              "SYS_ALIAS_11"."MINTATIPUS"<>9)
  30 - filter("MINMV"."MINTAID"=:B1 AND "MINMV"."ACTIVE"=1 AND
              "MINMV"."STATUS">=50 AND "MINMV"."STATUS"<=100)
  32 - filter("STMV"."MINTAID"=:B1 AND "CSOPORT"=1 AND "STMV"."ACTIVE"=1 AND
              "STMV"."STATUS">=50 AND "STMV"."STATUS"<=100)
  34 - filter("STMV"."MINTAID"=:B1 AND "CSOPORT"=2 AND "STMV"."ACTIVE"=1 AND
              "STMV"."STATUS">=50 AND "STMV"."STATUS"<=100)
  36 - filter("STMV"."MINTAID"=:B1 AND "CSOPORT"=3 AND "STMV"."ACTIVE"=1 AND
              "STMV"."STATUS">=50 AND "STMV"."STATUS"<=100)
  38 - filter("STMV"."MINTAID"=:B1 AND "CSOPORT"=4 AND "STMV"."ACTIVE"=1 AND
              "STMV"."STATUS">=50 AND "STMV"."STATUS"<=100)
  40 - filter("STMV"."MINTAID"=:B1 AND "CSOPORT"=5 AND "STMV"."ACTIVE"=1 AND
              "STMV"."STATUS">=50 AND "STMV"."STATUS"<=100)
  42 - filter("STMV"."MINTAID"=:B1 AND "CSOPORT"=6 AND "STMV"."ACTIVE"=1 AND
              "STMV"."STATUS">=50 AND "STMV"."STATUS"<=100)
  44 - filter("STMV"."MINTAID"=:B1 AND "CSOPORT"=7 AND "STMV"."ACTIVE"=1 AND
              "STMV"."STATUS">=50 AND "STMV"."STATUS"<=100)
  46 - filter("STMV"."MINTAID"=:B1 AND "CSOPORT"=8 AND "STMV"."ACTIVE"=1 AND
              "STMV"."STATUS">=50 AND "STMV"."STATUS"<=100)
  48 - filter("STMV"."MINTAID"=:B1 AND "CSOPORT"=10 AND "STMV"."ACTIVE"=1 AND
              "STMV"."STATUS">=50 AND "STMV"."STATUS"<=100)
  50 - filter("STMV"."MINTAID"=:B1 AND "CSOPORT"=9 AND "STMV"."ACTIVE"=1 AND
              "STMV"."STATUS">=50 AND "STMV"."STATUS"<=100)
  51 - filter(MIN("SYS_ALIAS_11"."STATUS")<100 AND
              MIN("SYS_ALIAS_11"."STATUS")>=50 AND MIN("SYS_ALIAS_11"."STATUS")<=60 AND
              MIN("SYS_ALIAS_11"."STATUS")>=50)
  53 - filter("SYS_ALIAS_11"."MINTATIPUS"=9 AND "SYS_ALIAS_11"."TIPUS"<>3)

 115 rows selected


I could not create a trace because the trace file was full of
OER 7451 in Load Indicator : Error Code = OSD-04500: illegal option specified
O/S-Error: (OS 1) Incorrect function. !
I use a 64bit window and XE.

Re: slow sql [message #613432 is a reply to message #613421] Tue, 06 May 2014 10:40 Go to previous messageGo to next message
fenor
Messages: 4
Registered: May 2014
Junior Member
At least I have no idea how I could do that because the status_* columns are aggregate columns and I do not know how could I have the same result with joins.
Re: slow sql [message #613505 is a reply to message #613432] Wed, 07 May 2014 10:18 Go to previous messageGo to next message
fenor
Messages: 4
Registered: May 2014
Junior Member
I wrote this to make the triggers working and it looks like it would work but I have not used this kind of trigger before so if somebody has an advice or something I would be appreciated.

CREATE GLOBAL TEMPORARY TABLE MINTACSOPORTSTATUS_TRHELP
(
	mintaid NUMBER(12,0) NOT NULL,
	csoport NUMBER(3,0)
) ON COMMIT DELETE ROWS;

CREATE OR REPLACE TRIGGER mintavizsgalat_compound_tr
	FOR INSERT OR UPDATE OR DELETE ON minta_vizsgalat
	COMPOUND TRIGGER

	AFTER EACH ROW IS
		lcount NUMBER;
		lmintaid NUMBER(12,0);
		lcsoport NUMBER(12,0);
	BEGIN
		IF DELETING THEN
			lmintaid := :old.mintaid;
			lcsoport := :old.csoport;
		ELSE
			lmintaid := :new.mintaid;
			lcsoport := :new.csoport;
		END IF;
		INSERT INTO MINTACSOPORTSTATUS_TRHELP VALUES (lmintaid, lcsoport);
	END AFTER EACH ROW;

	AFTER STATEMENT IS
	BEGIN
		FOR i IN (SELECT DISTINCT * FROM MINTACSOPORTSTATUS_TRHELP WHERE csoport IS NOT NULL) LOOP
			setMintaCsoportStatus(i.mintaid, i.csoport);
		END LOOP;
	END AFTER STATEMENT;

END mintavizsgalat_compound_tr;
/

CREATE OR REPLACE TRIGGER minta_compound_tr
	FOR INSERT OR UPDATE OR DELETE ON minta
	COMPOUND TRIGGER

	AFTER EACH ROW IS
		lcount NUMBER;
		lmintaid NUMBER(12,0);
	BEGIN
		IF DELETING THEN
			lmintaid := :old.mintaid;
		ELSE
			lmintaid := :new.mintaid;
		END IF;
		INSERT INTO MINTACSOPORTSTATUS_TRHELP VALUES (lmintaid, null);
	END AFTER EACH ROW;

	AFTER STATEMENT IS
	BEGIN
		FOR i IN (SELECT DISTINCT * FROM MINTACSOPORTSTATUS_TRHELP WHERE csoport IS NULL) LOOP
			setMintaStatus(i.mintaid);
		END LOOP;
	END AFTER STATEMENT;

END minta_compound_tr;
/
Re: slow sql [message #613507 is a reply to message #613505] Wed, 07 May 2014 10:38 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>OER 7451 in Load Indicator : Error Code = OSD-04500: illegal option specified
>O/S-Error: (OS 1) Incorrect function. !
>I use a 64bit window and XE.

it appears that you installed 32-bit Oracle on 64-bit OS & we can't fix this situation.
Only you can install & run complaint software on your system.
Previous Topic: Full table scan - Queries
Next Topic: High CPU Consuming Query
Goto Forum:
  


Current Time: Thu Mar 28 10:40:01 CDT 2024