It would seem a simple select, but Oracle thought

Question

PHP

Yesterday morning I had a chance to do some simple analytics. The task was next. Take data from Oracle database and MySQL database and compare them. I want to note right away that I am far from an expert on Oracle DBMS, but I, php and mysql have been familiar for a very long time. And we can be called good friends.

So, leaving the lyrics, I want to ask a question to the venerable publicwhy the code below hung the oracle database tightly (that is, until the sessions organized by OCI + php in Oracle were killed, this DBMS could not perform any actions with the data at all).

$conn = oci_connect(DB_oralogin, DB_orapass, DB_oradatabase, "CL8MSWIN1251");

$stid = oci_parse($conn, "select distinct

C.ID CID,

C.NAME CNAME,

get_conts_stcount(C.ID,PS.SEM) STCOUNT,

C.INYEAR,

PS.SEM,

F.ID FID,

F.NAME FNAME

from

CONTS C,

FACS F,

PLANBASE P,

PLANSPECS PS

where

C.ARCHIVE=0

and C.COMPANY=2198875

and C.FAC=F.ID

and P.CONTS=C.ID

and PS.PID=P.ID

and (PS.SEM=get_course(C.INYEAR,2011)*2-1)

order by FNAME desc");

oci_execute($stid);

while (($row = oci_fetch_array($stid, OCI_ASSOC+OCI_RETURN_NULLS)) != false) {

$query = $this->db->query('SELECT DISTINCT

COUNT(DISTINCT uid_gid.uid) as counts,

uid_gid.gid,

uid_gid.adopted,

uid_gid.prefix

FROM

uid_gid

WHERE

uid_gid.prefix = '.htmlentities($row["CID"], ENT_QUOTES, 'cp1251').' AND

uid_gid.adopted = 1 and

uid_gid.gid = 1');

foreach ($query->result() as $rows){

$counts = $rows->counts;

}

echo htmlentities($row["FNAME"], ENT_QUOTES, 'cp1251').' '.htmlentities($row["CNAME"], ENT_QUOTES, 'cp1251').' contingent: '.htmlentities($row["STCOUNT"], ENT_QUOTES, 'cp1251').' registered: '.$counts."

";

}

die();

those. the data to a question:
select from returns about 7000 lines.

Answer 1

Make sure the query is the one to blame. Run it in sqlplus. If it works, it's not Oracle that's to blame, but your PHP. If it does not work, then you need to deal with the request. To begin with, simplify the query, leaving a simple selection from one table without calling stored procedures. Then, step by step, add links to other tables and calls to stored procedures to the query. It will freeze at some step - you will at least know which way to dig further.