Home » RDBMS Server » Performance Tuning » query recommedation (Oracle 10.2.0.4 solaris 2.10)
query recommedation [message #563756] Thu, 16 August 2012 15:31 Go to next message
BeefStu
Messages: 189
Registered: October 2011
Senior Member
I have an APP that truncates tables and loads data, which in turn makes the stats stale. I ran the query advisor (see attachment) and of course it ecommends running stats or accept a profile.

I am a bit hestiant to accept a profile since I don't have much experience with that. As for running stats I really don't want to do that as it may cause a load on my DB.

In turn, I would like to consider having my APP team change the query to pass a hint to use the best query plan.


Can somebody please help me with the syntax to pass the hint to emulate good attached plan? Or is this a bad way to proceed?

Thanks to all who answer




select /* INDEX FAST FULL SCAN PK_PLACEMENT_REQUEST_QUEUE */
sum(lastshares) as "ROSEN"
from nyeo.fix_exec_reports fer, nyeo.placement_request_queue q,
nyeo.nyeo_block_control bc
where fer.clordid = q.sequence_number
and q.blockid = bc.blockid
and upper(bc.deskname) like '%ROSEN%'

  • Attachment: ORAEEOP.txt
    (Size: 5.64KB, Downloaded 32 times)
Re: query recommedation [message #563778 is a reply to message #563756] Fri, 17 August 2012 01:31 Go to previous messageGo to next message
John Watson
Messages: 4093
Registered: January 2010
Location: Global Village
Senior Member
Are you asking "what is the index fast full scan hint"? If so,
orcl> set autot traceonly exp
orcl> select empno from emp;

Execution Plan
----------------------------------------------------------
Plan hash value: 179099197

---------------------------------------------------------------------------
| Id  | Operation        | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT |        |    14 |    56 |     1   (0)| 00:00:01 |
|   1 |  INDEX FULL SCAN | PK_EMP |    14 |    56 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------

orcl> select /*+ index_ffs(emp pk_emp) */ empno from emp;

Execution Plan
----------------------------------------------------------
Plan hash value: 366039554

---------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |    14 |    56 |     2   (0)| 00:00:01 |
|   1 |  INDEX STORAGE FAST FULL SCAN| PK_EMP |    14 |    56 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

orcl>
Re: query recommedation [message #563781 is a reply to message #563756] Fri, 17 August 2012 01:36 Go to previous message
Michel Cadot
Messages: 57614
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Did you read the documentation about hints?
Given your skills, I recommend you to NOT use hints but learn SQL.

Regards
Michel
Previous Topic: how to find all indexes scripts in particular schema
Next Topic: Re: Get Unique row number for list of values (split from unrelated thread by bb)
Goto Forum:
  


Current Time: Mon Apr 21 03:21:16 CDT 2014

Total time taken to generate the page: 0.10371 seconds