On Mon, 19 May 2003 03:45:21 +0200, mar wrote:
>
> Hi group,
> As always, newbie working with 8.1.7 EE on Windows 2000.
>
> I would kindly ask for some advice on the star query below.
> It simply takes too much time to execute. I would expect
> instantaneous reaction from those mighty Oracle Bitmap Indexes
> and that powerful Oracle Star Transformation.
>
> Yes I have STAR_TRANSFORMATION_ENABLED = true.
>
> The query:
- How long exactly does it take? What is the amount of data?
- What is the session waiting for? What is the event that session is
waiting for most of the time spent in waiting? Have you checked
10046 trace? What does statspack say? How about gv$session_wait or
gv$session_event?
- What does your system monitor say? Is the system running mostly in
the user mode, kernel more or is sitting idly and waiting for IO to
to complete? How much CPU is being used by oracle.exe? Have you
checked alert.log and NT alerts?
- Do you have any I/O hot-spots? Are your data files on the fastest disks?
What does your gv$filestat say? How about the OS side? Is anybody
playing Doom or BattleZone on your database servers? How about surfing
the net? What is being done? What are the 10 most CPU intensive
processes? Top 10 most IO intensive processes? Are there any SMB
shared directories? Are people using "network disks"? Are they on the
same drives as the database files?
- What is your performance expectation? How many rows should the
query retrieve, in what amount of time? Given the sheer amount of
data and the fact that a physical I/O takes around 15 ms to complete,
are your expectations realistic? Can your hardware live up to
the expectations? Do you have sufficient number of IO controllers?
Are they capable of delivering data at sufficient rate? Are network
controllers fast enough to deliver the answer in time?
--
Mladen Gogala
Software is like sex, it is better when it is free.
Linus Torvalds
Received on Sun May 18 2003 - 23:06:33 CDT
