Skip to content

perf-tools linux

perf-tools linux published on Комментариев к записи perf-tools linux нет
perf-tools
looks awesome
example :
[root@nb-abushmelev perf-tools] # ./iosnoop -Q -ts -Qp 28951
Tracing block I/O issued by PID 28951. Ctrl-C to end.
STARTs          ENDs            COMM         PID    TYPE DEV      BLOCK        BYTES     LATms
439471.905014   439471.909309   ora_ckpt_orc 28951  WS   8,0      89272384     16384      4.29
439471.909341   439471.914798   ora_ckpt_orc 28951  WS   8,0      0            0          5.46
439471.914874   439471.914930   ora_ckpt_orc 28951  WS   8,0      99987520     16384      0.06
439471.914948   439471.918243   ora_ckpt_orc 28951  WS   8,0      0            0          3.30
439474.919898   439474.919962   ora_ckpt_orc 28951  WS   8,0      89272384     16384      0.06
439474.920002   439474.925042   ora_ckpt_orc 28951  WS   8,0      0            0          5.04
439474.925099   439474.925156   ora_ckpt_orc 28951  WS   8,0      99987520     16384      0.06
439474.925170   439474.927063   ora_ckpt_orc 28951  WS   8,0      0            0          1.89
439477.928636   439477.928700   ora_ckpt_orc 28951  WS   8,0      89272384     16384      0.06
439477.928717   439477.932240   ora_ckpt_orc 28951  WS   8,0      0            0          3.52
439477.932309   439477.932365   ora_ckpt_orc 28951  WS   8,0      99987520     16384      0.06
439477.932391   439477.934258   ora_ckpt_orc 28951  WS   8,0      0            0          1.87

rman catalog sbt_tape backup piece

rman catalog sbt_tape backup piece published on Комментариев к записи rman catalog sbt_tape backup piece нет
RMAN> configure channel device type 'SBT_TAPE' parms='ENV=(NB_ORA_SERVER=dc1-nb01,NB_ORA_CLIENT=DC1-DWHODI)';

new RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' PARMS  'ENV=(NB_ORA_SERVER=dc1-nb01,NB_ORA_CLIENT=DC1-DWHODI)';
new RMAN configuration parameters are successfully stored

now catalog works fine:

RMAN> catalog device type 'SBT_TAPE'  backuppiece 'al_11820_1_834711630';

allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: SID=3 device type=SBT_TAPE
channel ORA_SBT_TAPE_1: Veritas NetBackup for Oracle - Release 7.5 (2012020807)
cataloged backup piece
backup piece handle=al_11820_1_834711630 RECID=11820 STAMP=834950531

PRCD-1229 : after manual upgrade of oracle rac

PRCD-1229 : after manual upgrade of oracle rac published on 1 комментарий к записи PRCD-1229 : after manual upgrade of oracle rac

[oracle@p00xxxdb01 ~]$ srvctl status database -d p00xxx
PRCD-1027 : Failed to retrieve database p00xxx
PRCD-1229 : An attempt to access configuration of database p00xxx was rejected because its version 11.2.0.2.0 differs from the program version 11.2.0.4.0. Instead run the program from /oracle/app/base/db/11.2.0.2.
[oracle@p00xxxdb01 ~]$ srvctl upgrade database -d p00xxx -o /oracle/app/base/db/11.2.0.4
[oracle@p00xxxdb01 ~]$

Query optimization:

Query optimization: published on Комментариев к записи Query optimization: нет

awr_top2 script

18:44:12 (3)system@some_database> @awr_top2

 In |               |                |                                |     Top |        | Samp |       |   Wai |  User |  Sys |      |      |   Con |       |      |
 st | Time          | Top SQL        | Top event                      | blocker |    AAS |  les |   CPU |    ts |    IO |   IO | Conf |  Net |   cur |   App | LGWR |  RAC
