6f429244e498457b8400e72e4af683b0 Alberto Blog

venerdì 17 gennaio 2014

Oracle - Windows Sort Pushed Rank access

Dopo una attenta lettura di un'ottimo post nel blog di Cristian, mi sono incuriosito per uno strano comportamento di un explain plan di un test, per la funzione, WINDOW SORT PUSHED RANK in Oracle 12c.
Seguendo un articolo di Tom Kyte dove veniva illustrato la funzione fetch first X rows (only top n query) e cercando di riprodurre lo stesso comportamento nella nostri Oracle 12c ci siamo imbattuti in qualche stranezza.


create table t2 as select * from all_objects;

create index idx_t2 on t2(owner,object_name);

SET AUTOTRACE ON
select  owner,object_name,object_id from t2
order by owner,object_name fetch first 5 rows only;

Autotrace Enabled
Shows the execution plan as well as statistics of the statement.


OWNER           OBJECT_NAME                     OBJECT_ID
APEX_040200 APEX                         88901
APEX_040200 APEX$ARCHIVE_CONTENTS         89728
APEX_040200 APEX$ARCHIVE_CONTENTS_IDX1 89732
APEX_040200 APEX$ARCHIVE_HEADER         89726
APEX_040200 APEX$ARCHIVE_HISTORY         89733

Plan hash value: 3975347511
 
-----------------------------------------------------------------------------------------
| Id  | Operation                | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |      | 86477 |    23M|       |  3191   (1)| 00:00:01 |
|*  1 |  VIEW                    |      | 86477 |    23M|       |  3191   (1)| 00:00:01 |
|*  2 |   WINDOW SORT PUSHED RANK|      | 86477 |    11M|    13M|  3191   (1)| 00:00:01 |
|   3 |    TABLE ACCESS FULL     | T2   | 86477 |    11M|       |   412   (1)| 00:00:01 |
-----------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=5)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY "OWNER","OBJECT_NAME")<=5)
 
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

   Statistics
-----------------------------------------------------------
               2  CCursor + sql area evicted
              83  CPU used by this session
              83  CPU used when call started
              87  DB time
              25  Number of read IOs issued
              88  Requests to/from client
              88  SQL*Net roundtrips to/from client
              63  buffer is not pinned count
            1104  bytes received via SQL*Net from client
           35629  bytes sent via SQL*Net to client
               8  calls to get snapshot scn: kcmgss
              13  calls to kcmgcs
        15081472  cell physical IO interconnect bytes
            1585  consistent gets
            1510  consistent gets direct
               2  consistent gets examination
               2  consistent gets examination (fastpath)
              75  consistent gets from cache
              73  consistent gets pin
              22  consistent gets pin (fastpath)
               2  enqueue releases
               2  enqueue requests
               6  execute count
            1074  file io wait time
             331  free buffer requested
               2  index scans kdiixs1
          614400  logical read bytes from cache
            1573  no work - consistent read gets
             164  non-idle wait count
               1  non-idle wait time
               6  opened cursors cumulative
               2  opened cursors current
               2  parse count (hard)
               4  parse count (total)
               1  parse time cpu
               2  parse time elapsed
              76  physical read IO requests
        15081472  physical read bytes
              76  physical read total IO requests
        15081472  physical read total bytes
              25  physical read total multi block requests
            1841  physical reads
             331  physical reads cache
             280  physical reads cache prefetch
            1510  physical reads direct
               7  recursive calls
               1  recursive cpu usage
              -1  session cursor cache count
               3  session cursor cache hits
            1585  session logical reads
              51  shared hash latch upgrades - no wait
               3  sorts (memory)
           90762  sorts (rows)
            1571  table scan blocks gotten
           93514  table scan rows gotten
               1  table scans (direct read)
               2  table scans (short tables)
              89  user calls
               5  workarea executions - optimal
Ci siamo chiesti come mai nell'esempio proposto venisse utilizzato l'indice appositamente creato mentre nel nostro no. Nemmeno aggiornando le statistiche la situazione non cambiava. Sono andato a verificare in v$parameter il parametro dell'optimizer e l'ho trovato ALL_ROWS la cosa mi ha subito insospettito quindi ho provato con
alter session set OPTIMIZER_MODE = FIRST_ROWS

