DevOps, CI/CD and Automation

Good afternoon!

I'm currently moving one of our company applications from MSSQL to Oracle and somehow can't get rid of a major performance issue. Compared to MSSQL, my Oracle stored procedures run around 400% slower, thus making the entire application unusable ... each page generation currently takes between 9 - 15 seconds rendering time and that's way too slow :o(
I figured out that most time is lost when I fetch data from a refcursor returned by a Oracle stored procedure. The entire SPs are pretty fast if run within SQL Developer or isql*plus, but take really long if used within a PHP script.

If I load data directly from tables through SELECT statements, results are returned nearly immediately, but our programming policy dictates us to use stored procedures whenever possible (because of seperation from application and database structure).

I wrote a database class for my Oracle connections and this is the portion of code that takes so long:

	public function do_fetch_cursor($sql)
	{
		try {
			$cursor = oci_new_cursor ($this->dbh);
			$stmt = oci_parse ($this->dbh, $sql);
			oci_bind_by_name ($stmt, "cursor", $cursor, -1, OCI_B_CURSOR);
			oci_execute ($stmt);
			oci_execute ($cursor);
			
>> is there a way to bulk fetch an associative array instead of reading single rows?
			$data = array();
			while ($row = oci_fetch_assoc($cursor)){
				$data[] = $row;
			}
			return $data;
		}
		catch (Exception $ex){
			$this->lastError = $ex->getMessage();
			return false;
		}
	}

I guess that I'm doing something wrong here and would be really glad if you could point out problems in my way of retrieving data.

Thanks a lot in advance!!

Best regards, Sascha