Data Dictionary on Netezza

The data dictionary view on netezza are name as _V_*

For example:

_V_GROUP
_V_TABLE
_V_RELATION_COLUMN
_V_GROUPUSERS
_V_VIEW

To describe a table or view, use \d

SYSTEM(ADMIN)-> \d _V_GROUP
                             View "_V_GROUP"
      Attribute      |          Type          | Modifier | Default Value
---------------------+------------------------+----------+---------------
 OBJID               | OID                    |          |
 GROUPNAME           | NAME                   |          |
 OWNER               | NAME                   |          |
 CREATEDATE          | ABSTIME                |          |
 ROWLIMIT            | INTEGER                |          |
 SESSIONTIMEOUT      | INTEGER                |          |
 QUERYTIMEOUT        | INTEGER                |          |
 DEF_PRIORITY        | NAME                   |          |
 MAX_PRIORITY        | NAME                   |          |
 OBJDELIM            | BOOLEAN                |          |
 GRORSGPERCENT       | INTEGER                |          |
 RSGMAXPERCENT       | INTEGER                |          |
 CROSS_JOINS_ALLOWED | CHARACTER VARYING(255) |          |
 COLLECTHISTORY      | INTEGER                |          |
 ACCESSTIME          | INTEGER                |          |
 CONCURRENTSESS      | INTEGER                |          |
 JOBMAX              | INTEGER                |          |


Sources
http://wirewoods.wikidot.com/netezza-metadata-queries

AWR scripts

Questi script sono gli awr di un database 10.2:

/sbrdbms/app/oracle/product/10.2.0/db_rac/rdbms/admin/awrddinp.sql
/sbrdbms/app/oracle/product/10.2.0/db_rac/rdbms/admin/awrddrpi.sql
/sbrdbms/app/oracle/product/10.2.0/db_rac/rdbms/admin/awrddrpt.sql
/sbrdbms/app/oracle/product/10.2.0/db_rac/rdbms/admin/awrextr.sql
/sbrdbms/app/oracle/product/10.2.0/db_rac/rdbms/admin/awrinfo.sql
/sbrdbms/app/oracle/product/10.2.0/db_rac/rdbms/admin/awrinpnm.sql
/sbrdbms/app/oracle/product/10.2.0/db_rac/rdbms/admin/awrinput.sql
/sbrdbms/app/oracle/product/10.2.0/db_rac/rdbms/admin/awrload.sql
/sbrdbms/app/oracle/product/10.2.0/db_rac/rdbms/admin/awrrpt.sql
/sbrdbms/app/oracle/product/10.2.0/db_rac/rdbms/admin/awrrpti.sql
/sbrdbms/app/oracle/product/10.2.0/db_rac/rdbms/admin/awrsqrpi.sql
/sbrdbms/app/oracle/product/10.2.0/db_rac/rdbms/admin/awrsqrpt.sql
/sbrdbms/app/oracle/product/10.2.0/db_rac/rdbms/admin/catawrtb.sql
/sbrdbms/app/oracle/product/10.2.0/db_rac/rdbms/admin/catawrvw.sql
/sbrdbms/app/oracle/product/10.2.0/db_rac/rdbms/admin/catnoawr.sql
/sbrdbms/app/oracle/product/10.2.0/db_rac/rdbms/admin/dbmsawr.sql
/sbrdbms/app/oracle/product/10.2.0/db_rac/rdbms/admin/prvtawr.plb

Max extents reached

Un cliente mi ha segnalato questo errore:

Salve, durante il giro DAC ho ricevuto questo errore oracle

   ORA-01631: max # extents () reached in table

La tabella è DACADMIP_2005.TAWWTRV_AUTORICARICHE.

Questo link, suggerisce come fare:

alter table storage (maxextents 500);

Unix profile: showlist

Al fine di personalizzare l'utente oracle, all'interno del "profile", definiamo la funzione showlist

typeset -fx showlist
showlist(){
ps -ef -o %a|grep -i tnslsnr |grep -v grep|awk '{print $2}'|sort
}

Questo è un esempio di output

$> showlist
listener_mydb01t
listener_mydb02t
listener_mydb03t

Lo scopo di questa funzione è di mostrare tutti i listener che girano sulla macchina

Unix profile: setsid

Al fine di personalizzare l'utente oracle, all'interno del "profile", definiamo la funzione setsid

typeset -fx setsid
setsid()
{
_sidName=$(echo $1 |sed -e "s/\(^.*\)\(.\)/\1/")
_sidType=$(echo $1 |sed -e "s/\(^.*\)\(.\)/\2/")
_sidUpperName=$(echo ${_sidName} |tr '[:lower:]' '[:upper:]')
_sidLowerType=$(echo ${_sidType} |tr '[:upper:]' '[:lower:]')

echo ${_sidUpperName}${_sidLowerType}
}

Questo è un esempio di output

$> setsid VasMsrt
VASMSRt

Lo scopo di questa funzione è di rendere ORACLE_SID nel seguente formato:

ORACLE_SID ::= <logical_name><type>

dove


<logical_name> è una stringa che identifica l'applicazione

<type> può assumere 3 valori:

• t -> test

• d -> develop

• p -> production

AWR: %Total Call Time

Nella sezione "Top 5 Timed Events" la colonna "%Total Call Time" è calcolato come rapporto tra il tempo dell'evento d'attesa ed il "DB Time".

Nell'esempio che segue, il DB Time speso in "database user-call" è 463909, mentre il tempo di attesa sull'evento "PX Deq Credit: send blkd" è 222393.

 %Total Call Time of PX Deq Credit: send blkd => 222393/463909*100 = 47.9

