Home » RDBMS Server » Performance Tuning » slow running sql (10g)
slow running sql [message #654641] Mon, 08 August 2016 07:57 Go to next message
arifmd1705
Messages: 37
Registered: May 2016
Location: uae
Member
Hi experts ,

i am running a sql query which is taking a very long time to execute, appreciate if someone can give me useful tips to reduce the running time.

I have taken the explain plan , i cannot create inserts scripts as the size of table is huge.



SQL> EXPLAIN PLAN FOR
  2  SELECT  a.csh_comp_code,a.csh_sys_id, null SBSD_BAY, null SBSD_UNIT ,A.CSH_BATCH_NO,A.CSH_ACTIV
ITY_CODE,A.CSH_JH_JOB_NO,A.CSH_DT,
  3  A.CSH_NO,A.CSH_TXN_CODE,COUNT(b.CSD_PM_CODE) NO_OF_MARKS,SUM(DECODE(NVL(CSD_HOLD_YN_NUM,'X'),'Y
',1,0)) ON_HOLD,
  4  sum(NVL(b.csd_qty,0)) REQ_QTY, (sum(NVL(b.csd_qty,0)) - sum(NVL(d.cso_qty,0))) BAL_TO_COMP,D.CS
O_OPER_CODE
  5  FROM OT_CUTTING_SHEET_HEAD A,OT_CUTTING_SHEET_DETAIL B,OT_CUTTING_SHEET_OPERATION D
  6  WHERE  A.CSH_COMP_CODE = 'RAK' 
  7  AND A.CSH_SYS_ID = B.CSD_cSH_SYS_ID
  8  AND B.CSD_SYS_ID = D.CSO_CSD_SYS_ID
  9  AND A.CSH_APPR_UID IS NOT NULL
 10  AND B.CSD_PS_CODE IS NULL
 11  AND EXISTS ( SELECT 1 FROM OT_PROJECT_INFO_HEAD
 12  WHERE  PIH_WO_NO = CSH_JH_JOB_NO
 13  AND NVL(PIH_CLOSED_YN_NUM,2)=2 )
 14  GROUP BY A.CSH_COMP_CODE,A.CSH_SYS_ID, A.CSH_BATCH_NO,A.CSH_ACTIVITY_CODE,A.CSH_JH_JOB_NO,A.CSH_DT,
 15  A.CSH_NO,A.CSH_TXN_CODE,D.CSO_OPER_CODE
 16  HAVING sum(NVL(b.csd_qty,0)) - sum(NVL(d.cso_qty,0)) > 0
 17  ORDER BY 1,2,3
 18  ;


Explained.


SQL> select plan_table_output
  2  from table(dbms_xplan.display('plan_table',null,'basic +predicate +cost'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 2551292298

---------------------------------------------------------------------------
| Id  | Operation               | Name                       | Cost (%CPU)|
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |                            | 48451   (2)|
|*  1 |  FILTER                 |                            |            |
|   2 |   SORT GROUP BY         |                            | 48451   (2)|
|*  3 |    HASH JOIN            |                            | 38808   (2)|
|*  4 |     HASH JOIN           |                            | 16099   (1)|
|*  5 |      HASH JOIN          |                            |   146   (3)|
|   6 |       SORT UNIQUE       |                            |     3   (0)|
|*  7 |        TABLE ACCESS FULL| OT_PROJECT_INFO_HEAD       |     3   (0)|
|*  8 |       TABLE ACCESS FULL | OT_CUTTING_SHEET_HEAD      |   141   (2)|
|*  9 |      TABLE ACCESS FULL  | OT_CUTTING_SHEET_DETAIL    | 15950   (1)|
|  10 |     TABLE ACCESS FULL   | OT_CUTTING_SHEET_OPERATION | 12942   (2)|
---------------------------------------------------------------------------

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

   1 - filter(SUM(NVL("B"."CSD_QTY",0))-SUM(NVL("D"."CSO_QTY",0))>0)
   3 - access("B"."CSD_SYS_ID"="D"."CSO_CSD_SYS_ID")
   4 - access("A"."CSH_SYS_ID"="B"."CSD_CSH_SYS_ID")
   5 - access("PIH_WO_NO"="CSH_JH_JOB_NO")
   7 - filter(NVL("PIH_CLOSED_YN_NUM",2)=2)
   8 - filter("A"."CSH_APPR_UID" IS NOT NULL AND
              "A"."CSH_COMP_CODE"='RAK')
   9 - filter("B"."CSD_PS_CODE" IS NULL)

29 rows selected.


Report message to a moderator

Re: slow running sql [message #654642 is a reply to message #654641] Mon, 08 August 2016 08:03 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
columns in the WHERE clause should be indexed
SELECT a.csh_comp_code, 
       a.csh_sys_id, 
       NULL                                                SBSD_BAY, 
       NULL                                                SBSD_UNIT, 
       A.csh_batch_no, 
       A.csh_activ                                         ITY_CODE, 
       A.csh_jh_job_no, 
       A.csh_dt, 
       A.csh_no, 
       A.csh_txn_code, 
       Count(b.csd_pm_code)                                NO_OF_MARKS, 
       SUM(Decode(Nvl(csd_hold_yn_num, 'X'), 'Y ', 1, 
                                             0))           ON_HOLD, 
       SUM(Nvl(b.csd_qty, 0))                              REQ_QTY, 
       ( SUM(Nvl(b.csd_qty, 0)) - SUM(Nvl(d.cso_qty, 0)) ) BAL_TO_COMP, 
       D.cs                                                O_OPER_CODE 
FROM   ot_cutting_sheet_head A, 
       ot_cutting_sheet_detail B, 
       ot_cutting_sheet_operation D 
WHERE  A.csh_comp_code = 'RAK' 
       AND A.csh_sys_id = B.csd_csh_sys_id 
       AND B.csd_sys_id = D.cso_csd_sys_id 
       AND A.csh_appr_uid IS NOT NULL 
       AND B.csd_ps_code IS NULL 
       AND EXISTS (SELECT 1 
                   FROM   ot_project_info_head 
                   WHERE  pih_wo_no = csh_jh_job_no 
                          AND Nvl(pih_closed_yn_num, 2) = 2) 
GROUP  BY A.csh_comp_code, 
          A.csh_sys_id, 
          A.csh_batch_no, 
          A.csh_activity_code, 
          A.csh_jh_job_no, 
          A.csh_dt, 
          A.csh_no, 
          A.csh_txn_code, 
          D.cso_oper_code 
HAVING SUM(Nvl(b.csd_qty, 0)) - SUM(Nvl(d.cso_qty, 0)) > 0 
ORDER  BY 1, 
          2, 
          3;

Report message to a moderator

Re: slow running sql [message #654643 is a reply to message #654642] Mon, 08 August 2016 08:42 Go to previous messageGo to next message
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
Senior Member
This looks like it's getting most of the data from the three main tables, in which case just indexing the where clause columns isn't going to help much unless they've got all columns the query uses so it doesn't need to go to the table at all.
An index on ot_cutting_sheet_operation(cso_csd_sys_id, cso_oper_code, cso_qty) should make it use the index instead of the full table scan on that table.

@arifmd1705 - it would help if you made sure all the columns in the query had aliases - you've missed a few.

Report message to a moderator

Re: slow running sql [message #654644 is a reply to message #654641] Mon, 08 August 2016 08:43 Go to previous messageGo to next message
Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

What does "very long time" mean?
Post the plan with rows numbers.
Better than an explain plan, post an execution plan.

Report message to a moderator

Re: slow running sql [message #654645 is a reply to message #654643] Mon, 08 August 2016 08:44 Go to previous messageGo to next message
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
Senior Member
By the way - order by 3 makes no sense since column 3 is hard-coded to null.

Report message to a moderator

Re: slow running sql [message #654646 is a reply to message #654645] Mon, 08 August 2016 09:05 Go to previous messageGo to next message
Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

which leads me to a question: why there is no "SORT ORDER BY" operation at the end?

@OP,
Also post your Oracle version, with 4 decimals like 10.2.0.4.

[Updated on: Mon, 08 August 2016 09:05]

Report message to a moderator

Re: slow running sql [message #654647 is a reply to message #654642] Mon, 08 August 2016 09:40 Go to previous messageGo to next message
arifmd1705
Messages: 37
Registered: May 2016
Location: uae
Member
thanks blackswan , index exists for those columns already, do i need to rebuild them or create new ones.


select 
b.uniqueness, a.index_name, a.table_name, a.column_name 
from all_ind_columns a, all_indexes b
where a.index_name=b.index_name 
and a.table_name = 'OT_CUTTING_SHEET_HEAD'
order by a.table_name, a.index_name, a.column_position;


UNIQUENESS,INDEX_NAME,TABLE_NAME,COLUMN_NAME
UNIQUE,CSH_PK,OT_CUTTING_SHEET_HEAD,CSH_SYS_ID
UNIQUE,CSH_UK_01,OT_CUTTING_SHEET_HEAD,CSH_COMP_CODE
UNIQUE,CSH_UK_01,OT_CUTTING_SHEET_HEAD,CSH_TXN_CODE
UNIQUE,CSH_UK_01,OT_CUTTING_SHEET_HEAD,CSH_NO
UNIQUE,CSH_UK_02,OT_CUTTING_SHEET_HEAD,CSH_JH_JOB_NO
UNIQUE,CSH_UK_02,OT_CUTTING_SHEET_HEAD,CSH_ACTIVITY_CODE
UNIQUE,CSH_UK_02,OT_CUTTING_SHEET_HEAD,CSH_BATCH_NO
UNIQUE,CSH_UK_02,OT_CUTTING_SHEET_HEAD,CSH_PHASE_NO
NONUNIQUE,OT_CUTTING_SHEET_HEAD_001,OT_CUTTING_SHEET_HEAD,CSH_JH_JOB_NO
NONUNIQUE,OT_CUTTING_SHEET_HEAD_001,OT_CUTTING_SHEET_HEAD,CSH_ACTIVITY_CODE
NONUNIQUE,OT_CUTTING_SHEET_HEAD_001,OT_CUTTING_SHEET_HEAD,CSH_BATCH_NO
NONUNIQUE,OT_CUTTING_SHEET_HEAD_002,OT_CUTTING_SHEET_HEAD,CSH_SYS_ID
NONUNIQUE,OT_CUTTING_SHEET_HEAD_002,OT_CUTTING_SHEET_HEAD,CSH_FRZ_FLAG_NUM
NONUNIQUE,OT_CUTTING_SHEET_HEAD_002,OT_CUTTING_SHEET_HEAD,CSH_APPR_UID
NONUNIQUE,OT_CUTTING_SHEET_HEAD_002,OT_CUTTING_SHEET_HEAD,CSH_APPR_DT
NONUNIQUE,OT_CUTTING_SHEET_HEAD_002,OT_CUTTING_SHEET_HEAD,CSH_ACTIVITY_CODE
NONUNIQUE,OT_CUTTING_SHEET_HEAD_002,OT_CUTTING_SHEET_HEAD,CSH_BATCH_NO


--FOR OT_CUTTING_SHEET_DETAIL


UNIQUENESS,INDEX_NAME,TABLE_NAME,COLUMN_NAME
UNIQUE,CSD_PK,OT_CUTTING_SHEET_DETAIL,CSD_SYS_ID
NONUNIQUE,OT_CS_DETAIL_NX_01,OT_CUTTING_SHEET_DETAIL,CSD_CSH_SYS_ID
NONUNIQUE,OT_CS_DETAIL_NX_02,OT_CUTTING_SHEET_DETAIL,CSD_PM_CODE
NONUNIQUE,OT_CUTTING_SHEET_DETAIL_001,OT_CUTTING_SHEET_DETAIL,CSD_PS_CODE
NONUNIQUE,OT_CUTTING_SHEET_DETAIL_002,OT_CUTTING_SHEET_DETAIL,CSD_CSH_SYS_ID
NONUNIQUE,OT_CUTTING_SHEET_DETAIL_002,OT_CUTTING_SHEET_DETAIL,CSD_FLEX_01
NONUNIQUE,OT_CUTTING_SHEET_DETAIL_002,OT_CUTTING_SHEET_DETAIL,CSD_PS_CODE


--FOR CUTTING_SHEET_OPERATION



UNIQUENESS,INDEX_NAME,TABLE_NAME,COLUMN_NAME
NONUNIQUE,CSO_CSD_IDX,OT_CUTTING_SHEET_OPERATION,CSO_CSD_SYS_ID
UNIQUE,CSO_PK,OT_CUTTING_SHEET_OPERATION,CSO_SYS_ID
NONUNIQUE,OT_CUTTING_SHEET_OPERATION_001,OT_CUTTING_SHEET_OPERATION,CSO_OP_STAGE_NO
NONUNIQUE,OT_CUTTING_SHEET_OPERATION_002,OT_CUTTING_SHEET_OPERATION,CSO_OPER_CODE


Report message to a moderator

Re: slow running sql [message #654648 is a reply to message #654647] Mon, 08 August 2016 09:46 Go to previous messageGo to next message
Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

This has, of course, nothing of what I asked, really nothing not even a single word.
You need to answer the questions, all of them.

Report message to a moderator

Re: slow running sql [message #654649 is a reply to message #654641] Mon, 08 August 2016 09:50 Go to previous messageGo to next message
John Watson
Messages: 8964
Registered: January 2010
Location: Global Village
Senior Member
I don't think any indexes would help much. For example, this predicate 
AND Nvl(pih_closed_yn_num, 2) = 2
isn't really indexable: the use of a function suppresses index usage, and if you re-write it to 
pih_closed_yn_num=2 or pih_closed_yn_num is null
that isn't indexable either.

I would check the statistics first. How many rows do you get from each table, after applying the predicates? How many rows does the optimizxer expect to get ? How many rows are there in each table? Running dbms_xplan.display on defaults would help.

Report message to a moderator

Re: slow running sql [message #654650 is a reply to message #654644] Mon, 08 August 2016 09:56 Go to previous messageGo to next message
arifmd1705
Messages: 37
Registered: May 2016
Location: uae
Member
@Michael , please find below the details.


SQL> COL PRODUCT FORMAT A35
SQL> COL VERSION FORMAT A15
SQL> COL STATUS FORMAT A15 
SQL> SELECT * FROM PRODUCT_COMPONENT_VERSION;
NLSRTL                              10.2.0.4.0      Production
Oracle Database 10g Enterprise Edit 10.2.0.4.0      64bi
ion

PL/SQL                              10.2.0.4.0      Production
TNS for 64-bit Windows:             10.2.0.4.0      Production



SQL> EXPLAIN PLAN FOR
  2  SELECT  /*+ GATHER_PLAN_STATISTICS */  a.csh_comp_code,a.csh_sys_id, A.CSH_BATCH_NO,A.CSH_ACTIV
ITY_CODE,A.CSH_JH_JOB_NO,A.CSH_DT,
  3  A.CSH_NO,A.CSH_TXN_CODE,COUNT(b.CSD_PM_CODE) NO_OF_MARKS,SUM(DECODE(NVL(CSD_HOLD_YN_NUM,'X'),'Y
',1,0)) ON_HOLD,
  4  sum(NVL(b.csd_qty,0)) REQ_QTY, (sum(NVL(b.csd_qty,0)) - sum(NVL(d.cso_qty,0))) BAL_TO_COMP,D.CS
O_OPER_CODE
  5  FROM OT_CUTTING_SHEET_HEAD A,OT_CUTTING_SHEET_DETAIL B,OT_CUTTING_SHEET_OPERATION D
  6  WHERE  A.CSH_COMP_CODE = 'RAK' 
  7  AND A.CSH_SYS_ID = B.CSD_cSH_SYS_ID
  8  AND B.CSD_SYS_ID = D.CSO_CSD_SYS_ID
  9  AND A.CSH_APPR_UID IS NOT NULL
 10  AND B.CSD_PS_CODE IS NULL
 11  AND EXISTS ( SELECT 1 FROM OT_PROJECT_INFO_HEAD
 12  WHERE  PIH_WO_NO = CSH_JH_JOB_NO
 13  AND NVL(PIH_CLOSED_YN_NUM,2)=2 )
 14  GROUP BY A.CSH_COMP_CODE,A.CSH_SYS_ID, A.CSH_BATCH_NO,A.CSH_ACTIVITY_CODE,A.CSH_JH_JOB_NO,A.CSH
_DT,
 15  A.CSH_NO,A.CSH_TXN_CODE,D.CSO_OPER_CODE
 16  HAVING sum(NVL(b.csd_qty,0)) - sum(NVL(d.cso_qty,0)) > 0
 17  ORDER BY 1,2;

explained



SQL> SET LINESIZE 130
SQL> SET PAGESIZE 0
SQL> SELECT * 
  2  FROM   TABLE(DBMS_XPLAN.DISPLAY);
Plan hash value: 2551292298

----------------------------------------------------------------------------------------------------
| Id  | Operation               | Name                       | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |                            | 46513 |  5450K|       | 48451   (2)| 00:09:42 |
|*  1 |  FILTER                 |                            |       |       |       |            |          |
|   2 |   SORT GROUP BY         |                            | 46513 |  5450K|   125M| 48451   (2)| 00:09:42 |
|*  3 |    HASH JOIN            |                            |   930K|   106M|    43M| 38808   (2)| 00:07:46 |
|*  4 |     HASH JOIN           |                            |   386K|    39M|       | 16099   (1)| 00:03:14 |
|*  5 |      HASH JOIN          |                            | 11876 |   788K|       |   146   (3)| 00:00:02 |
|   6 |       SORT UNIQUE       |                            |    85 |   595 |       |     3   (0)| 00:00:01 |
|*  7 |        TABLE ACCESS FULL| OT_PROJECT_INFO_HEAD       |    85 |   595 |       |     3   (0)| 00:00
|*  8 |       TABLE ACCESS FULL | OT_CUTTING_SHEET_HEAD      | 16434 |   978K|       |   141   (2)| 00:
|*  9 |      TABLE ACCESS FULL  | OT_CUTTING_SHEET_DETAIL    |   535K|    19M|       | 15950   (1)| 00:0
|  10 |     TABLE ACCESS FULL   | OT_CUTTING_SHEET_OPERATION |  6342K|    78M|       | 12942   (2)| 00:02
----------------------------------------------------------------------------------------------------

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

   1 - filter(SUM(NVL("B"."CSD_QTY",0))-SUM(NVL("D"."CSO_QTY",0))>0)
   3 - access("B"."CSD_SYS_ID"="D"."CSO_CSD_SYS_ID")
   4 - access("A"."CSH_SYS_ID"="B"."CSD_CSH_SYS_ID")
   5 - access("PIH_WO_NO"="CSH_JH_JOB_NO")
   7 - filter(NVL("PIH_CLOSED_YN_NUM",2)=2)
   8 - filter("A"."CSH_APPR_UID" IS NOT NULL AND "A"."CSH_COMP_CODE"='RAK')
   9 - filter("B"."CSD_PS_CODE" IS NULL)

28 rows selected.

@cookiemonster you are right please ignore the previous order , it is 1 and 2 only.

Report message to a moderator

Re: slow running sql [message #654651 is a reply to message #654650] Mon, 08 August 2016 10:57 Go to previous messageGo to next message
Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

First review your indexes (or add new ones?) to fit the predicates.

Report message to a moderator

Re: slow running sql [message #654659 is a reply to message #654651] Mon, 08 August 2016 23:02 Go to previous messageGo to next message
arifmd1705
Messages: 37
Registered: May 2016
Location: uae
Member
@Michael based on cookiemonster advice i added index on ot_cutting_sheet_operation , there is a difference it is better than before , when you say review means do i need to rebuild the existing ones.

Report message to a moderator

Re: slow running sql [message #654661 is a reply to message #654659] Tue, 09 August 2016 00:49 Go to previous messageGo to next message
Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

No I meant, think again on the columns and the order of the columns on the indexes.

Quote:
i added index on ot_cutting_sheet_operation , there is a difference it is better than before
Post the new execution plan.

Report message to a moderator

Re: slow running sql [message #654664 is a reply to message #654661] Tue, 09 August 2016 01:39 Go to previous messageGo to next message
arifmd1705
Messages: 37
Registered: May 2016
Location: uae
Member
Hi Michael,

I change the query and added more indexes to the two table ot_cutting_sheet_detail and ot_cutting_sheet_operation, but indexes for ot_cutting_sheet_detail are not being used. i even gave the index hint but still it is not being used.
Kindly tell me how to choose the columns based on order of columns are based on where condition like filters, i have created the two indexes.



Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


CREATE UNIQUE INDEX CSD_DETL_PK
    ON ot_cutting_sheet_detail (csd_sys_id,csd_csh_sys_id,csd_pm_code,csd_ps_code,csd_qty);


CREATE UNIQUE INDEX CSO_OPER_PK
    ON ot_cutting_sheet_detail (cso_sys_id,cso_csd_sys_id,cso_oper_code,cso_qty);  


SQL> EXPLAIN PLAN FOR
  2  select /*+ index(OT_CUTTING_SHEET_DETAIL CSD_DETL_PK) */
  3   a.csh_comp_code,a.csh_sys_id, A.CSH_BATCH_NO,A.CSH_ACTIVITY_CODE,A.CSH_JH_JOB_NO,A.CSH_DT,
  4  A.CSH_NO,A.CSH_TXN_CODE,COUNT(b.CSD_PM_CODE) NO_OF_MARKS,SUM(DECODE(NVL(CSD_HOLD_YN_NUM,'X'),'Y
',1,0)) ON_HOLD,
  5  sum(NVL(b.csd_qty,0)) REQ_QTY, (sum(NVL(b.csd_qty,0)) - sum(NVL(d.cso_qty,0))) BAL_TO_COMP --,D
.CSO_OPER_CODE
  6  FROM OT_CUTTING_SHEET_HEAD A,OT_CUTTING_SHEET_DETAIL B,OT_CUTTING_SHEET_OPERATION D
  7  WHERE  A.CSH_COMP_CODE = 'RAK' 
  8  AND A.CSH_SYS_ID = B.CSD_cSH_SYS_ID
  9  AND B.CSD_SYS_ID = D.CSO_CSD_SYS_ID
 10  AND A.CSH_APPR_DT  > TO_DATE('01/01/2016','DD/MM/RRRR')
 11  AND B.CSD_CODE = 'P'
 12  AND d.CSO_OPER_CODE = 'Paint'
 13  AND EXISTS ( SELECT 1 FROM OT_PROJECT_INFO_HEAD
 14  WHERE  PIH_WO_NO = CSH_JH_JOB_NO
 15  AND NVL(PIH_CLOSED_YN_NUM,2)=2 )
 16  GROUP BY A.CSH_COMP_CODE,A.CSH_SYS_ID, A.CSH_BATCH_NO,A.CSH_ACTIVITY_CODE,A.CSH_JH_JOB_NO,A.CSH
_DT,
 17  A.CSH_NO,A.CSH_TXN_CODE --,D.CSO_OPER_CODE
 18  HAVING sum(NVL(b.csd_qty,0)) - sum(NVL(d.cso_qty,0)) > 0
 19  ORDER BY 1,2,8 ;

Explained.

SQL> SELECT * 
  2      FROM   TABLE(DBMS_XPLAN.DISPLAY);
Plan hash value: 3071537766

----------------------------------------------------------------------------------------------------
| Id  | Operation               | Name                    | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |                         |  5227 |   597K|       | 23239   (2)| 00:04:39 |
|*  1 |  FILTER                 |                         |       |       |       |            |          |
|   2 |   SORT GROUP BY         |                         |  5227 |   597K|       | 23239   (2)| 00:04:39 |
|*  3 |    HASH JOIN            |                         |   104K|    11M|  8832K| 23232   (2)| 00:04:39 |
|*  4 |     INDEX FAST FULL SCAN| CSO_OPER_PK             |   361K|  4588K|       |  5652   (2)| 00:01:08 |
|*  5 |     HASH JOIN           |                         |   154K|    15M|       | 16297   (3)| 00:03:16 |
|*  6 |      HASH JOIN          |                         |  2104 |   139K|       |   145   (3)| 00:00:02 |
|   7 |       SORT UNIQUE       |                         |    85 |   595 |       |     3   (0)| 00:00:01 |
|*  8 |        TABLE ACCESS FULL| OT_PROJECT_INFO_HEAD    |    85 |   595 |       |     3   (0)| 00:00:01 |
|*  9 |       TABLE ACCESS FULL | OT_CUTTING_SHEET_HEAD   |  2912 |   173K|       |   140   (1)| 00:00:0
|* 10 |      TABLE ACCESS FULL  | OT_CUTTING_SHEET_DETAIL |   534K|    18M|       | 16147   (2)| 00:03:14
----------------------------------------------------------------------------------------------------

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

   1 - filter(SUM(NVL("B"."CSD_QTY",0))-SUM(NVL("D"."CSO_QTY",0))>0)
   3 - access("B"."CSD_SYS_ID"="D"."CSO_CSD_SYS_ID")
   4 - filter("D"."CSO_OPER_CODE"='Paint')
   5 - access("A"."CSH_SYS_ID"="B"."CSD_CSH_SYS_ID")
   6 - access("PIH_WO_NO"="CSH_JH_JOB_NO")
   8 - filter(NVL("PIH_CLOSED_YN_NUM",2)=2)
   9 - filter("A"."CSH_APPR_DT">TO_DATE(' 2016-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "A"."CSH_COMP_CODE"='RAK')
  10 - filter("B"."CSD_CODE"='P')

30 rows selected.

SQL>

Report message to a moderator

Re: slow running sql [message #654678 is a reply to message #654664] Tue, 09 August 2016 08:16 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
add individual indexes on each of the columns below
"B"."CSD_SYS_ID"
"B"."CSD_CSH_SYS_ID"
"B"."CSD_CODE"='P'

Report message to a moderator

Re: slow running sql [message #654685 is a reply to message #654678] Wed, 10 August 2016 01:43 Go to previous messageGo to next message
arifmd1705
Messages: 37
Registered: May 2016
Location: uae
Member
@blackswan, do i need to delete the old combined indexes which are existing as combination and then create them individually.

Report message to a moderator

Re: slow running sql [message #654812 is a reply to message #654678] Sat, 13 August 2016 23:22 Go to previous message
arifmd1705
Messages: 37
Registered: May 2016
Location: uae
Member
thanks Blackswan, your advice really helped, i created the third index on csd_code and now i see the cost is reduced drastically, i have added a "FIRST_ROWS" hint as well.

Report message to a moderator

Previous Topic: server session exceed
Next Topic: improve performance using partitioning
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sun Feb 02 17:51:08 CST 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.