pioro / orasash

Oracle Simulation ASH

orasash

Oracle Simulation ASH

Project home page - http://pioro.github.io/orasash/

Change log:

Change Log:

2.3.1 repo_schema.sql

• new column in sashX table - osuser varchar2(30)
• new column in sash_event_names - event_id number
• change in v$active_session_history view definition for following columns qc_instance_id,qc_session_id,qc_session_serial#

sash_pkg.sql

• changes related to new columns

sash_repo.sql

• using DBID as a part of job name
• support for same database name on different hosts

installation script

• fix of hardcoded sash schema
• some other minor fixes

If you want to keep old data with new version of sash you need to do all 3 changes from repo_schema.sql manually.

2.4-rc1

This version of S-ASH is collecting event histograms, OS statistics from Oracle and sys time model.

v$active_session_history is still a main view but the following AWR views are now simulated:

• dba_hist_snapshot
• dba_hist_sysmetric_history
• dba_hist_sqlstat
• dba_hist_active_sess_history
• dba_hist_event_histogram
• dba_hist_sys_time_model

File changes:

sash_xplan.sql

• fix for duplicated sql_id / plan_hash for RAC monitoring
• view SASH_PLAN_TABLE moved from repo_schema.sql

sash_pkg.sql

• fix for hostname with dash
• collect_osstat - new procedure to collect v$osstat
• collect_sys_time_model - new procedure to collect v$sys_time_model
• collect_event_histogram - new procedure to collect v$event_histogram

sash_repo.sql

• fix for hostname with dash

repo_schema.sql

• add inst_num column to sash_target table
• all views are using dbid and inst_num - so if you want to consolidate results from RAC use raw sash tables
• split views to other file
• sash_sqlstats and sash_event_histogram have a "poor man" paritioning

switchdb.sql / current.sql

• instance switch added

upgarde: TODO from 2.3.1 to 2.4 without data loss

Number of metric changed - steps to run if you upgraded from lower version

PROD_SASH@XE > delete from sash_sysmetric_names where dbid = ;

21 rows deleted.

PROD_SASH@XE > commit;

Commit complete.

PROD_SASH@XE > exec sash_pkg.get_metrics('<db_link>')

PL/SQL procedure successfully completed.

PROD_SASH@XE > select * from sash_sysmetric_names where dbid = ;

...

Marcin

orasash's Issues

target_user_view_10.2.0.1.sql references nonexistent view

Hi Marcin,

when running target_user_view_10.2.0.1.sql there is a "grant select on v_$iostat_function to sash";
AFAIK this view is new in 11g.

thanks & regards
Jo

ORA-12505: TNS:listener does not currently know of SID given in connect descriptor

Hi Marcin,
I found another little issue when I run adddb.sql script, this error appears:

SQL> @adddb.sql

Enter database name orcl12c
Enter number of instances [default 1]
Enter host name for instance number 1 oel6
Enter instance name for instance number 1 [ default orcl12c ] PORCL12C
Enter listener port number [default 1521] 1522
Enter SASH password on target database SASH
BEGIN sash_repo.add_db('oel6', 1522, 'SASH', 'orcl12c', 'PORCL12C',1, null); END;

ERROR at line 1:
ORA-12505: TNS:listener does not currently know of SID given in connect descriptor
ORA-06512: at "SASH.SASH_REPO", line 366
ORA-06512: at line 1

There was a problem with sash_rep package in add_db procedure here:
v_dblink_target:='(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST =' || v_host || ')(PORT = ' || v_port || ')))(CONNECT_DATA = (SID = ' || v_sid || ')))';

I changed in :
v_dblink_target:='(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST =' || v_host || ')(PORT = ' || v_port || ')))(CONNECT_DATA = (SERVICE_NAME = ' || v_sid || ')))';

and all works fine.
In my Github repository (forked) I created a new procedure add_db12c, only for the new version Oracle DB 12c.
So I created a new script called adddb12c.sql
Ciao
Alberto

P.S.:For target DB in Oracle 12c, I used the same script for 11G R2: target_user_view_11g2.sql, tested, and all works fine.

SASH_OSSTAT_NAME, SASH_SYS_TIME_NAME

Hi Marcin,
It seems that these 2 tables are always empty and don't update ever.
For SASH_SYS_TIME_MODEL and SASH_OSSTAT_NAME tables (joined) no problem.
GET_METRICS procedure works fine for the sash_sysmetric_names, but not for the others two.
I've tryed to run manually others insert and now work fine.
In the SASH_LOG no entry for this issue.
when you can take a look.
Ciao
Alberto

Drop target