Top 5 Timed Events                                         Avg %Total
~~~~~~~~~~~~~~~~~~                                        wait   Call
Event                                 Waits    Time (s)   (ms)   Time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
PX Deq Credit: send blkd          1,555,214     222,393    143   47.9      Other
db file scattered read            1,878,085      30,323     16    6.5   User I/O
db file sequential read           3,073,012      17,900      6    3.9   User I/O
CPU time                                         17,118           3.7
db file parallel read               203,998       6,165     30    1.3   User I/O
          -------------------------------------------------------------




-> Total time in database user-calls (DB Time): 463909.1s
-> Statistics including the word "background" measure background process
   time, and so do not contribute to the DB time statistic
-> Ordered by % or DB time desc, Statistic name

Statistic Name                                       Time (s) % of DB Time
------------------------------------------ ------------------ ------------
sql execute elapsed time                            462,631.7         99.7
DB CPU                                               17,117.8          3.7
parse time elapsed                                      410.4           .1
sequence load elapsed time                              392.7           .1
hard parse elapsed time                                 220.0           .0
PL/SQL execution elapsed time                             8.3           .0
connection management call elapsed time                   5.0           .0
PL/SQL compilation elapsed time                           2.1           .0
failed parse elapsed time                                 1.0           .0
hard parse (sharing criteria) elapsed time                0.6           .0
hard parse (bind mismatch) elapsed time                   0.2           .0
repeated bind elapsed time                                0.1           .0
DB time                                             463,909.1          N/A
background elapsed time                               2,561.9          N/A
background cpu time                                     806.3          N/A
          -------------------------------------------------------------

Ho notato che esiste una piccola discrepanza tra il "DB Time" riportato al top del report AWR e quello riportato più sotto nello stesso output. Infatti

              Snap Id      Snap Time      Sessions Curs/Sess
            --------- ------------------- -------- ---------
Begin Snap:      9671 10-Aug-11 00:00:24       151       2.3
  End Snap:      9676 10-Aug-11 05:00:27       156       2.2
   Elapsed:              300.04 (mins)
   DB Time:            7,731.82 (mins)

mentre

-> Total time in database user-calls (DB Time): 463909.1s


7731 minuti sono 463860 secondi (7731*60 ). La discrepanza è di 49 secondi.

Netezza restore

Due diversi modi per effettuare un restore:

NZ_UTILITY_DIR=/nz/support/contrib/bin
BACKUP_DIR=/dwhtwf_fs/BackUpDir/Netezza/dwhtwf
LOG_DIR=/dwhtwf_fs/LogDir/Netezza/dwhtwf/NTZ_DWHD.20110812

setsid ${NZ_UTILITY_DIR}/nz_restore -dir ${BACKUP_DIR}/NTZ_DWDMP.20110812 -format binary -t `cat /export/home/nz/SALZANO/sources/NTZ_DWDMP.talble.list` -db NTZ_DWDMP_TRASH -threads 6 2>&1 > {LOG_DIR}/NTZ_DWDMP.20110812.restore &

setsid ${NZ_UTILITY_DIR}/nz_restore -dir ${BACKUP_DIR}/NTZ_DWHD.20110812 -format binary -db NTZ_DWHD -threads 6 2>&1 > ${LOG_DIR}/NTZ_DWHD.20110812.restore &

Import gzip and pipe

Dopo aver fatto l'export, si esegue l'import:

$for i in $(cat dwpr_test_01.list); do
>nohup ./import_dwpr_test.sh $i 2>&1 > $i.nohup &
>done

$cat import_dwpr_test.sh

WORKINGDIR=/dwhtest01a/export.20110804
NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1
ORACLE_BASE=/u1/app/oracle
ORACLE_SID=dwprt
ORACLE_TERM=vt100
ORA_NLS33=/u1/app/oracle/product/9.2.0/ocommon/nls/admin/data
ORACLE_HOME=/u1/app/oracle/product/9.2.0
ADMIN=system
PASSWORD=password
USERNAME=$1

rm ${WORKINGDIR}/${USERNAME}.pipe
mknod ${WORKINGDIR}/${USERNAME}.pipe p

gunzip <  ${WORKINGDIR}/${USERNAME}.dmp.gz > ${WORKINGDIR}/${USERNAME}.pipe &

imp $ADMIN/$PASSWORD FROMUSER=${USERNAME} TOUSER=NEW_${USERNAME} FILE=${WORKINGDIR}/${USERNAME}.pipe LOG=${WORKINGDIR}/${USERNAME}_dwprt_import.log

rm ${WORKINGDIR}/${USERNAME}.pipe

Export, gzip and pipe

$for i in $(cat dwpr_test_01.list); do
>nohup ./export_dwpr_test.sh $i 2>&1 > $i.nohup &
>done

dove

$cat export_dwpr_test.sh

WORKINGDIR=/dwhtest01a/export.20110804
NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1
ORACLE_BASE=/u1/app/oracle
ORACLE_SID=dwprt
ORACLE_TERM=vt100
ORA_NLS33=/u1/app/oracle/product/9.2.0/ocommon/nls/admin/data
ORACLE_HOME=/u1/app/oracle/product/9.2.0
ADMIN=system
PASSWORD=password
USERNAME=$1

rm ${WORKINGDIR}/${USERNAME}.pipe
mknod ${WORKINGDIR}/${USERNAME}.pipe p

gzip < ${WORKINGDIR}/${USERNAME}.pipe > ${WORKINGDIR}/${USERNAME}.dmp.gz &

exp $ADMIN/$PASSWORD OWNER=${USERNAME} FILE=${WORKINGDIR}/${USERNAME}.pipe LOG=${WORKINGDIR}/${USERNAME}_dwprt_export.log

rm ${WORKINGDIR}/${USERNAME}.pipe

$cat dwpr_test_01.list

PR_AI_INTEG
PR_AI_PRE
PR_DM_PRE
PR_EDW_COLL
PR_EDW_PRE
PR_LAY_PRE
PR_SRVDIFF_COLL
PR_SRVDIFF_PRE

KSV master wait message on ASM