SET AUTOTRACE ON
select  owner,object_name,object_id from t2
order by owner,object_name fetch first 5 rows only; 


Autotrace Enabled
Shows the execution plan as well as statistics of the statement.

OWNER           OBJECT_NAME                     OBJECT_ID
APEX_040200 APEX                         88901
APEX_040200 APEX$ARCHIVE_CONTENTS         89728
APEX_040200 APEX$ARCHIVE_CONTENTS_IDX1 89732
APEX_040200 APEX$ARCHIVE_HEADER         89726
APEX_040200 APEX$ARCHIVE_HISTORY         89733

Plan hash value: 2588503356
 
----------------------------------------------------------------------------------------
| Id  | Operation                     | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |        | 89906 |    24M| 69551   (1)| 00:00:03 |
|*  1 |  VIEW                         |        | 89906 |    24M| 69551   (1)| 00:00:03 |
|*  2 |   WINDOW NOSORT STOPKEY       |        | 89906 |  3160K| 69551   (1)| 00:00:03 |
|   3 |    TABLE ACCESS BY INDEX ROWID| T2     | 89906 |  3160K| 69551   (1)| 00:00:03 |
|   4 |     INDEX FULL SCAN           | IDX_T2 | 89906 |       |   524   (1)| 00:00:01 |
----------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=5)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY "OWNER","OBJECT_NAME")<=5)

   Statistics
-----------------------------------------------------------
              19  Requests to/from client
              19  SQL*Net roundtrips to/from client
               4  buffer is not pinned count
               9  buffer is pinned count
             463  bytes received via SQL*Net from client
           32492  bytes sent via SQL*Net to client
               2  calls to get snapshot scn: kcmgss
               2  calls to kcmgcs
               5  consistent gets
               2  consistent gets examination
               2  consistent gets examination (fastpath)
               5  consistent gets from cache
               3  consistent gets pin
               3  consistent gets pin (fastpath)
               1  cursor authentications
               2  execute count
               1  index scans kdiixs1
           40960  logical read bytes from cache
               3  no work - consistent read gets
              19  non-idle wait count
               2  opened cursors cumulative
               1  opened cursors current
               2  parse count (total)
               5  session logical reads
               1  sorts (memory)
             855  sorts (rows)
               6  table fetch by rowid
              20  user calls
               3  workarea executions - optimal

Adesso l'indice viene utilizzato correttamente e il piano di esecuzione è migliorato sensibilmente. Presumo che nell'esempio proposto da Tom Kyte questo parametro fosse impostato con appunto FIRST_ROWS.

martedì 10 dicembre 2013

Manage servers remotely with Glances - Python

Una buona idea, è quella di gestire e controllare le risorse di servers magari remoti, senza collegarsi con Remote Desktop se si tratta di Windows oppure entrare con ssh per Linux.
Una soluzione multi piattaforma è sicuramente Glances.
Questo software scritto in python, è molto versatile ed è ottimo per visualizzare tutti i processi, CPU, Hard Disk, Network ecc.
Inoltre è interamente custom, oltre che nel codice anche nella visualizzazione degli alert che vengono proposti per default.
Questo è il sito del progetto Glances, da provare assolutamente...


mercoledì 30 ottobre 2013

OraSash v2.4 RC2

Un breve post per comunicare che la nuova release 2.4 di  OraSash RC2 è disponibile.
Tra le nuove features testate e sviluppate, c'è la piena compatibilità ad Oracle 12c ed altro ancora, vi rimando alla pagina ufficiale del progetto per maggiori informazioni.

https://github.com/pioro/orasash/tree/v2.4-rc2



mercoledì 9 ottobre 2013

Java7 Update40 - Sql Developer - Ubuntu