Opened in behalf of Ernst Leber

There is no documented way to drop target database and data from repository

Issues on sash installation

ERROR at line 1:
ORA-01403: no data found
ORA-06512: at "SASH.SASH_REPO", line 371
ORA-06512: at line 1

ERROR at line 1:
ORA-20100: SASH configure_db error ORA-01403: no data found
ORA-06512: at "SASH.SASH_PKG", line 21
ORA-06512: at line 1

ERROR at line 1:
ORA-01403: no data found
ORA-06512: at "SASH.SASH_PKG", line 144
ORA-06512: at line 1

Support for 18c XE as repository

Add support for 18c XE as repository

Oracle 12c: get_sqlstats reports "ORA-06535: statement string in OPEN is NULL or 0 length"

I checked orasash 2.4 against Oracle 12c database (no container database) and found following message in sash_log:
get_sqlstats: ORA-06535: statement string in OPEN is NULL or 0 length

As a workaround I added the following lines in procedure sql_sqlstats line 359:
elsif (l_ver = '12.1') then
sql_stat:='select /*+driving_site(sql) */ :1, :2, :3,
sql_id, plan_hash_value, parse_calls, disk_reads,
direct_writes, buffer_gets, rows_processed, serializable_aborts,
fetches, executions, end_of_fetch_count, loads, version_count,
invalidations, px_servers_executions, cpu_time, elapsed_time,
avg_hard_parse_time, application_wait_time, concurrency_wait_time,
cluster_wait_time, user_io_wait_time, plsql_exec_time, java_exec_time,
sorts, sharable_mem, total_sharable_mem, typecheck_mem, io_interconnect_bytes,
0, physical_read_requests, physical_read_bytes, physical_write_requests,
physical_write_bytes, exact_matching_signature, force_matching_signature ,
0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
from sys.v_$sqlstats@' || v_dblink || ' sql
where (sql.sql_id, sql.plan_hash_value) in ( select sql_id, SQL_PLAN_HASH_VALUE from sash_hour_sqlid t)';

SASH report

Add a functionality to generate 'AWR' type of report from SASH repository.
Some work is already done years ago but as sash become more popular I think I should add it to official repository

User from target database

Opened in behalf of Ernst Leber

Hi Marcin,

while testing a demo with oraSASH, I found a small problem with oraSASH in procedure sash_pkg.get_users and changed it as follows to be able to drop users or recreate users in target database:

PROCEDURE get_users(v_dblink varchar2) is

l_dbid number;
TYPE t_username is table of varchar2(30);
type t_userid   is table of number;
   l_username t_username;
l_userid   t_userid;

begin
  execute immediate 'select dbid  from sys.v_$database@'||v_dblink into l_dbid;

  -- Ernst Leber 23.06.2014 improve get_users if user_id changes when user was recreated ** start
  execute immediate 'select username,user_id from dba_users@'||v_dblink bulk collect into l_username, l_userid;

  for i in 1 .. l_username.count
  loop

    begin
      insert into sash_users
           (dbid, username, user_id)
      values (l_dbid, l_username(i),  l_userid(i));
    exception
      when DUP_VAL_ON_INDEX then
          update sash_users
             set user_id = l_userid(i)
           where username = l_username(i);
      when others then      
        sash_repo.log_message('GET_USERS', 'Already configured ?','W');
    end;  
  end loop;

  -- Ernst Leber 23.06.2014 improve get_users if user_id changes when user was recreated ** end

/* execute immediate 'insert into sash_users
(dbid, username, user_id)
select ' || l_dbid || ',username,user_id from dba_users@'||v_dblink;
*/
exception
when DUP_VAL_ON_INDEX then
sash_repo.log_message('GET_USERS', 'Already configured ?','W');
end get_users;

ORA-01017 during install on Oracle 12c pluggable DB

Hi Marcin,
Here my scenario:
Create a pluggable database 12c called SASH.
Connect like sysdba on pluggable db to install repo.
Run config.sql script but

SQL> @config.sql
"------------------------------------------------------------------------------------"

Creating repository owner and job kill function using SYS user
"------------------------------------------------------------------------------------"
Enter user name (schema owner) [or enter to accept username sash] ? SASH
Enter user password ? SASH
Enter SASH user default tablespace [or enter to accept USERS tablespace] ?
SASH default tablespace is: users
"------------------------------------------------------------------------------------"
Existing SASH user will be deleted.
If you are not sure hit Control-C , else Return :
"------------------------------------------------------------------------------------"
New SASH user will be created.
Warning: Procedure created with compilation errors.

ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.

The problem was in config.sql here:
connect &SASH_USER/&SASH_PASS