A seguito di un crash di entrambe le istanze di un RAC, ho aperto una Service Request su metalink. Più sotto c'è la risposta.

Ecco le cose interessanti:

1. "KSV master wait" event, indicates the process on the RDBMS side is waiting for a reply from a process on the ASM side.
2. "ASM file metadata operation" is a wait envet generated by a database remote process (in this case, the database ABC is the clinet)
3. SIGLOST: SIG is a common prefix for signal names. LOST refers to a lost file lock.

=========
Support note
=========
According to the systemstate dump, from the instance, the majority of the background processes are found dead and last wait for service name: SYS$BACKGROUND , is for 'KSV master wait' . Event 'KSV master wait' indicates the process on the RDBMS side is waiting for a reply from a process on the ASM side.

In both ASM instances, ORA-07445 is being dumped , right before notifying the death of the corresponding instance:

Sun Jul 24 14:45:03 2011
NOTE: ASM client ABCp1:ABCp died unexpectedly.

According to the trace file, it was generated by a remote process waiting for 'ASM file metadata operation'. The call stack of the ORA-07445 is generic and per trace file, "Process received SIGLOST. Therefore the callstack does not point to the location of the problem." However, the callstack contains a hint, and that is the tcpip function, which could point to a problem at the network level.

Given the data above, please check with your system administrator if there's any network issue that could have interrupted the communication between the database and the ASM instances, and let me know the results.

======================
== ODM Data Collection ==
======================

Dump file /sbRdbms/app/oracle/diag/asm/+asm/+ASM1/incident/incdir_17865/+ASM1_ora_5029906_i17865.trc
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
ORACLE_HOME = /sbRdbms/app/oracle/product/ASM
System name: AIX
Node name: abcdbs01
Release: 1
Version: 6
Machine: 00CD96944C00
Instance name: +ASM1
Redo thread mounted by this instance: 0
Oracle process number: 33
Unix process pid: 5029906, image: oracle@abcdbs01 (TNS V1-V3)


*** 2011-07-24 14:45:00.080
*** SESSION ID:(83.3) 2011-07-24 14:45:00.080
*** CLIENT ID:() 2011-07-24 14:45:00.080
*** SERVICE NAME:() 2011-07-24 14:45:00.080
*** MODULE NAME:(oracle@abcdbs01 (TNS V1-V3)) 2011-07-24 14:45:00.080
*** ACTION NAME:() 2011-07-24 14:45:00.080

Dump continued from file: /sbRdbms/app/oracle/diag/asm/+asm/+ASM1/trace/+ASM1_ora_5029906.trc
ORA-07445: exception encountered: core dump [_ptrgl()] [SIGSEGV] [ADDR:0x7F8000007FC00000] [PC:0x10000DE98] [Address not mapped to object] []

========= Dump for incident 17865 (ORA 7445 [_ptrgl()]) ========
----- Beginning of Customized Incident Dump(s) -----
Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x7F8000007FC00000] [PC:0x10000DE98, _ptrgl()]
Registers:
iar: 0x000000010000de98 lr: 0x0000000102274170
msr: 0xa00000000000d032 cr: 0x0000000024202220
r00: 0x0000000000000028 r01: 0x0fffffffffff9250 r02: 0x0000000110352700
r03: 0x0fffffffffffaf50 r04: 0x0000000000000140 r05: 0x0000000000002683
r06: 0x0000000000000001 r07: 0x0000000000000005 r08: 0x00000000ffff9450
r09: 0x0fffffffffff9348 r10: 0x0700000010f9d400 r11: 0x7f8000007fc00000
r12: 0x0000000044202220 r13: 0x00000001103a8e18 r14: 0x0000000000000000
r15: 0x0fffffffffffac48 r16: 0x00000000000001d5 r17: 0x0000000110282a98
r18: 0x0fffffffffffb104 r19: 0x00000001100caa28 r20: 0x000000010762e2b0
r21: 0x0000000000000000 r22: 0x00000000000000b8 r23: 0x0000000000000000
r24: 0x000000000000008f r25: 0x0000000107d164d8 r26: 0x0000000000000000
r27: 0x00000001100cab80 r28: 0x0fffffffffff9a20 r29: 0x0700000000003640
r30: 0x000000010859bee8 r31: 0x0fffffffffffaf50

*** 2011-07-24 14:45:00.080
----- SQL Statement (None) -----
Current SQL information unavailable - no cursor.

----- Call Stack Trace -----
calling call entry argument values in hex
location type point (? means dubious value)
-------------------- -------- -------------------- ----------------------------
opiodr ttcpip opitsk opiino opiodr opidrv sou2o opimai_real main start

....................

Argument/Register addr=0x1078833c2.
Dump of memory from 0x107883382 to 0x1078834c2
107883380 7820206C 723A2030 78253031 366C780A [x lr: 0x%016lx.]
107883390 0049424D 6D73723A 20307825 3031366C [.IBMmsr: 0x%016l]
1078833A0 78202063 723A2030 78253031 36780A00 [x cr: 0x%016x..]
1078833B0 72253032 643A2030 78253031 366C7825 [r%02d: 0x%016lx%]
1078833C0 63000A00 00000000 00000000 4E4F5449 [c...........NOTI]
1078833D0 43453A20 50726F63 65737320 72656365 [CE: Process rece]
1078833E0 69766564 20736967 6E616C20 5349474C [ived signal SIGL]
1078833F0 4F53542E 20546865 7265666F 72652C20 [OST. Therefore, ]
107883400 74686520 63616C6C 0A202020 20202020 [the call. ]
107883410 20737461 636B2064 6F657320 6E6F7420 [ stack does not ]
107883420 706F696E 7420746F 20746865 206C6F63 [point to the loc]
107883430 6174696F 6E206F66 20746865 2070726F [ation of the pro]
107883440 626C656D 2E0A0A00 0A457863 65707469 [blem.....Excepti]
107883450 6F6E205B 74797065 3A202573 2C202573 [on [type: %s, %s]
107883460 5D205B41 4444523A 3078256C 585D0049 [] [ADDR:0x%lX].I]
107883470 205B5043 3A307825 6C582C20 0049424D [ [PC:0x%lX, .IBM]
107883480 7B656D70 74797D00 25735D00 205B6578 [{empty}.%s]. [ex]
107883490 63657074 696F6E20 69737375 65642062 [ception issued b]
1078834A0 79207069 643A2025 642C2075 69643A20 [y pid: %d, uid: ]
1078834B0 25645D00 50433A30 78256C58 0049424D [%d].PC:0x%lX.IBM]
1078834C0 41444452 [ADDR]
.....................

