OraFAQ Forum: SQL & PL/SQL » Global Temp Table

Home » SQL & PL/SQL » SQL & PL/SQL » Global Temp Table (11G)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Global Temp Table [message #620173]

Tue, 29 July 2014 12:46

Yuvraaj
Messages: 122
Registered: January 2011
Location: California, USA

Senior Member

Hi All,

I have a Global temporary table, select operation is taking time to fetch the records (90 mins) in dev but in production its failing with UNABLE TO CONNECT ORACLE.

Tried with /*Parallel*/ hint, i.e. with /*Parallel(Auto)*/, /*Parallel(8)*/ it fetched with in 10 to 15 mins.

Couple of runs went fine, but after that i received 'ORA-12805: parallel query server died unexpectedly.'

Can we use /*parallel*/ on Global Temp Tables ? If not, creating indexes will improve the performance?

I'm read only user to db so, couldn't able to provide much info.

Thanks.

Report message to a moderator

Re: Global Temp Table [message #620174 is a reply to message #620173]

Tue, 29 July 2014 12:51

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>I'm read only user to db so, couldn't able to provide much info.
so ask DBA for assistance, since you are read only & can't change anything.

Report message to a moderator

Re: Global Temp Table [message #620175 is a reply to message #620173]

Tue, 29 July 2014 12:52

John Watson
Messages: 8929
Registered: January 2010
Location: Global Village

Senior Member

yuvraaj, your post makes no sense. In no particular order:

Your hints are not hints. They are comments, because you have forgotten the "+" symbol.

If you have a read-only account, there will be no rows in the globsl temporary table, because you cannot insert any.

If you getting an UNABLE TO CONNECT error (which is certainly not an Oracle error message) how have you managed run any queries at all?

Lastly, please read How to use [code] tags and make your code easier to read

Report message to a moderator

Re: Global Temp Table [message #620177 is a reply to message #620175]

Tue, 29 July 2014 13:29

Yuvraaj
Messages: 122
Registered: January 2011
Location: California, USA

Senior Member

its a Typo, the query is like

 select /*+ first_rows parallel(auto)*/ ...

Regarding read_only, I mean, the actual sql is been executed by owner, but I do have only read access.

Sorry for the noise.

All I would like to know can we use parallel hint on GTT? if so, what could be the possible reason in getting this 'ORA-12805: parallel query server died unexpectedly.' exception.

Thanks!

Report message to a moderator

Re: Global Temp Table [message #620181 is a reply to message #620177]

Tue, 29 July 2014 13:37

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

post actual SQL & EXPLAIN PLAN
Please post reproducible test case.

12805, 00000, "parallel query server died unexpectedly"
// *Cause: A parallel query server died unexpectedly, PMON cleaning
//         up the process.
// *Action: Check your system for anomalies and reissue the statement.
//          If this error persists, contact Oracle Support Services.
//          See trace file for more details.

Report message to a moderator

Re: Global Temp Table [message #620184 is a reply to message #620177]

Tue, 29 July 2014 13:41

John Watson
Messages: 8929
Registered: January 2010
Location: Global Village

Senior Member

If you want to find out if you can use parallel query against a gtt, test it:

orclz>
orclz> create global temporary table gtt1 as select * from dual;

Table created.

orclz> set autot on exp
orclz> select /*+ parallel(2) */ * from gtt1;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 2686062243

-------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |     1 |     2 |     2   (0)| 00:00:01 |        |      |         |
|   1 |  PX COORDINATOR      |          |       |       |            |          |        |      |         |
|   2 |   PX SEND QC (RANDOM)| :TQ10000 |     1 |     2 |     2   (0)| 00:00:01 |  Q1,00 | P->S | QC (RAND)
|   3 |    PX BLOCK ITERATOR |          |     1 |     2 |     2   (0)| 00:00:01 |  Q1,00 | PCWC |         |
|   4 |     TABLE ACCESS FULL| GTT1     |     1 |     2 |     2   (0)| 00:00:01 |  Q1,00 | PCWP |         |
-------------------------------------------------------------------------------------------------------------

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - Degree of Parallelism is 2 because of hint

orclz>

Report message to a moderator

Previous Topic:	Short-Circuit Evaluation operators in oracle
Next Topic:	Convert SYSDATE to Julian Date format

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 15:36:55 CDT 2024