So I changed repo_user.sql here adding:
accept SASH_TNS default @sash prompt "TNS String Connection [or enter to accept @sash string connection] ? "

Where I set TNS connection string for my pluggable database.

After I changed also config.sql here:
connect &SASH_USER/&SASH_PASS&SASH_TNS

Now the connection works fine and installation is performed without problem.
Let me know ...when you have time.
Ciao
Alberto

v$sql to have two more columns

Would it be possible to add two more columns to v$sql?

It now obviously holds:
Name Null Type

---

SQL_ID VARCHAR2(13)
COMMAND_TYPE NUMBER
SQL_TEXT VARCHAR2(4000)

I am looking to get moats running against ORA SASH and from what I gather v$sql is missing two columns:
child_number
plan_hash_value
Rather than breaking moats, it would be worth to see if these two columns could be added.

sash_targets.sid column size

Hi,

orasash ver 2.3 repo_schema.sql creates table:

create table sash_targets (
...
        sid varchar2(8),
...

and this is problem with RAC DB and 8 chars DB name:

For example:

DB NAME: przepior
instance1 name: przepior1

we try to add this to the repo and...
...

BEGIN sash_repo.add_db('hostname1', 1521, 's3cr3tP4ss', 'przepior', 'przepior1',1, '11.2.0.2', 8); END;
*
ERROR at line 1:
ORA-12899: value too large for column "SASH"."SASH_TARGETS"."SID" (actual: 9, maximum: 8)
ORA-06512: at "SASH.SASH_REPO", line 353
ORA-06512: at line 1

of course it works after this change:

diff repo_schema.sql repo_schema.sql.orig
400c400
<       sid varchar2(9),
---
>       sid varchar2(8),

"------------------------------------------------------------------------------------"
Configuration completed. Exiting.
You can now connect using user name and password specified above
"------------------------------------------------------------------------------------"

pzdr,
Kamil

change sampling rate

Hello,

I would like to ask you a question regarding sample rate.
for my purpose it is sufficient to do a snap every 3 seconds, 1 sec is too much :)
I have tried to change job action in sash_pkg_collect to
begin sash_pkg.collect_ash(3,1200,''xxxxxxx'', 1)

is there somethink else I have to change in views ? e.g. in dba_hist_active_sess_history?
i mean are there any dependencies in views/tables to sample rate?

thank you very much for answer,
pavol

ORA-01732: data manipulation operation not legal on this view

Hi Marcin ,
I try today to run S-ASH with your last changes, occur an error, seems for :Enter TNS alias to connect to database - required for 12c plugable DB [leave it empty to use SID]?
I try it with Oracle 11G in (RAC enviroment for target) :
oracle@localhost sash_dev]$ sqlplus "/ as sysdba"

SQL*Plus: Release 11.2.0.2.0 Production on Fri Oct 18 09:07:42 2013

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @config.sql
"------------------------------------------------------------------------------------"
Creating repository owner and job kill function using SYS user
"------------------------------------------------------------------------------------"
Enter user name (schema owner) [or enter to accept username sash] ? SASH
Enter user password ? SASH
Enter SASH user default tablespace [or enter to accept USERS tablespace] ?
SASH default tablespace is: users
TNS String Connection [or enter to accept @sash string connection] ?
"------------------------------------------------------------------------------------"
Existing SASH user will be deleted.
If you are not sure hit Control-C , else Return :
"------------------------------------------------------------------------------------"
New SASH user will be created.

Warning: Procedure created with compilation errors.

Enter TNS alias to connect to database - required for 12c plugable DB [leave it empty to use SID]?

Connected.
"------------------------------------------------------------------------------------"
Installing SASH objects into SASH schema
"------------------------------------------------------------------------------------"
Create sequence
Create tables
Crating SASH_REPO package
No errors.
No errors.
Crating SASH_PKG package
No errors.
No errors.
"------------------------------------------------------------------------------------"
Instalation completed. Starting SASH configuration process
Press Control-C if you do not want to configure target database at that time.
"------------------------------------------------------------------------------------"
Enter database name KIRA
Enter number of instances [default 1]2
Enter host name for instance number 1 oraclerac1
Enter host name for instance number 2 oraclerac2
Enter instance name for instance number 1 [ default KIRA1 ]
Enter instance name for instance number 2 [ default KIRA2 ]
Enter listener port number [default 1521]
Enter SASH password on target database sash
BEGIN sash_pkg.set_dbid('KIRA_oraclerac1'); END;

ERROR at line 1:
ORA-01732: data manipulation operation not legal on this view
ORA-06512: at "SASH.SASH_PKG", line 144
ORA-06512: at line 1