----------------------------------------
SO: 0x700000010f9b9e0, type: 4, owner: 0x700000010e692d0, flag: INIT/-/-/0x00 if: 0x3 c: 0x3
proc=0x700000010e692d0, name=session, file=ksu.h LINE:10719 ID:, pg=0
(session) sid: 83 ser: 3 trans: 0x0, creator: 0x700000010e692d0
flags: (0x41) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
flags2: (0x8) -/-
DID: , short-term DID:
txn branch: 0x0
oct: 0, prv: 0, sql: 0x0, psql: 0x0, user: 0/SYS
ksuxds FALSE at location: 0
service name: SYS$USERS
client details:
O/S info: user: oracle, term: , ospid: 1716314
machine: abcdbs01 program: oracle@abcdbs01 (TNS V1-V3)
application name: oracle@abcdbs01 (TNS V1-V3), hash value=335489673
Current Wait Stack:
0: waiting for 'ASM file metadata operation'
msgop=28, locn=1, =0
wait_id=5596 seq_num=9859 snap_id=1
wait times: snap=2.076467 sec, exc=2.076467 sec, total=2.076467 sec
wait times: max=infinite
wait counts: calls=0 os=0
in_wait=1 iflags=0x5a0
Wait State:
auto_close=0 flags=0x22 boundary=0x0/-1
Session Wait History:
0: waited for 'SQL*Net message from client'
driver id=62657100, #bytes=1, =0
wait_id=5595 seq_num=9858 snap_id=1
wait times: snap=4.945453 sec, exc=4.945453 sec, total=4.945453 sec
wait times: max=infinite
wait counts: calls=0 os=0
occurred after 0.000004 sec of elapsed time
1: waited for 'SQL*Net message to client'
driver id=62657100, #bytes=1, =0
wait_id=5594 seq_num=9857 snap_id=1

G

M

T

Detect languageAfrikaansAlbanianArabicArmenianAzerbaijaniBasqueBelarusianBengaliBosnianBulgarianCatalanCebuanoChichewaChinese (Simplified)Chinese (Traditional)CroatianCzechDanishDutchEnglishEsperantoEstonianFilipinoFinnishFrenchGalicianGeorgianGermanGreekGujaratiHaitian CreoleHausaHebrewHindiHmongHungarianIcelandicIgboIndonesianIrishItalianJapaneseJavaneseKannadaKazakhKhmerKoreanLaoLatinLatvianLithuanianMacedonianMalagasyMalayMalayalamMalteseMaoriMarathiMongolianMyanmar (Burmese)NepaliNorwegianPersianPolishPortuguesePunjabiRomanianRussianSerbianSesothoSinhalaSlovakSlovenianSomaliSpanishSundaneseSwahiliSwedishTajikTamilTeluguThaiTurkishUkrainianUrduUzbekVietnameseWelshYiddishYorubaZulu

AfrikaansAlbanianArabicArmenianAzerbaijaniBasqueBelarusianBengaliBosnianBulgarianCatalanCebuanoChichewaChinese (Simplified)Chinese (Traditional)CroatianCzechDanishDutchEnglishEsperantoEstonianFilipinoFinnishFrenchGalicianGeorgianGermanGreekGujaratiHaitian CreoleHausaHebrewHindiHmongHungarianIcelandicIgboIndonesianIrishItalianJapaneseJavaneseKannadaKazakhKhmerKoreanLaoLatinLatvianLithuanianMacedonianMalagasyMalayMalayalamMalteseMaoriMarathiMongolianMyanmar (Burmese)NepaliNorwegianPersianPolishPortuguesePunjabiRomanianRussianSerbianSesothoSinhalaSlovakSlovenianSomaliSpanishSundaneseSwahiliSwedishTajikTamilTeluguThaiTurkishUkrainianUrduUzbekVietnameseWelshYiddishYorubaZulu

Text-to-speech function is limited to 200 characters

Options : History : Feedback : Donate

Close

Backup on Netezza

1. Creazione del database

SYSTEM(ADMIN)=> create database as_test;
CREATE DATABASE

Il database "as_test" non ha tabelle

2. Backup del database

[nz@dwhntzc SalzanoA]$ nz_backup -host dwhntzc -format binary -dir /dwhntzc_fs/SalzanoA/bck_asTest -db as_test

nz_backup of database AS_TEST started on 2011-08-02 12:18:19

NZ_HOST: dwhntzc
NZ_USER: ADMIN
NZ_DATABASE: AS_TEST
Backup Directory: /dwhntzc_fs/SalzanoA/bck_asTest
Backup Format: binary
Custom Script:
# Of Threads: 1
Using stableTXid: 24582039
The lastTXid assigned was: 24582040

=========================================================================

ERROR: 0 tables were processed !!
It would appear that there are no tables defined in the database that
is being backed up (or restored). Or, perhaps this database account
doesn't have access to those tables.

=========================================================================

nz_backup of database AS_TEST finished on 2011-08-02 12:18:19

Total # of tables processed: 0
Total # of seconds elapsed : 0

=========================================================================

3. Creazione di una tabella

