Home PC Games Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ Shell script to crawl through AWR SQL Report Problems     - Memory leak analysis using Android studio (Programming)

- Linux system security infrastructure Highlights (Linux)

- Easy to get hidden administrator account (Linux)

- Shell for loop (Programming)

- ORA-00824: can not set sga_target due to existing problem-solving (Database)

- shellinabox: one uses AJAX Web-based terminal emulator (Linux)

- Tab set to four spaces in Vim (Linux)

- Physical backup and recovery SYSTEM table space (Database)

- How Ubuntu Snappy Core 2 running on Raspberry Pi (Linux)

- The principle Httpclient4.4 (execution request) (Programming)

- How to write a new Git protocol (Linux)

- linux system optimization and security configuration (Linux)

- PostgreSQL procedural language learning (Database)

- How to Install Winusb in Ubuntu 14.04 (Linux)

- To install and use the Doxygen under Linux (Linux)

- MySQL optimization of the relevant Group By (Database)

- Linux use iptables ban Ping (Linux)

- Eclipse, Tomcat configuration JNDI connection Oracle data source example (Server)

- ORA-14400: inserted partition key does not map to any partition (Database)

- iSCSI + multipath achieve DM multipath storage (Server)

 
         
  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:      
 
- Repair Raspbian Encountered a section with no Package (Linux)
- Fedora 20 users install the Mate 1.8 desktop (Linux)
- Linux server Php injection prevention (Linux)
- Oracle user lock how to know what causes (Database)
- Tomcat configuration memory and remote debug port (Server)
- systemd Power Management (Linux)
- xCAT deployment (Linux)
- LogStash log analysis display system (Linux)
- Lenovo E431 notebook CentOS system is installed wireless network card driver (Linux)
- Merge Sort (Programming)
- How VirtualBox and VMware virtual machine conversion (Linux)
- Learning UNIX good habits (Linux)
- Create and modify Oracle temporary table space (Database)
- Python implementation Bursa transition model (Programming)
- php How to prevent being injected (Linux)
- Oracle 11g 10g induced into error (Database)
- Python image processing library (PIL) to install and simple to use (Linux)
- To install Redis under Linux (Database)
- CentOS6.6 ordinary users to use sudo command to borrow root user privileges (Linux)
- Python Basics: Search Path (Programming)
     
           
     
  CopyRight 2002-2022 newfreesoft.com, All Rights Reserved.