Oracle DB and beyond: practical tips, Q&A

Thursday, 6 October 2016

How to find the objects/extents residing on a specific datafile?

This can be useful, for example, if we want to drop a datafile, and it has to be empty in order to do this.

How can we check what objects are there in a datafile?

select distinct a.owner,a.segment_name
from dba_extents a,dba_data_files b
where a.file_id=b.file_id and b.file_name=<your datafile name with path>;

Note: the query above is usually very slow, since it has to full scan a few big fixed tables.

Friday, 9 September 2016

AWR Global RAC report: awrgrpt.sql

Nice site:

http://www.dba-oracle.com/t_rac_awr_awrgrpt_global_report.htm

SQL> @?/rdbms/admin/awrgrpt.sql

Tuesday, 6 September 2016

How to avoid Cartesian join between data dictionar views?

In one of our databases, which is 11.2.0.4, the below join is doing some merge join cartesian and it takes a very long time to complete.

SELECT
distinct sid, serial#, substr(username,1,12) username,
substr(osuser,1,10) osuser, process, program, a.sql_id,hash_value,command
FROM v$session a,v$sql b
WHERE sid > 7
AND status = 'ACTIVE' and username like '$USER_NAME'
and a.sql_id=b.sql_id
/

Execution Plan

----------------------------------------------------------

Plan hash value: 4038803543

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 78 | 5 |

| 1 | HASH UNIQUE | | 1 | 78 | 5 |

| 2 | NESTED LOOPS | | 1 | 78 | 1 |

| 3 | NESTED LOOPS | | 1 | 74 | 1 |

| 4 | MERGE JOIN CARTESIAN | | 32 | 1344 | 1 |

|* 5 | FIXED TABLE FULL | X$KGLCURSOR_CHILD | 1 | 34 | 1 |

| 6 | BUFFER SORT | | 32 | 256 | |

|* 7 | FIXED TABLE FULL | X$KSLWT | 32 | 256 | 1 |

|* 8 | FIXED TABLE FIXED INDEX| X$KSUSE (ind:1) | 1 | 32 | 1 |

|* 9 | FIXED TABLE FIXED INDEX | X$KSLED (ind:2) | 1 | 4 | 1 |

--------------------------------------------------------------------------------

Since we don't want to collect fresh stats for the fixed objects, a quick fix is to run the alter session below:

SQL> alter session set "_optimizer_cartesian_enabled" =false;

The new execution plan is below and the query is very fast.

Execution Plan

----------------------------------------------------------

Plan hash value: 2088405718

-----------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 78 | 4 |

| 1 | HASH UNIQUE | | 1 | 78 | 4 |

| 2 | NESTED LOOPS | | 1 | 78 | 1 |

| 3 | NESTED LOOPS | | 1 | 74 | 1 |

| 4 | NESTED LOOPS | | 1 | 40 | 1 |

|* 5 | FIXED TABLE FULL | X$KSLWT | 32 | 256 | 1 |

|* 6 | FIXED TABLE FIXED INDEX| X$KSUSE (ind:1) | 1 | 32 | 1 |

|* 7 | FIXED TABLE FIXED INDEX | X$KGLCURSOR_CHILD (ind:2) | 1 | 34 | 1 |

|* 8 | FIXED TABLE FIXED INDEX | X$KSLED (ind:2) | 1 | 4 | 1 |

-----------------------------------------------------------------------------------------

Wednesday, 31 August 2016

How to check for which tables is the supplemental logging enabled in the database?

Displaying Supplemental Log Groups at a Source Database

To check whether one or more log groups are specified for the table at the source database, run the following query:

COLUMN LOG_GROUP_NAME HEADING 'Log Group' FORMAT A20
COLUMN TABLE_NAME HEADING 'Table' FORMAT A15
COLUMN ALWAYS HEADING 'Conditional or|Unconditional' FORMAT A14
COLUMN LOG_GROUP_TYPE HEADING 'Type of Log Group' FORMAT A20