[nz@dwhntzc SalzanoA]$ nzsql -E as_test
********* QUERY *********
select current_catalog, current_user
*************************

SYSTEM(ADMIN)=> \c as_test asalzano a$a7zan0
You are now connected to database as_test as user asalzano.
********* QUERY *********
select current_catalog, current_user
*************************

AS_TEST(ASALZANO)=> create table as_mytab (one integer);
CREATE TABLE

[nz@dwhntzc SalzanoA]$ nz_backup -host dwhntzc -format binary -dir /dwhntzc_fs/SalzanoA/bck_asTest -db as_test

nz_backup of database AS_TEST started on 2011-08-02 13:38:39

NZ_HOST: dwhntzc
NZ_USER: ADMIN
NZ_DATABASE: AS_TEST
Backup Directory: /dwhntzc_fs/SalzanoA/bck_asTest
Backup Format: binary
Custom Script:
# Of Threads: 1
Using stableTXid: 24582153
The lastTXid assigned was: 24582154

=========================================================================

Info: table 1 of 1 AS_MYTAB
Info: backup process started 2011-08-02 13:38:39
Info: estimated # of records 0
Info: unloading data ( thread 1 of 1 )
Info: data flowing ...
Info: unload finished ( thread 1 of 1 ) seconds 0
Info: unload results ( thread 1 of 1 ) INSERT 0 0
Info: data flow finished
Info: backup process ended 2011-08-02 13:38:39
Info: # of records unloaded 0
Info: # of seconds elapsed 0

=========================================================================

nz_backup of database AS_TEST finished on 2011-08-02 13:38:39

Total # of tables processed: 1
Total # of records unloaded: 0
Total # of seconds elapsed : 0

=========================================================================

Managing privileges on Netezza

[nz@dwhntzc SalzanoA]$ nzsql -E
********* QUERY *********
select current_catalog, current_user
*************************

SYSTEM(ADMIN)=> grant connect on as_test to asalzano;
GRANT

SYSTEM(ADMIN)=> grant create table to asalzano;
GRANT

SYSTEM(ADMIN)=> \c as_test asalzano xxxxxx
You are now connected to database as_test as user asalzano.
********* QUERY *********
select current_catalog, current_user
*************************

AS_TEST(ASALZANO)=> create table as_mytab (one integer);
CREATE TABLE

Transaction Rollback Monitoring

V$FAST_START_TRANSACTIONS displays information about the progress of the transactions that Oracle is recovering.

alter session set nls_date_format='dd/mm/yyyy hh24:mi:ss';

select usn, state, undoblockstotal "Total", undoblocksdone||' ('||round(UNDOBLOCKSDONE/UNDOBLOCKSTOTAL*100,2)||'%)' "Done",
 undoblockstotal-undoblocksdone "ToDo",
         decode(cputime,0,'unknown',sysdate+(((undoblockstotal-undoblocksdone) / (undoblocksdone / cputime)) / 86400))||' ('|| round((undoblockstotal-undoblocksdone)/UNDOBLOCKSDONE*CPUTIME/3600,2)||'h)' "Estimated time to complete"
  from v$fast_start_transactions;

USN	STATE	Total	Done	ToDo	Estimated time to complete
63	RECOVERING	349526	194208 (55,56%)	155318	27/07/2011 16:34:04 (20,25h)
86	RECOVERING	309253	194095 (62,76%)	115158	27/07/2011 11:20:29 (15,02h)

Database Hangs Because SMON is taking 100% CPU doing transaction recovery [ID 414242.1]
How to Disable Parallel Transaction Recovery When Parallel Txn Recovery is Active [ID 238507.1]
Rollback Is Taking A Long Time After Session Killed [ID 1060831.6]
How Long Will My Rollback Take? Is my Transaction Doing Anything? [ID 169138.1]

SQL Tuning Sets

A SQL Tuning Set (STS) is a database object that includes one or more SQL statements along with their execution statistics and execution context, and could include a user priority ranking. The SQL statements can be loaded into a SQL Tuning Set from different SQL sources, such as the Automatic Workload Repository, the cursor cache, or custom SQL provided by the user.

An STS includes:

• A set of SQL statements

• Associated execution context, such as user schema, application module name and action, list of bind values, and the cursor compilation environment

• Associated basic execution statistics, such as elapsed time, CPU time, buffer gets, disk reads, rows processed, cursor fetches, the number of executions, the number of complete executions, optimizer cost, and the command type

• Associated execution plans and row source statistics for each SQL statement (optional)

A SQL Tuning Set can be used as input to the SQL Tuning Advisor, which performs automatic tuning of the SQL statements based on other input parameters specified by the user. SQL Tuning Sets are transportable across databases and can be exported from one system to another, allowing for the transfer of SQL workloads between databases for remote performance diagnostics and tuning. To transport SQL Tuning Sets, use the DBMS_SQLTUNE package procedures.

(Performance Tuning Guide - Ch12.9)

Choosing an Access Path

To choose an access path, the optimizer first determines which access paths are available by examining the conditions in the statement's WHERE clause and its FROM clause. The optimizer then generates a set of possible execution plans using available access paths and estimates the cost of each plan, using the statistics for the index, columns, and tables accessible to the statement. Finally, the optimizer chooses the execution plan with the lowest estimated cost.

When choosing an access path, the optimizer is influenced by the following:

• Optimizer hints: The optimizer's choice among available access paths can be overridden with hints. (Using hints requires changing the code)

• Statistics: For example, if a table has not been analyzed since it was created, and the table statistics show that it is small, then the optimizer uses a full table scan. The LAST_ANALYZED and BLOCKS columns in the ALL_TABLES table reflect the statistics used by the optimizer.

• SQL outlines, SQL baseline plans, and SQL profiles: If these exist they will override the optimizer choices. For more information see the SQL Performance Management lesson.

(Oracle Database 11g: Performance Tuning Ch10.17)

