# DColap.ctl:210:Collects OLAP Information # $Id: DColap.ctl,v 1.5 2016/04/21 13:38:48 RDA Exp $ # ARCS: $Header: /home/cvs/cvs/RDA_8/src/scripting/lib/collect/DB/DColap.ctl,v 1.5 2016/04/21 13:38:48 RDA Exp $ # # Change History # 20131105 MSC Improve code consistency. # 20160421 LVA Fix Bug 22902238. =head1 NAME DB:DColap - Collects OLAP Information =head1 DESCRIPTION This module collects OLAP-related information. The following reports can be generated and are regrouped under C: =cut if !${I_DBC/E} return echo tput('bold'),'Processing DB.OLAP module ...',tput('off') # Initialization pretoc '^1:RDBMS' pretoc '1+:OLAP' var $TOC = '%TOC%' var $TOP = '[[#Top][Back to top]]' # Load the common macros run DB:DBinfo() =head2 not_applicable - Not Applicable The OLAP module can be executed on Oracle 9i Release 2 or later only, where the OLAP option is present. =cut macro check_db {var ($typ) = @arg keep $flg if !$flg {set $sql {SELECT DECODE(COUNT(*),0,'NotEnterprise','Present') " FROM v$version " WHERE UPPER(banner) LIKE '%ENTERPRISE%'; "SELECT DECODE(COUNT(*),3,'Present','NoOlap') " FROM dba_registry " WHERE comp_id IN ('APS','XOQ','AMD') " AND status = 'VALID' " AND substr(version,1,2) in ('9.','10','11') "UNION ALL "SELECT DECODE(COUNT(*),2,'Present','NoOlap') " FROM dba_registry " WHERE comp_id IN ('APS','XOQ') " AND status = 'VALID' " AND substr(version,1,2) in ('12'); } call loadSql($sql) var $flg = true } return grepLastSql($typ) } debug ' Inside OLAP module, checking database version and OLAP presence' var $ORACLE_VERSION = get_db_version() if !match($ORACLE_VERSION,'^(92|10|11|12)') {report not_applicable write 'The OLAP module requires a database connection and can only be \ executed on 9i Release 2 or later.' toc '2:[[',getFile(),'][rda_report][Not Applicable]]' } elsif check_db('NotEnterprise') {report not_applicable write 'This is not a enterprise edition of the database' toc '2:[[',getFile(),'][rda_report][Not Applicable]]' } elsif check_db('Present') { =head2 report - OLAP Information Gathers OLAP information. =cut debug ' Inside OLAP module, getting OLAP information' report report var $TTL = '---+!! OLAP Information' var @TTL = ('',\ '---+ Components Installed',\ '---+ Analytic Workspace Attach Mode') var @HDR = ('',\ '|*Component Name*|*Status*|*Version*|',\ '|*User (SID,Serial#)*|*AW Name*|*Mode*|*Generation*|') set $sql {SELECT '|' || " comp_name || '|' || " status || '|' || " version || '|' " FROM dba_registry " WHERE comp_name LIKE '%OLAP%'; "PROMPT ___Macro_separator(2)___ "SELECT '|' || " username || ' (' || sid || ',' || serial# || ')' || '|' || " owner || '.' || daws.aw_name || '|' || " DECODE(attach_mode,'READ WRITE','RW','READ ONLY','RO',attach_mode) || " '|' || " generation || '|' " FROM dba_aws daws,v$aw_olap vawo,v$aw_calc vawc,v$session " WHERE daws.aw_number = vawo.aw_number " AND sid = vawo.session_id " AND vawc.session_id = sid " ORDER BY username,sid,daws.aw_name; } call separator(1) call writeSql($sql) call separator(0,'OLAP Information') set $sql {BREAK ON REPORT; "COLUMN siz FORMAT 999G999G990D9 "COMPUTE SUM LABEL "Total Disk:" OF siz ON REPORT; "SELECT l.owner||'.'||SUBSTR(l.table_name,4) own, " COUNT(s.segment_name) seg, " ROUND(SUM(s.bytes)/1048576,1) siz, " s.tablespace_name tsp, " l.logging " FROM dba_lobs l,dba_segments s " WHERE l.column_name = 'AWLOB' " AND l.segment_name = s.segment_name " GROUP BY l.owner,l.table_name,s.tablespace_name,l.logging " ORDER BY l.owner,l.table_name; } prefix {write '---+ Analytic Workspace Size' write '|*Analytic Workspace*| *Partitions*| *Size (in MB)*|\ *Tablespace Name*|*Logging*|' } call loadSql($sql) loop $lin (getSqlLines()) {break match($lin,'^\s*(ORA-|ERROR)') var ($tab,$seg,$siz,$tsc,$log) = split('\s+',$lin,5) if match($lin,'^Total Disk:') write "|''Total Disk:''|| ",$siz,'| ||' elsif !match($lin,'^\s*-') write '|',$tab,'| ',$seg,'| ',$siz,'|',$tsc,'|',$log,'|' } if hasOutput(true) write $TOP set $sql {BREAK ON REPORT; "COMPUTE AVG SUM MIN MAX OF use ON REPORT; "COMPUTE AVG SUM MIN MAX OF max ON REPORT; "COMPUTE AVG SUM MIN MAX OF siz ON REPORT; "COMPUTE AVG SUM MIN MAX OF cnt ON REPORT; "COMPUTE AVG MIN MAX OF hit ON REPORT; "SELECT vs.username || '(' || vs.sid || ',' || vs.serial# || ')', " ROUND(SUM(pga_used_mem)/1048576,1) use, " ROUND(SUM(pga_max_mem)/1048576,1) max, " ROUND(SUM(pool_size)/1048576,1) siz, " COUNT(aw_number) cnt, " ROUND(100*(SUM(pool_hits)/(SUM(pool_hits)+SUM(pool_misses))),1) hit " FROM v$process vp,v$session vs,v$aw_calc va,v$aw_olap vo " WHERE va.session_id = vs.sid " AND vo.session_id = va.session_id " AND addr = paddr " GROUP BY vs.username,vs.sid,vs.serial# " ORDER BY vs.username,vs.sid,vs.serial#; } prefix {write '---+ Memory Usage' write '|*User (SID,Serial#)*| *PGA Use (in MB)*| *PGA Max (in MB)*| \ *OLAP Pages (in MB)*| *AW Count*| *OLAP Hit Rate*|' } var %tbl = ('avg', "''Average''",\ 'maximum',"''Maximum''",\ 'minimum',"''Minimum''",\ 'sum', "''Sum''") call loadSql($sql) loop $lin (getSqlLines()) {break match($lin,'^\s*(ORA-|ERROR)') next match(trim($lin),'^-') var ($usr,$pus,$pmx,$psz,$cnt,$hit) = split('\s+',$lin) if length($cnt) write '|',nvl($tbl{lc($usr)},$usr),\ '| ',$pus,'| ',$pmx,'| ',$psz,'| ',$cnt,'| ',$hit,'|' } if hasOutput(true) write $TOP } else { =head2 no_olap - OLAP Components Checks if OLAP components are present and their status. =cut debug ' Inside OLAP module, getting the list of OLAP components and status' report no_olap var $TTL = '---+!! Status of OLAP components' var $TXT[1] = 'Some of the required OLAP components are not valid or are not \ found. For generating the OLAP reports, the APS, XOQ, and AMD \ (except in version 12) components must exist in a valid state.' var $HDR[1] = '|*Component ID*|*Component Name*|*Status*|*Version*|' set $sql {SELECT '|' || " comp_id || '|' || " comp_name || '|' || " status || '|' || " version || '|' " FROM dba_registry " WHERE comp_id IN ('APS','XOQ','AMD'); } call separator(1) call writeSql($sql) call separator(0,'OLAP Components') } # Disable the group title in next index if isTocCreated() toc '-:RDBMS' =head1 SEE ALSO L, L =begin credits =over 10 =item RDA 4.10: Marko Ruokonen, Harish Yagneshwar. =back =end credits =head1 COPYRIGHT NOTICE Copyright (c) 2002, 2016, Oracle and/or its affiliates. All rights reserved. =head1 TRADEMARK NOTICE Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners. =cut