Home PC Games Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ Shell script to crawl through AWR SQL Report Problems     - Use Oracle Data Guard to complete cross-platform database migration cases (Database)

- MySQL remote connection settings (Database)

- How to install and configure a VNC server on CentOS 7.0 (Server)

- Linux create and delete user step (Linux)

- Struts2 configure a static resource files without Struts processing (regular match) (Programming)

- How to install CentOS 7.x in OpenERP (Odoo) (Linux)

- Android Studio Clear Project or Rebuild Project Error (Linux)

- Linux --- file descriptors and redirection (Linux)

- Java loop list to solve the problem of Joseph ring (Programming)

- Build ftp server under CentOS 6.5 (Server)

- Port Telnet command to detect the remote host is turned on (Linux)

- Windows using SSH management Ubuntu (Linux)

- How to install PlayOnLinux 4.2.5 under Ubuntu 14.04 / 12.04 (Linux)

- The Linux-based security settings Ipchains Firewall (Linux)

- JavaScript event handling Detailed (Programming)

- Why I do not like the Go language style interface (ie Structural Typing) (Programming)

- Use preseed script to install Ubuntu 14.04 (Linux)

- Using Python multithreaded mistakes summary (Programming)

- Python variable type (Programming)

- MySQL backup tool to back up mydumper (Database)

 
         
  Shell script to crawl through AWR SQL Report Problems
     
  Add Date : 2018-11-21      
         
         
         
  Awr report sql detail some of the basic part of the must-see, especially SQL Order by Elapsed time this section, can clearly see what sql statement taking up more DB time, the proportion. This can serve as an important reference when tuning, which can be targeted to run sql need particular attention.

For instance, we got a awr report, Elapsed time the contents of this section are as follows. As is apparent sql_id as dfb15m5s2uwmc of sql need special attention, because it takes up nearly half of DB time.