Hierarchical Queries

Come ottenere un result set utilizzando query gerarichiche.

SELECT LPAD(' ', LEVEL*2)||columnX
FROM table
WHERE condition(s)
START WITH condition(s)
CONNECT BY PRIOR column1 = column2
ORDER SIBLINGS BY columnX;

Ci sono due modi di ricevere i dati (il top-down coincide con la visita in ordine anticipato di un albero):

=======
Top down
=======

column1 = Parent Key
column2 = Child Key

=======
Bottom up
=======

column1 = Child Key
column2 = Parent Key

The direction of the query, whether it is from parent to child or from child to parent, is determined by the CONNECT BY PRIOR column placement. The PRIOR operator refers to the parent row. To find the child rows of a parent row, the Oracle server evaluates the PRIOR expression for the parent row and the other expressions for each row in the table. Rows for which the condition is true are the child rows of the parent. The Oracle server always selects child rows by evaluating the CONNECT BY condition with respect to a current parent row.

(11g SQL Fundamentals II - App F.7)

You have to be careful when trying to order the rows of output in a hierarchical query. By default, if you omit the ORDER BY clause altogether, the query attempts to sort rows in an order that’s reflective of the hierarchy. Display will start first with a LEVEL 1 row. If that row is superior to any LEVEL 2 rows, those rows will display next before another LEVEL 1 row displays. The same approach is taken at LEVEL 2, so that rows will display down to leaf node levels before the next rows show at the higher levels. The result is a display that is meaningful to the hierarchy. But if you try to order these rows with the ORDER BY clause, you’ll create a syntactically correct statement that probably doesn’t help you much. ORDER SIBLINGS sorts rows within each given level, not across levels, thus retaining the hierarchical relationship across rows of output. Note that our earlier use of ORDER BY did not create an error message, just misleading output

(OCA Oracle Database SQL Certified Expert Exam Guide)

Per esempio, il top-down:

SELECT LPAD(' ', LEVEL*2)||last_name
FROM employees
START WITH last_name = 'King'
CONNECT BY PRIOR employee_id = manager_id
ORDER SIBLINGS BY last_name;

Userò il concetto di query gerarchiche in due casi:

1. lock
2. parallel query

Modify dos windows colours

Questi i parametri della finestra dos utilizzati da Power Shell

Layout: Dimensioni buffer dello schermo

Larghezza	120
Altezza	3000

Layout: Dimensioni finestra

Larghezza	120
Altezza	52

Layout: Posizione finestra

Sinistra	201
Alto	18

Colori: Testo normale

Rosso	255
Verde	255
Blu	255

Colori: Sfondo normale

Rosso	1
Verde	36
Blu	86

Colori: Testo popup

Rosso	128
Verde	0
Blu	128

Colori: Sfondo popoup

Rosso	255
Verde	255
Blu	255

How to change language of SQL Developer

Nella direcotry "bin" di SQL Developer, aprire il file "sqldeveloper.conf" ed aggiungere o modificare le seguenti due righe:

AddVMOption -Duser.language=en
AddVMOption -Duser.country=US

Una volta fatto, chiudere e riavviare l'applicazione.

Questo il link originale: How to change the language of SQL Developer's user interface?

Di seguito due immagini utili a mostrare le modifiche da fare.

Create new user and assign grant

[nz@dwhtwf01 ~]$ nzsql
Welcome to nzsql, the Netezza SQL interactive terminal.

Type:  \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit

SYSTEM(ADMIN)=> CREATE USER sarusso WITH PASSWORD 'please_change_me';
CREATE USER

SYSTEM(ADMIN)=> ALTER GROUP group_dev ADD USER sarusso;
ALTER GROUP

SYSTEM(ADMIN)=> ALTER GROUP group_analyst ADD USER sarusso;
ALTER GROUP



SYSTEM(ADMIN)=> \c NTZ_DWDMP
You are now connected to database NTZ_DWDMP.

NTZ_DWDMP(ADMIN)=> GRANT LIST ON TABLE TO sarusso;
GRANT

NTZ_DWDMP(ADMIN)=> GRANT SELECT ON TABLE TO sarusso;
GRANT



NTZ_DWDMP(ADMIN)=> \c NTZ_DWDMS
You are now connected to database NTZ_DWDMS.

NTZ_DWDMS(ADMIN)=> GRANT LIST ON TABLE TO sarusso;
GRANT

NTZ_DWDMS(ADMIN)=> GRANT SELECT ON TABLE TO sarusso;
GRANT



NTZ_DWDMS(ADMIN)=> \c NTZ_DWSAS
You are now connected to database NTZ_DWSAS.

NTZ_DWSAS(ADMIN)=> GRANT LIST ON TABLE TO sarusso;
GRANT

NTZ_DWSAS(ADMIN)=>  GRANT SELECT ON TABLE TO sarusso;
GRANT

NTZ_DWSAS(ADMIN)=> \q

Who is blocking me?

col username for a25
col IS_BLOCKED_BY for a20
col state for a40
col sql_id for a15
col obj for a80
set lines 220
set trim on

select
  nvl(USERNAME,schemaname)||' ('||SID||':'||INST_ID||')' USERNAME
 ,SQL_ID
 ,nvl2(BLOCKING_SESSION,BLOCKING_SESSION||':'||BLOCKING_INSTANCE||':'||
   (select
      sql_id
    from
      gv$session holder
    where
      holder.inst_id= s.BLOCKING_INSTANCE
    and holder.sid=s.BLOCKING_SESSION), null) IS_BLOCKED_BY
 ,decode(state, 'WAITING', 'WAITING', 'ON CPU')||'
 ('||substr(event,1,40)||')' state
 ,ROW_WAIT_FILE#||':'||ROW_WAIT_BLOCK#||':'||ROW_WAIT_ROW#||'