Ieri sera ho aggiornato la mia versione Java installando l'ultimo pacchetto di update nel mio Ubuntu 12.04, risultato Sql Developer non funzionava più correttamente.
Avevo gli stessi problemi di quando lo usavo con li librerie OpenJava, ossia funzionava ma mi dava dei problemi su alcune funzioni come Debugger e altro.
Per risolvere questo avevo installato Java7 update 25 e tutto funzionava correttamente, fino a ieri sera....
Il comportamento di Java7 errato ( Update40) consiste in questo per esempio, se vado per eseguire una procedura mi capita questo:

alberto@AbeInsp:~$ java -version

java version "1.7.0_40"

Java(TM) SE Runtime Environment (build 1.7.0_40-b43)

Java HotSpot(TM) 64-Bit Server VM (build 24.0-b56, mixed mode)




ossia la non mi compare la schermata successiva...
Mentre con la corretta versione mi compariva:


Come faccio per risolvere questo inghippo?
Nel mio pc ho le seguenti installazioni di java in /usr/lib/jvm :

drwxr-xr-x   8 root root  4096 giu 30 11:35 java-6-oracle

drwxr-xr-x   8 root root  4096 ott  8 21:48 java-7-oracle


Quella java7 è quella con il problema ma ho installato anche la versione 6 per fortuna.
Allora si fa tutto più semplice perchè con i seguenti comandi:

sudo update-alternatives --install "/usr/bin/javac" "javac" "/usr/lib/jvm/java-6-oracle/bin/javac" 1

sudo update-alternatives --install "/usr/bin/java" "java" "/usr/lib/jvm/java-6-oracle/bin/java" 1


imposto dove l'installazione si trova mentre con questo:

sudo update-alternatives --set "javac" "/usr/lib/jvm/java-6-oracle/bin/javac"
sudo update-alternatives --set "java" "/usr/lib/jvm/java-6-oracle/bin/java"

imposto qual'è la mia installazione di default.
Fatto questo e lanciando java -version:

java version "1.6.0_45"
Java(TM) SE Runtime Environment (build 1.6.0_45-b06)
Java HotSpot(TM) 64-Bit Server VM (build 20.45-b01, mixed mode)

Ora Sql Developer ha ripreso a funzionare correttamente.
La mia versione di Sql Developer  3.2.20.09 è pienamente supportata con la versione Java6, mentre con la versione 7 e relativi update è uscita la versione 4 scaricabile quì.
Nel mio caso ha funzionato correttamente fino alla versione 7 update 25.



venerdì 4 ottobre 2013

all_object - strange issue in Oracle 11GR2

Nei giorni scorsi mi sono trovato ad indagare per un problema strano.
Gli utenti mi dicevano che quando tentavano di collegare alcune tabelle Oracle tramite l'applicativo MSAccess via ODBC, l'applicazione impiegava diversi minuti, poi andava in time-out.
Come prima risoluzione ho tolto il time-out via ODBC e così ho fatto in modo che non andasse più in time-out, ma impiegava 10 min e più, prima di presentare tutti gli oggetti del DB, per scegliere la tabella desiderata.
Ho cominciato ad indagare e ho visto che per effettuare questa operazione con MSAccess lanciava questa query:

SELECT

        *

    FROM

        (

            SELECT

                    NULL table_qualifier

                    ,o1.owner table_owner

                    ,o1.object_name table_name

                    ,decode (

                        o1.owner

                        ,'SYS'

                        ,decode (

                            o1.object_type

                            ,'TABLE'

                            ,'SYSTEM TABLE'

                            ,'VIEW'

                            ,'SYSTEM VIEW'

                            ,o1.object_type

                        )

                        ,'SYSTEM'

                        ,decode (

                            o1.object_type

                            ,'TABLE'

                            ,'SYSTEM TABLE'

                            ,'VIEW'

                            ,'SYSTEM VIEW'

                            ,o1.object_type

                        )

                        ,o1.object_type

                    ) table_type

                    ,NULL remarks

                FROM

                    all_objects o1

                WHERE

                    o1.object_type IN (

                        'TABLE'

                        ,'VIEW'

                    )

            UNION

            SELECT

                    NULL table_qualifier

                    ,s.owner table_owner

                    ,s.synonym_name table_name

                    ,'SYNONYM' table_type

                    ,NULL remarks

                FROM

                    all_objects o3

                    ,all_synonyms s

                WHERE

                    o3.object_type IN (

                        'TABLE'

                        ,'VIEW'

                    )

                    AND s.table_owner = o3.owner

                    AND s.table_name = o3.object_name

            UNION

            SELECT

                    NULL table_qualifier

                    ,s1.owner table_owner

                    ,s1.synonym_name table_name

                    ,'SYNONYM' table_type

                    ,NULL remarks

                FROM

                    all_synonyms s1

                WHERE

                    s1.db_link IS NOT NULL

        ) tables

    WHERE

        1 = 1

        AND (

            table_type = 'TABLE'

            OR table_type = 'SYSTEM TABLE'

            OR table_type = 'VIEW'

            OR table_type = 'SYNONYM'

        )

    ORDER BY

        4

        ,2

        ,3;

       



