Why does a stored procedure take a long time to run?

Question

Oracle

Why does a stored procedure take a long time to run?
If you run the procedure in Oracle itself, then the procedure will be executed in a matter of seconds, but through python it will hang for 20-40 minutes.
From python, I call the Oracle stored procedure:

db = cx_Oracle.connect("C##aks1", "1234567890", "192.168.1.209:1522/ORCL12")
dbcur = db.cursor()
query_1 = "CALL  EXP_PROC(:period, :gp)"
dbcur.execute(query_1, (month + year, gp))

procedure in oracle:

spoiler

CREATE OR REPLACE PROCEDURE EXP_PROC (period VARCHAR2, s_gp VARCHAR2)
  IS

   query1  VARCHAR2(1700);

   TYPE uslage IS REF CURSOR;
   cur_strong uslage;
       
    idsluch ut.id_sluch % TYPE;
    idpacs  ut.id_pac % TYPE;
    mcod    ut.lpu_1 % TYPE;
    summv   ut.SUMV_USL % TYPE;
    idserv  ut.idserv % TYPE;

BEGIN  

      query1 := 'SELECT distinct(u.id_sluch) as id_sluch, u.id_pac, u.lpu_1, 
              SUM(u.SUMV_USL) AS summv, MAX(u.idserv) AS idserv 
              FROM XML_USL PARTITION (u'||period||') u 
              INNER JOIN (SELECT ID_PAC, GLPU, DR 
                             FROM XML_PACIENT PARTITION (p'||period||') 
                             WHERE GLPU = ' || '''' || s_gp || ''' AND novor = ''' || 0 || ''') ps
              ON u.id_pac = ps.id_pac AND u.LPU = ps.glpu 
              WHERE
                  SUBSTR(TRIM(u.CODE_USL), 1, 1) <> ''' || 5 || ''' AND                    
                  ((SUBSTR(TRIM(u.CODE_USL), -1, 1) = ''' || 1 || ''' AND
                  months_between(u.DATE_IN, ps.DR) / 12 < 17.4)
                  OR 
                  (SUBSTR(TRIM(u.CODE_USL), -1, 1) = ''' || 2 || ''' AND
                  months_between(u.DATE_IN, ps.DR) / 12 > 18.3)) AND
                  NOT EXISTS (SELECT s.ID_PAC, s.GLPU 
                                   FROM SANKC s 
                                   WHERE s.glpu = ' || '''' || s_gp || ''' AND s.period = ' || '''' || period || ''' AND 
                                      ps.id_pac = s.ID_PAC AND ps.GLPU = s.GLPU)
              GROUP BY u.id_sluch, u.id_pac, u.lpu_1';
    
     OPEN cur_strong FOR query1;
     LOOP
        FETCH cur_strong INTO idsluch, idpacs, mcod, summv, idserv;
        EXIT WHEN cur_strong%NOTFOUND;
  
        INSERT INTO SANKC (glpu, idpvd, flag, ID_PAC, id_sluch, idserv, DATE_EXP, period, comments, summ)
          VALUES (s_gp, '514', 4, idpacs, idsluch, idserv, SYSDATE, period_date, 'Неправильный возраст', summv);
     END LOOP;
     CLOSE cur_strong;
     COMMIT;   

END;
/

Answer 1

Create a simple procedure that does simple logic, like write logs, then try to call it from a Python script. If everything is OK, then add some of the logic you need to the procedure and try again. If it's bad, then the problem is not in the procedure and not in Oracle. You will need to dig in the other direction.
If you find that everything is the same and the problem is in Oracle, then set up logging, look at time metrics, look at the query plan that Oracle builds when calling the procedure from Python and the query plan that is built when called directly from the Oracle client.