How to access the Hana table in the oracle database?

 

How does it work?

SQL commands are fired from an oracle database (sqlplus), which will reach out to Oracle Gateway via DB LINK > tnsnames.ora

Oracle Gateway will have a parameter in its init<sid>.ora file and load unixODBC libraries from its LD_LIBRARY_PATH.

unixODBC will load HANA odbc drivers and goes through DSN setting to read data from the HANA database.

Meaning Oracle DB > DBLINK > tnsnames.ora > Oracle Gateway > unixODBC drivers > Data source DSN (odbc.ini) > HANA odbc drivers > HANA DB

If you notice above Oracle Gateway and HANA odbc do not talk to each other directly. Instead, talk through unixODBC drivers.

 

Step 1 –

The first step is to make unixODBC working

Installed unixODBC RPMs (both 32 and 64-bit) on the RHEL machine where you will run Oracle Gateway.

unixODBC-2.2.14-11.el6.x86_64

unixODBC-devel-2.2.14-11.el6.i686

unixODBC-devel-2.2.14-11.el6.x86_64

unixODBC-2.2.14-11.el6.i686

Step 2 –

Install SAP HANA client (64bit) on the RHEL machine where you will run Oracle Gateway. Please refer to the HANA client installation guide.

Step 3 –

Create /etc/odbc.ini contents look like below

[H1X]

Driver=/usr/sap/hdbclient/libodbcHDB.so

ServerNode=serverhana:30015

Step 4 –

Install Oracle Gateway software. You may please google to find step-by-step on how to install using Oracle Universal Installer.

Step 5 –

Set the environment variable of the user running the oracle gateway. In my case it is

LD_LIBRARY_PATH=/usr/lib64:/usr/sap/hdbclient:/oracle/BW1/112_64/lib

It is very important to have 64bit unixODBC libraries path (/usr/lib64/libodbc.so) in LD_LIBRARY_PATH because we run RHEL x64 and Oracle 64-bit software.

Step 6 –

Create init<sid>.ora. In my case, I will call this dg4odbc (initdg4odbc.ora). The content should be like below.

HS_DB_NAME = H1X

HS_FDS_CONNECT_INFO = H1X <===== This is the DSN name that comes from step 2 /etc/odbc.ini

HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so

HS_LANGUAGE=AMERICAN_AMERICA.UTF8

HS_NLS_NCHAR=UCS2

HS_FDS_TRANSACTION_MODEL=READ_ONLY

set ODBCINI=/etc/odbc.ini

Step 7 –

Create listener.ora

LISTENER =

(ADDRESS_LIST=

(ADDRESS=(PROTOCOL=tcp)(HOST=oragwhost)(PORT=1551))

)

SID_LIST_LISTENER=

(SID_LIST=

(SID_DESC=

(SID_NAME=dg4odbc)

(ORACLE_HOME=/oracle/BW1/112_64)

(PROGRAM=dg4odbc)

(ENV=”LD_LIBRARY_PATH=/usr/lib64:/oracle/BW1/112_64/lib:”)

)

)

Step 8 –

Start listener

lsnrctl start

Step 9 –

Let’s first test if unixODBC is working

Log in as the user which will run oracle gateway and check LD_LIBRARY_PATH (refer to step 4 above) and use the below commands.

isql -v <DSN name from step 2 /etc/odbc.ini> <hana user name> <password>

For example isql -v H1X SYSTEM password

+—————————————+

| Connected! |

| |

| sql-statement |

| help [tablename] |

| quit |

| |

+—————————————+

SQL> select * from dummy

+——+

| DUMMY|

+——+

| X |

+——+

SQLRowCount returns 1

1 rows fetched

If you see these outputs you are halfway through. unixODBC is working.

Now it’s time to work on the oracle database from where data will be read with SELECT statements.

Step 10 –

Add entries in tnsnames.ora In my case it will look like the below.

dg4odbc =

(DESCRIPTION=

(ADDRESS=(PROTOCOL=tcp)(HOST=oragwhost)(PORT=1551))

(CONNECT_DATA=(SID=dg4odbc))

(HS=OK)

)

And test it with tnsping

Step 11 –

Create a DB link in the oracle database and run a SELECT command. Commands look like this.

CREATE PUBLIC DATABASE LINK H1X CONNECT TO

“SYSTEM” IDENTIFIED BY “password” USING ‘dg4odbc’;

SQL> select * from dummy@H1X;

DUMMY
——–
X

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter: https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8

Resolving common Oracle Wait Events using the Wait Interface

Wait Event: 

log file parallel write

Possible Causes:

• LGWR waits while writing the contents of the redo log buffer cache to the online log files on the disk
• I/O wait on subsystem holding the online  redo log files

 

ACTIONS:

• Reduce the amount of redo being generated
• Do not leave tablespaces in hot backup mode for longer than
  necessary
• Do not use RAID 5 to redo log files
• Use faster disks to redo log files
• Ensure that the disks holding the archived redo log files and the online redo log files are separate so as to avoid contention
• Consider using NOLOGGING or UNRECOVERABLE options in SQL statements.

REMARKS:

• Reference Note# 34583.1

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter: https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8

Resolving common Oracle Wait Events using the Wait Interface

Wait Event: 

DB file scattered reads

Possible Causes:

• The Oracle session has requested and is waiting for multiple contiguous database blocks (up to DB_FILE_MULTIBLOCK_READ_COUNT) to be read into the SGA from the disk.
• Full Table scans
• Fast Full Index Scans

ACTIONS:

• Optimize multi-block I/O by setting the parameter DB_FILE_MULTIBLOCK_READ_COUNT
• Partition pruning to reduce the number of blocks visited
• Consider the usage of multiple buffers pools and caches of frequently used indexes/tables in the KEEP pool
• Optimize the SQL statement that initiated most of the waits. The goal is to minimize the number of physical and logical reads.
• Should the statement access the data by a full table scan or index FFS? Would an index range or unique scan be more efficient?
• Does the query use the right driving table?
• Are the SQL predicates appropriate for hash or merge join?
•  If full scans are appropriate, can parallel query improve the response time?
• The objective is to reduce the demands for both the logical and
  physical I/Os, and this is best achieved through SQL and application tuning.
• Make sure all statistics are representative of the actual data. Check the LAST_ANALYZED date.

REMARKS:

• If an application that has been running fine for a while suddenly clocks a lot of time on the DB file scattered read event and there hasn’t been a code change, you might want to check to see if one or more indexes have been dropped or become unusable.
• DB file scattered read Reference Note# 34558.1

 

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter: https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8

 

Resolving common Oracle Wait Events using the Wait Interface

Wait Event: 

DB file sequential reads

Possible Causes:

• Use of an unselective index
• Fragmented Indexes
• High I/O on a particular disk or mount point
• Bad application design
• Index reads performance can be affected by  slow I/O subsystem and/or poor database files layout, which results in a higher average
  wait time

Actions:

 

• Check indexes on the table to ensure that the right index is being used
• Check the column order of the index with the WHERE clause of the Top SQL statements
• Rebuild indexes with a high clustering factor
• Use partitioning to reduce the number of blocks being visited
• Make sure optimizer statistics are up to date
• Relocate ‘hot’ datafiles
• Consider the usage of multiple buffers pools and cache frequently used indexes/tables in the KEEP pool
• Inspect the execution plans of the SQL statements that access data
  through indexes
• Is it appropriate for the SQL statements to access data through
  index lookups?
  
• Is the application an online transaction processing (OLTP) or decision support system (DSS)?
• Would full table scans be more efficient?
• Do the statements use the right driving table?
• The optimization goal is to minimize both the number of logical and physical I/Os.

Remarks:

• The Oracle process wants a block that is currently not in the SGA, and it is waiting for the database block to be read into the SGA from the disk.
• Significant DB file sequential read wait time is most likely an application issue.
• If the DBA_INDEXES.CLUSTERING_FACTOR of the index approaches the number of blocks in the table, then most of the rows in the table are ordered. This is desirable.
• However, if the clustering factor approaches the number of rows in the table, it means the rows in the table are randomly ordered and thus it requires more I/Os to complete the operation. You can improve the index’s clustering factor by rebuilding the table so that rows are ordered according to the index key and rebuilding the index thereafter.
• The OPTIMIZER_INDEX_COST_ADJ and OPTIMIZER_INDEX_CACHING initialization parameters can influence the optimizer to favor the operation of the nested loop and choose an index access path over a full table scan.
• Tuning I/O-related waits for Note id  223117.1
• DB file sequential read Reference Note# 34559.1
  

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter: https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8

QUERY TO GET SIZE OF ALL TABLES IN AN ORACLE DATABASE SCHEMA

The following columns are returned in the below query:

• Owner schema.
• Object name and type (TABLE, INDEX, etc.).
• Name of the table this object is associated with. For E.g. indexes are associated with their parent tables.
• Database space is occupied by the object in megabytes.
• Tablespace where an object is stored.
• The number of extents allocated for the object.
• Size of the initial extent in bytes.
• Total database size occupied by the parent table. E.g. for indexes, it will be the size of the parent * table plus the sizes of all the indexes on that table.

 

SELECT * FROM (
SELECT
owner, object_name, object_type, table_name, ROUND(bytes)/1024/1024 AS MB,
tablespace_name, extents, initial_extent,
ROUND(Sum(bytes/1024/1024) OVER (PARTITION BY table_name)) AS total_table_MB
FROM (
SELECT owner, segment_name AS object_name, ‘TABLE’ AS object_type,
segment_name AS table_name, bytes,
tablespace_name, extents, initial_extent
FROM dba_segments
WHERE segment_type IN (‘TABLE’, ‘TABLE PARTITION’, ‘TABLE SUBPARTITION’)
UNION ALL
SELECT i.owner, i.index_name AS object_name, ‘INDEX’ AS object_type,
i.table_name, s.bytes,
s.tablespace_name, s.extents, s.initial_extent
FROM dba_indexes i, dba_segments s
WHERE s.segment_name = i.index_name
AND s.owner = i.owner
AND s.segment_type IN (‘INDEX’, ‘INDEX PARTITION’, ‘INDEX SUBPARTITION’)
UNION ALL
SELECT l.owner, l.column_name AS object_name, ‘LOB_COLUMN’ AS object_type,
l.table_name, s.bytes,
s.tablespace_name, s.extents, s.initial_extent
FROM dba_lobs l, dba_segments s
WHERE s.segment_name = l.segment_name
AND s.owner = l.owner
AND s.segment_type = ‘LOBSEGMENT’
UNION ALL
SELECT l.owner, l.column_name AS object_name, ‘LOB_INDEX’ AS object_type,
l.table_name, s.bytes,
s.tablespace_name, s.extents, s.initial_extent
FROM dba_lobs l, dba_segments s
WHERE s.segment_name = l.index_name
AND s.owner = l.owner
AND s.segment_type = ‘LOBINDEX’
)
WHERE owner in UPPER(‘&TABLE_NAME’)
)
WHERE total_table_MB > 10
ORDER BY total_table_MB DESC, MB DESC
/

 

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter: https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8