Ho provato a lanciarla da Sql Developer per verificare lo stesso comportamento.
Stesso comportamento, anche da altri tools.
Questa query ci impiega veramente più di 10 min per essere eseguita.
All'inizio pensavo che fosse in relazione anche alle statistiche del Data Dictionary e quindi ho lanciato:

DBMS_STATS.GATHER_DICTIONARY_STATS;


ma le performance rimanevano inalterate ossia scadenti.

Ho verificato nel metalink e ci sono diverse Note che riguardano l'eccessiva lentezza di questo oggetto, ma riguardavano le versioni precedenti alla mia ( 11.2.0.3.0).

Ho trovato però questa nota ,Compilation Against a 11g Database Hangs or Takes a Very Long Time (Doc ID 880660.1) inerente Oracle Form, ma verificando bene ho notato che la query usata da questo Doc Id non era molto diversa da quella mia.
Lasciando un attimo da parte la patch da installare visto che riguardava Form, ho provato con il workaround proposto ossia:

create synonym all_objects for sys.dba_objects;


nello schema di competenza dell'utente che doveva collegare le tabelle tramite MSAccess.

Dopo la creazione di questo synonym la query  impiega circa 2 sec per essere eseguita, sia da MSAccess via ODBC che da Sql Developer.
Strano comportamento....


giovedì 12 settembre 2013

ORA-24247: network access denied by access control list (ACL)

Dopo una migrazione da Oracle 10G a 11G ho notato che la mia  procedura di notifica attraverso la spedizione di mail non funziona più e mi restituiva l'errore: ORA-24247: network access denied by access control list (ACL)

Dalla versione 11 infatti Oracle ha introdotto nuove sicurezze (ACL) per l'utilizzo dei network packages come utl_tcp, utl_smtp, utl_mail, utl_http e utl_inaddr  perchè in passato quando un utente possedeva il grant per questi package non c'erano sufficienti controlli e quindi si poteva rendere il database non sicuro.

Nel mio caso come primo passo ho creato una ACL

begin
dbms_network_acl_admin.create_acl (
acl => 'send_mail_permissions.xml',
description => 'Send Mail',
principal => 'MY123',
is_grant => TRUE,
privilege => 'connect',
start_date => null,
end_date => null
);
end;
/
commit;


2) Aggiungo il privilegio all'utente per l'utilizzo del network

begin
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl => 'sendmail_permissions.xml',
principal => 'MY123',
is_grant => true,
privilege => 'connect');
end;
/
commit;

3) Questo è la novità più interessante la quale ci da la possibilità di gestire in modo capillare le risorse del network assegnando un ACL, specificando utente, host di destinazione e un range di porte.

BEGIN
dbms_network_acl_admin.assign_acl (
acl => 'sendmail_permissions.xml',
host => '56.3.6.81',
lower_port => 20,
upper_port => 80
);
END;
/
commit;


Nel parametro host ho inserito l'indirizzo IP ma c'è la possibilità di aggiungere anche il privilegio di resolve all'utente, inserendo invece il nome dell'host.

begin
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl => 'sendmail_permissions.xml',
principal => 'MY123',
is_grant => true,
privilege => 'resolve');
end;
/
commit;


Con DBMS_NETWORK_ACL_ADMIN è possibile gestire anche la drop della ACL

