tanelpoder / tpt-oracle Goto Github PK

Hello, i have two instance on the same database server (hub and img). My database version in 19.8

For some reason i am getting these error when i execute the snapper.sql on my hub instance. When i execute the same script on my other instance(img) it executes perfect.

Details of the error.

sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Sep 10 14:08:10 2020
Version 19.8.0.0.0

Copyright (c) 1982, 2020, Oracle. All rights reserved.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.8.0.0.0

SQL> @snapper.sql ash 5 1 all
Sampling SID all with interval 5 seconds, taking 1 snapshots...

-- Session Snapper v4.31 - by Tanel Poder ( http://blog.tanelpoder.com/snapper ) - Enjoy the Most Advanced Oracle Troubleshooting Script on the Planet! :)

ActSes %Thread | INST | SQL_ID | SQL_CHILD | EVENT | WAIT_CLASS

declare
*
ERROR at line 1:
ORA-20000: Snapper: Probably bad syntax or no execute rights on SYS.DBMS_LOCK
Check http://blog.tanelpoder.com/snapper for instructions
ORA-00979: not a GROUP BY expression
Stack Trace:
ORA-06512: at line 1206
ORA-06512: at line 1206
ORA-06512: at line 1789
ORA-06512: at line 1814

I already give the sys.dbms_lock privileges and i check these privileges with my working instance.

Any idea what could be? Maybe another privileges? I execute these script with sys, system and i get the same error.

Thanks,

Due to wrong Column Header. This should be enclosed in ". I can provide a pull request

[oracle@cltsadm01vm01 tpt-oracle-master]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Wed Jun 3 13:35:47 2020

Copyright (c) 1982, 2016, Oracle. All rights reserved.

Connected to:
Oracle Database 12c EE Extreme Perf Release 12.2.0.1.0 - 64bit Production

SQL> alter session set container=PRD605
2 /

Session altered.

SQL> @latchprof sid,name % % 1000000

-- LatchProf 2.10 by Tanel Poder ( https://blog.tanelpoder.com )
old 5: &_lhp_what
new 5: sid,name
old 8: , COUNT() / &_lhp_samples total_samples_pct
new 8: , COUNT() / 1000000 total_samples_pct
old 10: (SELECT /*+ NO_MERGE / 1 FROM DUAL CONNECT BY LEVEL <= &_lhp_samples) s,
new 10: (SELECT /+ NO_MERGE */ 1 FROM DUAL CONNECT BY LEVEL <= 1000000) s,
old 20: l.sid LIKE '&_lhp_sid'
new 20: l.sid LIKE '%'
old 21: AND (LOWER(l.name) LIKE LOWER('%&_lhp_name%') OR LOWER(RAWTOHEX(l.laddr)) LIKE LOWER('%&_lhp_name%'))
new 21: AND (LOWER(l.name) LIKE LOWER('%%%') OR LOWER(RAWTOHEX(l.laddr)) LIKE LOWER('%%%'))
old 24: &_lhp_what
new 24: sid,name
old 30: &_lhp_what
new 30: sid,name
old 34: , s.total_samples / &_lhp_samples * 100 latchprof_pct_total_samples
new 34: , s.total_samples / 1000000 * 100 latchprof_pct_total_samples
old 35: , (t2.hsecs - t1.hsecs) * 10 * s.total_samples / &_lhp_samples latchprof_total_ms
new 35: , (t2.hsecs - t1.hsecs) * 10 * s.total_samples / 1000000 latchprof_total_ms
old 37: , (t2.hsecs - t1.hsecs) * 10 * s.total_samples / dist_samples / &_lhp_samples latchprof_avg_ms
new 37: , (t2.hsecs - t1.hsecs) * 10 * s.total_samples / dist_samples / 1000000 latchprof_avg_ms
t1,
*
ERROR at line 39:
ORA-65011: Pluggable database does not exist.

SQL> exit
Disconnected from Oracle Database 12c EE Extreme Perf Release 12.2.0.1.0 - 64bit Production
[oracle@cltsadm01vm01 tpt-oracle-master]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Wed Jun 3 13:40:18 2020

Copyright (c) 1982, 2016, Oracle. All rights reserved.

Connected to:
Oracle Database 12c EE Extreme Perf Release 12.2.0.1.0 - 64bit Production

SQL> @latchprof sid,name % % 1000000

-- LatchProf 2.10 by Tanel Poder ( https://blog.tanelpoder.com )
old 5: &_lhp_what
new 5: sid,name
old 8: , COUNT() / &_lhp_samples total_samples_pct
new 8: , COUNT() / 1000000 total_samples_pct
old 10: (SELECT /*+ NO_MERGE / 1 FROM DUAL CONNECT BY LEVEL <= &_lhp_samples) s,
new 10: (SELECT /+ NO_MERGE */ 1 FROM DUAL CONNECT BY LEVEL <= 1000000) s,
old 20: l.sid LIKE '&_lhp_sid'
new 20: l.sid LIKE '%'
old 21: AND (LOWER(l.name) LIKE LOWER('%&_lhp_name%') OR LOWER(RAWTOHEX(l.laddr)) LIKE LOWER('%&_lhp_name%'))
new 21: AND (LOWER(l.name) LIKE LOWER('%%%') OR LOWER(RAWTOHEX(l.laddr)) LIKE LOWER('%%%'))
old 24: &_lhp_what
new 24: sid,name
old 30: &_lhp_what
new 30: sid,name
old 34: , s.total_samples / &_lhp_samples * 100 latchprof_pct_total_samples
new 34: , s.total_samples / 1000000 * 100 latchprof_pct_total_samples
old 35: , (t2.hsecs - t1.hsecs) * 10 * s.total_samples / &_lhp_samples latchprof_total_ms
new 35: , (t2.hsecs - t1.hsecs) * 10 * s.total_samples / 1000000 latchprof_total_ms
old 37: , (t2.hsecs - t1.hsecs) * 10 * s.total_samples / dist_samples / &_lhp_samples latchprof_avg_ms
new 37: , (t2.hsecs - t1.hsecs) * 10 * s.total_samples / dist_samples / 1000000 latchprof_avg_ms
t1,
*
ERROR at line 39:
ORA-65011: Pluggable database does not exist.

SQL>
SQL> show pdbs

CON_ID CON_NAME                       OPEN MODE  RESTRICTED

     2 PDB$SEED                       READ ONLY  NO
     3 IBMSPDB1                       READ WRITE NO
     4 PRD605                         READ WRITE NO

SQL>

I want to collect information of a over-all wait event, such as "parallel log write", How could I do it?
sincerely Yours Scott Yao China

Hello,
I am getting below erorr while using dstackprof on

Linux 4.1.12-124.46.4.1.el7uek.x86_64 #2 SMP x86_64 x86_64 x86_64 GNU/Linux
with root user. Not sure where to start looking for workaround. Can I get help please.

DStackProf v1.02 by Tanel Poder ( http://www.tanelpoder.com )
Sampling pid 26264 for 5.000 seconds with stack depth of 100 frames...

dtrace: invalid probe specifier
profile-1001
/pid == $target/ {
    @u[ustack(100)] = count();
    @k[stack(100)]  = count();
}
tick-1sec
/i++ >= 5.001/ {
    exit(0);
}
END {
    printa(@u);
    printa(@k);
}
: floating-point constants are not permitted
0 samples with stack below
__________________

0 Total samples captured

v$active_session_history and v$sql_plan are not aged out in a coordinated way. Multiple times I had to join to dba_hist_sql_plan.

While running snapper, an error may be raised if grants on executing DBMS_LOCK are missing and DBAs do not agree to provide such grants.

ERROR at line 904:
ORA-06550: line 1672, column 10:
PLS-00201: identifier 'DBMS_LOCK' must be declared
ORA-06550: line 1672, column 10:
PL/SQL: Statement ignored
ORA-06550: line 1675, column 6:
PLS-00201: identifier 'DBMS_LOCK' must be declared
ORA-06550: line 1675, column 6:
PL/SQL: Statement ignored

I suggest to create an internal SLEEP() procedure as a wrapper for the conditional execution of either DBMS_SESSION.SLEEP or DBMS_LOCK.SLEEP, depending on the underlying Oracle version.

在RAC环境中，无法正确执行curlog.sql脚本，建议修改如下
select member from v$logfile where group# in (select group# from v$log where status = 'CURRENT');
即将“=”修改为"in"

df 和dfm 无法临时表空间的空间使用率信息，一直显示%100，建议脚本修改如下
select t.tablespace_name, t.gb "TotalGB", t.gb - nvl(f.gb,0) "UsedGB", nvl(f.gb,0) "FreeGB"
,lpad(ceil((1-nvl(f.gb,0)/decode(t.gb,0,1,t.gb))100)||'%', 6) "% Used", t.ext "Ext",
'|'||rpad(lpad('#',ceil((1-nvl(f.gb,0)/decode(t.gb,0,1,t.gb))20),'#'),20,' ')||'|' "Used"
from (
select tablespace_name, trunc(sum(bytes)/(102410241024)) gb
from dba_free_space
group by tablespace_name
union all
select tablespace_name, trunc(sum(free_blocks)8192/(102410241024)) gb
from v$sort_segment
group by tablespace_name
) f, (
select tablespace_name, trunc(sum(bytes)/(102410241024)) gb, max(autoextensible) ext
from dba_data_files
group by tablespace_name
union all
select tablespace_name, trunc(sum(bytes)/(10241024*1024)) gb, max(autoextensible) ext
from dba_temp_files
group by tablespace_name
) t
where t.tablespace_name = f.tablespace_name (+)
order by t.tablespace_name;

Do you agree to adding the @init_custom call at the end of init.sql (just before termout on)? init_custom.sql itself wouldn't be delivered by you. Customers could rather optionally create it to preserve their own preferences after a new deployment. I intentionally specified the single @ to allow multiple users with different preferences have their own init_custom.sql in their search path.

ti.sql presently depends on init.sql, because it needs the initial value of &_ti_sequence. Would it make sense then to explicitly call init.sql within ti.sql, as the script hangs if we forget to do that?

Alternatively, I think I could come up with a solution that doesn't need any initialization. The idea is to generate a new value for tracefile_identifier based on the parsed current value, as opposed to maintaining a counter in a sqlplus variable.

What do you think?

I suggest to make some adjustments to columns sizes in asqlmon.sql and dasqlmon.sql and could provide a pull request

happy to do anything i can to help troubleshoot this. the following runs without an issue on 12.2.0.2:

SQL> @snapper ash,stats 5 1 all
Sampling SID all with interval 5 seconds, taking 1 snapshots...
and s.why0+s.why1+s.why2 > 0
*
ERROR at line 930:
ORA-06550: line 930, column 25:
PL/SQL: ORA-00904: "S"."WHY2": invalid identifier
ORA-06550: line 859, column 9:
PL/SQL: SQL Statement ignored

SQL> select * from v$version;

BANNER BANNER_FULL BANNER_LEGACY CON_ID

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production 0
Version 19.3.0.0.0

Hi Tanel,

Thank you for the mind blowing scripts. When i run dashtop in one of our 19c database it runs for long time so i cancelled it and checked the Execution and found that WRH$_active_session_history (table access by index local row id) spends user io 90% of time and that table is in stale stats including index though 1week before we gathered stats after the upgrade to 19c. Please suggest to fix it

Hi Tanel,
I am very happy to register on your site, I have already attended your seminar at Paris SCGIB 2016. and I often read your news and your good scripts :).

Please, I have an issue with snapper script on oracle veriosn 19.6

@snapper.sql ash 5 1 144

Sampling SID 144 with interval 5 seconds, taking 1 snapshots...
and s.why0+s.why1+s.why2 > 0
*
ORA-06550: line 905, column 26:
PL/SQL: ORA-00904: "S"."WHY2": invalid identifier
ORA-06550: line 842, column 9:
PL/SQL: SQL Statement ignored
Please there is the version of snapper that i used:
-- File name: snapper.sql (Oracle Session Snapper v4).
Please, Do you have a version of snapper script compatible with 19.6 Oracle.?

Thank you in advance.
Mehdi LISSIR

Hi Tanel,
I wanted to use you scripts to troubleshoot a performance problem. I downloaded and i ran the following against an 12.2 RAC instance, and i got the errors on the attached file.

@snapper ash,stats 5 1 sid=758@

tpt_error.txt

Did I miss anything or this is expected behaviour

Hi Tanel,

I have a question about the ash_wait_chains script.

https://drive.google.com/file/d/1hkvCbWN-5ofCv04rFubc_uISiCF4dWma/view?usp=sharing - the full output that it returned, and the picture there is as follows: session 433 has blocked many other sessions, and it is shown as idle. But just below there is this line

-> 433***3bk7zs1qb22p6 - > [idle blocker 1,2020,18565]

 i.e. it turns out that session 433  is blocked by session 2020.
 But why is this not shown in the chains for session 433?
 i.e. in my opinion, it should have looked something like this
 

 -> 2641***7aspktw7mn6w8 -> 3077***68t8xu5nfmw41 - >433***some_sql_id_or_null - > [idle blocker 1,2020,18565]
 -> 136***7aspktw7mn6w8 -> 3077***68t8xu5nfmw41 -> 433***some_sql_id_or_null - > [idle blocker 1,2020,18565]

 
 - and so on in the chains where 433 is set as the final blocker. Or its not possible for some reason?

With regards, Vyasheslav

The scripts is not populating the columns below:

Est. rows Opt. Card. Current Physical Physical
total missetimate gets read blks write blks

Hi Tanel,

I am getting below error while running the snapper code snippet.

could you please help me out
error:
Sampling SID 2161 with interval 1 seconds, taking 1 snapshots...

Error starting at line : 456 File @ H:\awr\snapper.sql
In command -
declare
-- Snapper start
-- forward declarations
procedure output(p_txt in varchar2);
procedure fout;
.
.
.
.

Hi Tanel,
Would it be possible to have tree-like output in wait chain scripts? below link has an implementation idea -

https://github.com/hyee/dbcli/blob/master/oracle/ora/ash/ashchain.sql

Sample Output:
==============
Leaf
Pct AAS EXECS AAS IO TOP_CURR_OBJ# WAIT_CHAIN EVENT_CHAIN FULL_EVENT_CHAIN
-------- --- ----- ---- --------- --------------- ----------------- ----------------------------------------- ---------------------------------------------------------------------------------------
37.5% 3 1 0 408.00 KB (3) x$kglst#2 adzjh275fvvx4 library cache load lock library cache load lock
| 12.50% 1 1 1 320.00 KB (1) 11613 | cvn54b7yz0s8u | db file sequential read library cache load lock > db file sequential read
| 12.50% 1 1 1 320.00 KB (1) 11613 | cvn54b7yz0s8u | ON CPU [file# block# blocks] library cache load lock > ON CPU [file# block# blocks]
| 12.50% 1 1 1 296.00 KB (1) 11613 | 3ktacv9r56b51 | ON CPU [file# block# blocks] library cache load lock > ON CPU [file# block# blocks]
25.% 2 2 0 1.02 MB (2) 122 0b0wj2ykgnnzg enq: TM - contention enq: TM - contention
| 25.00% 2 2 2 108.68 MB (1) 4 | 1b28hzmjun5t0 | db file sequential read enq: TM - contention > db file sequential read
12.5% 1 1 0 0 B (1) 12442 (Mnnn) (Mnnn) library cache pin (Mnnn) library cache pin
| 12.50% 1 1 0 8.00 KB (1) 5944 | (CJQn) | (CJQn) rdbms ipc reply (Mnnn) library cache pin > (CJQn) rdbms ipc reply
| 12.50% 1 1 1 72.00 KB (1) -1 | (DBRM) | (DBRM) resmgr:internal state change (Mnnn) library cache pin > (CJQn) rdbms ipc reply > (DBRM) resmgr:internal state change
12.5% 1 1 0 96.00 KB (1) data block 32hbap2vtmf53 read by other session [data block] read by other session [data block]
| 12.50% 1 1 1 120.00 KB (1) 162 | 32hbap2vtmf53 | db file sequential read read by other session [data block] > db file sequential read
12.5% 1 1 1 944.00 KB (1) -1 92b382ka0qgdt rdbms ipc reply > (Remote) rdbms ipc reply > (Remote)