Elapsed Time (s) Executions per Exec (s)% Total% CPU% IO SQL Id SQL Module SQL Text
3,601.90 0 48.52 99.77 0.16 dfb15m5s2uwmc JDBC Thin Client SELECT: 1, machinecode, cn, co ...
1,612.04 21 76.76 21.71 99.97 0.00 8tmf11fvxy09j JDBC Thin Client SELECT ROUND (AVG (SUM (END_TIME ...
1,593.80 20 79.69 21.47 99.97 0.00 cy55p6nrd31db JDBC Thin Client SELECT MAX (USER_CLASS) FROM S ...
298.34 20 14.92 4.02 99.96 0.00 29tdwfv5d9s4f JDBC Thin Client SELECT NVL (SUM (OTAL), ...
Awr feature set provided is complete, if we are able to more quickly locate out which sql taking up more DB time to go and do not always generate a awr report, in fact, can be achieved, we can customize.

Part log is as follows:
.. Exported "SYS". "WRH $ _SQL_PLAN" 432.1 KB 1089 rows
 .. Exported "SYS" "WRH $ _LATCH":. "WRH $ _LATCH_3645037571_0" 198.6 KB 3871 rows
 .. Exported "SYS". "WRH $ _SYSMETRIC_HISTORY" 180.1 KB 3600 rows

You can see awr base table is wrh $ beginning, and we used the data dictionary tables are closely related.
 For example sqlstat, data dictionary of historical data can be put wrh $ transposition dba_hist.
 Our view dba_hist_sqlstat base table, in fact, such a finding is wrh $ base table.
$ Ksh showdict.sh DBA_HIST_SQLSTAT
 object_details
 OWNER OBJECT_ID DATA_OBJECT_ID OBJECT_NAME OBJECT_TYPE
 ------------------------------ ---------- ---------- ---- ------------------------------ ---------------- ---
 SYS 9409 DBA_HIST_SQLSTAT VIEW
 PUBLIC 9410 DBA_HIST_SQLSTAT SYNONYM
 synonym_details
 OWNER SYNONYM_NAME
 ------------------------------ -------------------- ----------
 PUBLIC DBA_HIST_SQLSTAT

 view_details
 VIEW_NAME TEXT
 ------------------------------ -------------------- -------------------------------------------------- ----------
 DBA_HIST_SQLSTAT select sql.snap_id, sql.dbid, sql.instance_number,
                                xxxx from WRM $ _SNAPSHOT sn, WRH $ _SQLSTAT sql
                                  where sn.snap_id = sql.snap_id
                                        and sn.dbid = sql.dbid
                                        and sn.instance_number = sql.instance_number
                                        and sn.status = 0

So we can see directly from the data dictionary table history information they need to go and do not always regenerate a awr report.
 Of course, the process of implementation also slightly With some difficulty, the script a little processing, it becomes a shell version.
sqlplus -s $ DB_CONN_STR @ $ SH_DB_SID < < EOF
 break on db_name
 set pages 50
 set linesize 100
 col elapsed_time format a10
 col per_total format a10
 prompt
 prompt Current Instance
 prompt ~~~~~~~~~~~~~~~~
 select d.dbid dbid
      , D.name db_name
      , I.instance_number inst_num
      , I.instance_name inst_name
  from v \ $ database d,
        v \ $ instance i;
 select snap_id, sql_id, EXECUTIONS_DELTA, max_elapsed elapsed_time, per_total || '%' per_total from
 (Select distinct snap_id, sql_id, EXECUTIONS_DELTA, trunc (max (ELAPSED_TIME_DELTA) OVER (PARTITION BY snap_id, sql_id) / 1000000,0) || 's' max_elapsed,
  trunc ((max (ELAPSED_TIME_DELTA) OVER (PARTITION BY snap_id, sql_id)) / (SUM (ELAPSED_TIME_DELTA) OVER (PARTITION BY snap_id)), 2) * 100 per_total
  from dba_hist_sqlstat where snap_id = $ 1
  order by 5 desc
 ) Where rownum < = 5;


Content output results are as follows, and html format compared, it is still very clear, only need to enter the number to the end of the snapshot.
$ Ksh showsnapsql.sh 57584
 Current Instance
 ~~~~~~~~~~~~~~~~
      DBID DB_NAME INST_NUM INST_NAME
 ---------- --------- ---------- ----------------
 2534640677 TESTDB2 1 xxxxx

    SNAP_ID SQL_ID EXECUTIONS_DELTA ELAPSED_TI PER_TOTAL
 ---------- ------------- ---------------- ------------ ---------
      57494 dfb15m5s2uwmc 0 3601s 48%
      57494 8tmf11fvxy09j 21 1612s 21%
      57494 cy55p6nrd31db 20 1593s 21%
      57494 29tdwfv5d9s4f 20 298s 4%
      57494 c7k4g2urpu1sc 0 175s 2%
This time you can easily grab the problem sql, directly to determine whether more information is needed.
     
         
         
         
  More:      
 
- Android Studio Personalization (Linux)
- To modify the existing user ID and comments GPG key (Linux)
- CentOS source installation GitLab Chinese Version (Server)
- C ++ Supplements - Smart Pointers (Programming)
- PHP 7.0 Upgrade Notes (Linux)
- Linux package management operations Basic entry (Linux)
- The most concise explanation of JavaScript closures (Programming)
- Android LayoutInflater source parsing (Programming)
- iptables using summary (Linux)
- OpenSUSE 13.1 OpenSUSE 12.3 and how to install Cinnamon 2.2 Desktop (Linux)
- How to install and use the Snort in Ubuntu 15.04 (Linux)
- iSCSI + multipath achieve DM multipath storage (Server)
- Installation of Python2.7.10 under CentOS 6.4 (Linux)
- Standardized UNIX command-line tool (Linux)
- To setup NOTRACK and TRACK of conntrack in iptables (Linux)
- Debian 7.6 install Nvidia graphics driver (Linux)
- Linux server remote user to obtain root privileges prevention (Linux)
- Ruby and Python simple comparison (Programming)
- Python Basics: Search Path (Programming)
- Oracle Client + PL SQL Developer enables remote access to the Oracle database (Database)
     
           
     
  CopyRight 2002-2022 newfreesoft.com, All Rights Reserved.