--- | ------------- | -------------- | ------------------------------ | ------- | ------ | ---- | ----- | ----- | ----- | ---- | ---- | ---- | ----- | ----- | ---- | ----
  1 | 16-JUN 18:14  | c9p3ns1cusnmx  | ON CPU                         |     570 |    1.5 |   40 |    56 |     4 |     5 |    3 |    0 |    0 |     0 |     0 |    2 |    3
    | 16-JUN 18:15  | 3fp6nwg71dbna  | ON CPU                         |     570 |    1.3 |   59 |    74 |     4 |     3 |    4 |    0 |    0 |     0 |     0 |    4 |    0
    | 16-JUN 18:16  | 3fp6nwg71dbna  | ON CPU                         | <NULL>  |    1.7 |   60 |   101 |     0 |     0 |    0 |    0 |    0 |     1 |     0 |    0 |    0
    | 16-JUN 18:17  | 3fp6nwg71dbna  | ON CPU                         | <NULL>  |    1.2 |   60 |    74 |     0 |     0 |    1 |    0 |    0 |     0 |     0 |    0 |    0
    | 16-JUN 18:18  | c9p3ns1cusnmx  | ON CPU                         | <NULL>  |    1.3 |   60 |    75 |     3 |     2 |    1 |    0 |    1 |     0 |     0 |    0 |    1
    | 16-JUN 18:19  | 3fp6nwg71dbna  | ON CPU                         |     570 |    1.8 |   60 |   102 |     4 |     1 |    2 |    0 |    0 |     0 |     0 |    2 |    1
    | 16-JUN 18:20  | 3fp6nwg71dbna  | ON CPU                         |     570 |    1.3 |   60 |    75 |     2 |     0 |    5 |    0 |    0 |     0 |     0 |    2 |    0
    | 16-JUN 18:21  | <NULL>         | ON CPU                         | <NULL>  |    1.0 |   60 |    61 |     0 |     0 |    0 |    0 |    0 |     0 |     0 |    0 |    0
    | 16-JUN 18:22  | 3fp6nwg71dbna  | ON CPU                         | <NULL>  |    1.7 |   60 |    95 |     4 |     1 |    4 |    0 |    0 |     0 |     0 |    0 |    0
    | 16-JUN 18:23  | 3fp6nwg71dbna  | ON CPU                         |     570 |    1.5 |   59 |    87 |     3 |     0 |    3 |    0 |    0 |     0 |     0 |    2 |    0
    | 16-JUN 18:24  | f711myt0q6cma  | ON CPU                         |     570 |    1.1 |   60 |    64 |     1 |     0 |    2 |    0 |    0 |     0 |     0 |    1 |    0
    | 16-JUN 18:25  | 3fp6nwg71dbna  | ON CPU                         | <NULL>  |    1.2 |   60 |    72 |     0 |     0 |    0 |    0 |    0 |     0 |     0 |    0 |    0
    | 16-JUN 18:26  | 3fp6nwg71dbna  | ON CPU                         | <NULL>  |    1.6 |   60 |    92 |     1 |     0 |    2 |    0 |    0 |     0 |     0 |    0 |    1
    | 16-JUN 18:27  | 3fp6nwg71dbna  | ON CPU                         |     570 |    1.5 |   60 |    78 |     9 |     1 |    8 |    0 |    0 |     0 |     0 |    2 |    1
    | 16-JUN 18:28  | gjfr89gj2ngfu  | ON CPU                         |     570 |    1.1 |   60 |    62 |     5 |     1 |    5 |    0 |    0 |     0 |     0 |    2 |    0
    | 16-JUN 18:29  | 3fp6nwg71dbna  | ON CPU                         |     570 |    1.3 |   60 |    77 |     3 |     0 |    6 |    0 |    0 |     0 |     0 |    1 |    0
    | 16-JUN 18:30  | 3fp6nwg71dbna  | ON CPU                         |     570 |    1.4 |   59 |    80 |     2 |     0 |    2 |    0 |    0 |     0 |     0 |    2 |    1
    | 16-JUN 18:31  | dqrrfrf2d0h8q  | ON CPU                         | <NULL>  |    1.0 |   60 |    62 |     0 |     0 |    2 |    0 |    0 |     0 |     0 |    0 |    0
    | 16-JUN 18:32  | f1y8kbhh6v9sv  | ON CPU                         |     570 |    1.2 |   60 |    69 |     1 |     0 |    2 |    0 |    0 |     0 |     0 |    1 |    0
    | 16-JUN 18:33  | c9p3ns1cusnmx  | ON CPU                         |     570 |    1.4 |   60 |    75 |    10 |     3 |    5 |    0 |    0 |     0 |     0 |    4 |    2
    | 16-JUN 18:34  | cwd64da6cnm9j  | ON CPU                         | <NULL>  |    1.1 |   60 |    64 |     3 |     0 |    2 |    0 |    0 |     0 |     0 |    2 |    0
    | 16-JUN 18:35  | cwd64da6cnm9j  | ON CPU                         |     570 |    1.2 |   60 |    65 |     9 |     1 |    4 |    0 |    0 |     0 |     0 |    9 |    0
    | 16-JUN 18:36  | 5s7umwd3ppb7w  | ON CPU                         |     789 |    1.9 |   60 |    95 |    19 |     2 |    1 |    0 |    0 |     0 |     0 |    1 |   15
    | 16-JUN 18:37  | c9p3ns1cusnmx  | ON CPU                         |     570 |    1.2 |   59 |    67 |     3 |     1 |    1 |    0 |    0 |     0 |     0 |    2 |    2
    | 16-JUN 18:38  | d56fs2a7v6rkq  | ON CPU                         |     570 |    1.1 |   60 |    64 |     1 |     0 |    3 |    0 |    0 |     0 |     0 |    1 |    0
    | 16-JUN 18:39  | <NULL>         | ON CPU                         |     570 |    1.0 |   60 |    61 |     1 |     0 |    3 |    0 |    0 |     0 |     0 |    1 |    0
    | 16-JUN 18:40  | c9p3ns1cusnmx  | ON CPU                         | <NULL>  |    1.4 |   60 |    82 |     3 |     1 |    2 |    0 |    0 |     0 |     0 |    0 |    2
    | 16-JUN 18:41  | c9p3ns1cusnmx  | ON CPU                         |     570 |    1.2 |   60 |    66 |     5 |     0 |    5 |    0 |    0 |     0 |     0 |    3 |    1
    | 16-JUN 18:42  | 3fp6nwg71dbna  | ON CPU                         |     570 |    1.5 |   60 |    78 |    14 |     0 |   10 |    0 |    0 |     0 |     0 |   10 |    4
    | 16-JUN 18:43  | c9p3ns1cusnmx  | ON CPU                         |     570 |    1.1 |   60 |    63 |     3 |     0 |    7 |    0 |    0 |     0 |     0 |    3 |    0
    | 16-JUN 18:44  | c9p3ns1cusnmx  | ON CPU                         |     570 |    1.5 |   21 |    26 |     6 |     0 |    5 |    0 |    0 |     1 |     0 |    2 |    2

  3 | 16-JUN 18:14  | d3tcbyxaa65ku  | ON CPU                         | <NULL>  |    5.3 |   40 |   188 |    24 |    23 |    1 |    0 |    0 |     0 |     0 |    0 |    0
    | 16-JUN 18:15  | d3tcbyxaa65ku  | ON CPU                         | <NULL>  |    1.8 |   60 |    98 |    12 |    15 |    0 |    0 |    0 |     0 |     0 |    0 |    0
    | 16-JUN 18:16  | f5b45ajkg4rdd  | enq: TA - contention           |     854 |     .3 |   59 |    18 |     1 |     0 |    2 |    0 |    0 |     0 |     0 |    1 |    0
    | 16-JUN 18:17  | d3tcbyxaa65ku  | direct path read               |     570 |    2.3 |   60 |    81 |    59 |    52 |    2 |    0 |    0 |     0 |     0 |    4 |    1
    | 16-JUN 18:18  | d3tcbyxaa65ku  | ON CPU                         |     854 |    3.2 |   60 |   145 |    48 |    44 |    2 |    0 |    0 |     0 |     0 |    2 |    0
    | 16-JUN 18:19  | d3tcbyxaa65ku  | ON CPU                         |     854 |    3.9 |   59 |   209 |    21 |    20 |    1 |    0 |    0 |     0 |     0 |    0 |    1
    | 16-JUN 18:20  | d3tcbyxaa65ku  | ON CPU                         |     570 |    4.5 |   60 |   222 |    46 |    44 |    6 |    0 |    0 |     0 |     0 |    2 |    0
    | 16-JUN 18:21  | d3tcbyxaa65ku  | ON CPU                         |     854 |    4.6 |   60 |   245 |    30 |    30 |    0 |    0 |    0 |     0 |     0 |    0 |    0
    | 16-JUN 18:22  | d3tcbyxaa65ku  | ON CPU                         |     570 |    4.7 |   60 |   249 |    33 |    26 |    6 |    0 |    0 |     0 |     0 |    5 |    1
    | 16-JUN 18:23  | d3tcbyxaa65ku  | ON CPU                         |     854 |    4.2 |   60 |   178 |    71 |    69 |    2 |    0 |    0 |     0 |     0 |    1 |    0
    | 16-JUN 18:24  | d3tcbyxaa65ku  | ON CPU                         |     854 |    4.7 |   60 |   231 |    53 |    50 |    2 |    0 |    0 |     0 |     0 |    0 |    0
    | 16-JUN 18:25  | d3tcbyxaa65ku  | ON CPU                         | <NULL>  |    4.4 |   60 |   227 |    34 |    31 |    1 |    0 |    0 |     0 |     0 |    0 |    3
    | 16-JUN 18:26  | d3tcbyxaa65ku  | ON CPU                         |     854 |    4.5 |   59 |   228 |    37 |    36 |    2 |    0 |    0 |     0 |     0 |    1 |    0
    | 16-JUN 18:27  | d3tcbyxaa65ku  | ON CPU                         |     570 |    4.7 |   60 |   229 |    50 |    47 |    4 |    0 |    0 |     0 |     0 |    3 |    0
    | 16-JUN 18:28  | d3tcbyxaa65ku  | ON CPU                         | <NULL>  |    4.6 |   60 |   250 |    25 |    24 |    1 |    0 |    0 |     0 |     0 |    0 |    0
    | 16-JUN 18:29  | d3tcbyxaa65ku  | ON CPU                         |     854 |    4.7 |   60 |   242 |    37 |    32 |    3 |    0 |    0 |     0 |     0 |    3 |    2
    | 16-JUN 18:30  | d3tcbyxaa65ku  | ON CPU                         |     570 |    4.4 |   60 |   219 |    45 |    45 |    3 |    0 |    0 |     0 |     0 |    2 |    0
    | 16-JUN 18:31  | d3tcbyxaa65ku  | ON CPU                         |     854 |    4.8 |   60 |   259 |    27 |    26 |    0 |    0 |    0 |     0 |     0 |    0 |    1
    | 16-JUN 18:32  | d3tcbyxaa65ku  | ON CPU                         | <NULL>  |    4.9 |   60 |   249 |    44 |    43 |    0 |    0 |    0 |     0 |     0 |    0 |    1
    | 16-JUN 18:33  | d3tcbyxaa65ku  | ON CPU                         |     854 |    4.7 |   60 |   257 |    27 |    26 |    0 |    0 |    0 |     0 |     0 |    0 |    1
    | 16-JUN 18:34  | d3tcbyxaa65ku  | ON CPU                         |     854 |    4.7 |   59 |   236 |    42 |    41 |    4 |    0 |    0 |     0 |     0 |    1 |    0
    | 16-JUN 18:35  | d3tcbyxaa65ku  | ON CPU                         |     570 |    4.8 |   60 |   221 |    68 |    63 |    4 |    0 |    0 |     0 |     0 |    1 |    4
    | 16-JUN 18:36  | d3tcbyxaa65ku  | ON CPU                         |     854 |    5.5 |   60 |   280 |    47 |    42 |    1 |    0 |    0 |     0 |     0 |    0 |    4
    | 16-JUN 18:37  | d3tcbyxaa65ku  | ON CPU                         | <NULL>  |    4.7 |   60 |   257 |    24 |    24 |    2 |    0 |    0 |     0 |     0 |    0 |    0
    | 16-JUN 18:38  | d3tcbyxaa65ku  | ON CPU                         | <NULL>  |    4.5 |   60 |   244 |    26 |    26 |    0 |    0 |    0 |     0 |     0 |    0 |    0
    | 16-JUN 18:39  | d3tcbyxaa65ku  | ON CPU                         |     854 |    5.1 |   60 |   267 |    37 |    36 |    3 |    0 |    0 |     0 |     0 |    1 |    0
    | 16-JUN 18:40  | d3tcbyxaa65ku  | ON CPU                         |     570 |    4.9 |   60 |   247 |    45 |    41 |    3 |    0 |    0 |     0 |     0 |    1 |    1
    | 16-JUN 18:41  | d3tcbyxaa65ku  | ON CPU                         |     854 |    4.6 |   59 |   244 |    29 |    26 |    2 |    0 |    0 |     0 |     0 |    0 |    0
    | 16-JUN 18:42  | d3tcbyxaa65ku  | ON CPU                         |     570 |    4.4 |   60 |   196 |    69 |    56 |   16 |    0 |    0 |     0 |     0 |   10 |    2
    | 16-JUN 18:43  | d3tcbyxaa65ku  | ON CPU                         |     570 |    5.5 |   60 |   260 |    72 |    60 |   10 |    0 |    0 |     1 |     0 |    6 |    4
    | 16-JUN 18:44  | d3tcbyxaa65ku  | ON CPU                         |     854 |    4.9 |   21 |    85 |    17 |    16 |    1 |    0 |    0 |     0 |     0 |    1 |    0


