Tales From A Lazy Fat DBA

Its all about Databases & their performance, troubleshooting & much more …. ¯\_(ツ)_/¯

  • Prashant Dixit is the 'FatDBA' ...
  • Follow me on Twitter

Part 3: Using SQLT (SQLTXPLAIN) tool with Data Guard physical standby databases

Posted by FatDBA on January 28, 2022

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

 
%d bloggers like this: