Kryzysowy( kredytowy ) SQL - symulacja spłaty kredytu

Post został  inspirowany  nocnymi dyskusjami na temat sensowności korzystania z kredytów hipotecznych. W pierwszym podejściu miał być to pakiet, ale postanowiłem ułatwić sobie życie i napisałem zapytanie wyświetlające plan spłaty kredytu  dla rat stałych i malejących.
Zapytanie można w łatwy sposób parametryzować (parametry mają kolor ciemnozielony) zmieniając:

• kwotę kredytu
• czas spłaty
• oprocentowanie w skali rocznej

Przyjęte zostały następujące założenia:

1. Oprocentowanie  podczas spłaty kredytu jest stałe
2. Raty są płatne z góry od  bieżącego dnia co miesiąc 
3. Zakładamy, że oprocentowanie w obrębie każdego miesiąca jest jednakowe

Poniższe zapytanie przy odrobinie  fantazji, można w ciekawy sposób rozwinąć, np zakładając  rózne oprocentowanie w kolejnych latach.

  SELECT d + 1 AS "Numer raty",
          DATA_RATY AS "Data raty",        
          ROUND (RATA_MAL_KAPITAL, 2) AS "Kapitał raty malejącej",
          ROUND (RATA_MAL_ODSETKI, 2) AS "Kwota odsetek",
          ROUND (RATA_MAL_KAPITAL + RATA_MAL_ODSETKI, 2) AS "Kwota raty  malejącej",
          ROUND (KWOTA_MAL_DO_SPLATY, 2) AS "Zadłużenie - raty malejące",
          ROUND (RATA_STALA_KAPITAL, 2) AS "Kapitał raty stałej",
          ROUND (RATA_STALA_ODSETKI, 2) AS "Odsetki raty stałej",
          ROUND (RATA_STALA, 2) AS "Kwota raty stałej",
          ROUND (KWOTA_STALA_DO_SPLATY, 2) AS "Zadłużenie - raty stałe"
     FROM (SELECT 1 FROM DUAL)
   MODEL
      DIMENSION BY (0 d)
      MEASURES (300000 KWOTA_KREDYTU, 6 OPROCENTOWANIE, 240 LICZBA_RAT,
      TRUNC (SYSDATE) DATA_RATY,
             0 KWOTA_MAL_DO_SPLATY,
             0 RATA_MAL_KAPITAL,
             0 RATA_MAL_ODSETKI,            
             0 RATA_STALA,
             0 RATA_STALA_KAPITAL,
             0 RATA_STALA_ODSETKI,
             0 KWOTA_STALA_DO_SPLATY
             )
      RULES
         ITERATE (10000) UNTIL (ITERATION_NUMBER = LICZBA_RAT[0] -1)
         (              
         LICZBA_RAT [ITERATION_NUMBER] =  
               NVL (LICZBA_RAT[ITERATION_NUMBER - 1], LICZBA_RAT[0]),
          OPROCENTOWANIE [ITERATION_NUMBER] =
               NVL (OPROCENTOWANIE[ITERATION_NUMBER - 1], OPROCENTOWANIE[0]/1200),
         RATA_MAL_KAPITAL [ITERATION_NUMBER] =
               KWOTA_KREDYTU[0] / LICZBA_RAT[0],
         DATA_RATY [ITERATION_NUMBER] =
               ADD_MONTHS (DATA_RATY[0], ITERATION_NUMBER),
         KWOTA_MAL_DO_SPLATY [ITERATION_NUMBER] =
                 NVL (KWOTA_MAL_DO_SPLATY[ITERATION_NUMBER - 1],
                      KWOTA_KREDYTU[0])
               - NVL (RATA_MAL_KAPITAL[ITERATION_NUMBER - 1], 0),
         RATA_MAL_ODSETKI [ITERATION_NUMBER] =
                 KWOTA_MAL_DO_SPLATY[ITERATION_NUMBER]
               * OPROCENTOWANIE[ITERATION_NUMBER]  ,                                       
         RATA_STALA_KAPITAL [ITERATION_NUMBER]              
         = KWOTA_KREDYTU[0] *OPROCENTOWANIE [ITERATION_NUMBER] *POWER( 1+OPROCENTOWANIE [ITERATION_NUMBER], ITERATION_NUMBER)/
          ( POWER( 1+OPROCENTOWANIE [ITERATION_NUMBER],  LICZBA_RAT[ITERATION_NUMBER]) -1  ),
           KWOTA_STALA_DO_SPLATY [ITERATION_NUMBER] =
                 NVL (KWOTA_STALA_DO_SPLATY[ITERATION_NUMBER - 1],
                      KWOTA_KREDYTU[0])
               - NVL (RATA_STALA_KAPITAL[ITERATION_NUMBER - 1], 0),
           RATA_STALA_ODSETKI [ITERATION_NUMBER] =
                 KWOTA_STALA_DO_SPLATY[ITERATION_NUMBER]
               * OPROCENTOWANIE[ITERATION_NUMBER],
           RATA_STALA[ITERATION_NUMBER] = RATA_STALA_KAPITAL[ITERATION_NUMBER] + RATA_STALA_ODSETKI[ITERATION_NUMBER]       
          )

