BOB: Business Objects Board
Not endorsed by or affiliated with SAP

Register | Login 

Follow BOB on Twitter! 
Follow BOB on Twitter! (Opens a new window)  

General Notice: BOB is going to retire...please see details here.
General Notice: No events within the next 45 days.

Oracle hints

Search this topic... | Search Semantic Layer / Universe Designer... | Search Box
Register or Login to Post    Forum Index -> Semantic Layer / Universe Designer  Previous TopicPrint TopicNext Topic
Author Message
Forum Addict
Forum Addict

Joined: 01 Mar 2005

Posts: 2806
Location: Cheshire, England

PostPosted: Mon Oct 07, 2019 7:26 am 
Post subject: Oracle hints

Can anyone explain hints to me in non-DBA speak, please?

I'm running an SQL query (to populate a dashboard). It joins 10 large tables (all except one with inner joins - the single outer join is against a small indexed lookup table). I'm filtering the data very tightly, so my final result set is only around 2.5k rows.

I can run this from my pc in SQL Developer with a admin account on the data warehouse. It runs in a couple of minutes with no issues.

If I try to run it via a script from a server, it gives me tablespace errors and falls over. I have no idea why. I am running more complex queries with bigger results with no problems at all.

I know I need to tune/optimise the SQL somehow, but I'm not a DBA and don't really understand what I am doing. I've tried adding a PARALLEL hint and an ORDERED hint - but I don't know what order the tables ought to be in to start with. Our resident Oracle DBA/guru retired in the summer.

The current error is
ERROR: 3   
OraOLEDB: ORA-12801: error signaled in parallel query server P466
ORA-01652: unable to extend temp segment by 64 in tablespace TEMP

Any advice gratefully received!


ETA: Fixed (after two days of googling) by adding a PARALLEL hint, plus rewriting all the joins and filters into ANSI-92 syntax. It doesn't explain why other huge queries still run in non-ANSI-92, but at least it's working ...
Back to top
Forum Fanatic
Forum Fanatic

Joined: 29 Aug 2002

Posts: 6703
Location: Connecticut, USA

PostPosted: Tue Oct 08, 2019 7:09 am 
Post subject: Re: Oracle hints

It could just be luck, and not from the changes you made to the query. The easy answer is to add more temp space, but your DBA should do the analysis for you.
Back to top
Display posts from previous:   
Register or Login to Post    Forum Index -> Semantic Layer / Universe Designer  Previous TopicPrint TopicNext Topic
Page 1 of 1 All times are GMT - 5 Hours
Jump to:  

Index | About | FAQ | RAG | Privacy | Search |  Register |  Login 

Get community updates via Twitter:

Not endorsed by or affiliated with SAP
Powered by phpBB © phpBB Group
Generated in 0.0288 seconds using 17 queries. (SQL 0.0033 Parse 0.0009 Other 0.0246)
CCBot/2.0 (
Hosted by | Terms of Service
phpBB Customizations by the
Shameless plug for Moments of Light Logo