Netezza: List Skew Tables Using Query

Below will will find out tables having data slices with "data slices contain more than 3 time of perfectly distributed data for a table"

In the bellow query I have used "3*((cast(100 as decimal(5))/(select count(*) from _V_DUAL_DSLICE)))" when means below:
"3 * 100 / total number of data slices", that is 3 times of a 100% data that is distributed evenly on all the available data slices.


select database_name,table_name,dsid,ALLOCATED_BYTES,USED_BYTES,PCT_DATA_IN_DSID 
from 
(select  the_table.database as database_name,
the_table.objname table_name,
        storage.dsid,
storage.ALLOCATED_BYTES,
storage.USED_BYTES,
storage.ALLOCATED_BYTES*100/sum(ALLOCATED_BYTES) over (partition by database_name,table_name) PCT_DATA_IN_DSID -- % of total size in this data slice
  FROM  _V_OBJ_RELATION_XDB the_table LEFT OUTER JOIN _V_SYS_OBJECT_DSLICE_INFO storage 
        on (  the_table.objid = storage.tblid  and storage.tblid > 200000)) TBL
  where --DATABASE_NAME = '' and -- if want to specify database name
PCT_DATA_IN_DSID > 3*((cast(100 as decimal(5))/(select count(*) from _V_DUAL_DSLICE)))  -- data slices contain more than 3 time of perfectly distributed data for a table
and ALLOCATED_BYTES > 100*1024*1024 --100 MB
--and table_name = '' --if want to specify tables name
--and DSID=630 --if want to specify Dataslice
  order by PCT_DATA_IN_DSID;

Session Trace

Follow steps below to trace the current session or other running session:

1. Find the session info[for current session use v$mystat to get current session info]:
select a.sid, a.serial#, b.spid, a.username
from v$session a, v$process b
where a.username = '[USERNAME]'
and a.sid = [session id]
and a.PADDR = b.ADDR;

2. Start the trace[in this case event = 10046,trace level=12], trace file will be created at user_dump_dest as [DBNAME]_ora_[SPID].trc :
exec dbms_system.set_ev(&sid,&serial,10046,12,'');

3. stop the trace :
exec dbms_system.set_ev(&sid,&serial,10046,0,'');

4. convert the trace to readable format [ommit sys=no is you want sys queries in output file] :
tkprof [trace file name] [output file name] sys=no

**Trace Levels:
0 - No trace. Like switching sql_trace off.
2 - The equivalent of regular sql_trace.
4 - The same as 2, but with the addition of bind variable values.
8 - The same as 2, but with the addition of wait events.
12 - The same as 2, but with both bind variable values and wait events.

**Event Numbers:
10046: enable SQL statement timing
10053: CBO Enable optimizer trace

List of Trace Events:
https://netfiles.uiuc.edu/jstrode/www/oraparm/events.html
http://priitp.wordpress.com/oracle-11g-trace-events/

More about Trace:
http://www.oracle-base.com/articles/10g/SQLTrace10046TrcsessAndTkprof10g.php