Usuwanie indeksów z wysokim clustering factor

Opisany problem dotyczy bardzo niskiej wydajności operacji DML na  tabelach o trochę większym wolumenie (zetknąłem się problemem, kiedy wielkość tabel przekraczała około 300 mln rekordów), często partycjonowanych, zawierających klasyczne drzewiaste indeksy o  bardzo dużej selektywności. Wówczas najszybszą (w moi odczuciu :) ) metodą dokonania operacji DML na większym wolumenie danych jest:

1. Usunięcie indeksów o wysokim clustering factor
2. Wykonanie operacji DML
3. Ponowne stworzenie usuniętych indeksów 

W poniższym rozwiązaniu wykorzystałem własny pakiet logujący prosty logger wzorowany na log4j.
Do usuwania indeksów w innych schematach niż bieżący przydałyby się uprawnienia CREATE ANY INDEX i DROP ANY INDEX


CREATE TABLE TBL_INDEXES
(
  ID             NUMBER(10),
  SCHEMA_NAME     VARCHAR2(30 CHAR)              NOT NULL,
  TABLE_NAME     VARCHAR2(30 CHAR)              NOT NULL,
  INDEX_NAME     VARCHAR2(30 CHAR)              NOT NULL,
  DDL_STATEMENT  CLOB
);


