dincosman’s gists

dincosman / 10035_event.txt

Last active October 7, 2023 06:23

Setting 10035 ksd event to detect parse failures

	Until explicitly disabled, it will track sql statements with parsing errors.
	SQL> alter system set events '10035 trace name context forever, level 1';

	Levels:
	level 1+ Print out user SQL statements
	level >3 Print out recursive SQL
	level >5 Print out row cache cached cursor stats

	Turning off :
	SQL> alter system set events '10035 trace name context off';

dincosman / srvctl_config_asm.txt

Last active May 7, 2024 20:34

Asm instance config output

	* Problematic config
	[oracle@exadb01 ~]$ srvctl config asm
	ASM home: <CRS home>
	Password file: +DATA/orapwASM
	Backup of Password file: +RECO/orapwasm_backup
	ASM listener: ---> This attribute should not be empty.
	ASM instance count: ALL
	Cluster ASM listener: ASMNET1LSNR_ASM

dincosman / srvctl_modify_asm_output.txt

Last active October 7, 2023 06:23

Modifying asm listener config and detailed config output for asm proxy

	[oracle@exadb03 ~]$ srvctl modify asm -listener LISTENER
	PRKO-2207 : Warning:-listener option has been deprecated and will be ignored.

	[oracle@exadb01 trace]$ srvctl config asm -proxy -detail
	ASM home: <CRS home>
	ADVM proxy is enabled
	ADVM proxy is individually enabled on nodes:
	ADVM proxy is individually disabled on nodes:
	Spfile:

dincosman / Asm_alertlog_findings6.txt

Created December 31, 2022 09:43

asm instance alert log TNS connection error

	...
	01-APR-2022 10:57:20 * (CONNECT_DATA=(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=oracle))(SID=+APX1)) * (ADDRESS=(PROTOCOL=tcp)(HOST=XXX.XXX.XXX.XXX)(PORT=40650)) * establish * +APX1 * 12505
	TNS-12505: TNS:listener does not currently know of SID given in connect descriptor
	2022-04-01T10:57:23.208266+03:00
	...

dincosman / crsctl_stat_resourcegroup.txt

Last active October 7, 2023 06:23

asmgroup stats output

	* Correct one
	[oracle@s001db01 u02]$ crsctl stat resourcegroup ora.asmgroup -p
	NAME=ora.asmgroup
	TYPE=ora.asmgroup.gtype
	...
	START_DEPENDENCIES=weak(global:ora.gns,ora.LISTENER.lsnr) dispersion:active(site:type:ora.asmgroup.gtype)
	START_DEPENDENCIES_RTE_INTERNAL=<xml><Arg name="crslistener" type="ResList">ora.LISTENER.lsnr</Arg></xml>
	START_DEPENDENCIES_TEMPLATE=<xml>weak(global:ora.gns,<ResList>{crslistener}</ResList>) dispersion:active(site:type:ora.asmgroup.gtype)</xml>
	...

dincosman / crsctl_modify_resourcegroup.txt

Created December 31, 2022 10:09

Modify asmgroup start dependies

	[oracle@exadb03 ~]$ crsctl modify resourcegroup ora.asmgroup -attr "START_DEPENDENCIES='weak(global:ora.gns,ora.LISTENER.lsnr) dispersion:active(site:type:ora.asmgroup.gtype)'" -unsupported
	[oracle@exadb03 ~]$ crsctl modify resourcegroup ora.asmgroup -attr "START_DEPENDENCIES_RTE_INTERNAL='<xml><Arg name=\"crslistener\" type=\"ResList\">ora.LISTENER.lsnr</Arg></xml>'" -unsupported
	[oracle@exadb03 ~]$ crsctl modify resourcegroup ora.asmgroup -attr "AUTO_START='always'" -unsupported

dincosman / Sqls_most_shared_pool_usage.sql

Last active October 7, 2023 06:23

Top 20 SQL plans consuming most memory from shared pool

	-- Top 20 SQL plans consuming most memory from shared pool
	-- if plan_hash_value equals 0, then insert or pl/sql statement, look for sql_text with substr.

	SELECT
	*
	FROM
	(
	SELECT
	plan_hash_value,
	COUNT(sql_id),

dincosman / sql_statements_using_plan_hash_value.sql

Last active October 7, 2023 06:23

Query sql statements using the given plan_hash_value

	SQL> SELECT sql_id, sql_text FROM gv$sql b where plan_hash_value=44909958;
	SQL_ID SQL_TEXT
	------------- ----------------------------------------------------------------------------------------------------------------------------------------
	...
	6uzsj5a6b007x select sum(BLOCKS) from DBA_SEGMENTS where segment_name='SYS_FBA_DDL_COLMAP_85287' and owner='XXXX' and TABLESPACE_NAME='USERS_XXX'
	193rca1sdh0n0 select sum(BLOCKS) from DBA_SEGMENTS where segment_name='SYS_FBA_HIST_1584811' and owner='YYYY' and TABLESPACE_NAME='USERS_XXX'
	4xnnvvkkcn1m3 select sum(BLOCKS) from DBA_SEGMENTS where segment_name='SYS_FBA_TCRV_IDX_84869' and owner='ZZZZ' and TABLESPACE_NAME='USERS_XXX'
	gxskkznf5s057 select sum(BLOCKS) from DBA_SEGMENTS where segment_name='SYS_FBA_TCRV_1584668' and owner='TTTT' and TABLESPACE_NAME='USERS_XXX'
	79k6129w542gr select sum(BLOCKS) from DBA_SEGMENTS where s

dincosman / diagnose_background_proc.sql

Last active October 7, 2023 06:23

Diagnosing responsible background process for the problematic query

	SQL> SELECT
	sq.sql_id,
	sq.parsing_schema_name as schema
	sq.service,
	ses.program,
	ses.sample_time
	FROM
	v$sql sq,
	v$active_session_history ses
	WHERE

dincosman / test_case_for_fbda.sql

Last active October 7, 2023 06:23

Test Case Scenario for FBDA (not using bind variables)

	CREATE TABLESPACE USERS_TEST
	DATAFILE '+DATA' SIZE 12G;

	CREATE FLASHBACK ARCHIVE FDA_TEST TABLESPACE USERS_TEST QUOTA 512 M RETENTION 1 DAY;

	ALTER USER HR
	QUOTA 10G ON USERS_TEST;

	BEGIN
	FOR C1 IN 1..100 LOOP

Osman DINC dincosman