partition - ORACLEAGENT BLOG

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:

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:

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:

Oracle Database In-Memory concepts in 19c

Version 19.8 has below updates:

New Features

The following major features are new in this release.

Database In-Memory Base LevelStarting with Oracle Database release 19c, version 19.8, you can enable the Database In-Memory Base Level by setting the INMEMORY_FORCE initialization parameter to BASE_LEVEL. The Base Level enables you to experiment with In-Memory features without purchasing the Oracle Database In-Memory option.When the Base Level is enabled, the IM column store size is limited to 16 GB for a CDB or non-CDB and for every database instance in an Oracle RAC database. Also, the compression level for all objects and columns is set to QUERY LOW automatically and transparently, and Automatic In-Memory is disabled. The CELLMEMORY feature is disabled for Oracle Exadata.
Starting with Oracle Database release 19c, version 19.8, you can use the CellMemory feature without enabling the IM column store by setting INMEMORY_FORCE=CELLMEMORY_LEVEL and INMEMORY_SIZE=0. With these settings, the IM column store is not enabled and queries can use CellMemory to scan objects.In previous releases of Oracle Database, in order to use the CellMemory feature, you were required to enable the IM column store, even if you had no intention of using it. This incurred the overhead of enabling the IM column store without any benefit.

Changes in Oracle Database Release 19c, Version 19.1

New Features

Database In-Memory Base LevelEnable the Database In-Memory Base Level by setting the INMEMORY_FORCE initialization parameter to BASE_LEVEL. The Base Level enables you to experiment with In-Memory features without purchasing the Oracle Database In-Memory option.When the Base Level is enabled, the IM column store size is limited to 16 GB for a CDB or non-CDB and for every database instance in an Oracle RAC database. Also, the compression level for all objects and columns is set to QUERY LOW automatically and transparently, and Automatic In-Memory is disabled. The CELLMEMORY feature is disabled for Oracle Exadata.
Database In-Memory wait on populateThe DBMS_INMEMORY_ADMIN.POPULATE_WAIT function initiates population of all INMEMORY objects that have a priority greater than or equal to the specified priority, and returns a status value for the population. A user-specified interval specifies the maximum time that the function waits before returning the value to the caller.
- The ORACLE_HIVE and ORACLE_BIGDATA drivers are supported.
- Parallel query is supported.
- A full table scan populates an In-Memory external table. In previous releases, population required using the POPULATE or REPOPULATE procedure of DBMS_INMEMORY.
- The In-Memory background processes, rather than a foreground process, now drop IM segments.Big Data and performance enhancements for In-Memory external tablesThis release introduces several manageability and performance improvements to the In-Memory external tables feature:
Hybrid partitioned tablesPartitions can reside in both Oracle Database segments and in external files and sources. This feature significantly enhances partitioning for Big Data SQL, where large portions of a table can reside in external partitions. Only the internal partitions of a hybrid partitioned table inherit the INMEMORY attribute.
Oracle Database Resource Manager automatically enabled for Database In-MemoryWhen INMEMORY_SIZE is greater than 0, the Resource Manager is automatically enabled.
Oracle Data Guard Multi-Instance Redo Apply supports the IM column storeSetting the initialization parameter ENABLE_IMC_WITH_MIRA to TRUE enables the IM column store and Data Guard Multi-Instance Redo Apply at the same time on an Active Data Guard standby database. By default, ENABLE_IMC_WITH_MIRA is FALSE.

Reference : docs.oracle.com 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

Oracle Database In-Memory concepts in 19c

Version 19.8 has below updates:

New Features

The following major features are new in this release.

Database In-Memory Base LevelStarting with Oracle Database release 19c, version 19.8, you can enable the Database In-Memory Base Level by setting the INMEMORY_FORCE initialization parameter to BASE_LEVEL. The Base Level enables you to experiment with In-Memory features without purchasing the Oracle Database In-Memory option.When the Base Level is enabled, the IM column store size is limited to 16 GB for a CDB or non-CDB and for every database instance in an Oracle RAC database. Also, the compression level for all objects and columns is set to QUERY LOW automatically and transparently, and Automatic In-Memory is disabled. The CELLMEMORY feature is disabled for Oracle Exadata.
Starting with Oracle Database release 19c, version 19.8, you can use the CellMemory feature without enabling the IM column store by setting INMEMORY_FORCE=CELLMEMORY_LEVEL and INMEMORY_SIZE=0. With these settings, the IM column store is not enabled and queries can use CellMemory to scan objects.In previous releases of Oracle Database, in order to use the CellMemory feature, you were required to enable the IM column store, even if you had no intention of using it. This incurred the overhead of enabling the IM column store without any benefit.

Changes in Oracle Database Release 19c, Version 19.1

New Features

Database In-Memory Base LevelEnable the Database In-Memory Base Level by setting the INMEMORY_FORCE initialization parameter to BASE_LEVEL. The Base Level enables you to experiment with In-Memory features without purchasing the Oracle Database In-Memory option.When the Base Level is enabled, the IM column store size is limited to 16 GB for a CDB or non-CDB and for every database instance in an Oracle RAC database. Also, the compression level for all objects and columns is set to QUERY LOW automatically and transparently, and Automatic In-Memory is disabled. The CELLMEMORY feature is disabled for Oracle Exadata.
Database In-Memory wait on populateThe DBMS_INMEMORY_ADMIN.POPULATE_WAIT function initiates population of all INMEMORY objects that have a priority greater than or equal to the specified priority, and returns a status value for the population. A user-specified interval specifies the maximum time that the function waits before returning the value to the caller.
- The ORACLE_HIVE and ORACLE_BIGDATA drivers are supported.
- Parallel query is supported.
- A full table scan populates an In-Memory external table. In previous releases, population required using the POPULATE or REPOPULATE procedure of DBMS_INMEMORY.
- The In-Memory background processes, rather than a foreground process, now drop IM segments.Big Data and performance enhancements for In-Memory external tablesThis release introduces several manageability and performance improvements to the In-Memory external tables feature:
Hybrid partitioned tablesPartitions can reside in both Oracle Database segments and in external files and sources. This feature significantly enhances partitioning for Big Data SQL, where large portions of a table can reside in external partitions. Only the internal partitions of a hybrid partitioned table inherit the INMEMORY attribute.
Oracle Database Resource Manager automatically enabled for Database In-MemoryWhen INMEMORY_SIZE is greater than 0, the Resource Manager is automatically enabled.
Oracle Data Guard Multi-Instance Redo Apply supports the IM column storeSetting the initialization parameter ENABLE_IMC_WITH_MIRA to TRUE enables the IM column store and Data Guard Multi-Instance Redo Apply at the same time on an Active Data Guard standby database. By default, ENABLE_IMC_WITH_MIRA is FALSE.

Tag Archives: partition

How does it work?

Step 1 –

Step 2 –

Step 3 –

Step 4 –

Step 5 –

Step 6 –

Step 7 –

Step 8 –

Step 9 –

Step 10 –

Step 11 –

Wait Event:

log file parallel write

DB file scattered reads

DB file sequential reads

Actions:

Remarks:

Version 19.8 has below updates:

New Features

Changes in Oracle Database Release 19c, Version 19.1

New Features

Version 19.8 has below updates:

New Features

Changes in Oracle Database Release 19c, Version 19.1

New Features