OraFAQ Forum: Community Hangout » make_query

Home » Other » Community Hangout » make_query_fast hint (

) 1 Vote

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

make_query_fast hint [message #652661]

Wed, 15 June 2016 15:16

Barbara Boehmer
Messages: 9105
Registered: November 2002
Location: California, USA

Senior Member

This wasn't on our forums, but I just had to share. I am used to people comically suggesting the make_query_fast hint and such, but I don't think I have ever seen anybody not realize that it was a joke and actually test it and post the results.

https://community.oracle.com/thread/3940746

Then it continues to get better. No index. Still no query or explain plant posted. Waiting to see what happens next.

[Updated on: Wed, 15 June 2016 15:18]

Report message to a moderator

Re: make_query_fast hint [message #652662 is a reply to message #652661]

Wed, 15 June 2016 15:19

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

Senior Member

I always tell people that they should write all the SQL keywords in upper case.

Report message to a moderator

Re: make_query_fast hint [message #652663 is a reply to message #652662]

Wed, 15 June 2016 15:21

Barbara Boehmer
Messages: 9105
Registered: November 2002
Location: California, USA

Senior Member

John Watson wrote on Wed, 15 June 2016 13:19

I always tell people that they should write all the SQL keywords in upper case.

Actually, I frequently do that. I got used to the standards in documentation and find it easier to read.

Report message to a moderator

Re: make_query_fast hint [message #652664 is a reply to message #652663]

Wed, 15 June 2016 15:22

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

Senior Member

If you list the tables in alphabetical order that should help too Smile

Report message to a moderator

Re: make_query_fast hint [message #652680 is a reply to message #652664]

Thu, 16 June 2016 03:06

gazzag
Messages: 1119
Registered: November 2010
Location: Bedwas, UK

Senior Member

It's an undocumented, secret parameter but just between friends:

Code: [Select all] [Show/ hide]

SQL> ALTER SYSTEM SET _go_faster = 'YES' SCOPE = SPFILE;
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP;

Report message to a moderator

Re: make_query_fast hint [message #652683 is a reply to message #652680]

Thu, 16 June 2016 03:25

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

Senior Member

You can also reduce the temperature in the data centre. As it drops towards 0K, the superconductivity effect raises the machine's clock speed.

Report message to a moderator

Re: make_query_fast hint [message #652757 is a reply to message #652683]

Sat, 18 June 2016 01:38

Frank Naude
Messages: 4596
Registered: April 1998

Senior Member

Oracle can be terribly confusing. Note how the MAKE_QUERY_FAST hint actually helped Smile

Quote:

Without any hint query takes 735.417 Seconds
ALL_ROWS it takes 334.225 Seconds
NO_MERGE it takes 702.571 Seconds
MAKE_QUERY_FAST it takes 453.109 Seconds

Can you blame the guy for believing it? The cache effect is not immediately obvious to newbies.

Regards.
Frank

Report message to a moderator

Re: make_query_fast hint [message #658621 is a reply to message #652757]

Thu, 22 December 2016 00:32

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

Sorry to resurrect the dead, but this is my first visit back for a long time...

/*+ MAKE_QUERY_FAST */

You laugh, but in SQL Server, it exists (well, 'FAST' does). It's their version of the FIRST_ROWS hint. First time I saw it, I thought of this old Oracle meme.

Ross Leishman

Report message to a moderator

Re: make_query_fast hint [message #658974 is a reply to message #658621]

Tue, 03 January 2017 02:51

Roachcoach
Messages: 1576
Registered: May 2010
Location: UK

Senior Member

The other reason this sometimes works is when there's a series of legacy and now outdated hints in the code, someone slaps an invalid one at the start, takes out all the ones after it by accident and voilla, magically it is better because the old hints are now not applied.

Code: [Select all] [Show/ hide]

SQL>  explain plan for select /*+ full(t) */ count(*) from system.t t
  2  /

Explained.

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2966233522

-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |   148   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| T    | 33984 |   148   (0)| 00:00:01 |
-------------------------------------------------------------------

9 rows selected.

SQL>  explain plan for select /*+ GOD_PLEASE_GO_FASTER() full(t) */ count(*) from system.t t
  2  /

Explained.

SQL>  select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 325870156

----------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Cost (%CPU)| Time     |
----------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |     1 |    22   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE       |      |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| IDX1 | 33984 |    22   (0)| 00:00:01 |
----------------------------------------------------------------------

9 rows selected.

SQL>

[Updated on: Tue, 03 January 2017 02:56]

Report message to a moderator

Previous Topic:	Come to Vietnam, why and why not?
Next Topic:	cookiemonster

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat May 31 15:48:53 CDT 2025