select e.emp_name,e.emp_age,temp.emp_salary,temp.emp_highest_salary from Employee e ,emp_temporory_table temp where temp.emp_id = e.emp_id
The above query runs in one of our PL-SQL procedure,and found that it is hogging 90% CPU.
Here is the create table script for the global table.
CREATE GLOBAL TEMPORARY TABLE emp_TEMPORORY_TABLE
( "EMP_ID" NUMBER(12,0),
"EMP_HIGHEST_SALARY NUMBER(12,0)
) ON COMMIT DELETE ROWS ;
In UAT environment we really dont see the issue because I guess we dont have the volume of data in the Employee table(it is a massive table in production...around 8 million records)in uat.
But in production we see the issue everytime.Even after gathering the statistics in the employee table we still see the issue.
I am about to propose to create an index in the global temporory table emp_temporory_table (on emp_id) ,to see if this helps.
What do you guys think.