Home » RDBMS Server » Performance Tuning » query tunning (oracle10g)
query tunning [message #685968] Wed, 11 May 2022 06:53 Go to next message
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.


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 Go to previous messageGo to next message
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.


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 Go to previous messageGo to next message
John Watson
Messages: 8963
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
John Watson
Messages: 8963
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 Go to previous messageGo to next message
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.

/* (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 Go to previous messageGo to next message
John Watson
Messages: 8963
Registered: January 2010
Location: Global Village
Senior Member
If you use the SQL*Plus autotrace facility, all will be revealed. For example:
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
John Watson
Messages: 8963
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 Go to previous messageGo to next message
arun888
Messages: 100
Registered: June 2015
Location: INDIA
Senior Member
Please find the execution plan for the query.
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 Go to previous message
John Watson
Messages: 8963
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:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Jan 23 11:31:16 CST 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.