Oracle Interview Question and Answer
Why does the same query takes different amount of time to run?
Different Data Volume
When
you generate your initial timings, are you using data volumes that are similar
to the volumes that your query will encounter when it is actually run? If you
test a query on the first of the month and that query is getting all the data
for the current month and performing a bunch of aggregations, you would expect
that the query would get slower and slower over the course of the month because
it had to process more and more data. Or you may have a query that runs quickly
outside of month-end processing because various staging tables that it depends
on only get populated at month end. If you are generating your initial timings
in a test database, you'l very likely get different performance because test
databases frequently have a small subset of the actual production data.
Different System Load
If I
take a query and run it during the middle of the day against my data warehouse,
there is a good chance that the data warehouse is mostly idle and therefore has
lots of resources to give me to process the query. If I'm the only user, my
query may run very quickly. If I try to run exactly the same query during the
middle of the nightly load process, on the other hand, my query will be
competing for resources with a number of other processes. Even if my query has
to do exactly the same amount of work, it can easily take many times more clock
time to run. If you are writing reports that will run at month end and they're
all getting kicked off at roughly the same time, it's entirely possible that
they're all competing with each other for the limited system resources
available and that your system simply isn't sized for the load it needs to
process.
Different
system load can also encompass things like differences in what data is cached
at any point in time. If I'm testing a particular query in prod and I run it a
few times in a row, it is very likely that most of the data I'm interested in
will be cached by Oracle, by the operating system, by the SAN, etc. That can
make a dramatic difference in performance if every read is coming from one of
the caches rather than requiring a disk read. If you run the same query later after
other work has flushed out most of the blocks your query is interested in, you
may end up doing a ton of physical reads rather than being able to use the
nicely warmed up cache. There's not generally much you can do about this sort
of thing-- you may be able to cache more data or arrange for processes that
need similar data to be run at similar times so that the cache is more
efficient ut that is generally expensive and hard to do.
Different Query Plans
Over
time, your query plan may also change because statistics have changed (or not
changed depending on the statistic in question). Normally, that indicates that
Oracle has found a more efficient plan or that your data volumes have changed
and Oracle expects a different plan would be more efficient with the new data
volume. If, however, you are giving Oracle bad statistics (if, for example, you
have tables that get much larger during month-end processing but you gather
statistics when the tables are almost empty), you may induce Oracle to choose a
very bad query plan. Depending on the version of Oracle, there are various ways
to force Oracle to use the same query plan. If you can drill down and figure
out what the problem with statistics is, Oracle probably provides a way to give
the optimizer better statistics.
If you
take a look at AWR/ ASH data (if you have the appropriate licenses) or
Statspace data (if your DBA has installed that), you should be able to figure
out which camp your problems originate in. Are you getting different query
plans for different executions (you may need to capture a query plan from your
initial benchmarks and compare it to the current plan or you may need to
increase your AWR retention to retain query plans for a few months in order to
see this). Are you doing the same number of buffer gets over time but getting
vastly different amounts of I/O waits? Do you see a lot of contention for
resources from other sessions? If so, that probably indicates that the issue is
different load at different times.
Scenario
that might impact query execution plan are:-
·
A
lot of DML would have happened recently. Hence Statistics might be out dated.
·
Number
of records in the underlying base tables would have drastically changed (might
impact the access path).
·
Was
the table index dropped today?
·
Was
there any database patch applied recently?
·
Was
the query exactly the same? Or was it modified due to recently applied
application patch?
·
Any
schedule job is running at the same time?
·
Is
there any sudden increase in number on online users accessing the database?
·
Any
other background process is consuming more memory?
·
Is
the database machine is performing good?
·
Is
the DB parameters like TEMP space, buffer cache, etc is available(or consumed
to 100%)-check these
·
Change
in execution plan
·
Network
issue
43.) What is cursor and what are the types of
cursor in oracle?
A cursor is
pointer to a private SQL area that stores information about processing a
specific SELECT or DML statement.
OR
Pointer to a
memory location where the information about the SELECT statement or DML
statement are got executed.
OR
Memory
location where query results are stored.
Implicit
Cursors
Implicit cursor is managed by PLSQL. PLSQL opens implicit
cursor every time you run SELECT or DML statement. You cannot control cursor,
but you can get information from its attribute.
·
Implicit cursor are automatically opened, managed by
PLSQL
·
So, every time you execute a SELECT statement, oracle
opens it, gives the result and closes the cursor.
·
We do not have control over it.
·
We can get information in the form of attributes
Explicit Cursor
·
Explicit cursor is fully managed by the developer.
·
Where he give name to a cursor, associate a query to a
cursor.
·
He opens the cursor.
·
He typically loop through the cursor to get the
information
·
Then he closes the cursor.
Comments
Post a Comment