OraFAQ Forum: Performance Tuning

Home » RDBMS Server » Performance Tuning » query tunning (oracle10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

query tunning [message #685968]

Wed, 11 May 2022 06:53

arun888
Messages: 100
Registered: June 2015
Location: INDIA

Senior Member

Currently, i am currently the table and re-loading the data since the below query is running for long time.
can you suggest some ideas to tune the query and run faster.

Code: [Select all] [Show/ hide]

whenever sqlerror exit -1
set ver off
set term off
set feed on
set head off
set pause off
set pages 0
set trimspool on
set echo on
set lines 500
set serveroutput on
spool &1
DEFINE CTRY='SP';
DEFINE OWNER='&&CTRY._DICT1';      -- NOTE THERE IS NO PREFIX FOR US
DEFINE CTRYCODE=223939;
DEFINE LANGCODE=4;
INSERT INTO &&OWNER..&&CTRY._DICT_LOG_TBL(LOCAL_TBL_NAME, COUNT_DESC, COUNT_RECS)
  (SELECT '&&CTRY._COMP_TBL', 'REC COUNT BEFORE UPDATE', COUNT(*) FROM &&OWNER..&&CTRY._COMP_TBL)
/
/* DELETE ANY OLD COMPANIES WHERE THE ID NO LONGER EXISTS IN THE INDIA */
DELETE FROM &&OWNER..&&CTRY._COMP_TBL
 WHERE COMP_ID NOT IN
      (SELECT C.COMP_ID
         FROM COMP@INDIA_DICTP C, COMP_SALE_LOCATION@INDIA_DICTP S
        WHERE C.APPROVAL_CODE = 'APPRD'
          AND S.COMP_ID=C.COMP_ID
          AND S.SALE_LOCATION_ID=&&CTRYCODE.)
/
/* NOW DELETE ANY ROWS WHERE THE NAME OR THE LAST_WEEK HAVE CHANGED */
DELETE FROM &&OWNER..&&CTRY._COMP_TBL
 WHERE COMP_ID IN
      (SELECT INDIA.COMP_ID
         FROM &&OWNER..&&CTRY._COMP_TBL &&CTRY.,
              (SELECT C.COMP_ID, C.COMP_NAME, C.FIRST_WEEK, C.LAST_WEEK
                 FROM COMP@INDIA_DICTP C, COMP_SALE_LOCATION@INDIA_DICTP S
                WHERE C.APPROVAL_CODE = 'APPRD'
                  AND S.COMP_ID=C.COMP_ID
                  AND S.SALE_LOCATION_ID=&&CTRYCODE.) INDIA
        WHERE INDIA.COMP_ID=&&CTRY..COMP_ID
          AND (&&CTRY..COMP_NAME!=INDIA.COMP_NAME OR &&CTRY..LAST_WEEK!=INDIA.LAST_WEEK))
/
INSERT INTO &&OWNER..&&CTRY._DICT_LOG_TBL(LOCAL_TBL_NAME, COUNT_DESC, COUNT_RECS)
  (SELECT '&&CTRY._COMP_TBL', 'REC COUNT AFTER DELETE', COUNT(*) FROM &&OWNER..&&CTRY._COMP_TBL)
/
/* (RE-)INSERT ANY NEW OR CHANGED COMP_IDS TO THE &&CTRY. TABLE */
INSERT INTO &&OWNER..&&CTRY._COMP_TBL(COMP_ID, COMP_NAME, FIRST_WEEK, LAST_WEEK)
  (SELECT C.COMP_ID, C.COMP_NAME, C.FIRST_WEEK, C.LAST_WEEK
     FROM COMP@INDIA_DICTP C, COMP_SALE_LOCATION@INDIA_DICTP S
    WHERE C.APPROVAL_CODE = 'APPRD'
      AND S.COMP_ID=C.COMP_ID
      AND S.SALE_LOCATION_ID=&&CTRYCODE.
      AND NOT EXISTS (SELECT COMP_ID FROM &&OWNER..&&CTRY._COMP_TBL WHERE COMP_ID=C.COMP_ID))
/
INSERT INTO &&OWNER..&&CTRY._DICT_LOG_TBL(LOCAL_TBL_NAME, COUNT_DESC, COUNT_RECS)
  (SELECT '&&CTRY._COMP_TBL', 'REC COUNT AFTER INSERT', COUNT(*) FROM &&OWNER..&&CTRY._COMP_TBL)
/
commit;
select '&&OWNER..&&CTRY._COMP_TBL sync completed on '||REPLACE(TO_CHAR(sysdate,'DD-MON-YYYY HH24:MI:SS'),' ',' at ') from dual
/
spool off
exit

Report message to a moderator

query tunning [message #685969 is a reply to message #685968]

Wed, 11 May 2022 06:53

arun888
Messages: 100
Registered: June 2015
Location: INDIA

Senior Member

Currently, i am currently the table and re-loading the data since the below query is running for long time.
can you suggest some ideas to tune the query and run faster.

Code: [Select all] [Show/ hide]

whenever sqlerror exit -1
set ver off
set term off
set feed on
set head off
set pause off
set pages 0
set trimspool on
set echo on
set lines 500
set serveroutput on
spool &1
DEFINE CTRY='SP';
DEFINE OWNER='&&CTRY._DICT1';      -- NOTE THERE IS NO PREFIX FOR US
DEFINE CTRYCODE=223939;
DEFINE LANGCODE=4;
INSERT INTO &&OWNER..&&CTRY._DICT_LOG_TBL(LOCAL_TBL_NAME, COUNT_DESC, COUNT_RECS)
  (SELECT '&&CTRY._COMP_TBL', 'REC COUNT BEFORE UPDATE', COUNT(*) FROM &&OWNER..&&CTRY._COMP_TBL)
/
/* DELETE ANY OLD COMPANIES WHERE THE ID NO LONGER EXISTS IN THE INDIA */
DELETE FROM &&OWNER..&&CTRY._COMP_TBL
 WHERE COMP_ID NOT IN
      (SELECT C.COMP_ID
         FROM COMP@INDIA_DICTP C, COMP_SALE_LOCATION@INDIA_DICTP S
        WHERE C.APPROVAL_CODE = 'APPRD'
          AND S.COMP_ID=C.COMP_ID
          AND S.SALE_LOCATION_ID=&&CTRYCODE.)
/
/* NOW DELETE ANY ROWS WHERE THE NAME OR THE LAST_WEEK HAVE CHANGED */
DELETE FROM &&OWNER..&&CTRY._COMP_TBL
 WHERE COMP_ID IN
      (SELECT INDIA.COMP_ID
         FROM &&OWNER..&&CTRY._COMP_TBL &&CTRY.,
              (SELECT C.COMP_ID, C.COMP_NAME, C.FIRST_WEEK, C.LAST_WEEK
                 FROM COMP@INDIA_DICTP C, COMP_SALE_LOCATION@INDIA_DICTP S
                WHERE C.APPROVAL_CODE = 'APPRD'
                  AND S.COMP_ID=C.COMP_ID
                  AND S.SALE_LOCATION_ID=&&CTRYCODE.) INDIA
        WHERE INDIA.COMP_ID=&&CTRY..COMP_ID
          AND (&&CTRY..COMP_NAME!=INDIA.COMP_NAME OR &&CTRY..LAST_WEEK!=INDIA.LAST_WEEK))
/
INSERT INTO &&OWNER..&&CTRY._DICT_LOG_TBL(LOCAL_TBL_NAME, COUNT_DESC, COUNT_RECS)
  (SELECT '&&CTRY._COMP_TBL', 'REC COUNT AFTER DELETE', COUNT(*) FROM &&OWNER..&&CTRY._COMP_TBL)
/
/* (RE-)INSERT ANY NEW OR CHANGED COMP_IDS TO THE &&CTRY. TABLE */
INSERT INTO &&OWNER..&&CTRY._COMP_TBL(COMP_ID, COMP_NAME, FIRST_WEEK, LAST_WEEK)
  (SELECT C.COMP_ID, C.COMP_NAME, C.FIRST_WEEK, C.LAST_WEEK
     FROM COMP@INDIA_DICTP C, COMP_SALE_LOCATION@INDIA_DICTP S
    WHERE C.APPROVAL_CODE = 'APPRD'
      AND S.COMP_ID=C.COMP_ID
      AND S.SALE_LOCATION_ID=&&CTRYCODE.
      AND NOT EXISTS (SELECT COMP_ID FROM &&OWNER..&&CTRY._COMP_TBL WHERE COMP_ID=C.COMP_ID))
/
INSERT INTO &&OWNER..&&CTRY._DICT_LOG_TBL(LOCAL_TBL_NAME, COUNT_DESC, COUNT_RECS)
  (SELECT '&&CTRY._COMP_TBL', 'REC COUNT AFTER INSERT', COUNT(*) FROM &&OWNER..&&CTRY._COMP_TBL)
/
commit;
select '&&OWNER..&&CTRY._COMP_TBL sync completed on '||REPLACE(TO_CHAR(sysdate,'DD-MON-YYYY HH24:MI:SS'),' ',' at ') from dual
/
spool off
exit

Report message to a moderator

Re: query tunning [message #685970 is a reply to message #685969]

Wed, 11 May 2022 07:04

John Watson
Messages: 8977
Registered: January 2010
Location: Global Village

Senior Member

You have several statements in that script. How long does each take? How long would you like them to take?

Report message to a moderator

Re: query tunning [message #685971 is a reply to message #685970]

Wed, 11 May 2022 07:44

arun888
Messages: 100
Registered: June 2015
Location: INDIA

Senior Member

I have 19000 records in the table. Sometime, it is getting stuck up and truncating the tables and inserting the records. Would like to know any efficent way without truncating the tables.

Report message to a moderator

Re: query tunning [message #685972 is a reply to message #685970]

Wed, 11 May 2022 08:55

arun888
Messages: 100
Registered: June 2015
Location: INDIA

Senior Member

any efficient way to insert the records without long running.

Report message to a moderator

Re: query tunning [message #685977 is a reply to message #685972]

Thu, 12 May 2022 12:32

John Watson
Messages: 8977
Registered: January 2010
Location: Global Village

Senior Member

arun888 wrote on Wed, 11 May 2022 14:55

any efficient way to insert the records without long running.

Almost certainly. But you have to begin by providing some information. How long does each statement take? How long would you like it to take? Which statement is the worst problem? Having identified the problem, you will have to find out the execution plan and the runtime statistics. If you don't know how to do that, people here can help. Only then can you look at how it can be improved.

Report message to a moderator

Re: query tunning [message #685987 is a reply to message #685977]

Fri, 20 May 2022 01:13

arun888
Messages: 100
Registered: June 2015
Location: INDIA

Senior Member

The below query takes 25 minutes to insert the records eventhough if it is a small records.
can you guide me how to take the statistics and the execution plan.

Code: [Select all] [Show/ hide]

/* (RE-)INSERT ANY NEW OR CHANGED COMP_IDS TO THE &&CTRY. TABLE */
INSERT INTO &&OWNER..&&CTRY._COMP_TBL(COMP_ID, COMP_NAME, FIRST_WEEK, LAST_WEEK)
  (SELECT C.COMP_ID, C.COMP_NAME, C.FIRST_WEEK, C.LAST_WEEK
     FROM COMP@INDIA_DICTP C, COMP_SALE_LOCATION@INDIA_DICTP S
    WHERE C.APPROVAL_CODE = 'APPRD'
      AND S.COMP_ID=C.COMP_ID
      AND S.SALE_LOCATION_ID=&&CTRYCODE.
      AND NOT EXISTS (SELECT COMP_ID FROM &&OWNER..&&CTRY._COMP_TBL WHERE COMP_ID=C.COMP_ID))

Report message to a moderator

Re: query tunning [message #685988 is a reply to message #685987]

Fri, 20 May 2022 01:22

John Watson
Messages: 8977
Registered: January 2010
Location: Global Village

Senior Member

If you use the SQL*Plus autotrace facility, all will be revealed. For example:

Code: [Select all] [Show/ hide]

orclz>
orclz> set autotrace on
orclz>
orclz> insert into emp(empno) select max(empno)+1 from emp;

1 row created.


Execution Plan
----------------------------------------------------------
Plan hash value: 1707959928

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT            |        |     1 |     4 |     2   (0)| 00:00:01 |
|   1 |  LOAD TABLE CONVENTIONAL    | EMP    |       |       |            |          |
|   2 |   SORT AGGREGATE            |        |     1 |     4 |            |          |
|   3 |    INDEX FULL SCAN (MIN/MAX)| PK_EMP |     1 |     4 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
         87  recursive calls
          7  db block gets
        167  consistent gets
          6  physical reads
          0  redo size
        195  bytes sent via SQL*Net to client
        402  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
         12  sorts (memory)
          0  sorts (disk)
          1  rows processed

orclz>

(by the way, I wish you would not say "record" when you mean "row")

Report message to a moderator

Re: query tunning [message #685989 is a reply to message #685988]

Fri, 20 May 2022 01:35

arun888
Messages: 100
Registered: June 2015
Location: INDIA

Senior Member

only on the SQL*Plus is the facility is available. ?

Report message to a moderator

Re: query tunning [message #685990 is a reply to message #685989]

Fri, 20 May 2022 15:06

John Watson
Messages: 8977
Registered: January 2010
Location: Global Village

Senior Member

arun888 wrote on Fri, 20 May 2022 07:35

only on the SQL*Plus is the facility is available. ?

I don't know. It does help if you know what your chosen client tool can do. You must have a reason for choosing whatever tool it is that you use?

Report message to a moderator

Re: query tunning [message #685998 is a reply to message #685988]

Mon, 23 May 2022 12:48

arun888
Messages: 100
Registered: June 2015
Location: INDIA

Senior Member

Please find the execution plan for the query.

Code: [Select all] [Show/ hide]

SELECT C.COMP_ID, C.COMP_NAME, C.FIRST_WEEK, C.LAST_WEEK
     FROM COMP_TEMP1 C, COMP_SALE_LOCATION_TEMP1 S
    WHERE C.APPROVAL_CODE = 'APPRD'
      AND S.COMP_ID=C.COMP_ID
      AND S.SALE_LOCATION_ID=118444
      AND NOT EXISTS (SELECT COMP_ID FROM COMP_TBL WHERE COMP_ID=C.COMP_ID)

OMPANY_ID COMP_NAME                             FIRST_WEEK  LAST_WEEK
---------- ---------------------------------------- ---------- ----------
    904211 ICKLE BUBBA LIMITED                            2205       2228 
    904220 AUROBINDO PHARMA                               2208       2228 
    904214 EIGHTY ONE                                     2158       2228 
    904215 XITE ENERGY                                    2125       2228 
    904242 IGWT POULTRY SERVICE                           2172       2228 

Plan hash value: 350345163
 
---------------------------------------------------------------------------------------------------
| Id  | Operation           | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                             | 11992 |  1147K|  1169   (1)| 00:00:15 |
|   1 |  NESTED LOOPS ANTI  |                             | 11992 |  1147K|  1169   (1)| 00:00:15 |
|*  2 |   HASH JOIN         |                             | 12012 |  1090K|  1168   (1)| 00:00:15 |
|*  3 |    TABLE ACCESS FULL| COMP_SALE_LOCATION_TEMP1    | 12012 |   304K|   419   (1)| 00:00:06 |
|*  4 |    TABLE ACCESS FULL| COMP_TEMP1                  |   232K|    14M|   749   (1)| 00:00:09 |
|*  5 |   INDEX UNIQUE SCAN | PK_UKCOMPTBL_CMPID          |    31 |   155 |     0   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("S"."COMP_ID"="C"."COMP_ID")
   3 - filter("S"."SALE_LOCATION_ID"=118444)
   4 - filter("C"."APPROVAL_CODE"='APPRD')
   5 - access("COMP_ID"="C"."COMP_ID")

Report message to a moderator

Re: query tunning [message #686005 is a reply to message #685998]

Tue, 24 May 2022 06:26

John Watson
Messages: 8977
Registered: January 2010
Location: Global Village

Senior Member

Your query is returning 5 rows, but the optimizer is expecting 11992 rows. So possibly your object statistics are rubbish. Better gather table stats, including histograms, and see if it comes up with a better plan.

You could also run a few checks to see where it is going wrong:
How many rows are there in COMP_SALE_LOCATION_TEMP1 ? How many where "SALE_LOCATION_ID"=118444 ?
Same thing for COMP_TEMP1 and its filter.
How many when you join them on comp_id ?
And so on: break down the query, and check the figures at each stage.

Report message to a moderator

Previous Topic:	excessive parsing ?
Next Topic:	Percentage of CPU utilization per instance

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat May 31 04:12:47 CDT 2025