'||case
    when ROW_WAIT_OBJ# >0 then
     (select owner||':'||object_name||':'||nvl(subobject_name,'=nosub=')||' ('||object_type||')'
      from
       dba_objects do
      where
       do.object_id=s.ROW_WAIT_OBJ#)
  end obj
from
  gv$session s
where
  event in
    (select
       NAME
     from
       v$event_name
     where
       WAIT_CLASS != 'Idle')
order by sid;

Format on Windows 7 [1]

From dos window:

DISKPART
LIST DISK
SELECT DISK # (USB Stick)
CLEAN ALL
CREATE PARTITION PRIMARY
SELECT PARTITION 1
ACTIVE
FORMAT FS=FAT32
ASSIGN
EXIT
EXIT


[1] http://www.sevenforums.com/hardware-devices/61770-cannot-format-usb-flash-drive.html

Single quotes and double quote

Che differenza c'è tra gli apici singoli e quelli doppi in bash?

Semplicemente, nel primo caso la shell non interpreta le variabili, cosa che invece fa nel secondo. In altri termini, nel secondo caso, la shell sostituisce la variabile con il suo valore.

=========
Apici singoli
=========
[my-lap]$ var1=pippo
[my-lap]$ var2='$var1 & pluto'
[my-lap]$ echo $var2
$var1 & pluto

=========
Apici doppi
=========
[my-lap]$ var1=pippo
[my-lap]$ var3="$var1 & pluto"
[my-lap]$ echo $var3
pippo & pluto

Workload

Vediamo il significato di workload, preso direttamente dal dizionario:

Workload – The amount of work that a machine produces or can produce in a specified time period [1]

ovvero il "workload" rappresenta la quantità di lavoro che una macchina produce o può produrre nell'unità di tempo. E' una definizione interessante a cui però occorre presrestare attenzione. Il termine "macchina", anche se menzionato, non ha nulla a che fare con un computer o con un database. Qui, la "macchina" è pensata come statica e pertanto non c'è la possibilità di migliorare le prestazioni. Per una fotocopiatrice ad esempio, indipendentemente dalla risma di carta utilizzata o dal numero di copie da produrre, il lavoro svolto è costante.

Quando lavoriamo con i computer, il workload assume tutt'altro significato. Il carico di lavoro, nella sua definizione più generale, è il numero totale di richiesete che arrivano su di un sistema. Nel caso di un database, questo vuol dire che il workload è l'insieme degli statement SQL che, indipendentemente dalla loro esecuzione, arrivano su un'istanza e danno una risposta agli utenti che li hanno sottomessi.

E' chiaro che questo è un concetto duro da accettare perché per "insieme degli statement SQL che arrivano su un istanza" si intende proprio tutto l'SQL: sia quello visibile che quello non visibile. Ed in fondo è difficile fare il tuning dell'invisibile.

Dobbiamo allora utilizzare una definizione più ampia di workload considerando ciò che il database vede indipendentemente da quello che succede al di fuori del database stesso. Questo vuol dire campionare, misurare, tracciare quelle statistiche, all'interno dell'RDBMS, che misurano, quantificano o caratterizzano cosa sia il workload. Ed è fondamentale non solo scegliere quelle che lo misurano ma anche considerare quelle che non dipendono dalle performance del db. Statistiche come "CPU usage", "elapsed time" ed i vari "wait event" non sono buoni esempi di misura del workload poiché dipendono ampiamente dal sistema su cui gli statement SQL stano girando. Metriche più appropriate potrebbero invece essere quelle di gruppo come "numero di esecuzioni di SQL", "active session", "users" etc.

E' chiaro che mentre la scelta delle metriche deve essere indipendente da quanto avviene fuori dal db, ciò che misuriamo lo deve invece essere. Voglio dire che, quando definiamo un workload, al di là dalla metrica utilizzata, dobbiamo essere sicuri che quello che stiamo tracciando sia correlato alle risorse consumate all'interno del database. In questo possiamo allora dire, in base all'aumento o alla diminuzione del workload, qual è o quale sarà l'impatto sulle performance percepito.

[1] What Is Your Definition of Database Workload?

Metrics

Le metriche mantengono traccia di differenti eventi durante la vita del database [1]. Queste sono visibili da GV$METRICNAME.METRIC_NAME.

Sono statistiche che misurano la variazione dei cambiamenti in statistiche di performance comulative [2]. L'idea di base è la seguente [5]:

Tra di esse troviamo: "CPU Usage Per Sec", "Elapsed Time Per User Call", "Host CPU Utilization", etc.

Prendo un valore V1, all'istante T1 ed uno V2, all'istante T2. A questo punto, la metrica è definita come rapporto (V2 – V1) / (T2 – T1). AWR le raccoglie automaticamente. Frequenza della raccolta e retention dei dati, sono specificati in DBA_HIST_WR_CONTROL

SELECT snap_interval, retention FROM dba_hist_wr_control;

SNAP_INTERVAL
-------------------------------------
RETENTION
-------------------------------------
+00000 01:00:00.0
+00007 00:00:00.0

(profondità di 7 giorni, con raccolta ogni ora) e la modifica è possibileattraverso la funzione DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS

BEGIN
  dbms_workload_repository.modify_snapshot_settings(
  interval => 60,
  retention => 10*24*60
  );
END;

Sono collezionate ogni minuto, storicizzate in memoria e quindi salvate nelle tabelle WRH$ (H=History) di AWR (il tablespace su cui risiedono è SYSAUX). Complessivamente, sono disponibili le seguenti [3][5]:

Table: Global

VIEW/TABLE
GV$METRIC
GV$METRIC_HISTORY

Table: Information

VIEW/TABLE
GV$METRICGROUP
GV$METRICNAME
DBA_HIST_METRIC_NAME
WRH$_METRIC_NAME

Sono disponibili per [5]

» System
» Sessions
» Services
» Events
» Files

e raggruppate come di seguito:

select GROUP_ID, GROUP_NAME from V$METRICGROUP order by GROUP_ID

  GROUP_ID GROUP_NAME
---------- -------------------------------------------
         0 Event Metrics
         1 Event Class Metrics
         2 System Metrics Long Duration
         3 System Metrics Short Duration
         4 Session Metrics Long Duration
         5 Session Metrics Short Duration
         6 Service Metrics
         7 File Metrics Long Duration
         9 Tablespace Metrics Long Duration
        10 Service Metrics (Short)

Ed in V$METRICNAME.METRIC_UNIT, troviamo l'unità di misura in cui viene espressa quella particolare metrica. Sono espresse per

» Valori assoluti
» Percentuali
» Per secondi e per transactioni

Ad esempio:

select group_id id, metric_name, metric_unit from v$metricname where group_id=1

  ID METRIC_NAME                         METRIC_UNIT
---- -------------------------------     -----------
   1 Total Wait Counts                   Waits
   1 Total Time Waited                   CentiSeconds
   1 Database Time Spent Waiting (%)     % (TimeWaited / DBTime)
   1 Average Users Waiting Counts        Users

I valori degli utli 10 minuti sono visibili dalle GV$*, quelle degli utlimi 10+60 minuti dalle GV$*_HISTORY e quelle con retention superiore dalle DBA_HIST_* (le sottostanti tabelle sono le WRH$*). Le viste di sistema disponibili sono:

Table: Events

GROUP_ID	VIEW/TABLE
0	GV$EVENTMETRIC
1	GV$WAITCLASSMETRIC
	GV$WAITCLASSMETRIC_HISTORY
	WRH$_WAITCLASSMETRIC_HISTORY

Table: System

GROUP_ID	VIEW/TABLE
2/3	GV$SYSMETRIC
	GV$SYSMETRIC_HISTORY
	GV$SYSMETRIC_SUMMARY
	DBA_HIST_SYSMETRIC_HISTORY
	DBA_HIST_SYSMETRIC_SUMMARY
	WRH$_SYSMETRIC_HISTORY
	WRH$_SYSMETRIC_SUMMARY

Table: Session

GROUP_ID	VIEW/TABLE
4/5	V$SESSMETRIC
	DBA_HIST_SESSMETRIC_HISTORY
	WRH$_SESSMETRIC_HISTORY

Table: Service

GROUP_ID	VIEW/TABLE
6/10	GV$SERVICEMETRIC
	GV$SERVICEMETRIC_HISTORY

Table: Files

GROUP_ID	VIEW/TABLE
7	GV$FILEMETRIC
	GV$FILEMETRIC_HISTORY
	DBA_HIST_FILEMETRIC_HISTORY
	WRH$_FILEMETRIC_HISTORY

Per il GROPU_ID 9 della V$METRICGROUP, non ho trovato associazioni. Suppongo, visto che si tratta di tablespace, che le corrispondenti metriche si potrebbero associare a quelle per "Files".

GROUP_ID	VIEW/TABLE
9

[1] Oracle Metrics
[2] Database Metrics
[3] AUTOMATED WORKLOAD REPOSITORY
[4] AWR Metrics
[5] Automatic Workload Repository


Post update 2011/06/16

puser

Quali processo stanno utilizzando la porta del listener? Linux permette di rispondere a questa domanda utilizzando il comando fuser con l'opzione "-n". Il programma che segue è scritto in perl ed è necessario avere i privilegi di accesso a tutte le sottodirectory di /proc. Per utilizzarlo basta lanciare “puser” seguito dal numero della porta da controllare. Ad esempio:

#andrea> puser 1531
1531: 2506 18359

#!/usr/bin/perl
die "Usage: puser \n" if @ARGV[0] == 0;

$ENV{'PORT'} = @ARGV[0];
$shell_in = <<'IN'; echo "" > /tmp/checkPortTestPerl;

for PROC in /proc/*; do
  echo $PROC >> /tmp/checkPortTestPerl 2> /dev/null
  pfiles -F $PROC | grep port |grep $PORT >> /tmp/checkPortTestPerl 2> /dev/null

done 2> /dev/null

IN

$shell_out = `$shell_in`;
open(FH, "< /tmp/checkPortTestPerl") or die "can't open /tmp/checkPortTestPerl: $!";

$i=0;
$lineprep=;

for ($count=0; $row=; $count++) {
  if ($row =~ m/@ARGV[0]/){
    $lineprep =~ m#^/[a-z]+/([0-9]+)$#;
    $process[$i]=$1;

    $i++;
  }
  $lineprep=$row;
}

print "@ARGV[0]: @process\n";
unlink "/tmp/checkPortTestPerl";

Time Zone

Riporto questo documento che scrissi nel 2005

set events 10053

Alcuni eventi come il 10053, hanno bisogno del privilegio esplicito di ALTER SESSION.

* Creiamo innanzitutto un utente:

wedosas01.RAIDTRN(SYS)> create user pippo identified by pippo;
User created.

wedosas01.RAIDTRN(SYS)> grant connect to pippo;
Grant succeeded.

* Proviamo ad abilitare l'evento 10053

wedosas01.RAIDTRN(SYS)> conn pippo/pippo
Connected.

wedosas01.RAIDTRN(PIPPO)> alter session set events '10053 trace name context forever , level 1';

ERROR:
ORA-01031: insufficient privileges

* Assegnamo allora esplicitamente il privilegio di ALTER SESSION...

wedosas01.RAIDTRN(PIPPO)> conn / as sysdba
Connected.

wedosas01.RAIDTRN(SYS)> grant alter session to pippo;
Grant succeeded.

* ...e riproviamo ad abilitarlo nuovamente

wedosas01.RAIDTRN(SYS)> conn pippo/pippo
Connected.

wedosas01.RAIDTRN(PIPPO)> alter session set events '10053 trace name context forever , level 1';
Session altered.