"------------------------------------------------------------------------------------"
Database added.
"------------------------------------------------------------------------------------"
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Ciao
Alberto

bug in 10.2.0.5 support

Hi Marcin,

I recently added an Oracle 10.2.0.5 DB to OraSASH using adddb and got following error message:

BEGIN sash_pkg.configure_db('WOWO.wowo.int_10_91_2_30'); END;
*
ERROR at line 1:
ORA-20100: SASH configure_db error ORA-00904: "CUMULATIVE": invalid identifier
ORA-06512: at "SASH.SASH_PKG", line 21
ORA-06512: at line 1

Procedure sash_pkg.get_metrics(l_dblink); in sash_pkg.configure failed, because following select failed:

begin
EXECUTE IMMEDIATE 'insert into sash_osstat_name select distinct ' || L_DBID || ',OSSTAT_ID, STAT_NAME, COMMENTS, CUMULATIVE from sys.v_$osstat@'||V_DBLINK;
exception
WHEN DUP_VAL_ON_INDEX THEN
sash_repo.log_message('GET_METRICS', 'Already configured ?','W');
end;

Solution:
Columns COMMENTS and CUMULATIVE are not defined in Oracle 10 I temporarily changed the query to select nulls instead and everything was fine.

Regards

Ernst

sash_hist_sample is not purged in purge_tables

sash_hist_sample is not purged in purge_tables - one delete statement has to be added

Drop database link statement fails when adding a new target

With 2.4 I ran into this issue, and I don't know if it's me or if it's something in the software.
When adding a database to the repository, SASH_REPO gave an issue:

This is what the procedure did:

SQL> @adddb

For non multitenant databases use unique database name and instance name
For multitenant - use PDB name as database name and CDB name as a instance name

Enter database name db.domain
Enter number of instances [default 1]
Enter host name for instance number 1 host.domain
Enter instance name (or CDB for 12c) for instance number 1 [ default db.domain ] db
Enter listener port number [default 1521]
Enter SASH password on target database sash
BEGIN sash_repo.add_db(' plsorastage2.portrix.net', 1521, 'sash', 'plsora02.portrix.net', 'plsora02',1, null); END;

ERROR at line 1:
ORA-00933: SQL command not properly ended
ORA-06512: at "PORTRIXASH.SASH_REPO", line 365
ORA-06512: at line 1

This is the hack:
(commenting out the line for 'drop database' which was not yet required)

begin

-- execute immediate 'drop database link ' || v_dblink;
dbms_output.put_line('Link dropped');
exception when no_db_link then
log_message('add_db', 'no db link - moving forward ' || v_dblink ,'W');
end;

This is what the procedure now does:

SQL> @adddb

For non multitenant databases use unique database name and instance name
For multitenant - use PDB name as database name and CDB name as a instance name

Enter database name db.domain
Enter number of instances [default 1]
Enter host name for instance number 1 host.domain
Enter instance name (or CDB for 12c) for instance number 1 [ default db.domain ] db
Enter listener port number [default 1521]
Enter SASH password on target database sash
BEGIN sash_repo.start_collecting_jobs; END;

ERROR at line 1:
ORA-20037: SASH start_collecting_jobs error ORA-20036: SASH start_collecting_jobs error ORA-27478: job "PORTRIXASH.SASH_PKG_COLLECT_1_965977554" is running
ORA-06512: at "PORTRIXASH.SASH_REPO", line 313
ORA-06512: at line 1

Database added.

Possibly the last message is nothing significant because it already reports that the collecting jobs are running. This is also verified:

SQL> @job_stat

JOB_NAME LAST_START_DATE NEXT_RUN_DATE STATE FAILURE_COUNT

---

SASH_PKG_COLLECT_1_965977554 27-MAR-15 01.00.34.256487 PM +00:00 27-MAR-15 01.00.34.000000 PM +00:00 RUNNING 0
SASH_PKG_GET_ALL_1_965977554 27-MAR-15 01.01.29.733804 PM +00:00 27-MAR-15 01.15.00.000000 PM +00:00 SCHEDULED 0
SASH_REPO_PURGE 28-MAR-15 12.00.00.000000 AM +00:00 SCHEDULED 0
SASH_REPO_WATCHDOG 27-MAR-15 01.00.34.284346 PM +00:00 27-MAR-15 01.05.34.000000 PM +00:00 SCHEDULED 0

I have not further investigated the start_collection_jobs-issue, since they are running :-)
Furthermore I still need to figure out the correct syntax for the "drop dblink" line.

Add CON_ID to repository