62 rows selected.
18:46:33 (3)system@some_database> @plan_id d3tcbyxaa65ku

SQL_ID  d3tcbyxaa65ku, child number 2
-------------------------------------
select * from ( select row_.*, rownum rownum_ from ( select this_.id as
y0_, this_.status as y1_, this_.service as y2_, this_.request_date as
y3_, this_.response_timestamp as y4_, substr(this_.response_content, 0,
5) responseContent, this_.labels as y6_, this_.sign_subj as y7_,
this_.sign_org as y8_, this_.author as y9_ from SOME_TABLE
this_ left outer join SOME_TABLE2 authoruser1_ on
this_.author=authoruser1_.id where ((this_.author is null or
this_.author=:1  or authoruser1_.owner=:2  or this_.author in (select
user_.id as y0_ from SOME_TABLE2 user_ where user_.region=:3 )) and
this_.author is not null and this_.service in (:4 , :5 , :6 , :7 , :8 ,
:9 , :10 , :11 , :12 , :13 , :14 , :15 , :16 , :17 , :18 , :19 , :20 ,
:21 , :22 , :23 , :24 , :25 , :26 , :27 , :28 , :29 , :30 , :31 , :32 ,
:33 , :34 , :35 , :36 , :37 , :38 , :39 , :40 , :41 , :42 , :43 , :44 ,
:45 , :46 , :47 , :48 , :49 , :50 , :51 , :52 , :53 , :54 , :55 , :56 ,
:57 , :58 , :59 , :60 , :61 , :62 , :63 , :64 , :6


Plan hash value: 1496851868

-------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name                           | E-Rows | Cost (%CPU)|
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                                |        |    79 (100)|
|*  1 |  VIEW                            |                                |      2 |    79   (0)|
|*  2 |   COUNT STOPKEY                  |                                |        |            |
|   3 |    VIEW                          |                                |      2 |    79   (0)|
|*  4 |     FILTER                       |                                |        |            |
|   5 |      NESTED LOOPS OUTER          |                                |      2 |    79   (0)|
|*  6 |       TABLE ACCESS BY INDEX ROWID| SOME_TABLE                     |      2 |    77   (0)|
|   7 |        INDEX FULL SCAN DESCENDING| SOME_TABLE_REDIF_IDX           |    574K|     3   (0)|
|   8 |       TABLE ACCESS BY INDEX ROWID| SOME_TABLE2                    |      1 |     1   (0)|
|*  9 |        INDEX UNIQUE SCAN         | SYS_C00288211                  |      1 |     0   (0)|
|* 10 |      TABLE ACCESS BY INDEX ROWID | SOME_TABLE2                    |      1 |     2   (0)|
|* 11 |       INDEX UNIQUE SCAN          | SYS_C00288211                  |      1 |     1   (0)|
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("ROWNUM_">:199)
   2 - filter(ROWNUM<=:198)
   4 - filter(("SYS_ALIAS_1"."AUTHOR"=:1 OR "AUTHORUSER1_"."OWNER"=:2 OR  IS NOT NULL))
   6 - filter(("SYS_ALIAS_1"."AUTHOR" IS NOT NULL AND
              SYS_EXTRACT_UTC("LAST_UPDATED")>SYS_EXTRACT_UTC(:197) AND
              INTERNAL_FUNCTION("SYS_ALIAS_1"."SERVICE") AND "SYS_ALIAS_1"."PGU_CONV_ID" IS NULL))
   9 - access("SYS_ALIAS_1"."AUTHOR"="AUTHORUSER1_"."ID")
  10 - filter("USER_"."REGION"=:3)
  11 - access("USER_"."ID"=:B1)

 (3)system@some_database> l
  1* select * from table (dbms_xplan.display_cursor('&1',NULL,'ALLSTATS PREDICATE COST LAST +ALLSTATS' ))
18:44:21 (3)system@some_database> @http://blog.tanelpoder.com/files/scripts/sqlid.sql d3tcbyxaa65ku % %
Show SQL text, child cursors and execution stats for SQLID d3tcbyxaa65ku child %
old   9:        sql_id = ('&1')
new   9:        sql_id = ('d3tcbyxaa65ku')
old  10: and child_number like '&2'
new  10: and child_number like '%'

HASH_VALUE | PLAN_HASH_VALUE |  CH# | SQL_TEXT
---------- | --------------- | ---- | ------------------------------------------------------------------------------------------------------------------------------------------------------
1419974234 |      1496851868 |    2 | select * from ( select row_.*, rownum rownum_ from ( select this_.id as y0_, this_.status as y1_, this_.service as y2_, this_.request_date as y3_,
           |                 |      | this_.response_timestamp as y4_, substr(this_.response_content, 0, 5) responseContent, this_.labels as y6_, this_.sign_subj as y7_, this_.sign_org as
           |                 |      | y8_, this_.author as y9_ from SOME_TABLE this_ left outer join SOME_TABLE2 authoruser1_ on this_.author=authoruser1_.id where ((this_.author
           |                 |      | is null or this_.author=:1  or authoruser1_.owner=:2  or this_.author in (select user_.id as y0_ from SOME_TABLE2 user_ where user_.region=:3 )) and
           |                 |      | this_.author is not null and this_.service in (:4 , :5 , :6 , :7 , :8 , :9 , :10 , :11 , :12 , :13 , :14 , :15 , :16 , :17 , :18 , :19 , :20 , :21 ,
           |                 |      | :22 , :23 , :24 , :25 , :26 , :27 , :28 , :29 , :30 , :31 , :32 , :33 , :34 , :35 , :36 , :37 , :38 , :39 , :40 , :41 , :42 , :43 , :44 , :45 , :46 ,
           |                 |      | :47 , :48 , :49 , :50 , :51 , :52 , :53 , :54 , :55 , :56 , :57 , :58 , :59 , :60 , :61 , :62 , :63 , :64 , :6



 CH# | SQL_ID        | PARENT_HANDLE    | OBJECT_HANDLE    |  PLAN_HASH |     PARSES |   H_PARSES | EXECUTIONS |    FETCHES | ROWS_PROCESSED | ROWS_PER_FETCH |    CPU_SEC | CPU_SEC_EXEC |    ELA_SEC |       LIOS |       PIOS |      SORTS | USERS_EXECUTING | B_Sens | B_Aware
---- | ------------- | ---------------- | ---------------- | ---------- | ---------- | ---------- | ---------- | ---------- | -------------- | -------------- | ---------- | ------------ | ---------- | ---------- | ---------- | ---------- | --------------- | ------ | -------
   2 | d3tcbyxaa65ku | 00000004A07EB2B8 | 0000000555482BF8 | 1496851868 |      18565 |          2 |      18566 |     541935 |        5415140 |     9.99223154 | 81859.0228 |   4.40908234 | 145650.218 | 3694696752 |  244581883 |          0 |               5 | N      | N

Elapsed: 00:00:00.14

ASH_SQLMON

18:44:29 (3)system@some_database> @ash_sqlmon d3tcbyxaa65ku 1496851868 %
old   8:    where sql_id = '&1'
new   8:    where sql_id = 'd3tcbyxaa65ku'
old   9:      and sql_plan_hash_value = nvl('&2', sql_plan_hash_value)
new   9:      and sql_plan_hash_value = nvl('1496851868', sql_plan_hash_value)
old  10:    -- and NVL(sql_exec_id, 0) = nvl('&3', NVL(sql_exec_id, 0))
new  10:    -- and NVL(sql_exec_id, 0) = nvl('%', NVL(sql_exec_id, 0))

        ID | PLAN_OPERATION                                                                   | OBJECT_OWNER         | OBJECT_NAME                    | WAIT_PROFILE
---------- | -------------------------------------------------------------------------------- | -------------------- | ------------------------------ | --------------------------------------------------------------------------------
         0 |   SELECT STATEMENT                                                               | <NULL>               | <NULL>                         | ON CPU(351); ON CPU(135);
         1 |     VIEW                                                                         | <NULL>               | <NULL>                         | ON CPU(97);
         2 |       COUNT STOPKEY                                                              | <NULL>               | <NULL>                         | ON CPU(2);
         3 |         VIEW                                                                     | <NULL>               | <NULL>                         | ON CPU(29901); direct path read(26326); buffer exterminate(1);
         4 |           FILTER                                                                 | <NULL>               | <NULL>                         | ON CPU(1464); latch: cache buffers chains(2);
         5 |             NESTED LOOPS OUTER                                                   | <NULL>               | <NULL>                         | ON CPU(63);
         6 |               TABLE ACCESS BY INDEX ROWID                                        | some_user            | SOME_TABLE                     | ON CPU(7201); db file sequential read(67); read by other session(54); gc buffer
           |                                                                                  |                      |                                | busy acquire(4);
         7 |                 INDEX FULL SCAN DESCENDING                                       | some_user            | SOME_TABLE_REDIF_IDX           | ON CPU(642); read by other session(6); db file sequential read(4); gc buffer bus
           |                                                                                  |                      |                                | y acquire(1);

         8 |               TABLE ACCESS BY INDEX ROWID                                        | some_user            | SOME_TABLE2                    | ON CPU(1403);
         9 |                 INDEX UNIQUE SCAN                                                | some_user            | SYS_C00288211                  | ON CPU(2507);
        10 |             TABLE ACCESS BY INDEX ROWID                                          | some_user            | SOME_TABLE2                    | ON CPU(473);
        11 |               INDEX UNIQUE SCAN                                                  | some_user            | SYS_C00288211                  | ON CPU(1239); latch: cache buffers chains(5);

look at plan line 3 in ash:


(3)system@some_database> @ash_obj d3tcbyxaa65ku  1496851868 sysdate-1/24 sysdate
old   4:   where a.sql_id='&1' and a.sql_plan_hash_value='&2' and a.SAMPLE_TIME between &3 and nvl(&4,sysdate)
new   4:   where a.sql_id='d3tcbyxaa65ku' and a.sql_plan_hash_value='1496851868' and a.SAMPLE_TIME between sysdate-1/24 and nvl(sysdate,sysdate)

  COUNT(*) | SQL_PLAN_LINE_ID | CURRENT_OBJ# | OBJECT_NAME                    | OWNER                          | SESSION | Event
---------- | ---------------- | ------------ | ------------------------------ | ------------------------------ | ------- | ----------------------------------------
      6366 |                3 |       669478 | SYS_LOB0000669470C00005$$      | some_user                        | ON CPU  | <NULL>
      2446 |                3 |       669478 | SYS_LOB0000669470C00005$$      | some_user                        | WAITING | direct path read
      1511 |                6 |       669478 | SYS_LOB0000669470C00005$$      | some_user                        | ON CPU  | <NULL>
       474 |                9 |       669478 | SYS_LOB0000669470C00005$$      | some_user                        | ON CPU  | <NULL>
       298 |                8 |       669478 | SYS_LOB0000669470C00005$$      | some_user                        | ON CPU  | <NULL>
       290 |                4 |       669478 | SYS_LOB0000669470C00005$$      | some_user                        | ON CPU  | <NULL>
       212 |               11 |       669478 | SYS_LOB0000669470C00005$$      | some_user                        | ON CPU  | <NULL>
       140 |                7 |       669478 | SYS_LOB0000669470C00005$$      | some_user                        | ON CPU  | <NULL>
        95 |               10 |       669478 | SYS_LOB0000669470C00005$$      | some_user                        | ON CPU  | <NULL>
        72 | <NULL>           |       669478 | SYS_LOB0000669470C00005$$      | some_user                        | ON CPU  | <NULL>
        25 |                0 |       669478 | SYS_LOB0000669470C00005$$      | some_user                        | ON CPU  | <NULL>
        13 |                1 |       669478 | SYS_LOB0000669470C00005$$      | some_user                        | ON CPU  | <NULL>
        11 |                5 |       669478 | SYS_LOB0000669470C00005$$      | some_user                        | ON CPU  | <NULL>
         2 |                4 |       669478 | SYS_LOB0000669470C00005$$      | some_user                        | WAITING | latch: cache buffers chains
         1 |                2 |       669478 | SYS_LOB0000669470C00005$$      | some_user                        | ON CPU  | <NULL>

 (3)system@some_database> l
  1  select count(*),a.SQL_PLAN_LINE_ID,current_obj#,o.object_name,o.owner,session_state,event
  2    from gv$active_session_history a
  3       left join dba_objects o on a.current_obj#=o.object_id
  4    where a.sql_id='&1' and a.sql_plan_hash_value='&2' and a.SAMPLE_TIME between &3 and nvl(&4,sysdate)
  5    group by a.SQL_PLAN_LINE_ID,a.current_obj#,o.object_name,o.owner,session_state,event
  6*   order by 1 desc

select * from ( select row_.*, rownum rownum_ from ( select this_.id as y0_, this_.status as y1_, this_.service as y2_, this_.request_date as y3_, this_.response_timestamp as y4_, 
substr(this_.response_content, 0, 5) responseContent,
 this_.labels as y6_, this_.sign_subj as y7_, this_.sign_org as y8_, this_.author as y9_ from adp_conversation_out this_ left outer join adp_user authoruser1_ on this_.author=authoruser1_.id where ((this_.author is null or this_.author=:1 or authoruser1_.owner=:2 or this_.author in (select user_.id as y0_ from adp_user user_ where user_.region=:3 )) and this_.author is not null and this_.service in (:4 , :5 , :6 , :7 , :8 , :9 , :10 , :11 , :12 , :13 , :14 , :15 , :16 , :17 , :18 , :19 , :20 , :21 , :22 , :23 , :24 , :25 , :26 , :27 , :28 , :29 , :30 , :31 , :32 , :33 , :34 , :35 , :36 , :37 , :38 , :39 , :40 , :41 , :42 , :43 , :44 , :45 , :46 , :47 , :48 , :49 , :50 , :51 , :52 , :53 , :54 , :55 , :56 , :57 , :58 , :59 , :60 , :61 , :62 , :63 , :64 , :65 , :66 , :67 , :68 , :69 , :70 , :71 , :72 , :73 , :74 , :75 , :76 , :77 , :78 , :79 , :80 , :81 , :82 , :83 , :84 , :85 , :86 , :87 , :88 , :89 , :90 , :91 , :92 , :93 , :94 , :95 , :96 , :97 , :98 , :99 , :100 , :101 , :102 , :103 , :104 , :105 , :106 , :107 , :108 , :109 , :110 , :111 , :112 , :113 , :114 , :115 , :116 , :117 , :118 , :119 , :120 , :121 , :122 , :123 , :124 , :125 , :126 , :127 , :128 , :129 , :130 , :131 , :132 , :133 , :134 , :135 , :136 , :137 , :138 , :139 , :140 , :141 , :142 , :143 , :144 , :145 , :146 , :147 , :148 , :149 , :150 , :151 , :152 , :153 , :154 , :155 , :156 , :157 , :158 , :159 , :160 , :161 , :162 , :163 , :164 , :165 , :166 , :167 , :168 , :169 , :170 , :171 , :172 , :173 , :174 , :175 , :176 , :177 , :178 , :179 , :180 , :181 , :182 , :183 , :184 , :185 , :186 , :187 , :188 , :189 , :190 , :191 , :192 , :193 , :194 , :195 , :196 )) and this_.pgu_conv_id is null and this_.last_updated>:197 order by y3_ desc ) row_ where rownum <= :198 ) where rownum_ > :19

this substr(this_.response_content, 0, 5) query high compressed clob field

after talking with the developers on the subject, why would they need these first 5 characters, they agreed to remove them from the query. After editing query the server load is 3 times lower

oracle install to do

oracle install to do published on Комментариев к записи oracle install to do нет
groupadd --gid 54321 oinstall;
groupadd --gid 54322 dba;
groupadd --gid 54323 asmdba;
groupadd --gid 54324 asmoper;
groupadd --gid 54325 asmadmin;
groupadd --gid 54326 oper;
groupadd --gid 54327 backupdba;
groupadd --gid 54328 dgdba;
groupadd --gid 54329 kmdba;
useradd --uid 54321 --gid oinstall --groups 'dba,oper,asmdba,asmoper,backupdba,dgdba,kmdba' oracle;
useradd --uid 54322 --gid oinstall --groups 'dba,asmadmin,asmdba,asmoper' grid;
## set default passwords for these users?
echo  -e "aNYlbpdQKzBacjOg" | (passwd --stdin grid)
echo  -e "aNYlbpdQKzBacjOg" | (passwd --stdin oracle)

On Linux, Automatic Storage Management uses asynchronous Input-Output by default.
if database files are not on asm:

alter system set filesystemio_options=asynch scope=spfile;
alter system set disk_asynch_io=true scope=spfile; 
effect of disk async onoff:

async_off2
async_off1
async_off

async_on1
async_on3
async_on2

[свернуть]

save a lot of time later, need for logminer:

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA; 
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS; 
alter system set resumable_timeout=3600;
alter system set ddl_lock_timeout=60;

setup awr snapshot retention:

in case with ADG: Installing and Using Standby Statspack in 11g (Doc ID 454848.1)

Primary Sidebar

Яндекс.Метрика