Oracle BI Query not Cached.....know the reasons

There are several reasons for obiee query not being cached.Here are most of them.
First of all are you sure its not cached.

To Check,

1.Open your RPD in online mode.
2.Under Manage you will find cache.
3.When you click on it Cache manager is popped up.
4.If you find the query in the window then you are wrong,its cached else there is a problem we have to fix.

Now lets see the possible reasons for this

1.Cache Enabled :
Was your cache enabled.
         To Know open the NQSCONFIG.INI file
         Under Cache Sction if 'YES' tihen everything is fine here.
         If no then make it YES.

2.Use of Non-cacheable SQL functions:

      If any of the Non-cacheable functions are used in the request then the query will not be cached.

       i.e CURRENT_TIMESTAMP, CURRENT_DATE, CURRENT_TIME, RAND, POPULATE

3.Non-cacheable Table:
             If the query for the request makes any connection to a non cacheable table then the query/results will not be cached even if all other tables are set to cacheable.


4.  Query execution Terminated:
While running the request on Presentation Server if the execution is forcibly cancelled or time out occurred, cache will not create.

5.Bad cache configuration:
If the MAX_CACHE_ENTRY_SIZE parameter is set to a bigger value as compared to the actual storage space (DATA_STORAGE_PATHS) available on the drive then nothing can possibly be added to the cache.

6.Result set to Large to Handle by Cache:
The max row limit for cache is set in a parameter in nqsconfig.ini, named MAX_ROWS_PER_CACHE_ENTRY (default 100,000 rows) and MAX_CACHE_ENTRY_SIZE (default 1 MB). If the rows retrieved in the request exceeds the MAX ROW LIMIT or the data retrieved by the request exceeds the MAX CACHE ENTRY SIZE then the cache will not be created.

7.When a Query got a cache hit:
In general, if the query gets a cache hit on a previously cached query, then the results of the current query are not added to the cache. The only exception is the query hits that are aggregate “roll-up” hits, will be added to the cache if the nqsconfig.ini parameter POPULATE_AGGREGATE_ROLLUP_HITS has been set to Yes.

Downtime Query for Oracle Production Scheduling

SELECT org.organization_code organization_code,
    crd.resources resources,
    crm.resource_desc resource_description,
    TO_CHAR(grum.from_date, 'DD-MON-YYYY HH24:MI:SS') start_time,
    TO_CHAR(grum.to_date, 'DD-MON-YYYY HH24:MI:SS') stop_time,
    floor(((grum.to_date-grum.from_date)*24*60*60)/3600) || ':' || ROUND((((grum.to_date-grum.from_date)*24*60*60) -     floor(((grum.to_date-grum .from_date)*24*60*60)/3600)*3600)/60) || ':' || ROUND((((grum.to_date-grum.from_date)    *24*60*60) - floor(((grum.to_date-grum .from_date)*24*60*60)/3600)*3600 - (ROUND((((grum.to_date-    grum.from_date)*24*60* 60) - floor(((grum.to_date-grum.from_date)*24*60*60)/3600)*3600)/60)*60) ))
    elapsed_time,
    grum.reason_code reason_code,
    mtr.description reason_description
FROM     org_organization_definitions org,
    cr_rsrc_dtl crd,
    CR_RSRC_MST crm,
    gmp_rsrc_unavail_man grum,
    MTL_TRANSACTION_REASONS mtr
WHERE org.organization_id = crd.organization_id
    --AND org.organization_code = NVL(:ORG_CODE,org.organization_code)
    AND crd.resource_id = grum.resource_id
    AND crd.resources = crm.resources
    --AND crd.resources = NVL(:RESOURCES,crd.resources)
    AND grum.reason_code = mtr.reason_name(+)
    --AND grum.reason_code = NVL(:REASON_CODE,grum.reason_code)
    AND (TO_CHAR(grum.from_date,'YYYY/MM/DD') >= '2012/02/22'
    AND TO_CHAR(grum.to_date,'YYYY/MM/DD') <= '2012/02/22')
     -- and (grum.to_date >= '0-jan-12' and grum.to_date <'04-jan-12')
    -- AND to_char(nvl(grum.from_date,SYSDATE),'YYYY/MM/DD HH24:MI:SS') between nv l    (:START_FROM_DATE,to_char(nvl(grum.from_date,SYSDATE),'YYYY/MM/DD HH24:MI:SS'))
    AND nvl(:START_TO_DATE,to_char(nvl(grum.from_date,SYSDATE),'YYYY/MM/DD HH24:MI: SS'))
    -- AND to_char(nvl(grum.to_date,SYSDATE),'YYYY/MM/DD HH24:MI:SS')
    between nvl (:STOP_FROM_DATE,to_char(nvl(grum.to_date,SYSDATE),'YYYY/MM/DD HH24:MI:SS'))
    AND nvl(:STOP_TO_DATE,to_char(nvl(grum.to_date,SYSDATE),'YYYY/MM/DD HH24:MI:SS'))
ORDER BY org.organization_code, crd.resources, grum.from_date

Ship To and Bill To Address in Order Management

select     ooha.order_number,
    msi.segment1,
    hcsu_ship.site_use_id,
    hcasa_ship.cust_acct_site_id,
    hps.party_site_id,
    hp.party_name "Customer Name",
    sota.name "Order Type",
    CONCAT(CONCAT(CONCAT(CONCAT(CONCAT(hl_ship.address1,hl_ship.address2),h         l_ship.address3),hl_ship.address4),hl_ship.state),hl_ship.country) "Ship To Address" ,
    round(oola.ordered_quantity2) "Ordered Quantity"

from oe_order_lines_all oola
    inner join OE_ORDER_HEADERS_ALL ooha on oola.header_id= ooha.header_id
--inner join oe_order_lines_all oola_shipto oola_shipto.ship_to_org_id = hcsu_ship.site_use_id
inner join so_order_types_all sota on ooha.order_type_id = sota.order_type_id
inner join HZ_CUST_ACCOUNTS hca on hca.cust_account_id = ooha.sold_to_org_id
inner join HZ_PARTIES hp on hp.party_id = hca.party_id
inner join mtl_system_items_b msi on oola.inventory_item_id = msi.inventory_item_id
    AND oola.ship_from_org_id = msi.organization_id
 --inner join HZ_CUST_SITE_USES_ALL hcsu_ship on oola.ship_to_org_id = hcsu_ship.site_use_id
inner join HZ_CUST_SITE_USES_ALL hcsu_ship on oola.invoice_to_org_id = hcsu_ship.site_use_id
inner join HZ_CUST_ACCT_SITES_ALL hcasa_ship on hcsu_ship.cust_acct_s ite_id = hcasa_ship.cust_acct_site_id i
nner join HZ_PARTY_SITES hps on hcasa_ship.party_site_id = hps.party _site_id
inner join HZ_PARTY_SITE_USES hpsu_ship on hpsu_ship.party_site_id = hp s.party_site_id
inner join HZ_LOCATIONS hl_ship on hps.location_id = hl_ship.location _id
where ooha.order_number = :ORDER_NUMBER