Already a member?
Sign in
Welcome! Wikis are websites that everyone can build together. It's easy!
Location: Business Intelligence
Discussion: DISCOVERER: Quick Tips
Keyword tags:
analytics
BI Publisher
BI SIG
BI/DW
BIWA
Data Warehousing
Discoverer
ILM
OBIEE
OLAP
Oracle Data Mining
Siebel
Watch
 td1947 |
DISCOVERER: Quick Tips
Jan 8 2008, 10:26 AM EST DISCOVERER QUICK TIPS (covers 4.1.48, but may cover other levels) : 1) After cloning a DB and APPS code tree, the Discoverer part will not always work. Ensure that the pref.txt file has the correct IP address of the "new" server that runs the Discoverer code, and after the Discoverer services are started, create an "unregisterall.sh" script (like the "registerall.sh" script, but runs all 3 "unreg*" scripts one after the other). Run this, followed by a "registerall.sh", then "applypreferences.sh", and check the errors.txt file for zero length (no errors present). Run checkdiscoverer.sh, also to check all is OK. 2) switch off Query Prediction (QPPEnable – make his zero in the pref.txt), as it can help performance. 3) There are often several levels of heavy hash-joins going on in the PLAN that the CBO sets up for Discoverer report runs. If not catered for, these overflow into the TEMP TS, and get slowed down ten-fold by excessive disk I/O in there. I would recommend if your DB server has , say 32GB memory, set your pga_aggregate_target to a quarter of this, to 8GB, but also override the AUTO settings, by adding hash_area_size = 200M or even 400M, and the sort_area_size to 100M, or 200M in the init<DB>.ora if still getting disk sorts. 4) Check for orphaned ws processes on the Discoverer server (or on your main DB server if running all under on code-tree), and kill them regularly. These can occur after a “Memory Heap” error in the user stack. I have user session logging on, so I can detect this with a cron check of the user logs, then locate the process list, and kill them. This stops CPU hogging. I can provide the scripts I use for this if you need them. 5) When stopping Discoverer services each night (If you are not 24x7) for cleanup, ensue that all the following files are removed from /tmp, as they are sometimes missed: /tmp/orb*, /tmp/dc*, /tmp/opm* 1 out of 3 found this valuable. Do you? |
|
td1947 |
RE: DISCOVERER: Quick Tips
Jan 14 2008, 8:57 AM EST IN ADDITION TO THE ABOVE: I have found that the gatekeeper startup quotes the log file as beign in the sme directory as the startup code (utils), but in fact the MAIN log is in: $IAS_ORACLE_HOME/Apache/Apache/htdocs/discwb4/applet as gatekeeper.log. NOTE if you are having port conflicts, with multiple database on your system, check the gatekeeper.properties file, and ensure you do not have, say a default port like 8007 used for the "exterior_port". I recommend using 150xx, where xx is the last 2 numerics of your WEB port, e.g. 15084. If you want to check the information that is in your current IOR files (locator.ior, and gatekeeper.ior), create a script that calls the discwb.sh env sourcer, then define a variable where the vbroker/bin lives, and export it as VBIN. Then execute in the script: ${VBIN}/printIOR $IAS_ORACLE_HOME/Apache/Apache/htdocs/discwb4/applet/locator.ior ${VBIN}/printIOR $IAS_ORACLE_HOME/Apache/Apache/htdocs/discwb4/applet/gatekeeper.ior NOTE: ensure each of the above lines are on ONE executable line. TD1947. 0 out of 1 found this valuable. Do you? |
|
td1947 |
RE: DISCOVERER: Quick Tips (dealing with Query stats index growth)
Apr 25 2008, 7:03 AM EDT FYI: even with QPPEnable = 0 (Query Prediction OFF), there are statistics being gathered all the time for every report/workbook/query that is run. Over time, the indexes, particularly: <EUL_OWNER>.EUL4_QS1_I <EUL_OWNER>.EUL4_QS2_I <EUL_OWNER>.EUL4_QS_PK Can grow to thousands of extents, and cause errors (MAXEXTENT) if not monitored. Most clients will NOT require stats over 90 days, and there is a nice bit of SQL to remove old records. It is in the ADMIN edition, and it is called: eulstdel.sql. I just copied it to the DB server, and added a spool file declaration for capturing output. When it runs, it displays the numbers of STATS records split by 10 day increments (that part takes about 40 seconds or so). Then is requests the age parameter to keep. I used 90. That part can take 20 minutes or more, depending on how long the stats have been collected. NOTE: it is worth doing a REBUILD on the 3 indexes above, after getting rid of the old STATS. This gave excellent results on the live system where the indexes are now down to between 100 and 200 extents, from several thousand. If NO STATS are required, then alter the following items in the pref.txt file from 1 to 0: QPPEnable = 0 QPPCreateNewStats = 0 QPPUseCpuTime = 0 QPPCBOEnforced = 0 Then run applypreferences.sh (check errors.txt for no error entries after this). Do you find this valuable? |