SELECT 
    LOG_GROUP_NAME, 
    TABLE_NAME, 
    DECODE(ALWAYS,
             'ALWAYS', 'Unconditional',
             'CONDITIONAL', 'Conditional') ALWAYS,
    LOG_GROUP_TYPE
  FROM DBA_LOG_GROUPS;

Your output looks similar to the following:

                                     Conditional or
Log Group            Table           Unconditional  Type of Log Group
-------------------- --------------- -------------- --------------------
LOG_GROUP_DEP_PK     DEPARTMENTS     Unconditional  USER LOG GROUP
SYS_C002105          REGIONS         Unconditional  PRIMARY KEY LOGGING
SYS_C002106          REGIONS         Conditional    FOREIGN KEY LOGGING
SYS_C002110          LOCATIONS       Unonditional   ALL COLUMN LOGGING
SYS_C002111          COUNTRIES       Conditional    ALL COLUMN LOGGING
LOG_GROUP_JOBS_CR    JOBS            Conditional    USER LOG GROUP

Friday, 26 August 2016

Linux: how to transfer files and bypass the "jump" server

If we need to transfer files from the server A to the server C, but we need to jump through server B, since we don't have connectivity straight to C, we can use the setup below:

On server A, put the lines below in the file : .ssh/config:

# MYPROJ

Host C*

ProxyCommand ssh serverB nc -w 43200 %h 22 2> /dev/null

StrictHostKeyChecking=no

How to determine the ascii value of a specific character within a row?

How to determine the ascii value of a specific character within a row?
Let's say we are searching for the character § inside the DB.

SQL> select dump('§') from dual;

DUMP('§')
---------------------
Typ=96 Len=2: 194,167

SQL> select * from k where col1 like '%'||chr(194)||'%'||chr(167)||'%';

COL1
--------------------------------------------------------------------------------
§

Thanks to the site below:

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:2143700800346224648

Tuesday, 2 August 2016

How often are the archived logs generated?

SQL> set line 200 pages 80
SQL> alter session set nls_date_format='dd-mon-yy hh24:mi:ss';

Session altered.

Hourly report:

select trunc(COMPLETION_TIME,'HH') Hour,thread# , count(*) Archives from v$archived_log
where COMPLETION_TIME > sysdate-1
group by trunc(COMPLETION_TIME,'HH'),thread# order by 1;

HOUR THREAD# ARCHIVES
------------------ ---------- ----------
07-mar-17 15:00:00 1 3
07-mar-17 16:00:00 1 6
07-mar-17 17:00:00 1 6
07-mar-17 18:00:00 1 9
07-mar-17 19:00:00 1 19
07-mar-17 20:00:00 1 5
07-mar-17 21:00:00 1 20
07-mar-17 22:00:00 1 8
07-mar-17 23:00:00 1 4
08-mar-17 00:00:00 1 4
08-mar-17 01:00:00 1 19
08-mar-17 02:00:00 1 15
08-mar-17 03:00:00 1 25
08-mar-17 04:00:00 1 10
08-mar-17 05:00:00 1 5
08-mar-17 06:00:00 1 12
08-mar-17 07:00:00 1 4
08-mar-17 08:00:00 1 16
08-mar-17 09:00:00 1 6
08-mar-17 10:00:00 1 20
08-mar-17 11:00:00 1 6
08-mar-17 12:00:00 1 6
08-mar-17 13:00:00 1 19
08-mar-17 14:00:00 1 7
08-mar-17 15:00:00 1 3

25 rows selected.

Daily report:

select trunc(COMPLETION_TIME,'DD') Day,thread# , count(*) Archives from v$archived_log
where COMPLETION_TIME > sysdate-10
group by trunc(COMPLETION_TIME,'DD'),thread# order by 1
/

Another question would be, how many archived logs per day are generated, on average?

On average we generate 77 logs/day.

SQL> l

select avg(count(*)) Archives from v$archived_log

where COMPLETION_TIME > sysdate-100

group by trunc(COMPLETION_TIME,'DD')

SQL> /