BEGIN
DBMS_NETWORK_ACL_ADMIN.DROP_ACL(
 acl => 'sendmail_permissions.xml');
END;
commit;

Questo package ha numerose funzionalità ma per questo vi indirizzo alla documentazione ufficiale Oracle.

venerdì 30 agosto 2013

Scheduled Job Running Shell Script Fails With ORA-27369

Nei giorni scorsi mi sono trovato a combattere con questo errore ORA-27369:  Operation not permitted, durante una schedulazione di un job.
La versione di Oracle è la 11GR2.
Ho voluto riportare tutti gli scripts che venivano lanciati attraverso il crontab , nello scheduler di Oracle.
Quindi per prima cosa:

BEGIN
DBMS_SCHEDULER.CREATE_PROGRAM(
program_name => 'TEST',
program_action => '/home/oracle/test.sh',
program_type => 'EXECUTABLE',
comments => 'test');
END;
/


BEGIN
DBMS_SCHEDULER.CREATE_SCHEDULE(
schedule_name => 'TEST_schedule',
start_date => SYSTIMESTAMP,
repeat_interval => 'FREQ=DAILY; BYHOUR=20',
comments => 'Run 20 P.M.');
END;
/

BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'JOB_TEST',
program_name => 'TEST',
schedule_name => 'TEST_schedule',
enabled=> true,
auto_drop=> false
);
END;
/

Ho verificato nel log dello specifico job e ho trovato l'errore ORA-27369 con la descrizione STANDARD_ERROR="standard in must be a tty"
Questo dipende dal fatto che gli script che venivano eseguiti con il crontab  essendo lanciati con root avevano il comando su - oracle -c "home/oracle/test.sh" e questo generava questo specifico errore, mentre se vengono lanciati  mediante lo scheduler di Oracle vengono eseguiti con l'utente oracle (impostato tramite il file externaljob.ora), quindi ho provveduto ad eliminare il comando su - oracle nello script test.sh.
Ho provato a rischedulare il job  e ancora mi ritornava l'errore ORA-27369:  questa volta con la descrizione Operation not permitted.
Eseguendo lo script dalla shell del sistema operativo, funzionava.
Ed è quì che ho focalizzato la mia indagine, visto che l'utente che lancia il job è oracle, ho pensato che caricasse da solo le variabili di ambiente dell'utente, prendendole dal profilo del OS, nel mio caso Oracle Enterprise Linux 6.3.
Sbagliato.
Il problema è che non caricava le variabili del profilo dell'utente, bisogna inserirle dentro lo script test.sh..
Dopo aver inserito infatti le variabili (le stesse del file .bash_profile) come per esempio:

ORACLE_SID=test1; export ORACLE_SID
ORACLE_UNQNAME=test; export ORACLE_UNQNAME
ORACLE_BASE=/u02/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1; export ORACLE_HOME

LD_LIBRARY_PATH=$ORACLE_HOME/lib
LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:$ORACLE_HOME/oracm/lib
LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:/lib:/usr/lib:/usr/local/lib
export LD_LIBRARY_PATH

CLASSPATH=$ORACLE_HOME/JRE
CLASSPATH=${CLASSPATH}:$ORACLE_HOME/jlib
CLASSPATH=${CLASSPATH}:$ORACLE_HOME/rdbms/jlib
CLASSPATH=${CLASSPATH}:$ORACLE_HOME/network/jlib
export CLASSPATH

PATH=$PATH:$HOME/bin
PATH=$PATH:$ORACLE_HOME/bin

export PATH


Lo script lanciato attraverso lo schedulatore, ha cominciato a funzionare bene.

P.S.: Prima di effettuare i testi ho provveduto ad impostare e modificare così i seguenti file:


- rdbms/admin/externaljob.ora: deve avere l'owner root:oraclegroup ed i permessi  644 (rw-r--r--) all'interno deve essere configurato con
run_user = oracle
run_group = dba


- bin/extjob: deve avere come proprietario root:oraclegroup ed i permessi 4750 (-rwsr-x---)

- bin/extjobo permessi 755 (rwxr-xr-x) con proprietario oracle:oraclegroup