Exadata Scripts and Info

select name,value/1024/1024 as mb from v$statname natural join v$mystat where name in ('cell physical IO interconnect bytes returned by smart scan', 'cell physical IO bytes saved by storage index');

select name,value byt from v$statname natural join v$mystat where name in ('cell physical IO interconnect bytes returned by smart scan', 'cell physical IO bytes saved by storage index');

select name,value byt from v$statname natural join v$sysstat where name in ('cell physical IO interconnect bytes returned by smart scan', 'cell physical IO bytes saved by storage index');

select * from (select first_name, salary, avg(salary) over (partition by department_id order by employee_id) as avg_sal from emp_lg_rnd) where salary > avg_sal


CREATE USER APP1 IDENTIFIED BY APP1 DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP;

GRANT DBA TO APP1;

sqlplus app1/APP1

create table sales as select 'Oracle Enterprise Edition' as product, mod(rownum,5) as channel_id, mod(rownum,1000) as cust_id , 5000 as amount_sold, to_date ('01.' || lpad(to_char(mod(rownum,12)+1),2,'0') || '.2010' ,'dd.mm.yyyy') as time_id from dual connect by level<=100000 ;

create table sales as select 'Oracle Enterprise Edition' as product, mod(rownum,5) as channel_id, mod(rownum,1000) as cust_id , 5000 as amount_sold, to_date ('01.' || lpad(to_char(mod(rownum,12)+1),2,'0') || '.2010' ,'dd.mm.yyyy') as time_id from dual connect by level<=100000 order by 2;

col segment_name format a30;
select bytes/1024/1024 MB, segment_name as mb from user_segments;

alter table sales nologging;
alter table sales_ord nologging;

BEGIN
FOR I IN 1..2
LOOP
insert /*+ append */ into sales select * from sales; END LOOP;
END;
/

insert /*+ append */ into sales select * from sales order by channel_id;

create table sales_ord as select * from sales order by channel_id;

select bytes/1024/1024/1024, segment_name as gb from user_segments;
exec dbms_stats.gather_table_stats('app1','SALES');
exec dbms_stats.gather_table_stats('app1','SALES_ord');

alter session set cell_offload_processing=false;
set timing on
select /* NO_SMART_SCAN */ count(*) from sales where channel_id=1;

select /* NO_SMART_SCAN */ count(*) from sales where channel_id=1;

select name,value byt from v$statname natural join v$mystat where name in ('cell physical IO interconnect bytes returned by smart scan', 'cell physical IO bytes saved by storage index');

alter session set cell_offload_processing=true;
select /* WITH_SMART_SCAN */ count(*) from sales where channel_id=1;

select /* WITH_SMART_SCAN */ count(*) from sales where channel_id=1;

select name,value byt from v$statname natural join v$mystat where name in ('cell physical IO interconnect bytes returned by smart scan', 'cell physical IO bytes saved by storage index');

select /* WITH_SMART_SCAN */ count(*) from sales_ord where channel_id=1;

select /* WITH_SMART_SCAN */ count(*) from sales_ord where channel_id=1;

select name,value byt from v$statname natural join v$mystat where name in ('cell physical IO interconnect bytes returned by smart scan', 'cell physical IO bytes saved by storage index');

select sql_id from v$sql where sql_text like '%NO_SMART_SCAN%' and sql_text not like '%like%';
select plan_table_output from table (dbms_xplan.display_cursor(‘above sql id‘);

select sql_id from v$sql where sql_text like '%WITH_SMART_SCAN%' and sql_text not like '%like%';

select plan_table_output from table (dbms_xplan.display_cursor(‘above sql id‘);

select /*+ parallel(sales) */ count(*) from sales where channel_id=1;
select sql_id from v$sql where sql_text like '%NO_SMART_SCAN%' and sql_text not like '%like%';

select plan_table_output from table (dbms_xplan.display_cursor(‘above sql id‘);

select 0.17/4 as GB_PER_SEC from dual; 


create table pba_order AS SELECT rownum pba_id, column_name, owner, table_name from (SELECT column_name, owner, table_name from dba_tab_columns where rownum <= 10000), (select rownum from dual CONNECT BY LEVEL <=1000) order by pba_id;

select name,value/1024/1024 as mb from v$statname natural join v$mystat where name in ('cell physical IO interconnect bytes returned by smart scan', 'cell physical IO bytes saved by storage index');

select sum(amount_sold) from sales;

alter session set parallel_degree_policy=auto;

alter session set parallel_degree_limit=2;

alter session set parallel_min_time_threshold=1;

set autotrace on explain

set timing on

IO calibrate : EM / DBMS_RESOURCE_MANAGER.CALIBRATE_IO procedure

alter session set parallel_degree_policy=manual;

alter session force parallel query;

select count(*) from customers;

alter session enable parallel query -- the default, no force;

alter table customers parallel -- would cause parallel query before 11gR2;

select count(*) from customers;

alter session set parallel_degree_policy=auto;

alter session set parallel_degree_limit=2;

select sum(amount_sold) from sales;

select count(1) from emp_lg_rnd;

select bytes/1024/1024 MB, segment_name as mb from user_segments;

select /*+ parallel (sales,2) */ count(1) from sales;

select /*+ parallel (emp_larger,2) */ count(1) from emp_larger;

select count(1) from sales;

select count(1) from emp_larger;

mod parallel_min_servers=2 in init file

-----------------------

Info

ps -ef| grep pmon

sqlplus / as sysasm

select * from v$asm_diskgroup

alter diskgroup fra mount

sqlplus app1/appl

set autotrace on explain;

select * from tab;

desc all_objects

create table obj_copy as select * from all_objects;

select count(1) from obj_copy;

create index idx_objid_objcpy on obj_copy(object_id);

select name,value/1024/1024 as mb  from v$statname natural join v$sysstat where name like '%cell%';

select owner, object_type, count(1) from obj_copy group by owner, object_type;

select name from v$sqlfn_metadata where OFFLOADABLE = 'YES';

/etc/oracle/oracle/rdbms/admin

alter index IDXBMP_OBJTYP visible ;

alter index IDX_OBJID_OBJCPY visible ;

alter index INDOWNEROBJTYPE visible ;

select segment_name,segment_type, sum(bytes)/1024/1024 MB from user_segments;

 select segment_name,segment_type, sum(bytes)/1024/1024 MB from user_segments group by segment_name,segment_type ;

;

declare

 v_blkcnt_cmp     pls_integer;

 v_blkcnt_uncmp   pls_integer;

 v_row_cmp        pls_integer;

 v_row_uncmp      pls_integer;

 v_cmp_ratio      number;

 v_comptype_str   varchar2(60);

begin

 dbms_compression.get_compression_ratio(

 scratchtbsname   => upper(&x),      

 ownname          => user,           

 tabname          => upper(&y),   

 partname         => NULL,           

 comptype         => dbms_compression.comp_for_query_high,    

 blkcnt_cmp       => v_blkcnt_cmp,    

 blkcnt_uncmp     => v_blkcnt_uncmp,  

 row_cmp          => v_row_cmp,    

 row_uncmp        => v_row_uncmp,  

 cmp_ratio        => v_cmp_ratio,  

 comptype_str     => v_comptype_str);

 dbms_output.put_line('Estimated Compression Ratio: '||to_char(v_cmp_ratio));

 dbms_output.put_line('Blocks used by compressed sample: '||to_char(v_blkcnt_cmp));

 dbms_output.put_line('Blocks used by uncompressed sample: '||to_char(v_blkcnt_uncmp));

end;

/