SQLT (Oracle’s Tool written by Carlos Sierra, read MOS note 215187.1 for more details) is an important tool when it comes to doing in-depth and advance troubleshooting & analysis of a SQL statement, it’s pretty easy to install, configure and use, but things gets little tricky when it comes to running it for the SQL that is slow or in question on the read-only standby database. The problem is obvious as ADG or standby databases doesn’t allow WRITE operations and you will get error “ORA-16000: database open for read-only access” when try on standby database
The only problem being that SQLTXTRACT and SQLTXECUTE need read/write access to the database (to store data in the SQLT repository and to install packages and procedures). How is it possible for SQLT to help us if we can’t even store data about the performance on the database with the performance problem? This is where SQLTXTRSBY comes into play. XTRSBY solves this problem by using local users (on a read/write database) and creating procedures that use database links to the read-only database.
First step : Install SQLTXPLAIN on the primary and allow the DDL to be propagated to the standby database.
-- On primary database
-- DDL Will get propogated to the standby in few secons/mins.
[oracle@monkeyos1931jd install]$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Thu Jan 13 16:13:41 2022
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL>
SQL>
SQL> @1
DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE
_________________ _____________ ________________
DIXITP READ WRITE PRIMARY
SQL>
SQL> START sqcreate.sql
zip warning: name not matched: *_sq*.log
zip error: Nothing to do! (SQLT_installation_logs_archive.zip)
zip warning: name not matched: *_ta*.log
........
...........
..............
SQUTLTEST completed.
adding: 220113161601_10_squtltest.log (deflated 59%)
SQLT users must be granted SQLT_USER_ROLE before using this tool.
SQCREATE completed. Installation completed successfully.
SQL>
SQL>
Step 2 : Create a database link accessible to the SQLTXPLAIN schema linking to the standby database as changes will be fetched from the standby database SQLTXPLA in schema.
-- On Primary database to connect to the standby database.
SQL> create public database link dblink_tostandby connect to sqltxplain identified by oracle90 using 'DIXITSTAN';
Database link DBLINK_TOSTANDBY created.
SQL>
SQL>
SQL> select * from dba_db_links;
OWNER DB_LINK USERNAME HOST CREATED HIDDEN
_________ ____________________________ _____________ ___________ ____________ _________
PUBLIC DBLINK_TOSTANDBY SQLTXPLAIN DIXITSTAN 16-JAN-22 NO
SQL> select sysdate from dual@DBLINK_TOSTANDBY;
SYSDATE
____________
16-JAN-22
SQL>
Step 3: Let’s run some SQLs on standby database which we will using against the SQLT tool as an input to generate the SQLT report.
-- On Standby Database
SQL> @1
DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE
------------------------------ -------------------- ----------------
DIXITSTAN READ ONLY WITH APPLY PHYSICAL STANDBY
SQL>
SQL> select count(*) from bigtab where weight in ( select distinct( weight) from bigtab);
COUNT(*)
----------
2431464
SQL> select sql_id, sql_fulltext from v$sqlarea where sql_text like '%distinct( weight) from bigtab%';
SQL_ID SQL_FULLTEXT
------------- --------------------------------------------------------------------------------------------------------------------------------------------
6mg40znnrhzm8 select count(*) from bigtab where weight in ( select distinct( weight) from bigtab)
In the steps above we ran some arbitrary SQL and got the SQL ID for that SQL. Remember we ran the SQL on the standby database (where our reports might have run). We can’t store any data on the Data Guard Physical Standby database so now we have to capture information about the SQL from across the database link from the primary database.
Step 4: Will run the SQLT for SQLID that we have captured from STANDBY database, from PRIMARY database. The script “sqltxtrsby.sql” is present under RUN directory of the tool.
[oracle@monkeyos1931jd run]$ ls *sqltxtrsby*
sqltxtrsby.sql
[oracle@monkeyos1931jd run]$ !sql
sqlplus
SQL*Plus: Release 12.2.0.1.0 Production on Sun Jan 16 06:01:12 2022
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Enter user-name: / as sysdba
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL>
SQL> @1
DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE
_________________ _____________ ________________
DIXITP READ WRITE PRIMARY
SQL>
SQL> @sqltxtrsby 6mg40znnrhzm8 DBLINK_TOSTANDBY
PL/SQL procedure successfully completed.
Parameter 1:
SQL_ID or HASH_VALUE of the SQL to be extracted (required)
Paremeter 2:
SQLTXPLAIN password (required)
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
Parameter 3:
DBLINK to stand-by database (required)
Enter value for 3: DBLINK_TOSTANDBY
Values passed to sqltxtrsby:
SQL_ID_OR_HASH_VALUE: "6mg40znnrhzm8"
DB_LINK : "@DBLINK_TOSTANDBY"
PL/SQL procedure successfully completed.
SQLT_VERSION
----------------------------------------
SQLT version number: 19.1.200226
SQLT version date : 2020-02-26
Installation date : 2022-01-13/16:15:27
... please wait ...
adding: alert_DIXITP.log (deflated 86%)
NOTE:
You used the XTRSBY method connected as SYS.
.......
..............
...............
....
..................
File sqlt_s19812_xtrsby_6mg40znnrhzm8.zip for 6mg40znnrhzm8 has been created.
SQLTXTRSBY completed.
Now look out for sqlt_xxxx_main.html file, We’ll see the main sqlt_xxxx_main.html file, but fewer files than for a “normal” sqltxtract run: no 10053 trace file, no SQL profile script and no SQL Tuning Advisor reports. This is because the read-only status of the standby restricts what can be done.
Hope It Helped!
Prashant Dixit
Tales From A Lazy Fat DBA 