pioro / orasash Goto Github PK
View Code? Open in Web Editor NEWOracle Simulation ASH
Oracle Simulation ASH
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
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.
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
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.
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
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
Add support for 18c XE as repository
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
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
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
sash_hist_sample is not purged in purge_tables - one delete statement has to be added
Opened in behalf of Ernst Leber
There is no documented way to drop target database and data from repository
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)';
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.
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;
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
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
A declarative, efficient, and flexible JavaScript library for building user interfaces.
๐ Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. ๐๐๐
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google โค๏ธ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.