ALTER TABLE TBL_INDEXES ADD (
  CONSTRAINT PK_INDEXES_COMPLEX
  PRIMARY KEY (ID, SCHEMA_NAME, TABLE_NAME, INDEX_NAME);

CREATE SEQUENCE SEQ_INDEXES START  WITH 1 NOCACHE NOCYCLE;

CREATE OR REPLACE PACKAGE PCKG_INDEXES
AS
FUNCTION Fdb_CreateIndexes(pc_SchemaName VARCHAR2, pc_TableName VARCHAR2)   RETURN BOOLEAN;
FUNCTION Fdb_DropIndexes(pc_SchemaName VARCHAR2, pc_TableName VARCHAR2)   RETURN BOOLEAN;
END PCKG_INDEXES;

CREATE OR REPLACE PACKAGE BODY PCKG_INDEXES
AS
FUNCTION Fdb_CreateIndexes(pc_SchemaName VARCHAR2, pc_TableName VARCHAR2)   RETURN BOOLEAN
AS
vn_LiczbaBledow NUMBER(10) :=0;  
CURSOR C IS
    SELECT            
          INDEX_NAME,
          DDL_STATEMENT
        FROM TBL_INDEXES
        WHERE ID =
        (
            SELECT MAX(ID) FROM TBL_INDEXES WHERE TABLE_NAME = pc_TableName AND SCHEMA_NAME = pc_SchemaName
        );

BEGIN
    COMMIT; -- aby zamknąc wszystkie locki w danym połączeniu
     FOR EL IN C
        LOOP
            BEGIN              
                EXECUTE IMMEDIATE  EL.DDL_STATEMENT;
                PCKG_LOGGING.PDB_INFO('PCKG_INDEXES.Fdb_CreateIndexes', 'Pomyślnie utworzono indeks: ' || EL.INDEX_NAME);
            EXCEPTION WHEN OTHERS THEN          
                PCKG_LOGGING.PDB_ERROR('PCKG_INDEXES.Fdb_CreateIndexes', 'Błąd podczas tworzenia indeksu: ' || EL.INDEX_NAME || ', ' || SQLERRM );              
                vn_LiczbaBledow := vn_LiczbaBledow +1;
            END;       
        END LOOP;  

    IF vn_LiczbaBledow > 0 THEN      
        RETURN FALSE;
    ELSE
        RETURN TRUE;
    END IF;      
END;
------------------------------------------------------------------------------------------
FUNCTION Fdb_DropIndexes(pc_SchemaName VARCHAR2, pc_TableName VARCHAR2)   RETURN BOOLEAN
AS
vn_SeqId NUMBER(10):= SEQ_INDEXES.nextval;
CURSOR C IS
    SELECT
            vn_SeqId AS ID,
            OWNER,
            TABLE_NAME,
            INDEX_NAME,
            DBMS_METADATA.GET_DDL( 'INDEX', INDEX_NAME, pc_SchemaName)  AS DDL_STATEMENT
        FROM ALL_INDEXES -- Tutaj działającą perspektywą zawsze będzieALL_INDEXES, dla ALL_INDEXES trzeba mieć specjalne uprawnienia
        WHERE TABLE_NAME = pc_TableName
            AND OWNER = pc_SchemaName
            AND 4*CLUSTERING_FACTOR > NUM_ROWS
            AND INDEX_TYPE = 'NORMAL';
BEGIN
     FOR EL IN C
        LOOP
            BEGIN
                INSERT INTO TBL_INDEXES VALUES EL;
                EXECUTE IMMEDIATE 'DROP INDEX ' || EL.INDEX_NAME;--- DDL czyli niejawny COMMIT = zatwierdzainstrukcję  insert
                PCKG_LOGGING.PDB_INFO('PCKG_INDEXES.Fdb_DropExtractIndexes', 'Pomyślnie usunięto indeks: ' || EL.INDEX_NAME);
            EXCEPTION WHEN OTHERS THEN          
                DELETE FROM TBL_INDEXES WHERE ID = vn_SeqId;
                PCKG_LOGGING.PDB_ERROR('PCKG_INDEXES.Fdb_DropExtractIndexes', 'Błąd podczas usuwania indeksu: ' || EL.INDEX_NAME || ', ' || SQLERRM );
                COMMIT;
                RETURN FALSE;
            END;       
        END LOOP;
    RETURN TRUE;
END;
END PCKG_INDEXES;

Unikalne nazwy dla obiektów

Poniższy trigger wynika z własnych przemyśleń na temat nazywania obiektów w bazie danych i mnóstwa problemów, jeśli w różnych schematach są obiekty o tej samej nazwie np.

• problemy ze scalaniem schematów, występujące w wyniku rotacji użytkowników baz danych
• redundancja danych i problemy związane z identyfikacją biznesowego znaczenia obiektów o takiej samej nazwie  
• kłopoty z utrzymaniem spójności kodu i odwołaniami do obiektów powtarzających się w wielu schematach
• nadmierne zużycie przestrzeni dyskowej

Poniżej przykłady  problematycznych instrukcji
CREATE TABLE DUAL AS
SELECT * FROM DUAL
        UNION
SELECT * FROM DUAL

RENAME myTable TO DUAL
 
Trigger TRG_CHECK_DUPLICATES jest przykładem rozwiązania omawianego problemu. Został napisany przy następujących założeniach:

• nie działa dla użytkowników SYS i SYSTEM
• działa przed operacjami CREATE i RENAME
• dla operacji RENAME dokonujemy prostej analizy polecenia DDL, dla polecenia CREATE nazwa obiektu pobierana jest z predefiniowanej zmiennej ORA_DICT_OBJ_NAME
• w komunikacie wyjątku podawane są nazwy już istniejących obiektów dla instrukcji DDL
• jest instalowany przez DBA w schemacie SYS

CREATE OR REPLACE TRIGGER SYS.TRG_CHECK_DUPLICATES
   BEFORE CREATE
   OR RENAME
   ON  DATABASE
DECLARE
    CURSOR C (pc_ObjectName VARCHAR2) IS
        SELECT OWNER|| '.' || OBJECT_NAME || ' typu '   || OBJECT_TYPE  AS MSG FROM DBA_OBJECTS
          WHERE OBJECT_NAME = pc_ObjectName
              AND OWNER <> ORA_DICT_OBJ_OWNER;
    vc_ErrorMsg VARCHAR2(500 CHAR);
    vc_SQLStatement VARCHAR2(200 CHAR);
    vc_ObjectName VARCHAR2(30 CHAR);
    vt_List DBMS_STANDARD.ORA_NAME_LIST_T := DBMS_STANDARD.ORA_NAME_LIST_T();
    vn_Size    PLS_INTEGER;
BEGIN
    IF USER IN ('SYS', 'SYSTEM') THEN
          RETURN;
    END IF;
    BEGIN
        IF ORA_SYSEVENT = 'RENAME' THEN
            vn_Size := ora_sql_txt (vt_List);
            FOR i IN 1 .. vn_Size
            LOOP
                vc_SQLStatement := vc_SQLStatement || vt_List (i);
            END LOOP;
           
            vc_ObjectName := REGEXP_SUBSTR(TRIM(vc_SQLStatement), '[[:alnum:]]+[[:cntrl:]]*$',1,1);
            vc_ObjectName := REGEXP_SUBSTR(TRIM(vc_ObjectName), '[[:alnum:]]+',1,1);
         ELSIF ORA_SYSEVENT = 'CREATE' THEN
            vc_ObjectName:= ORA_DICT_OBJ_NAME;
        END IF;
       
        FOR EL IN C(vc_ObjectName)
         LOOP
           IF vc_ErrorMsg IS NULL THEN
                vc_ErrorMsg := 'istnieje już ' || CHR(13) || CHR(10) || EL.MSG;
            ELSE
                vc_ErrorMsg := vc_ErrorMsg || CHR(13) || CHR(10) || EL.MSG ;
            END IF;
         END LOOP;
       
     EXCEPTION WHEN OTHERS THEN
        NULL;
     END;
     IF  vc_ErrorMsg IS NOT NULL THEN
        RAISE_APPLICATION_ERROR(-20101, 'Zdublowane nazwy obiektów: ' || vc_ErrorMsg );
     END IF;
END;
/

Kiedy serwer śpi

Istotnym problemem jest wykrywanie obciążenia na serwerach bazodanowych i szukanie dziur czasowych na uruchomienie dodatkowych funkcjonalności. Niektóre moje funkcjonalności  nie mają kluczowego znaczenia, natomiast nie powinny być uruchamianie, kiedy instrukcja top pokazuje pełne wykorzystanie zasobów serwera.. W tym celu napisałem sobie funkcję zwracającą wartość IDLE z  polecenia top.  funkcja działa poprawnie na serwerach jedno i wieloprocesorowych dla pojedyńczej instancji danej bazy, nie była testowana w konfiguracjach  wieloinstancyjnych z przełączaniem.. Rozbieżnośc między top a poniższą funkcją była mniejsza niż 8 %...

CREATE OR REPLACE FUNCTION  Fdb_GetIdlePercent(pn_Seconds NUMBER DEFAULT 5) RETURN NUMBER

AS

 vn_IdleTime    NUMBER;
 vn_CPUCount NUMBER;BEGIN
    SELECT VALUE INTO vn_IdleTime FROM V$OSSTAT WHERE STAT_NAME = 'IDLE_TIME';
    SELECT VALUE INTO vn_CPUCount FROM V$OSSTAT WHERE STAT_NAME = 'NUM_CPUS';
    SYS.DBMS_LOCK.SLEEP( pn_Seconds);
  SELECT VALUE - vn_IdleTime    INTO vn_IdleTime FROM V$OSSTAT WHERE STAT_NAME = 'IDLE_TIME';  
    RETURN ROUND(vn_IdleTime/(vn_CPUCount *pn_Seconds*100)  *100, 2 );   
    EXCEPTION WHEN OTHERS THEN
           RETURN 100;
END;

Funkcja wymaga następujących niestandardowych uprawnień:
GRANT EXECUTE ON   SYS.DBMS_LOCK TO :current_user;
GRANT SELECT ON SYS.V$OSSTAT TO :current_user;

Istotnym parametrem jest czas pomiaru - 5 sekund lub dłużej powinno wystarczyć..

 Poniższy  blok czeka, aż parametr idle spadnie do 50% na podstawie  sprawdzania przedziałów czasowych o długości 300 sekund.

DECLARE

      vn_Idle NUMBER;

      vn_Delay NUMBER :=  300;

BEGIN

    LOOP
         vn_Idle := Fdb_GetIdlePercent(vn_Delay);
         EXIT WHEN vn_Idle <= 50;
    END LOOP; 

--- Insert your code here 

END;