When I try running the stored procedure with only this query:

SELECT TAB1.COL6 
FROM TAB1
INNER JOIN TAB2 ON TAB1.COL1 = TAB2.COL1
WHERE TAB1.COL2 = 10;

It takes 5 seconds to get the resultset and if I run the same query outside of the stored procedure, the query fetches the result in only 250ms.

Is there any way to optimize this query so that the SP fetches the result in <1 Sec?

TAB 1 structure

(
    Col0 Bigint (primary key)
    Col1  Char(8)
    Col2 Smallint
    Col3 Timestamp
    Col5 Timestamp
    Col6 Bigint
)

Indexes on TAB 1:

Create Index Index_Name ON TAB1 (Col1,Col2);

TAB1 has a row count of around 2 million And TAB2 has a row count of 30. The number of columns in TAB1 is 6 (structure shown above) and TAB2 has only 1 column.

TAB 2 Structure

    (Col1  Char(8))

TAB2 is a global temporary session table I created in the SP to store only the IDs that is needed and then I inner joined that table with TAB1.

The expected rowcount of the resultset will be around 4500.

PS: The table TAB1 is a very large table and I am not able to get the Explain Plans for both the query and stored procedure.